MySQL/Parcourir les bases de données
mysqlModifier
mysql
est une base de données système contenant des variables propres au serveur, telles que le fuseau horaire[1].
Il est possible d'y stocker l'historique des requêtes entrées sur le serveur en activant :
SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';
Elles apparaissent ainsi dans la table general_log, ce qui peut être pratique pour déboguer une application en boite noire. Par contre il faut vite les désactiver après la mesure car elles peuvent devenir très volumineuses.
SET GLOBAL general_log = 'OFF';
SELECT * FROM mysql.general_log;
INFORMATION_SCHEMAModifier
information_schema
est une base de données virtuelle apparue dans MySQL 5, qui contient des métadonnées sur le serveur et ses bases. Elle se remplit automatiquement et n'est pas modifiable (ni la structure, ni les données), on peut donc juste la lire.
Beaucoup de ses informations sont récupérables aussi avec la commande SHOW
, plus rapide. Toutefois information_schema
est plus flexible.
La table de INFORMATION_SCHEMA sur les bases est SCHEMATA. Le programme mysqlshow
(en ligne de commande DOS/Unix) peut aussi être utilisé à la place.
Cela ne peut fonctionner que si le serveur est démarré, et sans l'option --skip-all-databases
.
En l'absence des privilèges SHOW DATABASES
, seule les bases sur lesquelles le compte a des permissions seront visibles.
Exemples d'utilisationModifier
Rechercher un nom de colonne dans toutes les tables d'une baseModifier
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ma_recherche%';
Lister les tables les plus volumineusesModifier
Pour trier les tables par taille :
SELECT TABLE_NAME, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH
FROM information_schema.TABLES
WHERE table_schema = "ma_base"
ORDER BY DATA_LENGTH DESC;
Pour avoir la taille d'une table, ajouter :
AND table_name = "ma_table";
Lister les basesModifier
Les commandes SQL suivantes fournissent les informations relatives aux bases de données situées sur le serveur courant.
Toutes :
SHOW DATABASES;
le mot clé SCHEMA
peut être utilisé en lieu et place de DATABASES
. MySQL ne supporte pas les SCHEMA
des standards SQL, donc il est synonyme de DATABASES
. il a été ajouté pour la compatibilité avec d'autres SGBD.
Ajouter un filtre sur les noms des basesModifier
SHOW DATABASES LIKE 'expression';
L'opérateur LIKE
fonctionne selon le langage de manipulation de données standard. Donc il est faisable de lister toutes les bases commençant par 'wiki' ainsi :
SHOW DATABASES LIKE 'wiki%';
Filtres complexesModifier
En utilisant la clause WHERE
:
SHOW DATABASES WHERE conditions;
Elle autorise les expressions rationnelles, les opérateur de comparaison '=', '<' et '>', et les fonctions sur les chaînes de caractères.
Lister les tables et les vuesModifier
Les tables `TABLES` et `VIEWS` de la base INFORMATION_SCHEMA fournissent des informations sur les tables et les vues de toutes les bases du serveur.
Les commandes SQL suivantes donnant relativement peu d'information sur les vues, il faudra recourir à la table `VIEWS` pour les métadonnées.
mysqlshow
peut aussi être utilisé à la place.
Show all tablesModifier
USE `database`;
SHOW TABLES;
SHOW TABLES FROM `database`;
Les deux formes sont équivalentes.
Appliquer un filtreModifier
La syntaxe est la même que pour les bases :
SHOW TABLES LIKE `expression`;
SHOW TABLES WHERE condition;
De plus, par défaut SHOW TABLES
ne retourne que la colonne du nom des tables. Le mot FULL
permet d'en ajouter une deuxième appelée `Table_type` :
SHOW FULL TABLES;
Elle peut contenir trois valeurs différentes : 'BASE TABLE' pour les tables, 'VIEW' pour les vues, et 'SYSTEM VIEW' pour les tables spéciales du serveur (généralement celles de la base INFORMATION_SCHEMA).
Donc pour lister les vues :
SHOW FULL TABLES WHERE `Table_type`='VIEW';
Filtrer les tables ouvertesModifier
La liste des tables non temporaires (sans les vues) ouvertes dans le cache :
SHOW OPEN TABLES;
Supprimer toutes les tablesModifier
Il faut exécuter le résultat de cette requête :
SELECT CONCAT('DROP ', table_name, ';') as stmt
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'nom_de_la_base';
Supprimer toutes les vuesModifier
SELECT CONCAT('DROP VIEW ', table_name, ';') as stmt
FROM information_schema.views
WHERE TABLE_SCHEMA = 'nom_de_la_base';
Supprimer toutes les procédures stockéesModifier
SELECT CONCAT('DROP ', ROUTINE_TYPE, ' `', ROUTINE_SCHEMA, '`.`', ROUTINE_NAME, '`;') as stmt
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'nom_de_la_base';
Lister les champsModifier
Les commandes suivantes correspondent aux informations de la table COLUMNS de INFORMATION_SCHEMA.
mysqlshow
le permet également.
DESCRIBEModifier
USE `base`;
DESCRIBE `table`;
-- ou
DESCRIBE `base`.`table`;
Le résultat contient six colonnes :
Field | Type | Null | Key | Default | Extra |
---|---|---|---|---|---|
... | ... | ... | ... | ... | ... |
DESC
est un alias de DESCRIBE
.
USE `base`;
DESC `table` 'filtre';
'filtre' peut être un nom de colonne. S'il est spécifié, seule cette colonne sera affichée. Si 'filtre' contient '%' ou '_', il sera évalué comme une condition LIKE
. Par exemple, pour obtenir tous les champs commençant par 'wiki' :
DESC `table` 'wiki%';
EXPLAINModifier
Synonyme de DESC
:
EXPLAIN `table`;
SHOW FIELDSModifier
Autre synonyme de DESC
:
SHOW FIELDS FROM `table`;
Remarque : le mot FULL rajoute une colonne "Privileges" et une "Comment" :
SHOW FULL FIELDS FROM `table`;
Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
---|---|---|---|---|---|---|---|---|
... | ... | ... | ... | ... | ... | ... | ... | ... |
La colonne des commentaires peut servir à expliquer la signification du nom d'un champ, en apparaissant sous ce dernier lors des sélection dans PhpMyAdmin, et peut même être interprétée par certains logiciels. Par exemple, l'ORM Doctrine comprend le commentaire "DC2Type:array" pour choisir comment désérialiser les tableaux JSON stockés en LONGTEXT.
SHOW COLUMNSModifier
Autre synonyme de DESC
:
SHOW COLUMNS FROM `table`;
En fait FIELDS
et COLUMNS
sont synonymes. EXPLAIN et DESC ne supportent pas toutes leurs clauses (filtre).
De plus, les syntaxes ci-dessous sot équivalentes :
SHOW COLUMNS FROM `table` FROM `base`;
-- ou
SHOW COLUMNS FROM `base`.`table`;
Lister les indexesModifier
Les commande suivantes renseignent sur les indexes d'une table, ses clés. Elles sont aussi dans la table `COLUMNS` de INFORMATION_SCHEMA, et accessibles via mysqlshow -k
.
SHOW INDEX FROM `TABLE`;
SHOW INDEX FROM `TABLE` FROM `bases`;
Exemple de résultat :
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
---|---|---|---|---|---|---|---|---|---|---|---|---|
Table1 | 0 | PRIMARY | 1 | id | A | 19 | NULL | NULL | BTREE |
Le mot KEYS
est synonyme de INDEX
. Aucune autre clause n'est possible avec.
Avec phpMyAdmin il est facile de créer plusieurs fois le même index, ce qui ralentit ensuite toutes les requêtes.
Pour supprimer un index :
DROP INDEX `date_2` on `Table1`
Un alias existe aussi : SHOW KEYS FROM `TABLE`;
.
Lister les clés étrangèresModifier
Pour le nom des clés étrangères d'une table :
SELECT column_name, constraint_name FROM `information_schema`.`KEY_COLUMN_USAGE` where table_name = 'maTable'