MySQL/Parcourir les bases de données

Base information_schema dans phpMyAdmin.

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'

Références

modifier