MySQL LIMIT & OFFSET med exempel

Vad är LIMIT-nyckelordet i MySQL?

Ocuco-landskapet BEGRÄNSA Nyckelordet begränsar antalet rader som returneras i ett frågeresultat. Det kan användas med SELECT-, UPDATE- och DELETE-satserna, så det begränsar antalet rader som en fråga läser såväl som antalet rader som en skrivning påverkar.

Syntaxen för LIMIT-nyckelordet är följande.

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

HÄR

• "VÄLJ {fältnamn(n) | *} FRÅN tabellnamn" är SELECT-sats som innehåller de fält som vi skulle vilja returnera i vår förfrågan.
• "[WHERE skick]" är valfritt, men när det anges anger det ett filter på resultatmängden. VAR klausul tillämpas före LIMIT, så filtrering sker först och taket tillämpas på det som överlever.
• "GRÄNS N" är nyckelordet, och N är vilket tal som helst som börjar från 0. Om 0 anges som gränsvärde returneras inga poster alls. Om ett tal som 5 anges returneras fem poster. Om tabellen innehåller färre poster än N returneras alla och inget fel uppstår.

Syntaxen är kort, men anledningen till att den existerar är värd att ange före exemplen.

Varför ska vi använda nyckelordet LIMIT?

Anta att vi utvecklarping applikationen som körs ovanpå myflixdb. Systemdesignerna har bett oss att begränsa antalet poster som visas på en sida till 20 poster för att motverka långsamma laddningstider. Hur implementerar vi ett system som uppfyller ett sådant krav?

Nyckelordet LIMIT hanterar just denna situation. Istället för att hämta varje medlemsrad in i applikationen och ignorera de flesta av dem, returnerar frågan 20 poster per sida och databasen gör jobbet. Tre fördelar följer av det.

• Snabbare svar: mindre data läses från disken och mindre data passerar nätverket.
• Lägre minnesanvändning: Applikationen innehåller en sida med rader, inte hela tabellen.
• Säkrare skriver: en GRÄNS för en UPPDATERING eller en RADERA ett kommando begränsar hur många rader ett misstag kan beröra.

MySQL LIMIT-frågeexempel

Exemplen nedan körs mot medlemstabellen i myflixdb-databasen. Det första returnerar två rader och inget mer.

SELECT * FROM members LIMIT 2;

Som resultatet ovan visar har endast två medlemmar återvänt.

Hämta en lista med tio (10) medlemmar från databasen

Anta att vi vill ha en lista över de första 10 registrerade medlemmarna från Myflix-databasen. Skriptet nedan frågar efter dem.

SELECT * FROM members LIMIT 10;

Att köra skriptet ger resultatet som visas nedan.

Endast 9 medlemmar har returnerats, eftersom N i LIMIT-klausulen är större än antalet poster i tabellen. Att begära 9 rader ger explicit samma resultatuppsättning.

SELECT * FROM members LIMIT 9;

💡 Tips: LIMIT väljer rader från den ordning servern råkar producera. Lägg till en SORTERA EFTER klausul närhelst radernas identitet har betydelse, annars är det inte garanterat att ”de första 10 medlemmarna” betyder samma nio personer två gånger.

Att begränsa radantalet är den första halvan av funktionen. Att välja var fönstret börjar är den andra.

Använda OFFSET-värdet i LIMIT-frågan

Ocuco-landskapet OFFSET värde används oftast tillsammans med nyckelordet LIMIT. Det anger vilken rad servern börjar hämta data från, så rader före den punkten hoppas över.

Anta att vi vill ha ett begränsat antal medlemmar med början från mitten av tabellen. Skriptet nedan börjar på andra raden och begränsar resultatet till två poster.

SELECT * FROM `members` LIMIT 1, 2;

Utför det i MySQL Arbetsbänk mot myflixdb ger följande resultat.

Observera att här FÖRSKYTTNING = 1, alltså är rad #2 den första raden som returneras, och GRÄNS = 2, därför kommer bara 2 poster tillbaka.

I formen med två argument skrivs offseten först och radantalet sedan, vilket är lätt att reversera av misstag. MySQL accepterar också en explicit form som tar bort tvetydigheten, och det är den att föredra i ny kod.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Båda satserna returnerar samma två rader. När offseten är förstådd faller pagineringsmönstret som varje listskärm förlitar sig på direkt ur den.

Hur man paginerar frågeresultat med LIMIT och OFFSET

Paginering delar upp en stor resultatmängd i numrerade sidor, och LIMIT tillsammans med OFFSET är den mekanism som gör det. Två värden styr varje sidförfrågan: sidstorleken, vilket är hur många poster som visas på en skärm, och sidnumret som begärs av användaren.

Offseten härleds från dem med en enda formel.

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

Sida 2 behåller samma gräns och flyttar förskjutningen framåt med en sidstorlek.

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

Tre regler håller en paginerad lista korrekt och snabb.

1. Sortera alltid: En paginerad fråga utan ORDER BY kan visa samma post på två olika sidor och dölja en annan helt, eftersom servern kan ändra radordningen mellan anrop.
2. Sortera på en unik kolumn: Obligationer i sorteringskolumnen lämnar ordningen på de bundna raderna odefinierad. Att sortera på primärnyckeln, eller lägga till den som en oavgjord nyckel, eliminerar problemet.
3. Titta på djupa sidor: FÖRSKJUTNING 100000 krafter MySQL att läsa hundratusen rader och kasta bort dem innan man återkommer till nästa tjugo. Svarstiden ökar med sidnumret.

För mycket djup paginering undviker nyckeluppsättningspaginering förskjutningen helt. Istället för att räkna rader att hoppa över, kommer frågan ihåg den sista nyckeln från föregående sida och frågar efter raderna efter den.

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

Denna form förblir snabb oavsett djup, eftersom indexet hoppar direkt till starttangenten istället för att gå igenom raderna framför. Avvägningen är att sidorna måste gås igenom i ordning, så hoppaping direkt till sidan 500 är inte längre möjligt.

LIMIT i MySQL vs TOP och HÄMTA FÖRST

LIMIT finns inte i alla SQL-dialekter, vilket är viktigt så snart en fråga måste flyttas mellan databasmotorer. MySQL, PostgreSQLoch SQLite dela nyckelordet LIMIT. SQL Server använder TOP, och Oracle använder standardklausulen FETCH FIRST. Tabellen nedan jämför de tre.

Beteendet är detsamma i båda fallen: begränsa antalet rader och hoppa eventuellt över ett antal rader först. Endast stavningen ändras. En fråga som måste köras på mer än en motor bör därför isolera radbegränsande klausul snarare än att sprida den genom kodbasen.

Vanliga frågor