MySQL LIMITE ET DÉCALAGE avec exemples

Quel est le mot-clé LIMIT dans MySQL?

Le LIMIT Le mot-clé `LIMIT` limite le nombre de lignes renvoyées dans le résultat d'une requête. Il peut être utilisé avec les instructions SELECT, UPDATE et DELETE, et limite ainsi le nombre de lignes lues par une requête ainsi que le nombre de lignes affectées par une écriture.

La syntaxe du mot-clé LIMIT est la suivante.

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

ICI. (en anglais seulement)

• « SÉLECTIONNER {nom(s) de champ | *} DEPUIS nom(s) de table" est le Instruction SELECT contenant les champs que nous souhaitons renvoyer dans notre requête.
• "[OÙ condition]" est facultatif, mais lorsqu'il est fourni, il spécifie un filtre sur l'ensemble de résultats. clause O est appliqué avant LIMIT, le filtrage a donc lieu en premier et la limite est appliquée à ce qui reste.
• « LIMITE N » est le mot-clé, et N N représente n'importe quel nombre à partir de 0. Si la limite est fixée à 0, aucun enregistrement ne sera retourné. Si la limite est fixée à 5, cinq enregistrements seront retournés. Si la table contient moins d'enregistrements que N, ils seront tous retournés et aucune erreur ne sera signalée.

La syntaxe est concise, mais il convient d'expliquer sa raison d'être avant de donner les exemples.

Pourquoi devrions-nous utiliser le mot-clé LIMIT ?

Supposons que nous soyons en développementping L'application s'exécute sur myflixdb. Les concepteurs du système nous ont demandé de limiter à 20 le nombre d'enregistrements affichés par page afin d'optimiser les temps de chargement. Comment mettre en œuvre un système répondant à cette contrainte ?

Le mot-clé LIMIT gère précisément ce cas de figure. Au lieu d'importer chaque ligne de membre dans l'application et d'en supprimer la plupart, la requête renvoie 20 enregistrements par page, laissant le soin à la base de données de traiter les données. Il en découle trois avantages.

• Réponse plus rapide : Moins de données sont lues sur le disque et moins de données transitent par le réseau.
• Utilisation de la mémoire réduite : L'application contient une seule page de lignes, et non le tableau entier.
• Safer écrit : une LIMITE sur une MISE À JOUR ou une EFFACER Cette instruction limite le nombre de lignes qu'une erreur peut affecter.

MySQL Exemples de requêtes LIMIT

Les exemples ci-dessous sont exécutés sur la table « members » de la base de données myflixdb. Le premier renvoie deux lignes, et rien de plus.

SELECT * FROM members LIMIT 2;

Comme le montre le résultat ci-dessus, seuls deux membres ont été renvoyés.

Récupération d'une liste de dix (10) membres à partir de la base de données

Supposons que nous souhaitions obtenir la liste des 10 premiers membres inscrits dans la base de données Myflix. Le script ci-dessous permet de les obtenir.

SELECT * FROM members LIMIT 10;

L'exécution du script donne le résultat indiqué ci-dessous.

Seuls 9 membres ont été retournés, car N dans la clause LIMIT est supérieur au nombre d'enregistrements dans la table. Demander explicitement 9 lignes produit le même résultat.

SELECT * FROM members LIMIT 9;

Astuce : LIMIT sélectionne les lignes dans l'ordre où le serveur les renvoie. Ajoutez un COMMANDÉ PAR clause chaque fois que l'identité des rangs importe, sinon « les 10 premiers membres » ne garantit pas de désigner deux fois les mêmes neuf personnes.

Limiter le nombre de lignes constitue la première partie de la fonctionnalité. Choisir le point de départ de la fenêtre constitue la seconde.

Utilisation de la valeur OFFSET dans la requête LIMIT

Le OFFSET La valeur est le plus souvent utilisée avec le mot-clé LIMIT. Elle spécifie la ligne à partir de laquelle le serveur commence à récupérer les données ; les lignes précédentes sont ignorées.

Supposons que nous souhaitions limiter le nombre de membres à partir du milieu du tableau. Le script ci-dessous commence à la deuxième ligne et limite le résultat à deux enregistrements.

SELECT * FROM `members` LIMIT 1, 2;

L'exécuter dans MySQL Workbench La requête contre la base de données myflixdb donne le résultat suivant.

Notez qu'ici DÉCALAGE = 1, la ligne n°2 est donc la première ligne renvoyée, et LIMITE = 2, donc seuls 2 enregistrements ont été retournés.

Sous cette forme à deux arguments, le décalage est écrit en premier et le nombre de lignes en second, ce qui peut facilement être inversé par erreur. MySQL Elle accepte également une forme explicite qui lève l'ambiguïté, et c'est celle qu'il faut privilégier dans le nouveau code.

SELECT * FROM `members` LIMIT 2 OFFSET 1;

Les deux instructions renvoient les mêmes deux lignes. Le décalage étant compris, le modèle de pagination sur lequel repose chaque écran de liste en découle directement.

Comment paginer les résultats d'une requête avec LIMIT et OFFSET

La pagination divise un grand ensemble de résultats en pages numérotées ; les fonctions LIMIT et OFFSET permettent cette division. Chaque requête de page dépend de deux valeurs : la taille de la page, c’est-à-dire le nombre d’enregistrements affichés à l’écran, et le numéro de page demandé par l’utilisateur.

Le décalage est calculé à partir de ces données à l'aide d'une formule unique.

-- 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 page 2 conserve la même limite et décale le décalage d'une page.

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

Trois règles permettent de garantir une pagination correcte et rapide.

1. Toujours trier : Une requête paginée sans ORDER BY peut afficher le même enregistrement sur deux pages différentes et en masquer un autre complètement, car le serveur est libre de modifier l'ordre des lignes entre les appels.
2. Trier sur une colonne unique : En cas d'égalité dans la colonne de tri, l'ordre des lignes concernées reste indéfini. Trier selon la clé primaire, ou l'ajouter comme critère de départage, résout ce problème.
3. Consultez les pages profondes : DÉCALAGE 100000 forces MySQL Il faut lire cent mille lignes et les ignorer avant d'en afficher les vingt suivantes. Le temps de réponse augmente avec le numéro de page.

Pour une pagination très profonde, la pagination par ensemble de clés évite complètement le décalage. Au lieu de compter les lignes à ignorer, la requête conserve la dernière clé de la page précédente et demande les lignes suivantes.

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

Ce format reste rapide quelle que soit la profondeur, car l'index accède directement à la clé de départ au lieu de parcourir les lignes précédentes. En contrepartie, les pages doivent être parcourues séquentiellement.ping L'accès direct à la page 500 n'est plus possible.

LIMITE dans MySQL contre TOP et FETCH FIRST

L'instruction LIMIT ne fait pas partie de tous les dialectes SQL, ce qui a son importance dès lors qu'une requête doit être transférée entre différents moteurs de base de données. MySQL, PostgreSQL et SQLite SQL Server utilise le mot-clé LIMIT. SQL Server utilise TOP et TOP. Oracle utilise la clause FETCH FIRST standard. Le tableau ci-dessous compare les trois.

Le comportement est identique dans tous les cas : limiter le nombre de lignes et, éventuellement, en ignorer certaines au préalable. Seule la formulation diffère. Une requête devant s’exécuter sur plusieurs moteurs de recherche doit donc isoler la clause de limitation du nombre de lignes plutôt que de la disperser dans le code.

FAQ