Как подсчитать и ограничить запись в одном запросе в MYSQL?

Я ищу записи в таблице следующим образом:

SELECT Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

Теперь я добавляю LIMIT, чтобы поддерживать мой пейджинг. Но когда пользователь ищет слово "prashant", тогда у всех записей у меня есть 124 для "prashant". Но в качестве предела, применяемого к запросу, поэтому он извлекает только 10 записей в моем PHP script, и когда я подсчитываю переменную mysql в PHP-коде, он возвращает общее количество найденных записей - 10.

Итак, в основном я хочу рассчитывать и ограничивать использование одного запроса, сделав некоторую модификацию в указанном выше запросе, я хочу, чтобы общее количество записей (124) записей. Я не хочу запускать отдельный запрос count (*) только для подсчета общего результата, найденного в запросе.

Спасибо.

Ответы

Ответ 1

SELECT SQL_CALC_FOUND_ROWS
  Id, Name
FROM my_table
WHERE
  Name LIKE '%prashant%'
LIMIT 0, 10;

# Find total rows
SELECT FOUND_ROWS();

дополнительная информация

Ответ 2

MySQL поддерживает это с помощью SQL_CALC_FOUND_ROWS, как упоминалось Ionut. Тем не менее, оказывается, что во многих случаях он фактически быстрее делает это старомодным способом, используя два утверждения, где один из них является обычным count(). Тем не менее, это требует, чтобы подсчет можно было выполнить с помощью сканирования индекса, что не будет иметь место для этого самого запроса, но я думал, что все-таки упомянул об этом.

Ответ 3

Это для других с такой же потребностью (учитывая, что прошло 3 года с момента этого вопроса).

У меня была аналогичная проблема, и я не хотел создавать 2 запроса. Так что я сделал, чтобы создать дополнительный столбец для общего числа и переместил предложения LIMIT и OFFSET в конце:

SELECT SQL_CALC_FOUND_ROWS * FROM (
    SELECT `id`, `name`
    FROM `my_table`
    WHERE `name` LIKE '%prashant%'
) res,
(SELECT /*CEIL(FOUND_ROWS()/10) AS 'pages',*/ FOUND_ROWS() AS 'total') tot
LIMIT 0, 10

Таким образом, результат выглядит как

| id  |      name      | total |
+-----+----------------+-------+
|  1  | Jason Prashant |  124  |
|  2  | Bob Prashant   |  124  |
|  3  | Sam Prashant   |  124  |
|  4  | etc. prashant  |  124  |

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

Ответ 4

В случае огромных таблиц и выбора нескольких полей (а не только Id, Name, как в вашем примере), я бы рекомендовал использовать 2 запроса. Сначала выберите счетчик (0) со всеми этими предложениями WHERE и только затем создайте разбиение на страницы, выбор данных и т.д. Например, он будет работать быстрее на каком-то популярном веб-сайте электронной коммерции.

Ответ 5

Не используйте SQL_CALC_FOUND_ROWS и FOUND_ROWS()

есть сообщения об ошибках
здесь: https://bugs.mysql.com/bug.php?id=18454
и здесь: https://bugs.mysql.com/bug.php?id=19553
В то время как на небольших таблицах BENCHMARK больше зависит от других вещей, и полученное вами время SELECT будет примерно таким же, как COUNT (0). SQL_CALC_FOUND_ROWS все еще ставит ограничения на оптимизацию базы данных LIMIT и ORDER BY, поэтому, если вы зависят от них не использовать SQL_CALC_FOUND_ROWS
на больших таблицах разница BENCHMARK становится огромной, где COUNT (0) может принимать 0,003 секунды, тот же самый SQL_CALC_FOUND_ROWS теперь может занимать 20 секунд

По всем метрикам COUNT (0) является лучшим выбором

COUNT (0) СИНТАКСИС:

(SELECT COUNT(0) FROM tableNames WHERE condition) AS alias
// alias is optional but needed if you need to use the result later

Итак, ваш общий запрос будет выглядеть следующим образом:

(SELECT COUNT(0) FROM my_table WHERE name LIKE '%prashant%') AS countResults;

SELECT Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

А затем просто вызовите countResults, когда вам это нужно в другом месте...

Еще одно преимущество использования COUNT (0) - вы можете использовать его для поиска в той же таблице, что и здесь, или вы можете использовать ее для поиска в другой таблице...

Так, например, если каждый найденный человек также имеет 3, 2, 1, 5 задания diffenrent соответственно... вы могли бы просто добавить

(SELECT COUNT(0) FROM my_jobs_table WHERE name = name_row_in_jobs_table) AS jobs

внутри вашего оригинального SELECT, как это

SELECT Id, Name (SELECT COUNT(0) FROM my_jobs_table WHERE name = name_row_in_jobs_table) AS jobs FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

Предоставление вам:

--------------------
| id | Name | jobs |
--------------------
| 1  | Alice| 3    |
--------------------
| 2  | John | 2    |
--------------------
| 3  | Bob  | 1    |
--------------------
| 4  | Jill | 5    |
--------------------

Итак, при показе имени [3] (т.е. Bob) вы также можете показать, что Боб имеет 1 задание, вызывая задания [3]...