SQLite LIMIT и OFFSET: ограничение и смещение строк в запросе

Когда я впервые столкнулся с таблицей на несколько миллионов строк, первый же SELECT без LIMIT положил соединение на несколько секунд. С тех пор LIMIT — одна из первых вещей, которую я проверяю в любом запросе, где не нужен полный результат

Оператор SELECT может возвращать миллионы строк, и если вам нужны только первые 10 из них, просто добавьте LIMIT к запросу

Синтаксис предложения LIMIT

Базовый синтаксис выглядит так:

SELECT column_list FROM table LIMIT row_count;

Здесь row_count — положительное целое число, задающее количество возвращаемых строк

Например, чтобы получить первые 10 строк из таблицы tracks, используйте следующий оператор:

SELECT trackId, name FROM tracks LIMIT 10;

Если нужно получить первые 10 строк, начиная с 10-й строки результирующего набора, используйте ключевое слово OFFSET (смещение):

SELECT column_list FROM table LIMIT row_count OFFSET offset;

Или можно использовать сокращённый синтаксис предложения LIMIT OFFSET:

SELECT column_list FROM table LIMIT offset, row_count;

Например, чтобы получить 10 строк, начиная с 11-й строки таблицы tracks:

SELECT trackId, name FROM tracks LIMIT 10 OFFSET 10;

Использование OFFSET часто встречается в веб-приложениях для постраничного отображения результирующих наборов

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

LIMIT совместно с ORDER BY

Рекомендуется всегда использовать LIMIT в сочетании с ORDER BY, чтобы вернуть строки в определённом порядке, а не в случайном

Предложение ORDER BY указывается перед предложением LIMIT в операторе SELECT. SQLite сортирует результирующий набор перед тем, как выбрать количество строк, указанное в предложении LIMIT

SELECT column_list FROM table ORDER BY column_1 LIMIT row_count;

Например, чтобы получить 10 самых больших треков по размеру:

SELECT trackid, name, bytes FROM tracks ORDER BY bytes DESC LIMIT 10;

Для получения 5 самых коротких треков отсортируйте их по длине в столбце milliseconds с помощью ORDER BY, а затем выберите первые 5 строк с LIMIT

SELECT trackid, name, milliseconds FROM tracks ORDER BY milliseconds ASC LIMIT 5;

Я рекомендую всегда явно указывать ORDER BY при использовании LIMIT — это делает поведение запроса предсказуемым и воспроизводимым

Получение n-го наибольшего и наименьшего значения

Предложения ORDER BY и LIMIT можно использовать для получения строк с n-м наибольшим или наименьшим значением. Например, может потребоваться узнать второй по длине трек, третий по размеру наименьший трек и т. д

Для этого выполните следующие шаги:

Во-первых, используйте ORDER BY для сортировки результирующего набора по возрастанию, если нужно получить n-е наименьшее значение, или по убыванию, если нужно получить n-е наибольшее значение

Во-вторых, используйте предложение LIMIT OFFSET для получения строки с n-м наибольшим или n-м наименьшим значением

Следующий оператор возвращает второй по длине трек из таблицы tracks:

SELECT trackid, name, milliseconds FROM tracks ORDER BY milliseconds DESC LIMIT 1 OFFSET 1;

Следующий оператор получает третий по размеру наименьший трек из таблицы tracks:

SELECT trackid, name, bytes FROM tracks ORDER BY bytes LIMIT 1 OFFSET 2;

Обратите внимание: OFFSET начинается с нуля, поэтому OFFSET 2 означает «пропустить первые две строки» и вернуть третью

Пагинация в SQLite через LIMIT и OFFSET

Одним из распространённых способов применения LIMIT является постраничная навигация в веб-приложениях, где каждая страница отображает фиксированное количество записей, а OFFSET указывает, с какой строки начинать следующую

Например, если на странице отображается 10 треков, то:

• страница 1: LIMIT 10 OFFSET 0
• страница 2: LIMIT 10 OFFSET 10
• страница 3: LIMIT 10 OFFSET 20

На практике я использую этот подход в большинстве проектов, где нужно выводить длинные списки данных без загрузки всей таблицы целиком

Типичные ошибки при использовании LIMIT

Несколько ситуаций, с которыми сталкиваются при работе с LIMIT:

Использование LIMIT без ORDER BY может привести к тому, что SQLite вернёт строки в непредсказуемом порядке, который меняется при каждом выполнении запроса

OFFSET начинается с нуля: чтобы получить вторую строку, укажите OFFSET 1, а не OFFSET 2. Эта ошибка часто встречается при реализации постраничной навигации

Отрицательные значения. SQLite не поддерживает отрицательные значения для LIMIT и OFFSET. Передача отрицательного числа может привести к неожиданному поведению в зависимости от версии SQLite

Путаница с сокращённым синтаксисом. В записи LIMIT offset, row_count первое число — это смещение, а второе — количество строк. Порядок противоположен тому, что можно ожидать интуитивно

Применение LIMIT в реальных задачах

Предложение LIMIT полезно не только для постраничной навигации. Вот несколько практических сценариев:

Быстрый просмотр данных. При отладке или исследовании таблицы удобно добавить LIMIT 5 или LIMIT 10, чтобы не загружать весь набор данных

Топ-N записей. Получение топ-10 самых продаваемых товаров, топ-5 самых активных пользователей — всё это решается комбинацией ORDER BY и LIMIT

Случайная выборка. В сочетании с ORDER BY RANDOM() предложение LIMIT позволяет получить случайное подмножество строк из таблицы

Проверка существования. Иногда достаточно знать, есть ли хотя бы одна строка, удовлетворяющая условию. В таком случае LIMIT 1 ускоряет запрос, останавливая сканирование после первого совпадения

Ответы на эти вопросы могут быть для вас полезными

Можно ли использовать LIMIT без OFFSET? Да. OFFSET — необязательная часть. Если указать только LIMIT 10, SQLite вернёт первые 10 строк результирующего набора без какого-либо смещения

Что произойдёт, если значение LIMIT больше числа строк в таблице? SQLite вернёт все доступные строки без ошибки. Если в таблице 5 строк, а вы указали LIMIT 100, в результате будет 5 строк

Обязательно ли использовать ORDER BY вместе с LIMIT? Технически нет, но настоятельно рекомендуется. Без ORDER BY порядок строк не гарантирован, и результат может отличаться при каждом выполнении запроса

Как получить последние N строк таблицы? Используйте ORDER BY по нужному столбцу в порядке убывания (DESC) и добавьте LIMIT N. Например: SELECT * FROM tracks ORDER BY trackid DESC LIMIT 5 вернёт 5 треков с наибольшими идентификаторами

Влияет ли LIMIT на производительность запроса? Да, положительно. SQLite останавливает сканирование, как только набирает нужное количество строк. Это особенно заметно при использовании LIMIT 1 — запрос завершается сразу после нахождения первого подходящего результата