MySQL LIMIT I PRZESUNIĘCIE z przykładami

Co oznacza słowo kluczowe LIMIT w MySQL?

LIMIT Słowo kluczowe ogranicza liczbę wierszy zwracanych w wyniku zapytania. Można go używać z poleceniami SELECT, UPDATE i DELETE, ograniczając liczbę wierszy odczytywanych przez zapytanie, a także wierszy, na które ma wpływ zapis.

Składnia słowa kluczowego LIMIT jest następująca.

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

TUTAJ

• „WYBIERZ {nazwy pól | *} Z nazw tabel” jest SELECT oświadczenie zawierający pola, które chcielibyśmy zwrócić w naszym zapytaniu.
• „[Warunek GDZIE]” jest opcjonalny, ale jeśli zostanie podany, określa filtr na zestawie wyników. klauzula GDZIE jest stosowany przed LIMIT, więc filtrowanie odbywa się najpierw, a limit jest stosowany do tego, co przetrwa.
• „LIMIT N” jest słowem kluczowym i N to dowolna liczba zaczynająca się od 0. Ustawienie 0 jako granicy nie zwraca żadnych rekordów. Ustawienie liczby takiej jak 5 zwraca pięć rekordów. Jeśli tabela zawiera mniej rekordów niż N, wszystkie są zwracane i nie jest zgłaszany żaden błąd.

Składnia jest krótka, ale przed przykładami warto podać powód, dla którego została stworzona.

Dlaczego powinniśmy używać słowa kluczowego LIMIT?

Załóżmy, że jesteśmy rozwinięciping Aplikacja działająca na bazie danych myflixdb. Projektanci systemu poprosili nas o ograniczenie liczby rekordów wyświetlanych na stronie do 20, aby przeciwdziałać powolnemu ładowaniu. Jak wdrożyć system, który spełnia to wymaganie?

Słowo kluczowe LIMIT obsługuje dokładnie tę sytuację. Zamiast pobierać każdy wiersz elementu do aplikacji i odrzucać większość z nich, zapytanie zwraca 20 rekordów na stronę, a baza danych wykonuje całą pracę. Wynikają z tego trzy korzyści.

• Szybsza odpowiedź: mniej danych jest odczytywanych z dysku i mniej danych przesyłanych jest przez sieć.
• Mniejsze wykorzystanie pamięci: aplikacja przechowuje jedną stronę wierszy, a nie całą tabelę.
• Safer pisze: LIMIT na AKTUALIZACJĘ lub DELETE instrukcja ogranicza liczbę wierszy, których może dotyczyć błąd.

MySQL Przykłady zapytań LIMIT

Poniższe przykłady są uruchamiane na tabeli members w bazie danych myflixdb. Pierwszy zwraca dwa wiersze i nic więcej.

SELECT * FROM members LIMIT 2;

Jak pokazuje powyższy wynik, tylko dwóch członków zostało wybranych.

Pobieranie listy dziesięciu (10) członków z bazy danych

Załóżmy, że chcemy uzyskać listę pierwszych 10 zarejestrowanych użytkowników z bazy danych Myflix. Poniższy skrypt prosi o ich podanie.

SELECT * FROM members LIMIT 10;

Wykonanie skryptu daje wynik pokazany poniżej.

Zwrócono tylko 9 elementów, ponieważ N w klauzuli LIMIT jest większe niż liczba rekordów w tabeli. Jawne zapytanie o 9 wierszy daje ten sam zestaw wyników.

SELECT * FROM members LIMIT 9;

💡 Wskazówka: LIMIT wybiera wiersze z dowolnej kolejności, jaką serwer wygeneruje. Dodaj ZAMÓW PRZEZ klauzulę tę należy stosować zawsze, gdy tożsamość wierszy ma znaczenie, w przeciwnym razie nie ma gwarancji, że „pierwszych 10 członków” będzie oznaczać dwa razy te same dziewięć osób.

Ograniczenie liczby wierszy to pierwsza połowa funkcji. Wybór miejsca rozpoczęcia okna to druga.

Korzystanie z wartości OFFSET w zapytaniu LIMIT

OFFSET Wartość jest najczęściej używana razem ze słowem kluczowym LIMIT. Określa ono, od którego wiersza serwer rozpoczyna pobieranie danych, więc wiersze przed tym punktem są pomijane.

Załóżmy, że chcemy uzyskać ograniczoną liczbę członków, zaczynając od środka tabeli. Poniższy skrypt zaczyna od drugiego wiersza i ogranicza wynik do dwóch rekordów.

SELECT * FROM `members` LIMIT 1, 2;

Wykonanie tego w MySQL Workbench w stosunku do myflixdb daje następujący wynik.

Zauważ, że tutaj PRZESUNIĘCIE = 1stąd wiersz nr 2 jest pierwszym zwróconym wierszem, a LIMIT = 2, dlatego powróciły tylko 2 rekordy.

W przypadku formy dwuargumentowej najpierw zapisywane jest przesunięcie, a dopiero potem liczba wierszy, co można łatwo przez przypadek odwrócić. MySQL akceptuje również formę jawną, która usuwa niejednoznaczności, i jest tą, którą należy preferować w nowym kodzie.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Oba polecenia zwracają te same dwa wiersze. Po zrozumieniu przesunięcia, wzorzec paginacji, na którym opiera się każdy ekran listingu, zostaje z niego bezpośrednio wyparty.

Jak podzielić wyniki zapytania na strony za pomocą LIMIT i OFFSET

Paginacja dzieli duży zestaw wyników na ponumerowane strony, a LIMIT wraz z OFFSET to mechanizm, który to umożliwia. Każde żądanie strony zależy od dwóch wartości: rozmiaru strony, czyli liczby rekordów wyświetlanych na jednym ekranie, oraz numeru strony żądanego przez użytkownika.

Przesunięcie jest wyliczane na podstawie jednego wzoru.

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

Strona 2 zachowuje ten sam limit, ale przesuwa przesunięcie o jeden rozmiar strony.

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

Trzy zasady sprawiają, że lista stronicowana jest poprawna i szybka.

1. Zawsze sortuj: zapytanie stronicowane bez ORDER BY może wyświetlić ten sam rekord na dwóch różnych stronach i całkowicie ukryć inną stronę, ponieważ serwer może swobodnie zmieniać kolejność wierszy pomiędzy wywołaniami.
2. Sortuj według unikalnej kolumny: Remisy w kolumnie sortowania pozostawiają niezdefiniowaną kolejność powiązanych wierszy. Sortowanie według klucza podstawowego lub dodanie go jako elementu rozstrzygającego remis rozwiązuje problem.
3. Obejrzyj głębokie strony: OFFSET 100000 sił MySQL przeczytać sto tysięcy wierszy i odrzucić je, zanim zwrócimy kolejne dwadzieścia. Czas reakcji rośnie wraz z liczbą stron.

W przypadku bardzo głębokiej paginacji, paginacja zestawów kluczy całkowicie pomija przesunięcie. Zamiast liczyć wiersze do pominięcia, zapytanie zapamiętuje ostatni klucz z poprzedniej strony i pyta o wiersze po nim.

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

Ta forma pozostaje szybka na dowolnej głębokości, ponieważ indeks przeskakuje bezpośrednio do klucza początkowego, zamiast przechodzić przez wiersze przed nim. Kompromisem jest to, że strony muszą być przeglądane sekwencyjnie, więc…ping Przejście bezpośrednio do strony 500 nie jest już możliwe.

LIMIT w MySQL vs TOP i FETCH FIRST

LIMIT nie jest częścią każdego dialektu SQL, co ma znaczenie, gdy zapytanie musi przejść między silnikami baz danych. MySQL, PostgreSQL, SQLite Udostępnij słowo kluczowe LIMIT. SQL Server używa TOP i Oracle Używa standardowej klauzuli FETCH FIRST. Poniższa tabela porównuje te trzy.

W każdym przypadku zachowanie jest takie samo: ogranicz liczbę wierszy i opcjonalnie pomiń najpierw kilka wierszy. Zmienia się tylko pisownia. Zapytanie, które musi zostać uruchomione na więcej niż jednym silniku, powinno zatem wyizolować klauzulę ograniczającą liczbę wierszy, zamiast rozpraszać ją po bazie kodu.

FAQ