php - tiene - ¿Cómo contar y limitar el registro en una sola consulta en MYSQL?

mostrar resultados de una consulta en php en una tabla

Question

Estoy buscando registros en una tabla de la siguiente manera:

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

Ahora, agrego LIMIT para mantener mi paginación. Pero cuando el usuario busca la palabra ''prashant'', el total de registros que tengo es 124 para ''prashant''. Pero como el límite se aplica a la consulta, solo obtiene 10 registros en mi script PHP y cuando estoy contando la variable mysql en el código PHP, el total de registros encontrados es 10.

Entonces, básicamente, quiero contar y limitar el uso de una sola consulta, al hacer algunas modificaciones en la consulta anterior, quiero el recuento total (124) de los registros. No quiero ejecutar una consulta separada de conteo (*) solo por contar el resultado total encontrado por la consulta.

Gracias.

Answer 1

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

# Find total rows
SELECT FOUND_ROWS();

más información

Answer 2

En el caso de tablas enormes y seleccionar múltiples campos (no solo Id, Nombre como en su ejemplo) recomendaría usar 2 consultas. Seleccionando el recuento (0) primero con todas esas cláusulas WHERE y solo luego crea la paginación, seleccionando datos, etc. Funcionará realmente más rápido en algún sitio web popular de comercio electrónico, por ejemplo.

Answer 3

Esto es para otros con la misma necesidad (considerando que han pasado 3 años desde el momento de esta pregunta).

Tuve un problema similar y no quería crear 2 consultas. Entonces, lo que hice fue crear una columna adicional para el número total y mover las cláusulas LIMIT y OFFSET al final:

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

Entonces el resultado es algo así como

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

y creo que esta solución no tiene ninguna desventaja en el tiempo porque solo obtiene el resultado una vez y luego utiliza el recuento de filas ya calculado para la columna adicional.

Answer 4

MySQL admite hacer esto usando SQL_CALC_FOUND_ROWS como lo menciona Ionut. Sin embargo, resulta que en muchos casos es más rápido hacerlo a la antigua usanza de dos instrucciones, donde una de ellas es una count() regular count() . Sin embargo, esto requiere que el recuento se pueda hacer usando un escaneo de índice, que no será el caso para esta misma consulta, pero pensé que lo mencionaría de todos modos.

Answer 5

No use SQL_CALC_FOUND_ROWS y FOUND_ROWS ()

1. hay errores reportados
   aquí: https://bugs.mysql.com/bug.php?id=18454
   y aquí: https://bugs.mysql.com/bug.php?id=19553

2. mientras que en tablas pequeñas BENCHMARK depende más de otras cosas y el tiempo resultante que tomará su SELECT será aproximadamente el mismo que COUNT (0) - SQL_CALC_FOUND_ROWS aún pone restricciones en las optimizaciones de base de datos LIMIT y ORDER BY, por lo que si depende de ellas no lo haga usa SQL_CALC_FOUND_ROWS

3. en tablas grandes, la diferencia de BENCHMARK se vuelve enorme donde un COUNT (0) puede tardar 0.003 segundos en lo mismo SQL_CALC_FOUND_ROWS ahora puede tomar 20 segundos

Por todas las mediciones COUNT (0) es la elección superior

COUNT (0) SINTAXIS:

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

Por lo tanto, su consulta total se vería así

(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;

Y luego solo llame a CountResults donde lo necesite en otro lugar ...

Otra ventaja de usar COUNT (0) es que puede usarla para buscar en la misma tabla que aquí o puede usarla para buscar en una tabla diferente ...

Entonces, por ejemplo, si cada persona encontrada también tiene 3, 2, 1, 5 empleos diferentes ... solo podría agregar un

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

dentro de tu original SELECT como este

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;

Dandote:

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

Entonces, al mostrar el nombre [3] (es decir, Bob), también podría mostrar que Bob tiene 1 trabajo llamando a trabajos [3] ...