MySQL

La source de tout: la doc!

Cet article se base énormément sur la documentation de MySQL (8.0) , à laquelle je vous invite à vous référer régulièrement. Pensez aussi à suivre les nouveautés à chaque nouvelle version de MySQL .

Types de colonnes

Les types de colonnes (en fait, les types de données que MySQL sait manipuler) sont listés dans le chapitre 11 de la doc MySQL . Les plus intéressants (exotiques) sont BIT BOOL TIMESTAMP ENUM SET BLOB GEOMETRY POINT LINESTRING POLYGON JSON et les variantes BINARY|CHARACTER SET|ASCII|UNICODE pour les types "string" et UNSIGNED|ZEROFILL pour les nombres. Je vous laisse lire le détail de ce chapitre pour plus d'infos.

Dates et timestamps

MySQL n'enregistre pas la timezone d'un DATETIME: ce type de colonne stocke un concept de date (Noël) et non un point de la ligne du temps (utilisez TIMESTAMP pour ça).
Attention aussi à la timzone de votre client (PHP) et de la connexion (SET time_zone = '00:00';).

CREATE TABLE tx ( tz VARCHAR(20) NOT NULL, d DATETIME NOT NULL, t TIMESTAMP NOT NULL ); SET time_zone = '+00:00'; INSERT INTO tx VALUES (@@time_zone, NOW(), NOW()); SELECT SLEEP(2); SELECT tz, d, t, NOW(), CURRENT_TIMESTAMP(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(t) FROM tx; SET time_zone = '+03:00'; INSERT INTO tx VALUES (@@time_zone, NOW(), NOW()); SELECT SLEEP(2); SELECT tz, d, t, NOW(), CURRENT_TIMESTAMP(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(t) FROM tx; SET time_zone = '+00:00'; SELECT tz, d, t, NOW(), CURRENT_TIMESTAMP(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(t) FROM tx; DROP TABLE tx; La connection utilise la même timezone (heure hiver/été incluse) à l'insertion et à la selection, Date et Timestamp sont consistants Les timezones à l'insertion et à la selection diffèrent: on a une incohérence avec Date mais pas avec Timestamp Idem si on revient à la timezone UTC initiale
Comparatif entre Date (timezone-dependant) et Timestamp (timezone-agnostic)

Dans cet exemple, on insère une même date UTC (time_zone à 00:00) dans une colonne Date et une colonne Timestamp, qu'on récupère en UTC: pas de soucis pour aucune des deux colonnes: les timezone à l'insertion à la lecture sont les mêmes.
Ensuite, on change la timezone (03:00) puis on insère à nouveau une même date dans les deux colonnes: là, problème pour le type Date, car la première ligne indique toujours 9h alors que la seconde indique 12h.

En pratique, MySQL a sauvé le texte 2019-10-18 09:28:02 issu de la timezone au moment de l'insertion dans la première colonne, et renverra toujours ce texte quelque soit la timezone de la connection (on le voit dans la 3e screen, à nouveau en UTC): si elles ne sont pas identiques, on a une incohérence.
Avec une colonne Timestamp, Mysql convertit la date actuelle en Timestamp (sans timezone) au moment de l'insertion (en prenant donc en compte la timezone de la connection à ce moment-là) et sauve cette valeur (entier) dans la colonne. A la lecture, cette valeur est convertie de nouveau dans la timezone de la connection et la date est consistante: si on ajoute 3h à la timezone de la connection (en passant de 00:00 à 03:00) alors la date affichée est décalée aussi de 3h, correspondant donc au même moment sur la "ligne du temps réel".

Vous aurez presque toujours besoin d'un TIMESTAMP, quasiment jamais d'un DATETIME.

Concepts utiles

Je ne paraphraserait pas la doc, mais les concepts (avancés) suivants vous seront sûrement utiles.
CHECK : valide une contrainte sur une colonne/une table entière pour chaque insertion, modification, etc
VIEW : une forme de "query stockée" ou de "table virtuelle"
CREATE PROCEDURE/CREATE FUNCTION : du code stocké dans le serveur SQL, pour éviter les allers-retours avec PHP/Java/etc. Permettent aussi d'utiliser les boucles (while), les conditions (if), les "exceptions" ( signal / handler ) etc
Generated columns (virtual columns) : des colonnes dont la valeur est calculée à la volée, ou à chaque insertion/modification de la table. Très utile pour stockée des "max id" d'un historique, ou un booléen indiquant si une ligne est celle d'id ou de date la plus élevée, etc
JSON_TABLE : convertit du JSON en une table SQL; pensez aussi à lire les autres fonctions JSON disponibles .
ENGINE=FEDERATED : permet de stocker les données d'une table sur un autre serveur (= accéder aux tables d'un serveur Y depuis un serveur X).
Extension "GEOMETRY" : permet de stocker, indexer, et manipuler des données géométriques en 2D (idéal pour une carte de jeu web!)
Window Functions : calculs sur des groupes de lignes dans un resultset

SET @idMax := (SELECT MAX(id) FROM so_place); SELECT * FROM ( SELECT DISTINCT FLOOR(RAND()*@idMax+1) AS id_place, id, name FROM so_object) AS t INNER JOIN so_place AS p ON p.id = t.id_place;
Sans "DISTINCT", MySQL ≥5.7 ré-utilisera la même valeur de RAND au lieu de générer une valeur par ligne

UTF-8

Utilisez toujours le CHARACTER SET 'utf8mb4' et SET NAMES 'utf8mb4' pour toutes vos colonnes stockant des textes issus des utilisateurs, sans certains textes ne seront pas stockables en BDD et génèreront une erreur (emojis, caractères chinois spécifiques, etc).

Sélectionner les 3 plus grands …

-- MySQL 8 SELECT ROW_NUMBER() OVER (PARTITION BY joueur.id_alliance ORDER BY joueur.points DESC) AS rk, * FROM joueur ORDER BY joueur.id_alliance, joueur.points DESC HAVING rk <= 3
Sélectionner les infos des 3 plus forts joueurs de chaque alliance

N'utilisez pas de variable (@xyz) dans les SELECT: la documentation l'a déprécié, et déconseille l'assignement en dehors d'un SET!

Récupérer une ligne au hasard

SELECT * FROM table WHERE id >= FLOOR(RAND()*(SELECT MAX(id) FROM table)) ORDER BY id ASC LIMIT 1
Evitez ORDER BY RAND() LIMIT 1 (très lourd)

Cette approche n'est pas tout à fait equiprobable si vous avez des "trous" dans les ids.

Compter les lignes répondant à une condition

SELECT SUM(1) AS casesTotal, SUM(mc.type = 'TERRE') AS casesTerrestres, SUM(mc.type = 'MER') AS casesMaritimes, SUM(b.id IS NOT NULL) AS casesOccupees, SUM(b.id IS NULL) AS casesVides FROM map_cases AS mc LEFT JOIN batiments AS b ON b.id = mc.id_batiment WHERE mc.id_pays = ?
Compte le nombre de cases terrestres, maritimes, occupées et vides du territoire d'une carte de jeu

Historiser une table

Pour historiser une table à chaque modification, utilisez un TRIGGER qui va se charger de copier la ligne existante de la table à historiser vers une table d'archive, ou qui marquera la ligne comme étant une ligne d'historique.

Cette historisation sauve toutes les infos d'un objet. Il est souvent plus intéressant de créer une table dédiée aux actions faites, et d'enregistrer ces actions quand elles ont lieu (log).

UPDATE et ordre des colonnes

Dans un UPDATE, l'ordre des colonnes est important car MySQL utilisera la nouvelle valeur des colonnes précédente pour mettre à jour la colonne suivante. Par exemple, dans UPDATE ... SET c = 1, c = c+1, MySQL va d'abord mettre la valeur de la colonne c à 1, puis à c+1. Elle vaudra donc 2.

Les commandes SQL (queries)

La doc MySQL contient de nombreuses petites variantes aux syntaxes de base, dont les plus exotiques sont:

Optimisations, intégrité

Je ne ferai ici qu'un tour rapide des pistes possibles pour optimiser la partie DB de votre jeu web. Consultez le chapitre 8 de la doc pour des détails plus poussés.

L'optimiseur de MySQL ≥5.7

L'optimiseur de MySQL ≥5.7 a parfois tendance à trop simplifier les requêtes imbriquées, cf ma question sur StackOverflow ). Une query peut donc renvoyer des résultats différents entre Mysql 5.6 et MySQL 5.7.

Si besoin, matérialisez les subqueries via DISTINCT par exemple.