MySQL LIMIT & OFFSET esimerkein

Mikä on LIMIT-avainsana kohdassa MySQL?

RAJOITA avainsana rajoittaa kyselytuloksessa palautettavien rivien määrää. Sitä voidaan käyttää SELECT-, UPDATE- ja DELETE-lausekkeiden kanssa, joten se rajaa kyselyn lukemat rivit sekä rivit, joihin kirjoitus vaikuttaa.

LIMIT-avainsanan syntaksi on seuraava.

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

TÄÄLTÄ

• "VALITSE {kentännimi(t) | *} FROM tableName(s)” on SELECT-lause jotka sisältävät kentät, jotka haluamme palauttaa kyselyssämme.
• "[WHERE ehto]" on valinnainen, mutta kun se annetaan, se määrittää suodattimen tulosjoukolle. WHERE-lauseke käytetään ennen LIMIT-arvoa, joten suodatus tapahtuu ensin ja rajaus kohdistuu vain siihen, mikä säilyy.
• ”RAJA N” on avainsana, ja N on mikä tahansa luku, joka alkaa nollasta. Jos rajaksi asetetaan 0, ei tietueita ole lainkaan. Jos taulukossa on vähemmän tietueita kuin N, ne kaikki palautetaan eikä virhettä synny.

Syntaksi on lyhyt, mutta syy sen olemassaoloon on syytä mainita ennen esimerkkejä.

Miksi meidän pitäisi käyttää LIMIT-avainsanaa?

Oletetaan, että olemme kehittymässäping myflixdb-tietokannan päällä toimiva sovellus. Järjestelmän suunnittelijat ovat pyytäneet meitä rajoittamaan sivulla näytettävien tietueiden määrän 20 tietueeseen hidasten latausaikojen estämiseksi. Miten toteutamme järjestelmän, joka täyttää tämän vaatimuksen?

LIMIT-avainsana käsittelee juuri tätä tilannetta. Sen sijaan, että jokainen jäsenrivi vedettäisiin sovellukseen ja useimmat hylättäisiin, kysely palauttaa 20 tietuetta sivua kohden ja tietokanta tekee työn. Tästä seuraa kolme etua.

• Nopeampi vastaus: Levyltä luetaan vähemmän dataa ja verkossa kulkee vähemmän dataa.
• Pienempi muistin käyttö: Sovellus säilyttää yhden sivun rivejä, ei koko taulukkoa.
• Turvailija kirjoittaa: RAJA PÄIVITYKSELLE tai POISTA lauseke rajoittaa kuinka monta riviä virhe voi koskea.

MySQL LIMIT-kyselyesimerkkejä

Alla olevat esimerkit suorittavat myflixdb-tietokannan members-taulukkoa. Ensimmäinen palauttaa kaksi riviä eikä mitään muuta.

SELECT * FROM members LIMIT 2;

Kuten yllä olevasta tuloksesta käy ilmi, vain kaksi jäsentä on palautettu.

Kymmenen (10) jäsenen listan hakeminen tietokannasta

Oletetaan, että haluamme listan kymmenestä ensimmäisestä rekisteröityneestä jäsenestä Myflixin tietokannasta. Alla oleva skripti pyytää heitä.

SELECT * FROM members LIMIT 10;

Skriptin suorittaminen antaa alla olevan tuloksen.

Vain 9 jäsentä on palautettu, koska LIMIT-lausekkeen N on suurempi kuin taulukon tietueiden lukumäärä. 9 rivin pyytäminen tuottaa eksplisiittisesti saman tulosjoukon.

SELECT * FROM members LIMIT 9;

💡 Vinkki: LIMIT valitsee rivit palvelimen tuottamasta järjestyksestä. Lisää TILAUS lauseketta aina, kun rivien identiteetillä on merkitystä, muuten ei ole taattua, että "ensimmäiset 10 jäsentä" tarkoittavat samoja yhdeksää henkilöä kahdesti.

Rivien määrän rajoittaminen on ominaisuuden ensimmäinen puolisko. Ikkunan aloituskohdan valitseminen on toinen puolisko.

OFFSET-arvon käyttäminen LIMIT-kyselyssä

OFFSET arvoa käytetään useimmiten yhdessä LIMIT-avainsanan kanssa. Se määrittää, miltä riviltä palvelin alkaa hakea tietoja, joten sitä edeltävät rivit ohitetaan.

Oletetaan, että haluamme rajoitetun määrän jäseniä alkaen taulukon keskeltä. Alla oleva skripti alkaa toiselta riviltä ja rajoittaa tuloksen kahteen tietueeseen.

SELECT * FROM `members` LIMIT 1, 2;

Suorittamalla sen sisään MySQL Työpöytä myflixdb:tä vastaan ​​antaa seuraavan tuloksen.

Huomaa, että tässä SIIRTYMÄ = 1, joten rivi #2 on ensimmäinen palautettu rivi, ja RAJA = 2, joten vain 2 tietuetta tulee takaisin.

Kaksiargumenttisessa muodossa offset kirjoitetaan ensin ja rivien lukumäärä toiseksi, mikä on helppo peruuttaa vahingossa. MySQL hyväksyy myös eksplisiittisen muodon, joka poistaa epäselvyyden, ja sitä kannattaa käyttää uudessa koodissa.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Molemmat lauseet palauttavat samat kaksi riviä. Kun offset ymmärretään, sivutuskuvio, johon jokainen listausnäyttö perustuu, putoaa siitä suoraan pois.

Kyselytulosten sivuttaminen LIMIT- ja OFFSET-operaattorien avulla

Sivutus jakaa suuren tulosjoukon numeroituihin sivuihin, ja LIMIT yhdessä OFFSETin kanssa on mekanismi, joka tekee tämän. Jokaista sivupyyntöä ohjaa kaksi arvoa: sivun koko, joka kertoo, kuinka monta tietuetta näkyy yhdellä näytöllä, ja käyttäjän pyytämä sivunumero.

Offset johdetaan niistä yhdellä kaavalla.

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

Sivulla 2 rajoitus pysyy samana ja siirtymää siirretään yhden sivukoon verran eteenpäin.

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

Kolme sääntöä pitää sivutetun listauksen oikeina ja nopeasti.

1. Lajittele aina: Sivutettu kysely ilman ORDER BY -metodia voi näyttää saman tietueen kahdella eri sivulla ja piilottaa toisen kokonaan, koska palvelin voi muuttaa rivien järjestystä kutsujen välillä.
2. Lajittele yksilöllisen sarakkeen mukaan: Lajittelusarakkeen tasapisteet jättävät tasapisteiden rivien järjestyksen määrittelemättömäksi. Lajittelu perusavaimen mukaan tai sen lisääminen tasapisteiden ratkaisemiseksi poistaa ongelman.
3. Katso syväsivuja: SIIRTO 100 000 voimaa MySQL lukea satatuhatta riviä ja heittää ne pois ennen seuraavien kahdenkymmenen palauttamista. Vastausaika kasvaa sivunumeron mukana.

Hyvin syvää sivutusta varten avainjoukkojen sivutus välttää siirtymän kokonaan. Ohitettavien rivien laskemisen sijaan kysely muistaa edellisen sivun viimeisen avaimen ja kysyy sen jälkeisiä rivejä.

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

Tämä muoto pysyy nopeana millä tahansa syvyydellä, koska indeksi hyppää suoraan aloitusnäppäimeen sen sijaan, että kävelisi edeltävien rivien läpi. Kompromissi on, että sivut on käveltävä peräkkäin, joten hyppääping suoraan sivulle 500 ei ole enää mahdollista.

LIMIT sisään MySQL vs. TOP ja FETCH FIRST

LIMIT ei ole osa jokaista SQL-dialektia, millä on merkitystä heti, kun kyselyn on siirryttävä tietokantamoottorien välillä. MySQL, PostgreSQLja SQLite jaa LIMIT-avainsana. SQL Server käyttää TOP-avainsanaa ja Oracle käyttää standardia FETCH FIRST -lauseketta. Alla olevassa taulukossa vertaillaan näitä kolmea.

Toiminta on sama jokaisessa tapauksessa: rivien määrä rajoitetaan ja halutessaan ensin ohitetaan tietty määrä rivejä. Vain kirjoitusasu muuttuu. Kyselyn, jonka on suoritettava useammalla kuin yhdellä hakukoneella, tulisi siksi eristää rivirajoituslauseke sen sijaan, että se hajaantuisi koodikantaan.

UKK