MySQL LIMIT & OFFSET с примери

Каква е ключовата дума LIMIT в MySQL?

- ОГРАНИЧАВА Ключовата дума ограничава броя на редовете, върнати в резултат от заявка. Може да се използва с операторите SELECT, UPDATE и DELETE, така че ограничава редовете, които заявката чете, както и редовете, които записът засяга.

Синтаксисът на ключовата дума LIMIT е следният.

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

ТУК

• „ИЗБЕРЕТЕ {име на поле(а) | *} FROM tableName(s)” е SELECT израз съдържащи полетата, които бихме искали да върнем в нашата заявка.
• „[WHERE условие]“ е незадължителен, но когато е предоставен, той определя филтър върху резултатния набор. WHERE клауза се прилага преди LIMIT, така че филтрирането се извършва първо и ограничението се прилага към това, което оцелява.
• „ЛИМИТ N“ е ключовата дума и N е всяко число, започващо от 0. Поставянето на 0 като ограничение не връща никакви записи. Поставянето на число като 5 връща пет записа. Ако таблицата съдържа по-малко записи от N, всички те се връщат и не се генерира грешка.

Синтаксисът е кратък, но причината за съществуването му си струва да се посочи преди примерите.

Защо трябва да използваме ключовата дума LIMIT?

Да предположим, че се развивамеping приложението, което работи върху myflixdb. Системните дизайнери ни помолиха да ограничим броя на записите, показвани на страница, до 20 записа, за да се противодейства на бавното зареждане. Как да внедрим система, която отговаря на такова изискване?

Ключовата дума LIMIT обработва точно тази ситуация. Вместо да изтегля всеки ред член в приложението и да отхвърля повечето от тях, заявката връща 20 записа на страница и базата данни върши работата. От това следват три предимства.

• По-бърз отговор: по-малко данни се четат от диска и по-малко данни преминават през мрежата.
• По-ниско използване на паметта: приложението съдържа една страница с редове, а не цялата таблица.
• Сейфър пише: ОГРАНИЧЕНИЕ на АКТУАЛИЗАЦИЯ или ИЗТРИЙ Изразът ограничава броя на редовете, които една грешка може да докосне.

MySQL Примери за заявки LIMIT

Примерите по-долу се изпълняват спрямо таблицата members на базата данни 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

- ИЗМЕСТВАНЕ Аргументът „value“ най-често се използва заедно с ключовата дума LIMIT. Той указва от кой ред сървърът започва да извлича данни, така че редовете преди тази точка се пропускат.

Да предположим, че искаме ограничен брой членове, започвайки от средата на таблицата. Скриптът по-долу започва от втория ред и ограничава резултата до два записа.

SELECT * FROM `members` LIMIT 1, 2;

Изпълнението му в MySQL Workbench спрямо 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 вече не е възможно.

ЛИМИТ в MySQL срещу TOP и FETCH FIRST

LIMIT не е част от всеки SQL диалект, което е важно, щом заявката трябва да се премества между бази данни. MySQL, PostgreSQL, и SQLite споделят ключовата дума LIMIT. SQL Server използва TOP и Oracle използва стандартната клауза FETCH FIRST. Таблицата по-долу сравнява трите.

Поведението е едно и също във всеки случай: ограничаване на броя на редовете и, по избор, пропускане на определен брой редове първо. Променя се само правописът. Заявка, която трябва да се изпълнява на повече от една програма, следователно трябва да изолира клаузата за ограничаване на редовете, вместо да я разпръсква из кодовата база.

Въпроси и Отговори