La majorité des systèmes de bases de données permettent de trier une sélection de données en fonction de nos besoins, souvent de manière simple. La majorité des besoins courants impliquent des tris ascendants ou descendants (comprendre « par ordre alphabétique croissant ou descendant ») sur une plusieurs colonnes (des dates, des titres ou des codes particuliers) mais de temps à autre, il arrive que les besoins soient plus complexes puisque l’on voudra soit :
- un ordre aléatoire
- un ordre bien précis, ni ascendant ni descendant
Le mécanisme de tri (ORDER BY)
Pour bien comprendre comment fonctionne en détail la commande ORDER BY, je vous invite à lire la documentation concernant les optimisations relatives à ORDER BY. Ce que l’on doit en retenir :
- le tri est effectué sur les données filtrées par un éventuel
WHERE; - il y a 2 algorithmes de tri : l’un utilisant deux passes, l’autre n’utilisant qu’une passe mais davantage de mémoire ;
- l’algorithme en une passe n’est pas valable pour des tris sur des données de type
TEXTouBLOB; - un tri sur un index est largement plus rapide et plus performant que sur des valeurs non-indexées
Lorsqu’on veut trier des données, il vaut donc mieux écrémer au mieux la plage de données grâce à de judicieux filtres WHERE et de préférence, sur des colonnes indexées.
Mais revenons à nos moutons.
Tri aléatoire (ORDER BY RAND())
Deux solutions s’offrent au développeur pour effectuer un tri aléatoire : recueillir les données voulues et les trier via langage de programmation (sort(), ksort() etc.) ou alors trier directement à la source via la base de données.
Si les bases de données portent leur nom, c’est bien parce qu’elles sont performantes dans leur travail donc autant leur laisser celui-là, celui du tri aléatoire, qui sera bien plus rapide et performant, surtout sur des volumes importants de données.
Cette solution est relativement connue, ORDER BY RAND() mélange les données recueillies de manière totalement aléatoire, sans tenir compte des index ni de quoi que ce soit d’autre. Pensez à utiliser une clause LIMIT si vous ne voulez récupérer qu’un nombre défini de lignes.
SELECT * FROM `ma_table` ORDER BY RAND() LIMIT 1 ;Cette instruction prendra une ligne et une seule, au hasard. Rapide, concis et efficace.
Tri personnalisé (ORDER BY FIELD())
Pire que le tri aléatoire, il y a celui du tri personnalisé, celui qui n’est ni ascendant, ni descendant, ni aléatoire : on veut un ordre précis. Imaginons que nous nous trouvions avec la table suivante :
| jour_id | jour_nom |
|---|---|
| ven | Vendredi |
| lun | Lundi |
| dim | Dimanche |
| mer | Mercredi |
| sam | Samedi |
| jeu | Jeudi |
| mar | Mardi |
Comment faire pour récupérer la liste des jours de semaine dans l’ordre de notre choix ? Tout simplement en utilisant la fonction FIELD() (depuis MySQL 4) :
SELECT jour_nom
FROM `dates_semaine`
ORDER BY FIELD( jour_id, 'lun', 'mar', 'mer', 'jeu', 'ven', 'sam', 'dim' ) ;J’aurais tendance à dire que la requête parle d’elle-même : on spécifie un tri sur le champ jour_id dans l’ordre passé dans les paramètres suivants.
Ce qui est intéressant dans notre cas c’est qu’on peut donc gérer des semaines débutant le jeudi, le samedi, le dimanche ou le lundi (histoire de coller avec tous les calendriers existants). Seuls les paramètres à donner à la requête changeraient.
C’est également intéressant pour trier le résultat d’une requête en fonction de l’ordre des lignes d’un fichier qui aurait été uploadé sur un serveur.
Conclusion
La clause ORDER BY est plus puissante qu’il n’y paraît. Elle est garante de l’ordre des résultats retournés. Si dans certains cas l’ordre n’a pas d’importance, le contraire peut également être valable. Et c’est précisément dans ce cas que l’on aime à pouvoir manipuler les données comme on l’entend.
Il existe d’autres possibilités intéressantes en combinant par exemple le tri et les recherches FULLTEXT pour récupérer les lignes par ordre de pertinence ou pourquoi pas sur des résultats de calcul.
Commentaires
Bien vu ce petit topo sur le tri MySql, je ne connaissais ni le tri personnalisé ni l’aléatoire, merci !
Merci pour ce cours de tri trés bien expliqué, qui m’a permis de résoudre une problème de tri grâce à l’utilisation de ORDER BY FIELD()!!!!!
continuez ainsi.
Extra, cet article… C’est clair et précis
Pour pousser le bouchon plus loin, comment doit-on procéder pour trier en fonction du nombre d’enregistrements? ex.: SELECT DISTINCT code_postal FROM adresses ORDER BY (codes_postaux_les_plus_fréquents)
Où (Codes_postaux_les_plus_fréquents) serait la clé de tri, clé qui serait l’image du nombre d’enregistrements pour chaque code postal distinct?
merci
Il faudrait faire un comptage de tes codes postaux avec une requête de ce style (à vue de nez, sans tester) :
Code postal doit être un index sinon je ne parle même pas des performances qui s’effondrent
Merci,
Cela ne marche pas tel quel, mais gràce à votre code, je perçois la façon d’y arriver. Je vous tiens au courant.
Voila mon code (celui-ci fonctionne)
SELECT code_postal, COUNT(code_postal) as count FROM adresses GROUP BY code_postal ORDER BY count
Ne me demandez pas pourquoi votre code ne marche pas et le mien bien! Je n’y comprends rien… J’avoue en plus, que ma manière de faire n’est pas « du code propre »
Je vous remercie néanmoins pour votre aide
A+
Non non c’est bien aussi. Je n’avais volontairement pas créé d’alias pour montrer que MySQL est capable de reconnaître un champ calculé sans avoir à le recalculer justement. Bizarre.
L’utilisation d’alias n’est pas sale du tout au contraire, c’est là pour ça.
Merci,
je ne connaissais pas la fonction FIELD() qui est vraiment très pratique.
Une petite question doit on obligatoirement connaitre le contenu d’un champ pour pouvoir utiliser FIELD ?
Qu’est-ce que tu entends par « connaître le contenu du champ » ?
Faut que tu connaisses au moins les valeurs sur lesquelles tu veux trier. Si tu ne veux que ces valeurs, faut filtrer avec IN().
Merci effectivement. C’est clair, net et précis…
Mais pourquoi la fonction field() n’est-elle pas dans le manuel mysql ?
@Dendrite : si si c’est documenté, j’ai même indiqué un lien vers sa référence. Tu as dû rater la ligne mais c’est pas grave
Oups… Tu as encore raison !
Ceci dit, en passant par la fonction recherche du manuel… ça ne marche pas…
@Dendrite à vrai dire j’aime pas trop le moteur de recherche du site mysql.com, il retourne souvent des résultats peu intéressants pour ce que l’on cherche (FIELD() notamment). Dommage qu’on ne puisse pas avoir y avoir un moyen pour limiter la recherche à des fonctions histoire d’éviter des résultats de son API, alors qu’on s’en fiche
Merci (et merci Google pour m’avoir guidé), je ne connaissais pas le tri personnalisé dont j’avais besoin !
Merci pour l’article ! Perso. j’ai présenté un moyen de réaliser l’enregistrement et la récupération d’images en base de données au format BLOB (MySQL) (ici si ça peut intéresser : http://bit.ly/8vMEDz)
Bonjour,
j’ai un problème que la fonction FIELD() ne permet pas de résoudre (pour le moment). Pour reprendre votre exemple, j’aimerais pouvoir récupérer les jours de la semaine sur 2 semaines :
SELECT jour_nom
FROM `dates_semaine`
ORDER BY FIELD( jour_id, ‘lun’, ‘mar’, ‘mer’, ‘jeu’, ‘ven’, ‘sam’, ‘dim’, ‘lun’, ‘mar’, ‘mer’, ‘jeu’, ‘ven’, ‘sam’, ‘dim’) ;
et donc obtenir un tableau de 14 cases qui contient 2 fois chaque nom. Pour le moment, ce que je viens d’écrire me renvoie les 7 jours de la semaine une seule fois chacun, sans doublon. Quelqu’un aurait-il une idée?