MySQL/Parcourir les bases de données

MySQL

1. Introduction
2. PhpMyAdmin
3. Parcourir les bases de données
4. Spécifier les noms
5. LDD, LMD et LCD
6. Variables utilisateurs
7. Alias
8. Types de données
9. Manipulation de base
   • mysqldump
10. Manipulation de table
11. Manipulation de données
12. Requêtes
13. NULL
14. Opérateurs
15. Fonctions
16. Procédures stockées
17. Regex
18. Bases de données spatiales
19. Importer et exporter
20. Réplication
21. Optimisation
22. API
23. Sécurité
24. Mots réservés
25. Débogage

Base information_schema dans phpMyAdmin.

mysql

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.

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

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

Rechercher un nom de colonne dans toutes les tables d'une base

SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%ma_recherche%';

Lister les tables les plus volumineuses

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

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

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

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

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

 USE `database`;
 SHOW TABLES;

 SHOW TABLES FROM `database`;

Les deux formes sont équivalentes.

Appliquer un filtre

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

La liste des tables non temporaires (sans les vues) ouvertes dans le cache :

SHOW OPEN TABLES;

Supprimer toutes les tables

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

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

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

Les commandes suivantes correspondent aux informations de la table COLUMNS de INFORMATION_SCHEMA.

mysqlshow le permet également.

DESCRIBE

 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

Synonyme de DESC :

EXPLAIN `table`;

SHOW FIELDS

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

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

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

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'