MySQL/Parcourir les bases de données
mysql Modifier
mysql est une base de données système contenant des variables propres au serveur, telles que les comptes utilisateurs (dans la table "user"), le fuseau horaire (dans la table "time_zone") ou les logs[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_SCHEMA Modifier
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'utilisation Modifier
Rechercher un nom de colonne dans toutes les tables d'une base Modifier
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ma_recherche%';
Lister les tables les plus volumineuses Modifier
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 bases Modifier
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 bases Modifier
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 complexes Modifier
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 vues Modifier
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 tables Modifier
USE `database`;
SHOW TABLES;
SHOW TABLES FROM `database`;
Les deux formes sont équivalentes.
Appliquer un filtre Modifier
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 ouvertes Modifier
La liste des tables non temporaires (sans les vues) ouvertes dans le cache :
SHOW OPEN TABLES;
Supprimer toutes les tables Modifier
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 vues Modifier
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ées Modifier
SELECT CONCAT('DROP ', ROUTINE_TYPE, ' `', ROUTINE_SCHEMA, '`.`', ROUTINE_NAME, '`;') as stmt
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'nom_de_la_base';
Lister les champs Modifier
Les commandes suivantes correspondent aux informations de la table COLUMNS de INFORMATION_SCHEMA.
mysqlshow le permet également.
DESCRIBE Modifier
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%';
EXPLAIN Modifier
Synonyme de DESC :
EXPLAIN `table`;
SHOW FIELDS Modifier
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 COLUMNS Modifier
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 indexes Modifier
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ères Modifier
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'