Aide-mémoire MySQL ou MariaDB

Quelques trucs à retenir

Lire la taille d'une base de données en Mo :
SELECT SUM((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nom_de_ma_base'
Récupérer l'espace disque après avoir fait un gros DELETE :
OPTIMIZE TABLE nom_de_ma_table
Remettre la valeur d'auto-incrément à 0 :
ALTER TABLE nom_de_ma_table AUTO_INCREMENT = 0
Lire les logs de requêtes sous forme de requête :
```
SET GLOBAL log_output = 'table';
SET GLOBAL general_log = 1;
SELECT * FROM mysql.general_log WHERE argument LIKE 'delete%';
```
Ne pas oublier ensuite : SET GLOBAL general_log = 0; sinon ça remplit vite le disque !

Pour vider ces logs : TRUNCATE TABLE mysql.general_log; (pas DELETE… sinon erreur "You can't use locks with log tables")

Rappel pour voir les variables : SHOW GLOBAL VARIABLES LIKE 'log_output' (par défaut c'est SESSION, pas GLOBAL, cf la doc)
Il faut utiliser SHOW EXPLAIN plutôt que EXPLAIN parce que EXPLAIN ne tient pas compte de toutes les optimisations du SELECT. Voir la doc.
Pour supprimer une floppée de tables commençant par le même préfixe, on ne peut pas faire DROP TABLE LIKE 'foobar\_%' mais on peut ruser avec un dump intermédiaire des tables qu'on veut garder :
sudo mysql -N -e 'show tables like "bidule\_%"' nom_base | xargs sudo mysqldump nom_base > nom_base.mysql
Ensuite, on vérifie le fichier tranquillement, et si c'est bon :
- drop puis create la base
- et on envoie le fichier dans sudo mysql nom_base < nom_base.mysql
Rechercher des valeurs qui ne sont pas dans la base. Par exemple, si je veux afficher "foobar" parce que ça ne figure pas dans la base, mais je ne veux pas afficher "barbatruc" qui y figure bien :
SELECT "foobar" WHERE NOT EXISTS (SELECT * FROM ma_table WHERE un_champ = "foobar") retourne "foobar"
SELECT "barbatruc" WHERE NOT EXISTS (SELECT * FROM ma_table WHERE un_champ = "barbatruc") ne retourne rien.
Pour gérer des coordonnées GPS avec le type géométrique POINT, il faut mettre dans cet ordre :
POINT(longitude, latitude)
Sinon ça fausse le calcul de distance avec la fonction ST_Distance_Sphere(POINT(…, …), POINT(…, …))
Pour afficher les coordonnées, utiliser ST_AsText(…) ou juste la latitude ST_Y(…), juste la longitude ST_X(…)

Grouper des chaînes de caractères par leur longueur avec des paliers de 50 caractères :

SELECT 50 * (length(commentaire) div 50) as 'de',
    50 * (length(commentaire) div 50) + 49 as 'à',
    COUNT(*) FROM ma_table GROUP BY length(commentaire) div 50;

Ce qui va donner ce genre de résultat :

+------+------+----------+
| de   | à    | COUNT(*) |
+------+------+----------+
|    0 |   49 |      615 |
|   50 |   99 |      374 |
|  100 |  149 |      217 |
|  150 |  199 |      100 |
|  200 |  249 |       45 |
|  250 |  299 |       42 |
|  300 |  349 |       25 |
|  350 |  399 |        9 |
|  400 |  449 |       17 |
|  450 |  499 |        5 |
|  500 |  549 |        1 |
|  550 |  599 |        1 |
|  600 |  649 |        1 |
|  650 |  699 |        3 |
+------+------+----------+

Des curseurs, je n'en utilise pas tous les jours. Et lire la doc dans l'urgence, c'est un peu galère. Donc je garde ici un exemple minimal inspiré d'un travail que j'ai eu à faire un jour :

DROP PROCEDURE IF EXISTS curseurexemple;

DELIMITER //

CREATE PROCEDURE curseurexemple()
BEGIN
    DECLARE fin INT DEFAULT FALSE;
    DECLARE var1 VARCHAR(50);
    DECLARE var2 DATETIME;
    DECLARE curseur1 CURSOR FOR SELECT colonne1, colonne2 FROM ma_table WHERE …;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin = TRUE;

    OPEN curseur1;

    boucle1: LOOP

        FETCH curseur1 INTO var1, var2;

        IF fin THEN
            LEAVE boucle1;
        END IF;

        DELETE FROM autre_table WHERE truc = var1 AND chose > var2;

    END LOOP;

    CLOSE curseur1;
END//

DELIMITER ;

CALL curseurexemple;

L'idée, c'est de récupérer des infos d'une table dans des variables (ici var1 et var2 en rouge), et pouvoir les utiliser dans une manip sur une autre table qui ne serait pas forcément facile à faire en une seule requête. Ce SELECT et ce DELETE sont juste des exemples pour situer, mais on peut imaginer des requêtes beaucoup plus compliquées.