MySQL LIMIT & OFFSET με Παραδείγματα

Ποια είναι η λέξη-κλειδί LIMIT στο MySQL?

The LIMIT Η λέξη-κλειδί περιορίζει τον αριθμό των γραμμών που επιστρέφονται σε ένα αποτέλεσμα ερωτήματος. Μπορεί να χρησιμοποιηθεί με τις εντολές SELECT, UPDATE και DELETE, επομένως περιορίζει τις γραμμές που διαβάζει ένα ερώτημα καθώς και τις γραμμές που επηρεάζει μια εγγραφή.

Η σύνταξη για τη λέξη-κλειδί LIMIT έχει ως εξής.

SELECT {fieldname(s) | *} FROM tableName(s) [WHERE condition] LIMIT N;

ΕΔΩ

• «ΕΠΙΛΟΓΗ {όνομα(α) πεδίου | *} FROM Όνομα(τα) πίνακα" είναι το Δήλωση SELECT που περιέχει τα πεδία που θα θέλαμε να επιστρέψουμε στο ερώτημά μας.
• "[ΠΟΥ συνθήκη]" είναι προαιρετικό, αλλά όταν παρέχεται καθορίζει ένα φίλτρο στο σύνολο αποτελεσμάτων. Το ΟΤΙ ρήτρα εφαρμόζεται πριν από το LIMIT, επομένως το φιλτράρισμα γίνεται πρώτα και το όριο εφαρμόζεται σε ό,τι επιβιώνει.
• «ΟΡΙΟ Ν» είναι η λέξη-κλειδί, και N είναι οποιοσδήποτε αριθμός που ξεκινά από το 0. Αν θέσουμε το 0 ως όριο, δεν επιστρέφουμε καμία εγγραφή. Αν θέσουμε έναν αριθμό όπως το 5, επιστρέφουμε πέντε εγγραφές. Εάν ο πίνακας περιέχει λιγότερες εγγραφές από N, επιστρέφονται όλες και δεν προκύπτει σφάλμα.

Η σύνταξη είναι σύντομη, αλλά ο λόγος ύπαρξής της αξίζει να αναφερθεί πριν από τα παραδείγματα.

Γιατί πρέπει να χρησιμοποιούμε τη λέξη-κλειδί LIMIT;

Ας υποθέσουμε ότι είμαστε σε ανάπτυξηping η εφαρμογή που εκτελείται πάνω από το myflixdb. Οι σχεδιαστές του συστήματος μας ζήτησαν να περιορίσουμε τον αριθμό των εγγραφών που εμφανίζονται σε μια σελίδα σε 20 εγγραφές, προκειμένου να αντιμετωπιστούν οι αργοί χρόνοι φόρτωσης. Πώς υλοποιούμε ένα σύστημα που πληροί μια τέτοια απαίτηση;

Η λέξη-κλειδί LIMIT χειρίζεται ακριβώς αυτήν την περίπτωση. Αντί να τραβάει κάθε γραμμή μέλους στην εφαρμογή και να απορρίπτει τις περισσότερες από αυτές, το ερώτημα επιστρέφει 20 εγγραφές ανά σελίδα και η βάση δεδομένων κάνει τη δουλειά. Τρία οφέλη προκύπτουν από αυτό.

• Ταχύτερη απόκριση: λιγότερα δεδομένα διαβάζονται από τον δίσκο και λιγότερα δεδομένα διασχίζουν το δίκτυο.
• Χαμηλότερη χρήση μνήμης: Η εφαρμογή περιέχει μία σελίδα γραμμών, όχι ολόκληρο τον πίνακα.
• Ο Safer γράφει: ένα ΟΡΙΟ σε μια ΕΝΗΜΕΡΩΣΗ ή ένα ΔΙΑΓΡΑΦΗ Η εντολή περιορίζει τον αριθμό των γραμμών που μπορεί να αγγίξει ένα λάθος.

MySQL Παραδείγματα ερωτημάτων LIMIT

Τα παρακάτω παραδείγματα εκτελούνται στον πίνακα μελών της βάσης δεδομένων myflixdb. Το πρώτο επιστρέφει δύο γραμμές και τίποτα περισσότερο.

SELECT * FROM members LIMIT 2;

Όπως φαίνεται από το παραπάνω αποτέλεσμα, έχουν επιστραφεί μόνο δύο μέλη.

Λήψη λίστας δέκα (10) μελών από τη βάση δεδομένων

Ας υποθέσουμε ότι θέλουμε μια λίστα με τα πρώτα 10 εγγεγραμμένα μέλη από τη βάση δεδομένων Myflix. Το παρακάτω σενάριο τα ζητάει.

SELECT * FROM members LIMIT 10;

Η εκτέλεση του σεναρίου δίνει το αποτέλεσμα που φαίνεται παρακάτω.

Μόνο 9 μέλη έχουν επιστραφεί, επειδή το N στον όρο LIMIT είναι μεγαλύτερο από τον αριθμό των εγγραφών στον πίνακα. Η ζήτηση 9 γραμμών παράγει ρητά το ίδιο σύνολο αποτελεσμάτων.

SELECT * FROM members LIMIT 9;

💡 Συμβουλές: Το LIMIT επιλέγει γραμμές από οποιαδήποτε σειρά τυχαίνει να παράγει ο διακομιστής. Προσθέστε ένα ΤΑΞΙΝΟΜΗΣΗ ΚΑΤΑ ρήτρα όποτε η ταυτότητα των γραμμών έχει σημασία, διαφορετικά «τα πρώτα 10 μέλη» δεν είναι εγγυημένο ότι σημαίνει τα ίδια εννέα άτομα δύο φορές.

Ο περιορισμός του αριθμού των γραμμών είναι το πρώτο μισό της λειτουργίας. Η επιλογή του σημείου έναρξης του παραθύρου είναι το δεύτερο μισό.

Χρήση της τιμής OFFSET στο ερώτημα LIMIT

The OFFSET Η τιμή χρησιμοποιείται συχνότερα μαζί με τη λέξη-κλειδί LIMIT. Καθορίζει από ποια γραμμή ξεκινά η ανάκτηση δεδομένων από τον διακομιστή, επομένως οι γραμμές πριν από αυτό το σημείο παραλείπονται.

Ας υποθέσουμε ότι θέλουμε έναν περιορισμένο αριθμό μελών ξεκινώντας από τη μέση του πίνακα. Το παρακάτω σενάριο ξεκινά από τη δεύτερη γραμμή και περιορίζει το αποτέλεσμα σε δύο εγγραφές.

SELECT * FROM `members` LIMIT 1, 2;

Εκτελώντας το σε MySQL Πάγκος εργασίας έναντι του myflixdb δίνει το ακόλουθο αποτέλεσμα.

Σημειώστε ότι εδώ Μετατόπιση = 1, επομένως η σειρά #2 είναι η πρώτη σειρά που επιστρέφεται, και ΟΡΙΟ = 2, επομένως επιστρέφουν μόνο 2 εγγραφές.

Στη μορφή δύο ορισμάτων, η μετατόπιση γράφεται πρώτη και ο αριθμός γραμμών δεύτερος, κάτι που είναι εύκολο να αντιστραφεί τυχαία. MySQL δέχεται επίσης μια ρητή μορφή που εξαλείφει την ασάφεια και είναι αυτή που προτιμάται στον νέο κώδικα.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Και οι δύο εντολές επιστρέφουν τις ίδιες δύο γραμμές. Με την κατανοητή μετατόπιση, το μοτίβο σελιδοποίησης στο οποίο βασίζεται κάθε οθόνη καταχώρισης εξαλείφεται άμεσα από αυτήν.

Πώς να σελιδοποιήσετε τα αποτελέσματα ερωτήματος με LIMIT και OFFSET

Η σελιδοποίηση διαιρεί ένα μεγάλο σύνολο αποτελεσμάτων σε αριθμημένες σελίδες και ο μηχανισμός LIMIT μαζί με το OFFSET είναι ο μηχανισμός που το κάνει. Δύο τιμές καθορίζουν κάθε αίτημα σελίδας: το μέγεθος σελίδας, το οποίο είναι ο αριθμός των εγγραφών που εμφανίζονται σε μία οθόνη, και ο αριθμός σελίδας που ζητά ο χρήστης.

Η αντιστάθμιση προκύπτει από αυτά με έναν μόνο τύπο.

-- OFFSET = page_size * (page_number - 1)
SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 0;   -- page 1

Η Σελίδα 2 διατηρεί το ίδιο όριο και μετακινεί την μετατόπιση προς τα εμπρός κατά ένα μέγεθος σελίδας.

SELECT membership_number, full_names
FROM members
ORDER BY membership_number ASC
LIMIT 20 OFFSET 20;  -- page 2

Τρεις κανόνες διατηρούν μια σελιδοποιημένη καταχώριση σωστή και γρήγορη.

1. Ταξινόμηση πάντα: Ένα σελιδοποιημένο ερώτημα χωρίς ORDER BY μπορεί να εμφανίσει την ίδια εγγραφή σε δύο διαφορετικές σελίδες και να αποκρύψει εντελώς μια άλλη, επειδή ο διακομιστής είναι ελεύθερος να αλλάξει τη σειρά των γραμμών μεταξύ των κλήσεων.
2. Ταξινόμηση σε μοναδική στήλη: Οι συνδέσεις στη στήλη ταξινόμησης αφήνουν τη σειρά των συνδεδεμένων γραμμών απροσδιόριστη. Η ταξινόμηση με βάση το πρωτεύον κλειδί ή η προσθήκη του ως παράγοντα διακοπής σύνδεσης, εξαλείφει το πρόβλημα.
3. Παρακολουθήστε σελίδες με βάθος: Μετατόπιση 100000 δυνάμεων MySQL να διαβάσει εκατό χιλιάδες γραμμές και να τις απορρίψει πριν επιστρέψει τις επόμενες είκοσι. Ο χρόνος απόκρισης αυξάνεται με τον αριθμό της σελίδας.

Για πολύ βαθιά σελιδοποίηση, η σελιδοποίηση με βάση το σύνολο κλειδιών αποφεύγει εντελώς την μετατόπιση. Αντί να μετράει τις γραμμές που θα παραλειφθούν, το ερώτημα θυμάται το τελευταίο κλειδί από την προηγούμενη σελίδα και ζητά τις γραμμές που ακολουθούν.

SELECT membership_number, full_names
FROM members
WHERE membership_number > 20      -- last id from the previous page
ORDER BY membership_number ASC
LIMIT 20;

Αυτή η φόρμα παραμένει γρήγορη σε οποιοδήποτε βάθος, επειδή το ευρετήριο μεταβαίνει κατευθείαν στο αρχικό κλειδί αντί να περπατάει στις γραμμές που βρίσκονται μπροστά του. Το συμβιβασμό είναι ότι οι σελίδες πρέπει να περπατούνται σε σειρά, οπότε πηδάτεping Η απευθείας μετάβαση στη σελίδα 500 δεν είναι πλέον δυνατή.

LIMIT σε MySQL έναντι TOP και FETCH FIRST

Το LIMIT δεν αποτελεί μέρος κάθε διαλέκτου SQL, κάτι που έχει σημασία όταν ένα ερώτημα πρέπει να μετακινηθεί μεταξύ μηχανών βάσεων δεδομένων. MySQL, PostgreSQLκαι SQLite κοινοποιήστε τη λέξη-κλειδί LIMIT. Ο SQL Server χρησιμοποιεί TOP και Oracle χρησιμοποιεί την τυπική ρήτρα FETCH FIRST. Ο παρακάτω πίνακας συγκρίνει τις τρεις.

Η συμπεριφορά είναι η ίδια σε κάθε περίπτωση: περιορισμός του αριθμού των γραμμών και, προαιρετικά, παράλειψη ενός αριθμού γραμμών πρώτα. Μόνο η ορθογραφία αλλάζει. Ένα ερώτημα που πρέπει να εκτελεστεί σε περισσότερες από μία μηχανές θα πρέπει επομένως να απομονώνει τον όρο περιορισμού γραμμών αντί να τον διασκορπίζει σε όλη τη βάση κώδικα.

Συχνές Ερωτήσεις