MySQL LÍMITE Y COMPENSACIÓN con ejemplos

¿Qué es la palabra clave LIMIT en MySQL?

El LIMITE LAS La palabra clave restringe el número de filas que se devuelven en el resultado de una consulta. Se puede usar con las sentencias SELECT, UPDATE y DELETE, por lo que limita tanto las filas que lee una consulta como las que se modifican al escribir en ella.

La sintaxis para la palabra clave LIMIT es la siguiente.

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

AQUÍ

• “SELECCIONAR {nombre(s) de campo | *} DESDE nombre(s) de tabla” son los Instrucción SELECT que contiene los campos que nos gustaría devolver en nuestra consulta.
• “[DÓNDE condición]” es opcional, pero cuando se proporciona especifica un filtro en el conjunto de resultados. Dónde cláusula se aplica antes de LIMIT, por lo que el filtrado se realiza primero y el límite se aplica a lo que sobrevive.
• “LÍMITE N” es la palabra clave, y N es cualquier número que comience desde 0. Si se establece 0 como límite, no se devuelve ningún registro. Si se establece un número como 5, se devuelven cinco registros. Si la tabla contiene menos registros que N, se devuelven todos y no se genera ningún error.

La sintaxis es breve, pero vale la pena explicar el motivo de su existencia antes de presentar los ejemplos.

¿Por qué deberíamos usar la palabra clave LIMIT?

Supongamos que estamos desarrollandoping La aplicación se ejecuta sobre myflixdb. Los diseñadores del sistema nos han pedido que limitemos la cantidad de registros mostrados por página a 20, para evitar tiempos de carga lentos. ¿Cómo implementamos un sistema que cumpla con este requisito?

La palabra clave LIMIT maneja precisamente esta situación. En lugar de cargar todas las filas de miembros en la aplicación y descartar la mayoría, la consulta devuelve 20 registros por página y la base de datos se encarga del resto. Esto conlleva tres ventajas.

• Respuesta más rápida: Se leen menos datos del disco y se transmiten menos datos a través de la red.
• Menor consumo de memoria: La aplicación contiene una página de filas, no la tabla completa.
• Safer escribe: un LÍMITE en una ACTUALIZACIÓN o una BORRAR Esta instrucción limita la cantidad de filas que puede afectar un error.

MySQL Ejemplos de consultas LIMIT

Los siguientes ejemplos se ejecutan sobre la tabla de miembros de la base de datos myflixdb. El primero devuelve dos filas y nada más.

SELECT * FROM members LIMIT 2;

Como muestra el resultado anterior, solo se han devuelto dos miembros.

Obtener una lista de diez (10) miembros de la base de datos

Supongamos que queremos una lista de los primeros 10 miembros registrados en la base de datos de Myflix. El siguiente script los solicita.

SELECT * FROM members LIMIT 10;

Al ejecutar el script se obtiene el resultado que se muestra a continuación.

Solo se han devuelto 9 miembros, porque N en la cláusula LIMIT es mayor que el número de registros en la tabla. Solicitar explícitamente 9 filas produce el mismo conjunto de resultados.

SELECT * FROM members LIMIT 9;

💡 Consejo: LIMIT selecciona filas en cualquier orden que el servidor produzca. Agregue un ORDEN POR cláusula siempre que importe la identidad de las filas, de lo contrario no se garantiza que "los primeros 10 miembros" signifique las mismas nueve personas dos veces.

Limitar el número de filas es la primera parte de la función. Elegir dónde comienza la ventana es la segunda.

Uso del valor OFFSET en la consulta LIMIT

El OFFSET El valor se usa con mayor frecuencia junto con la palabra clave LIMIT. Especifica desde qué fila comienza el servidor a recuperar los datos, por lo que se omiten las filas anteriores a ese punto.

Supongamos que queremos un número limitado de miembros a partir del centro de la tabla. El siguiente script comienza en la segunda fila y limita el resultado a dos registros.

SELECT * FROM `members` LIMIT 1, 2;

Ejecutándolo en MySQL Banco de trabajo La consulta a myflixdb arroja el siguiente resultado.

Tenga en cuenta que aquí DESPLAZAMIENTO = 1, por lo tanto, la fila #2 es la primera fila devuelta, y LÍMITE = 2Por lo tanto, solo se obtienen 2 registros.

En el formato de dos argumentos, primero se escribe el desplazamiento y después el número de filas, lo que facilita invertirlo por accidente. MySQL También acepta una forma explícita que elimina la ambigüedad, y es la que se debe preferir en el código nuevo.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Ambas instrucciones devuelven las mismas dos filas. Una vez comprendido el desplazamiento, el patrón de paginación en el que se basa cada pantalla de listado se deduce directamente.

Cómo paginar los resultados de una consulta con LIMIT y OFFSET

La paginación divide un conjunto de resultados extenso en páginas numeradas, y la función LIMIT, junto con OFFSET, es el mecanismo que lo hace. Dos valores determinan cada solicitud de página: el tamaño de la página, que indica cuántos registros aparecen en una pantalla, y el número de página solicitado por el usuario.

El desplazamiento se obtiene a partir de ellos mediante una única fórmula.

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

La página 2 mantiene el mismo límite y desplaza el margen hacia adelante el tamaño de una página.

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

Tres reglas mantienen un listado paginado correcto y rápido.

1. Ordenar siempre: Una consulta paginada sin ORDER BY puede mostrar el mismo registro en dos páginas diferentes y ocultar otro por completo, porque el servidor tiene la libertad de cambiar el orden de las filas entre llamadas.
2. Ordenar por una columna única: Los empates en la columna de ordenación dejan indefinido el orden de las filas empatadas. Ordenar por la clave primaria, o añadirla como criterio de desempate, soluciona el problema.
3. Mira las páginas profundas: DESPLAZAMIENTO 100000 fuerzas MySQL leer cien mil filas y descartarlas antes de devolver las siguientes veinte. El tiempo de respuesta aumenta con el número de página.

Para una paginación muy profunda, la paginación por conjuntos de claves evita por completo el desplazamiento. En lugar de contar las filas que se deben omitir, la consulta recuerda la última clave de la página anterior y solicita las filas posteriores.

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

Este formato se mantiene rápido a cualquier profundidad, porque el índice salta directamente a la clave inicial en lugar de recorrer las filas que tiene delante. La desventaja es que las páginas deben recorrerse en secuencia, por lo que saltaping Ya no es posible acceder directamente a la página 500.

LÍMITE en MySQL vs TOP y FETCH FIRST

LIMIT no forma parte de todos los dialectos SQL, lo cual es importante en cuanto una consulta tiene que moverse entre motores de bases de datos. MySQL, PostgreSQL, y SQLite comparte la palabra clave LIMIT. SQL Server utiliza TOP y Oracle Utiliza la cláusula estándar FETCH FIRST. La siguiente tabla compara las tres.

El comportamiento es el mismo en ambos casos: se limita el número de filas y, opcionalmente, se omiten algunas filas. Solo cambia la redacción. Por lo tanto, una consulta que deba ejecutarse en más de un motor debería aislar la cláusula de limitación de filas en lugar de dispersarla por todo el código.

Preguntas Frecuentes