Métadonnées MySQL

Les métadonnées MySQL sont des informations sur la base de données et ses objets (tels que les tables, les colonnes, les index, etc.).

Les métadonnées sont stockées dans des tables système, qui se trouvent dans la base de données information_schema de la base de données MySQL. En interrogeant ces tables système, vous pouvez obtenir des informations détaillées sur la structure de la base de données, les objets et d'autres informations connexes.

Vous pouvez souhaiter connaître les trois types d'informations suivants concernant MySQL :

  • Informations sur le résultat de la requête :Le nombre d'enregistrements affectés par les instructions SELECT, UPDATE ou DELETE.
  • Informations sur la base de données et les tables de données :Contient des informations structurelles sur les bases de données et les tables de données.
  • Informations sur le serveur MySQL :Contient l'état actuel, le numéro de version, etc. du serveur de base de données.

Dans l'invite de commande MySQL, nous pouvons facilement obtenir les informations de serveur ci-dessus, mais si vous utilisez des langages de script tels que Perl ou PHP, vous devez appeler des fonctions d'interface spécifiques pour les obtenir. Nous présenterons cela en détail ensuite.

Voici quelques requêtes de métadonnées MySQL couramment utilisées :

Afficher toutes les bases de données :

SHOW DATABASES;

Sélectionner une base de données :

USE database_name;

Afficher toutes les tables de la base de données :

SHOW TABLES;

Afficher la structure de la table :

DESC table_name;

Afficher les index de la table :

SHOW INDEX FROM table_name;

Afficher l'instruction de création de la table :

SHOW CREATE TABLE table_name;

Afficher le nombre de lignes de la table :

SELECT COUNT(*) FROM table_name;

Afficher les informations sur les colonnes :

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME = 'your_table_name';

Dans les instructions SQL ci-dessus, 'your_database_name' et 'your_table_name' sont respectivement le nom de votre base de données et le nom de votre table.

Afficher les informations sur les clés étrangères :

SELECT
    TABLE_NAME,
    COLUMN_NAME,
    CONSTRAINT_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    TABLE_SCHEMA = 'your_database_name'
    AND TABLE_NAME = 'your_table_name'
    AND REFERENCED_TABLE_NAME IS NOT NULL;

Veuillez remplacer 'your_database_name' et 'your_table_name' dans les instructions SQL ci-dessus par le nom réel de la base de données et le nom réel de la table.


Base de données information_schema

information_schema est une base de données système dans la base de données MySQL. Elle contient des informations de métadonnées sur le serveur de base de données, et ces informations sont stockées sous forme de tables dans la base de données information_schema.

Table SCHEMATA

Stocke des informations sur les bases de données, telles que le nom de la base de données, le jeu de caractères, l'interclassement, etc.

SELECT * FROM information_schema.SCHEMATA;

Table TABLES

Contient des informations sur toutes les tables de la base de données, telles que le nom de la table, le nom de la base de données, le moteur, le nombre de lignes, etc.

SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database_name';

Table COLUMNS

Contient des informations sur les colonnes des tables, telles que le nom de la colonne, le type de données, si NULL est autorisé, etc.

SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Table STATISTICS

Fournit des informations statistiques sur les index des tables, telles que le nom de l'index, le nom de la colonne, l'unicité, etc.

SELECT * FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Table KEY_COLUMN_USAGE

Contient des informations sur les clés étrangères dans les tables, telles que le nom de la clé étrangère, le nom de la colonne, la table référencée, etc.

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Table REFERENTIAL_CONSTRAINTS

Stocke des informations sur les contraintes de clé étrangère, telles que le nom de la contrainte, la table référencée, etc.

SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';

Ces tables fournissent de riches informations de métadonnées qui peuvent être utilisées pour interroger la structure de la base de données, les informations sur les tables, les informations sur les colonnes, les informations sur les index, etc.

Veuillez noter que vous devez remplacer 'your_database_name' et 'your_table_name' dans les requêtes par le nom réel de la base de données et de la table.


Obtenez le nombre d'enregistrements affectés par une instruction de requête

Exemple PERL

Dans les scripts DBI, le nombre d'enregistrements affectés par une instruction est renvoyé par la fonction do( ) ou execute( ) :

# 方法 1
# 使用do( ) 执行  $query 
my $count = $dbh->do ($query);
# 如果发生错误会输出 0
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

# 方法 2
# 使用prepare( ) 及 execute( ) 执行  $query 
my $sth = $dbh->prepare ($query);
my $count = $sth->execute ( );
printf "%d 条数据被影响\n", (defined ($count) ? $count : 0);

Exemple PHP

En PHP, vous pouvez utiliser la fonction mysqli_affected_rows( ) pour obtenir le nombre d'enregistrements affectés par une instruction de requête.

$result_id = mysqli_query ($conn_id, $query);
# 如果查询失败返回 
$count = ($result_id ? mysqli_affected_rows ($conn_id) : 0);
print ("$count 条数据被影响\n");

Liste des bases de données et des tables de données

Vous pouvez facilement obtenir la liste des bases de données et des tables de données dans le serveur MySQL. Si vous ne disposez pas de permissions suffisantes, le résultat renverra null.

Vous pouvez également utiliser les instructions SHOW TABLES ou SHOW DATABASES pour obtenir la liste des bases de données et des tables de données.

Exemple PERL

# 获取当前数据库中所有可用的表。
my @tables = $dbh->tables ( );
foreach $table (@tables ){
   print "表名 $table\n";
}

Exemple PHP

L'exemple suivant affiche toutes les bases de données sur le serveur MySQL :

Afficher toutes les bases de données

<?php $dbhost = 'localhost'; //adresse de l'hôte du serveur MySQL $dbuser = 'root'; //nom d'utilisateur MySQL $dbpass = '123456'; //Mot de passe de l'utilisateur MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Échec de la connexion :' . mysqli_error($conn)); } //Définir l'encodage pour éviter les caractères chinois corrompus $db_list = mysqli_query($conn, 'SHOW DATABASES'); while ($db = mysqli_fetch_object($db_list)) { echo $db->Database . "<br />"; } mysqli_close($conn); ?>

Obtenir les métadonnées du serveur

Les instructions de commande suivantes peuvent être utilisées dans l'invite de commande MySQL, et peuvent également être utilisées dans des scripts, tels que des scripts PHP.

CommandeDescription
SELECT VERSION( )Informations sur la version du serveur
SELECT DATABASE( )Nom de la base de données actuelle (ou renvoie vide)
SELECT USER( )Nom d'utilisateur actuel
SHOW STATUSÉtat du serveur
SHOW VARIABLESVariables de configuration du serveur
Autres extensions