MySQL avancé
La suite des Bases du SQL : sous-requêtes et WITH, transactions et verrous, clés étrangères, analyse des requêtes lentes, UNION et vues, fonctions de fenêtrage, jeux de caractères et collation, et sauvegardes de base de données. Tous les exemples sont vérifiés sous MySQL et MariaDB.
Une sous-requête est un SELECT à l’intérieur d’une autre requête. Elle sert lorsque la condition dépend d’autres données – les produits plus chers que la moyenne, les clients sans commande, l’article le plus cher de chaque catégorie. Comment écrire des sous-requêtes, pourquoi NOT IN peut renvoyer un résultat vide et comment mettre de l’ordre dans une longue requête avec WITH.
Deux clients achètent à la même seconde le dernier article en stock. Qui l’obtient et comment s’assurer qu’il ne soit pas vendu deux fois ? Transactions, verrous de lignes, niveaux d’isolation, interblocages et commande sûre depuis PHP.
Une commande d’un client qui n’existe pas, ou un client supprimé avec tout l’historique de ses commandes. Une clé étrangère empêche ce genre d’erreurs directement dans la base de données, quel que soit le script qui modifie les données. Comment l’ajouter à une table existante, ce que font les options ON DELETE et à quoi faire attention lors d’un import.
Un site qui était rapide avec quelques centaines d’enregistrements devient de plus en plus lent au fil des années. Comment trouver les requêtes lentes, comment lire le résultat d’EXPLAIN, colonne Extra comprise, et comment accélérer une requête avec le bon index – sur une table de dix mille lignes.
UNION place les résultats de plusieurs requêtes les uns sous les autres, une vue (VIEW) enregistre une requête complexe sous un nom, de sorte qu’on peut l’utiliser comme une table. Quand utiliser UNION et quand UNION ALL, comment obtenir l’intersection et la différence et pourquoi une vue cesse de fonctionner après le déplacement de la base de données.
Les fonctions de fenêtrage calculent comme GROUP BY sur plusieurs lignes, mais conservent les lignes dans le résultat. Le cumul des ventes, le classement dans une catégorie, les trois produits les plus vendus de chaque groupe ou l’évolution par rapport au mois précédent – chacune de ces tâches tient en une seule requête.
Des caractères bizarres à la place des lettres accentuées, des points d’interrogation à la place des emoji ou l’erreur « Illegal mix of collations » lors de la jointure de deux tables. Tout cela a la même origine : les jeux de caractères et la collation. Où ils se définissent, comment réparer un texte déjà abîmé et comment convertir une ancienne base de données en utf8mb4.
Une sauvegarde que personne n’a encore essayé de restaurer n’est qu’un espoir. Comment sauvegarder une base de données avec mysqldump (sous MariaDB mariadb-dump) avec les bonnes options, comment la restaurer, comment faire en sorte que la sauvegarde se fasse automatiquement chaque nuit et comment vérifier qu’elle est complète.