Cours MariaDB / Trier et paginer les résultats avec ORDER BY et LIMIT

Dans MariaDB, ORDER BY définit l’ordre des lignes du résultat et LIMIT fixe le nombre maximal de lignes à renvoyer, éventuellement après un décalage. Si plusieurs produits ont le même prix, ajoutez une clé unique comme dernier critère de tri. Vous obtenez ainsi un ordre déterminé pour un jeu de données inchangé, ce qui rend les premières places et les limites de pages explicites.

Définir séparément l’ordre et la taille du résultat

Afficher les trois produits les plus chers exige deux décisions : trier les prix du plus grand au plus petit et limiter le résultat à trois lignes. LIMIT 3 seul ne définit pas quels produits doivent arriver en tête. ORDER BY décrit l’ordre de lecture du résultat ; il ne réorganise pas physiquement les lignes stockées.

Expression Signification Usage ici
ASC Ordre croissant, direction par défaut. Plus petit numéro d’abord lorsque les prix sont égaux.
DESC Ordre décroissant. Prix le plus élevé d’abord.
LIMIT 3 Au maximum trois lignes. Liste des trois premiers produits.
LIMIT 2 OFFSET 2 Ignorer deux lignes, puis en renvoyer au maximum deux. Deuxième page de deux produits.

Chaque expression de tri a sa propre direction. Dans ORDER BY price DESC, product_id ASC, DESC s’applique au prix et ASC au numéro. Une colonne textuelle qui contient des chiffres ne suit pas nécessairement l’ordre d’une colonne numérique : vérifiez les types réels. Pour choisir les lignes avant de les trier, employez les conditions de WHERE.

Préparer des produits ayant volontairement des prix égaux

Connectez-vous au serveur de formation avec course_user, puis sélectionnez la base shopcourse. Le guide du client et des fichiers SQL UTF-8 explique la connexion et SOURCE. Exécutez le SQL dans le client MariaDB, pas dans l’invite de commandes du système.

Contrôlez SELECT VERSION(), DATABASE(); puis, dans shopcourse, SHOW TABLES LIKE 'products_17_lab';. Si la table existe, inspectez sa définition et son contenu avant toute suppression. L’exercice utilise une table indépendante et laisse products ainsi que les autres tables de formation en place. Le compte doit pouvoir créer, remplir, consulter et supprimer les tables de cette base.

Enregistrez ce bloc dans un fichier UTF-8 nommé lesson17-order-limit.sql. Depuis le client connecté, lancez SOURCE C:/mariadb-lab/lesson17-order-limit.sql; en remplaçant le chemin par celui du fichier réellement enregistré.

USE shopcourse;
CREATE TABLE products_17_lab (
  product_id INT NOT NULL AUTO_INCREMENT,
  product_name VARCHAR(100) NOT NULL,
  category VARCHAR(30) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  stock INT NOT NULL DEFAULT 0,
  note VARCHAR(100) NULL DEFAULT NULL,
  PRIMARY KEY (product_id),
  CONSTRAINT ck_p17_price CHECK (price >= 0),
  CONSTRAINT ck_p17_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO products_17_lab
  (product_id, product_name, category, price, stock, note)
VALUES (1, '노트', '문구', 3500.00, 20, NULL),
  (2, '볼펜', '문구', 1500.00, 0, ''),
  (3, '머그컵', '생활', 8000.00, 12, '선물 포장'),
  (4, '스탠드', '생활', 25000.00, 5, NULL),
  (5, '파일', '문구', 1500.00, 50, ''),
  (6, '케이블', '전자', 8000.00, 0, '호환 확인');

Les chaînes coréennes d’origine sont conservées pour comparer le même jeu UTF-8. Les produits 1 à 6 sont un carnet, un stylo, un mug, une lampe, un dossier et un câble ; les catégories signifient papeterie, maison et électronique. Entrez les littéraux tels quels et comparez les numéros des produits.

Les produits 3 et 6 valent tous deux 8000 ; les produits 2 et 5 valent tous deux 1500. Ces égalités rendent le critère secondaire observable. Comme product_id est une clé primaire et diffère pour chaque ligne, il peut départager les ex æquo jusqu’au bout.

Ajouter une clé unique pour départager les prix

La requête suivante prend les trois premiers produits après un tri par prix décroissant et numéro croissant :

SELECT product_id, product_name, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 3;
Position ID Produit Prix
1 4 Lampe 25000.00
2 3 Mug 8000.00
3 6 Câble 8000.00

MariaDB compare d’abord price, puis product_id seulement quand les prix sont égaux. Remplacez uniquement le ASC du numéro par DESC : le produit 6 passe devant le produit 3, tandis que le produit 4 reste premier. Rétablissez ensuite ASC et vérifiez que l’ordre 4, 3, 6 revient.

Retirer le critère secondaire parce que les résultats semblent stables ne constitue pas une règle de classement. Même si tous les prix sont différents aujourd’hui, des égalités peuvent apparaître après une nouvelle saisie. La référence de ORDER BY précise comment les expressions successives départagent les lignes.

Comprendre OFFSET et les changements entre deux pages

Ces deux requêtes utilisent exactement le même tri et demandent deux lignes par page :

SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 0;
SELECT product_id, price FROM products_17_lab
ORDER BY price DESC, product_id ASC LIMIT 2 OFFSET 2;

La première page renvoie 4 et 3 ; la deuxième renvoie 6 et 1. OFFSET compte les lignes à ignorer, pas les pages. Avec une taille de page de deux lignes, la troisième page commence à OFFSET 4. Plus généralement, pour une numérotation des pages commençant à 1, le décalage vaut (page - 1) * taille.

La forme LIMIT 2, 2 exprime aussi le deuxième exemple, mais ses arguments sont dans l’ordre « décalage, nombre ». La forme LIMIT nombre OFFSET décalage rend leurs rôles plus visibles. LIMIT fixe un maximum : une dernière page peut contenir moins de lignes, et un décalage au-delà du résultat donne une page vide. La documentation de LIMIT détaille les deux écritures.

Le critère unique résout les égalités, mais ne fige pas les données entre deux requêtes. Une insertion, une suppression ou un changement de prix peut déplacer les positions : le deuxième appel peut alors répéter un produit ou en sauter un par rapport à la première lecture. OFFSET décrit une position dans l’ensemble visible au moment de chaque requête.

Un grand décalage peut aussi demander de traiter de nombreuses lignes précédentes. Renvoyer seulement deux lignes ne garantit donc pas que le serveur a filtré ou trié seulement deux lignes. L’intérêt d’un autre parcours doit être évalué avec les conditions, les index et le plan de la requête réelle, pas avec le seul nombre retourné par LIMIT.

Repartir des dernières valeurs et choisir la place de NULL

Si la première page se termine sur le produit 3 au prix de 8000, vous pouvez chercher ce qui vient après ces deux valeurs. Avec le tri de l’exemple, il faut un prix plus petit, ou le même prix accompagné d’un numéro plus grand :

SELECT product_id, price FROM products_17_lab
WHERE price < 8000 OR (price = 8000 AND product_id > 3)
ORDER BY price DESC, product_id ASC LIMIT 2;

Le résultat est 6 puis 1, comme la page obtenue avec OFFSET 2 sur les données initiales. Cette méthode s’appelle pagination par clé, ou keyset pagination. Conservez le prix et le numéro du dernier produit de la page afin de former le prochain curseur. Ne remplacez pas ces deux valeurs par le seul numéro : le prix reste le premier critère du classement.

La condition du curseur doit suivre les deux directions de tri. Modifier DESC ou ASC exige de revoir les comparaisons correspondantes. Ici, les colonnes de tri sont NOT NULL ; des colonnes pouvant être NULL demandent aussi une politique explicite de positionnement et de progression. La pagination par clé sert au parcours suivant/précédent et ne fournit pas à elle seule un saut direct vers un numéro de page arbitraire.

Elle ne garantit pas non plus une liste immuable si les valeurs de tri changent. Si votre application exige que toutes les pages représentent exactement le même état malgré des écritures concurrentes, définissez une politique de cohérence, par exemple un instantané ou une liste matérialisée adaptée au besoin.

Pour les notes, vous pouvez rendre explicite le placement des valeurs absentes :

SELECT product_id, note FROM products_17_lab
ORDER BY note IS NULL ASC, note ASC, product_id ASC;

Dans un tri croissant habituel, NULL est considéré comme inférieur aux valeurs présentes. Ici, note IS NULL vaut 0 pour une note présente et 1 pour une note absente : les notes non NULL passent donc d’abord. Les notes elles-mêmes, puis les numéros, départagent chaque groupe ; les notes NULL des produits 1 et 4 arrivent à la fin dans cet ordre. Les notes vides des produits 2 et 5 sont présentes et restent dans le premier groupe.

La collation détermine l’ordre détaillé des textes. Concentrez-vous ici sur la règle d’absence et le critère unique final ; le cours sur les comparaisons de NULL explique pourquoi vide et absent restent deux états distincts.

Diagnostiquer un ordre inattendu et encadrer les options

Retirez temporairement LIMIT et affichez la clé primaire avec toutes les expressions de tri. Vérifiez les types, les directions de chaque critère et les alias employés. Le cours sur les expressions et alias de SELECT aide à lire ces colonnes de résultat. L’ordre apparent d’un index ou d’une exécution précédente ne remplace jamais un ORDER BY explicite.

Si un programme laisse choisir le tri, associez chaque option autorisée à un nom de colonne et une direction connus. Les paramètres de valeurs ne servent généralement pas à remplacer arbitrairement des noms de colonnes ou des mots-clés ASC/DESC. Validez également la taille des pages et les décalages comme des entiers dans des limites définies ; évitez les requêtes négatives ou excessives et contrôlez les valeurs du curseur.

Pour recommencer, consultez SELECT DATABASE();, SHOW CREATE TABLE products_17_lab; et les lignes présentes. Sauvegardez toute donnée utile. Après avoir confirmé que cette table ne contient que les données jetables de l’exercice, supprimez uniquement cette table :

DROP TABLE shopcourse.products_17_lab;

DROP TABLE retire la structure et les données ; un simple ROLLBACK ne peut pas annuler cette suppression. La commande laisse shopcourse.products et les autres tables de formation en place. Relancez ensuite le fichier de préparation pour retrouver les six produits et leurs prix initiaux.