PHP MySQL SELECT с LIMIT: подробное руководство

В базе данных часто нужна лишь часть таблицы — десять последних записей, первая страница результатов поиска, единственный «лидер» — а не все строки целиком. Оператор LIMIT в запросе MySQL SELECT ограничивает количество возвращаемых строк. В сочетании с OFFSET он служит основой постраничной навигации: крупные наборы результатов отдаются по одной странице за раз, а не загружаются тысячами строк в память.

В этой главе рассматриваются синтаксис LIMIT, пропуск строк через OFFSET, безопасное построение пагинации с помощью подготовленных запросов и типичные подводные камни. Предполагается, что вы уже умеете подключаться к MySQL и выполнять базовый SELECT.

Синтаксис SELECT с LIMIT

Базовая форма задаёт максимальное количество возвращаемых строк:

SELECT column1, column2, ... FROM table_name LIMIT row_count;

Чтобы дополнительно пропустить строки с начала результата, укажите смещение. MySQL поддерживает два равнозначных варианта записи:

SELECT ... FROM table_name LIMIT offset, row_count;   -- offset first
SELECT ... FROM table_name LIMIT row_count OFFSET offset;  -- explicit OFFSET

Что означает каждая часть:

• row_count — максимальное количество возвращаемых строк.
• offset — сколько строк пропустить перед началом выборки. Смещение начинается с нуля, поэтому OFFSET 0 соответствует первой строке.
• LIMIT 10, 5 возвращает 5 строк, начиная после первых 10 (то есть строки 11–15). Тот же запрос читается понятнее как LIMIT 5 OFFSET 10.

LIMIT без ORDER BY недетерминирован. Без явного ORDER BY MySQL вправе возвращать строки в любом порядке, поэтому «первые 2 строки» могут различаться от запуска к запуску. Всегда сочетайте LIMIT с ORDER BY, когда порядок строк важен.

Пример: получение первых строк

Рассмотрим таблицу students:

+----+---------+--------+-------+
| id | name    | class  | marks |
+----+---------+--------+-------+
|  1 | John    | 10     | 90    |
|  2 | Michael | 9      | 85    |
|  3 | Jessica | 8      | 80    |
|  4 | Sarah   | 10     | 88    |
|  5 | David   | 9      | 72    |
+----+---------+--------+-------+

Чтобы получить первых двух студентов (упорядоченных по id для стабильного результата):

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$query = "SELECT id, name, class, marks FROM students ORDER BY id LIMIT 2";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: {$row['id']} Name: {$row['name']} Class: {$row['class']} Marks: {$row['marks']}<br>";
}

mysqli_close($conn);
?>

Результат:

ID: 1 Name: John Class: 10 Marks: 90
ID: 2 Name: Michael Class: 9 Marks: 85

Обратите внимание на использование mysqli_fetch_assoc() (только ассоциативный массив) вместо mysqli_fetch_array(), который возвращает и числовые, и строковые ключи, расходуя вдвое больше памяти для тех же данных.

Пропуск строк с помощью OFFSET

Чтобы получить вторую страницу из двух студентов — строки 3 и 4 — пропустите первые две:

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$query = "SELECT id, name, marks FROM students ORDER BY id LIMIT 2 OFFSET 2";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: {$row['id']} Name: {$row['name']} Marks: {$row['marks']}<br>";
}

mysqli_close($conn);
?>

Результат:

ID: 3 Name: Jessica Marks: 80
ID: 4 Name: Sarah Marks: 88

Безопасное построение пагинации

При реальной пагинации номер страницы поступает из пользовательского ввода (URL вида ?page=3), поэтому смещение нельзя напрямую подставлять в SQL. Используйте подготовленный запрос с привязанными целочисленными параметрами:

<?php
$conn = mysqli_connect("localhost", "username", "password", "database");

$perPage = 2;
$page    = max(1, (int) ($_GET['page'] ?? 1)); // force a positive integer
$offset  = ($page - 1) * $perPage;

$stmt = mysqli_prepare(
    $conn,
    "SELECT id, name, marks FROM students ORDER BY id LIMIT ? OFFSET ?"
);
mysqli_stmt_bind_param($stmt, "ii", $perPage, $offset); // "ii" = two integers
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);

while ($row = mysqli_fetch_assoc($result)) {
    echo "ID: {$row['id']} Name: {$row['name']} Marks: {$row['marks']}<br>";
}

mysqli_close($conn);
?>

Приведение страницы к (int) и передача её как целого числа ("ii") предотвращают SQL-инъекцию, поскольку LIMIT/OFFSET в любом случае принимают только числа.

Чтобы отображать «Страница 3 из N», нужно также знать общее количество строк. Выполните отдельный запрос COUNT(*) и разделите на размер страницы:

$total = (int) mysqli_fetch_row(
    mysqli_query($conn, "SELECT COUNT(*) FROM students")
)[0];
$pageCount = (int) ceil($total / $perPage); // 5 rows / 2 per page = 3 pages

Частые подводные камни

• Нет ORDER BY — нет гарантии. Как отмечено выше, LIMIT возвращает стабильный срез только при наличии ORDER BY по уникальному (или однозначно определяемому) столбцу.
• Большие смещения работают медленно. LIMIT 20 OFFSET 100000 всё равно заставляет MySQL просмотреть и отбросить 100 000 строк. Для глубокой пагинации предпочтительна keyset-пагинация — WHERE id > :lastSeenId ORDER BY id LIMIT 20 — которая сразу переходит к нужному месту по индексу. См. WHERE.
• OFFSET нельзя использовать без LIMIT. MySQL требует наличия LIMIT при использовании OFFSET. Чтобы пропустить строки и вернуть «всё остальное», задайте очень большой лимит: LIMIT 18446744073709551615 OFFSET 10.
• Страницы нумеруются с нуля внутри. Распространённая ошибка на единицу — забыть, что страница 1 соответствует OFFSET 0, отсюда формула ($page - 1) * $perPage в примере выше.

Практика