MySQL LIMIT & OFFSET mit Beispielen

Was ist das LIMIT-Schlüsselwort in MySQL?

Das LIMIT Das Schlüsselwort beschränkt die Anzahl der in einem Abfrageergebnis zurückgegebenen Zeilen. Es kann mit den Anweisungen SELECT, UPDATE und DELETE verwendet werden und begrenzt somit sowohl die von einer Abfrage gelesenen als auch die von einer Schreiboperation betroffenen Zeilen.

Die Syntax für das Schlüsselwort LIMIT lautet wie folgt.

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

HIER KLICKEN

• „SELECT {fieldname(s) | *} FROM Tabellenname(n)“ ist das SELECT-Anweisung enthält die Felder, die wir in unserer Abfrage zurückgeben möchten.
• „[WHERE-Bedingung]“ ist optional, aber wenn es angegeben wird, legt es einen Filter für das Ergebnis-Set fest. WHERE-Klausel wird vor LIMIT angewendet, daher findet die Filterung zuerst statt und die Begrenzung wird auf das angewendet, was übrig bleibt.
• „LIMIT N“ ist das Schlüsselwort, und N Jede Zahl ab 0 ist zulässig. Die Angabe von 0 als Grenzwert liefert keine Ergebnisse. Eine Zahl wie 5 liefert fünf Datensätze. Enthält die Tabelle weniger als N Datensätze, werden alle zurückgegeben, und es wird kein Fehler ausgelöst.

Die Syntax ist kurz, aber der Grund für ihre Existenz sollte vor den Beispielen erläutert werden.

Warum sollten wir das Schlüsselwort LIMIT verwenden?

Angenommen, wir entwickelnping Die Anwendung läuft auf MyFlixDB. Die Systementwickler haben uns gebeten, die Anzahl der angezeigten Datensätze pro Seite auf 20 zu begrenzen, um lange Ladezeiten zu vermeiden. Wie implementieren wir ein System, das diese Anforderung erfüllt?

Das LIMIT-Schlüsselwort behandelt genau diese Situation. Anstatt alle Datensätze in die Anwendung zu laden und die meisten davon zu verwerfen, gibt die Abfrage 20 Datensätze pro Seite zurück, und die Datenbank erledigt die Arbeit. Daraus ergeben sich drei Vorteile.

• Schnellere Reaktion: Es werden weniger Daten von der Festplatte gelesen und weniger Daten werden über das Netzwerk übertragen.
• Geringerer Speicherverbrauch: Die Anwendung enthält nur eine Seite mit Zeilen, nicht die gesamte Tabelle.
• Safer schreibt: eine Begrenzung für ein Update oder ein LÖSCHEN Diese Anweisung begrenzt, wie viele Zeilen ein Fehler betreffen kann.

MySQL LIMIT-Abfragebeispiele

Die folgenden Beispiele werden auf die Tabelle „members“ der Datenbank „myflixdb“ angewendet. Das erste Beispiel gibt zwei Zeilen zurück, und sonst nichts.

SELECT * FROM members LIMIT 2;

Wie das obige Ergebnis zeigt, wurden nur zwei Mitglieder zurückgebracht.

Abrufen einer Liste von zehn (10) Mitgliedern aus der Datenbank

Angenommen, wir möchten eine Liste der ersten 10 registrierten Mitglieder aus der Myflix-Datenbank. Das folgende Skript fragt diese ab.

SELECT * FROM members LIMIT 10;

Die Ausführung des Skripts führt zu dem unten dargestellten Ergebnis.

Es wurden nur 9 Einträge zurückgegeben, da N in der LIMIT-Klausel größer ist als die Anzahl der Datensätze in der Tabelle. Die explizite Abfrage von 9 Zeilen liefert dasselbe Ergebnis.

SELECT * FROM members LIMIT 9;

💡 Tipp: LIMIT wählt Zeilen in der vom Server zufälligen Reihenfolge aus. Füge ein SORTIEREN NACH Klausel immer dann, wenn die Identität der Reihen von Bedeutung ist, andernfalls ist nicht garantiert, dass „die ersten 10 Mitglieder“ zweimal dieselben neun Personen bedeuten.

Die Begrenzung der Zeilenanzahl ist die erste Hälfte der Funktion. Die Auswahl des Startpunkts des Fensters ist die zweite.

Verwendung des OFFSET-Werts in der LIMIT-Abfrage

Das OFFSET Der Wert wird meist zusammen mit dem Schlüsselwort LIMIT verwendet. Er gibt an, ab welcher Zeile der Server Daten abruft, sodass Zeilen vor dieser Zeile übersprungen werden.

Angenommen, wir möchten eine begrenzte Anzahl von Elementen ab der Mitte der Tabelle auswählen. Das folgende Skript beginnt in der zweiten Zeile und beschränkt das Ergebnis auf zwei Datensätze.

SELECT * FROM `members` LIMIT 1, 2;

Die Ausführung in MySQL Werkbank Der Vergleich mit myflixdb liefert folgendes Ergebnis.

Beachten Sie, dass hier VERSATZ = 1Daher ist Zeile 2 die erste zurückgegebene Zeile, und LIMIT = 2Daher werden nur 2 Datensätze zurückgegeben.

Bei der Zwei-Argument-Form wird zuerst der Offset und dann die Zeilenanzahl angegeben, was leicht versehentlich vertauscht werden kann. MySQL Akzeptiert auch eine explizite Form, die die Mehrdeutigkeit beseitigt, und diese sollte in neuem Code bevorzugt werden.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Beide Anweisungen liefern dieselben zwei Zeilen. Da der Offset bekannt ist, ergibt sich das Paginierungsmuster, auf dem jede Listenansicht basiert, direkt daraus.

Wie man Abfrageergebnisse mit LIMIT und OFFSET paginiert

Die Paginierung unterteilt große Ergebnismengen in nummerierte Seiten. Die Kombination aus LIMIT und OFFSET ist der Mechanismus, der dies bewirkt. Zwei Werte bestimmen jede Seitenanforderung: die Seitengröße, also die Anzahl der Datensätze pro Bildschirm, und die vom Benutzer angeforderte Seitenzahl.

Der Offset wird daraus mit einer einzigen Formel abgeleitet.

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

Seite 2 behält die gleiche Begrenzung bei und verschiebt den Versatz um eine Seitengröße nach vorne.

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

Drei Regeln sorgen dafür, dass eine paginierte Auflistung korrekt und schnell ist.

1. Immer sortieren: Eine paginierte Abfrage ohne ORDER BY kann denselben Datensatz auf zwei verschiedenen Seiten anzeigen und einen anderen vollständig ausblenden, da der Server die Reihenfolge der Zeilen zwischen den Aufrufen ändern kann.
2. Nach einer eindeutigen Spalte sortieren: Bei Gleichständen in der Sortierspalte ist die Reihenfolge der betroffenen Zeilen undefiniert. Durch Sortieren nach dem Primärschlüssel oder dessen Verwendung als Kriterium bei Gleichständen lässt sich dieses Problem beheben.
3. Schauen Sie sich die Tiefenseiten an: OFFSET 100000 Kräfte MySQL Hunderttausend Zeilen zu lesen und zu verwerfen, bevor die nächsten zwanzig zurückgegeben werden. Die Antwortzeit steigt mit der Seitenzahl.

Bei sehr tiefer Paginierung umgeht die Schlüsselsatz-Paginierung den Offset vollständig. Anstatt die zu überspringenden Zeilen zu zählen, merkt sich die Abfrage den letzten Schlüssel der vorherigen Seite und fragt die darauf folgenden Zeilen ab.

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

Diese Form bleibt in jeder Tiefe schnell, da der Index direkt zum Startschlüssel springt, anstatt die Zeilen davor zu durchlaufen. Der Nachteil ist, dass die Seiten der Reihe nach durchlaufen werden müssen.ping Ein direkter Aufruf von Seite 500 ist nicht mehr möglich.

LIMIT in MySQL vs TOP und FETCH FIRST

LIMIT ist nicht Bestandteil jedes SQL-Dialekts, was sich als wichtig erweist, sobald eine Abfrage zwischen verschiedenen Datenbank-Engines übertragen werden muss. MySQL, PostgreSQL und SQLite Das LIMIT-Schlüsselwort wird geteilt. SQL Server verwendet TOP, und Oracle verwendet die Standard-FETCH-FIRST-Klausel. Die folgende Tabelle vergleicht die drei Varianten.

Das Verhalten ist in jedem Fall gleich: Die Anzahl der Zeilen wird begrenzt und optional können zunächst einige Zeilen übersprungen werden. Lediglich die Schreibweise ändert sich. Eine Abfrage, die auf mehreren Engines ausgeführt werden soll, sollte daher die Zeilenbegrenzungsklausel isolieren, anstatt sie im gesamten Code zu verteilen.

Häufig gestellte Fragen