MySQL LIMIT & OFFSET com exemplos

Qual é a palavra-chave LIMIT em MySQL?

O LIMITE A palavra-chave `WHERE` restringe o número de linhas retornadas no resultado de uma consulta. Ela pode ser usada com as instruções `SELECT`, `UPDATE` e `DELETE`, limitando assim o número de linhas que uma consulta lê, bem como o número de linhas que uma operação de gravação afeta.

A sintaxe da palavra-chave LIMIT é a seguinte.

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

AQUI

• “SELECIONE {nome(s) do(s) campo(s) | *} FROM nome(s) da(s) tabela(s)” é o Instrução SELECT contendo os campos que gostaríamos de retornar em nossa consulta.
• “[ONDE condição]” É opcional, mas quando fornecido, especifica um filtro no conjunto de resultados. Cláusula WHERE é aplicado antes do LIMIT, portanto a filtragem ocorre primeiro e o limite é aplicado ao que resta.
• “LIMITE N” é a palavra-chave, e N é qualquer número a partir de 0. Definir 0 como limite não retorna nenhum registro. Definir um número como 5 retorna cinco registros. Se a tabela contiver menos registros que N, todos eles serão retornados e nenhum erro será gerado.

A sintaxe é curta, mas vale a pena explicar o motivo de sua existência antes dos exemplos.

Por que devemos usar a palavra-chave LIMIT?

Suponha que estamos desenvolvendoping O aplicativo que roda sobre o banco de dados MyflixDB. Os desenvolvedores do sistema nos pediram para limitar o número de registros exibidos por página a 20, a fim de evitar lentidão no carregamento. Como podemos implementar um sistema que atenda a esse requisito?

A palavra-chave LIMIT lida exatamente com essa situação. Em vez de puxar todas as linhas de membros para o aplicativo e descartar a maioria delas, a consulta retorna 20 registros por página e o banco de dados faz o trabalho. Três benefícios decorrem disso.

• Resposta mais rápida: Menos dados são lidos do disco e menos dados trafegam pela rede.
• Reduzir o uso de memória: O aplicativo contém uma página de linhas, não a tabela inteira.
• Safer escreve: um LIMITE em uma ATUALIZAÇÃO ou um EXCLUIR A declaração limita quantas linhas um erro pode afetar.

MySQL Exemplos de consulta LIMIT

Os exemplos abaixo são executados na tabela de membros do banco de dados myflixdb. O primeiro retorna duas linhas e nada mais.

SELECT * FROM members LIMIT 2;

Conforme demonstrado no resultado acima, apenas dois membros foram devolvidos.

Obtendo uma lista de dez (10) membros do banco de dados

Suponha que desejemos uma lista dos 10 primeiros membros registrados no banco de dados do Myflix. O script abaixo solicita essa lista.

SELECT * FROM members LIMIT 10;

A execução do script produz o resultado mostrado abaixo.

Apenas 9 membros foram retornados, porque N na cláusula LIMIT é maior que o número de registros na tabela. Solicitar explicitamente 9 linhas produz o mesmo conjunto de resultados.

SELECT * FROM members LIMIT 9;

💡 Dica: LIMIT seleciona linhas de qualquer ordem em que o servidor as produza. Adicione um ORDENAR POR cláusula sempre que a identidade das filas for importante, caso contrário, “os 10 primeiros membros” não garante que se refiram às mesmas nove pessoas duas vezes.

Limitar o número de linhas é a primeira parte da funcionalidade. Escolher onde a janela começa é a segunda.

Utilizando o valor OFFSET na consulta LIMIT

O DESVIO O parâmetro `value` é usado com mais frequência em conjunto com a palavra-chave `LIMIT`. Ele especifica a partir de qual linha o servidor começa a recuperar os dados, de modo que as linhas anteriores a esse ponto são ignoradas.

Suponha que desejamos um número limitado de membros a partir do meio da tabela. O script abaixo começa na segunda linha e limita o resultado a dois registros.

SELECT * FROM `members` LIMIT 1, 2;

Executando-o em MySQL Workbench A consulta ao banco de dados myflixdb produz o seguinte resultado.

Observe que aqui DESLOCAMENTO = 1, portanto, a linha nº 2 é a primeira linha retornada, e LIMITE = 2Consequentemente, apenas 2 registros são retornados.

Na forma de dois argumentos, o deslocamento é escrito primeiro e a contagem de linhas em segundo lugar, o que é fácil de inverter acidentalmente. MySQL Também aceita uma forma explícita que elimina a ambiguidade, e é essa que deve ser preferida em novos códigos.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Ambas as instruções retornam as mesmas duas linhas. Com o deslocamento compreendido, o padrão de paginação do qual todas as telas de listagem dependem decorre diretamente disso.

Como paginar resultados de consulta com LIMIT e OFFSET

A paginação divide um grande conjunto de resultados em páginas numeradas, e as funções LIMIT e OFFSET são o mecanismo que realiza essa tarefa. Dois valores controlam cada solicitação de página: o tamanho da página, que indica quantos registros aparecem em uma tela, e o número da página solicitada pelo usuário.

O deslocamento é derivado deles com uma ú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

A página 2 mantém o mesmo limite e avança o deslocamento em um tamanho de página.

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

Três regras mantêm uma listagem paginada correta e rápida.

1. Sempre classificar: Uma consulta paginada sem ORDER BY pode mostrar o mesmo registro em duas páginas diferentes e ocultar completamente outro, porque o servidor tem liberdade para alterar a ordem das linhas entre as chamadas.
2. Classificar por uma coluna única: Empates na coluna de classificação deixam a ordem das linhas empatadas indefinida. Classificar pela chave primária ou adicioná-la como critério de desempate resolve o problema.
3. Veja as páginas detalhadas: DESLOCAMENTO 100000 forças MySQL Ler cem mil linhas e descartá-las antes de retornar as próximas vinte. O tempo de resposta aumenta com o número da página.

Para paginação muito profunda, a paginação por conjunto de chaves evita completamente o deslocamento. Em vez de contar as linhas a serem ignoradas, a consulta memoriza a última chave da página anterior e solicita as linhas subsequentes.

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 mantém a velocidade em qualquer profundidade, porque o índice salta diretamente para a chave inicial em vez de percorrer as linhas anteriores. A desvantagem é que as páginas devem ser percorridas em sequência, então o saltoping Não é mais possível acessar diretamente a página 500.

LIMITE em MySQL vs TOP e FETCH FIRST

LIMIT não faz parte de todos os dialetos SQL, o que é importante quando uma consulta precisa ser transferida entre mecanismos de banco de dados. MySQL, PostgreSQL e SQLite compartilhar a palavra-chave LIMIT. O SQL Server usa TOP e Oracle usa a cláusula padrão FETCH FIRST. A tabela abaixo compara as três.

O comportamento é o mesmo em todos os casos: limitar o número de linhas e, opcionalmente, ignorar algumas linhas inicialmente. Apenas a grafia muda. Portanto, uma consulta que precisa ser executada em mais de um mecanismo de busca deve isolar a cláusula de limitação de linhas em vez de espalhá-la pelo código.

Perguntas Frequentes