Метаданные MySQL

Метаданные MySQL — это информация о базе данных и её объектах (таких как таблицы, столбцы, индексы и т. д.).

Метаданные хранятся в системных таблицах, которые находятся в базе данных information_schema базы данных MySQL. С помощью запросов к этим системным таблицам можно получить подробную информацию о структуре базы данных, объектах и другую связанную информацию.

Возможно, вы хотите узнать следующие три вида информации о MySQL:

• Информация о результате запроса:Количество записей, затронутых операторами SELECT, UPDATE или DELETE.
• Информация о базе данных и таблицах данных:Содержит структурную информацию о базах данных и таблицах данных.
• Информация о сервере MySQL:Содержит текущее состояние, номер версии и т. д. сервера базы данных.

В командной строке MySQL мы можем легко получить указанную выше информацию о сервере, но если вы используете скриптовые языки, такие как Perl или PHP, для её получения нужно вызывать определённые функции интерфейса. Подробно мы расскажем об этом далее.

Ниже приведены некоторые часто используемые запросы метаданных MySQL:

Просмотр всех баз данных:

SHOW DATABASES;

Выбор базы данных:

USE database_name;

Просмотр всех таблиц в базе данных:

SHOW TABLES;

Просмотр структуры таблицы:

DESC table_name;

Просмотр индексов таблицы:

SHOW INDEX FROM table_name;

Просмотр оператора создания таблицы:

SHOW CREATE TABLE table_name;

Просмотр количества строк в таблице:

SELECT COUNT(*) FROM table_name;

Просмотр информации о столбцах:

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';

В приведённых выше SQL-операторах 'your_database_name' и 'your_table_name' — это соответственно имя вашей базы данных и имя вашей таблицы.

Просмотр информации о внешних ключах:

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;

Пожалуйста, замените 'your_database_name' и 'your_table_name' в приведённых выше SQL-операторах на фактические имя базы данных и имя таблицы.

---

База данных information_schema

information_schema — это системная база данных в базе данных MySQL. Она содержит информацию метаданных о сервере базы данных, и эта информация хранится в виде таблиц в базе данных information_schema.

Таблица SCHEMATA

Хранит информацию о базах данных, таких как имя базы данных, набор символов, сопоставление и т. д.

SELECT * FROM information_schema.SCHEMATA;

Таблица TABLES

Содержит информацию обо всех таблицах в базе данных, таких как имя таблицы, имя базы данных, движок, количество строк и т. д.

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

Таблица COLUMNS

Содержит информацию о столбцах в таблицах, таких как имя столбца, тип данных, допускается ли NULL, и т. д.

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

Таблица STATISTICS

Предоставляет статистическую информацию об индексах таблицы, таких как имя индекса, имя столбца, уникальность и т. д.

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

Таблица KEY_COLUMN_USAGE

Содержит информацию о внешних ключах в таблицах, таких как имя внешнего ключа, имя столбца, таблица, на которую ссылаются, и т. д.

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

Таблица REFERENTIAL_CONSTRAINTS

Хранит информацию об ограничениях внешних ключей, таких как имя ограничения, таблица, на которую ссылаются, и т. д.

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

Эти таблицы предоставляют богатую информацию метаданных, которую можно использовать для запроса структуры базы данных, информации о таблицах, информации о столбцах, информации об индексах и т. д.

Обратите внимание, что вам нужно заменить 'your_database_name' и 'your_table_name' в запросах на фактическое имя базы данных и имя таблицы.

---

Получить количество записей, затронутых оператором запроса

Пример PERL

В скриптах DBI количество записей, затронутых оператором, возвращается через функцию do( ) или 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);

Пример PHP

В PHP можно использовать функцию mysqli_affected_rows( ), чтобы получить количество записей, затронутых запросом.

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

---

Список баз данных и таблиц данных

Вы можете легко получить список баз данных и таблиц данных на сервере MySQL. Если у вас недостаточно прав, результат вернёт null.

Вы также можете использовать операторы SHOW TABLES или SHOW DATABASES, чтобы получить список баз данных и таблиц данных.

Пример PERL

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

Пример PHP

Следующий пример выводит все базы данных на сервере MySQL:

---

Получить метаданные сервера

Следующие команды можно использовать в командной строке MySQL, а также в скриптах, таких как PHP-скрипты.

Команда	Описание
SELECT VERSION( )	Информация о версии сервера
SELECT DATABASE( )	Имя текущей базы данных (или возвращает пустое значение)
SELECT USER( )	Текущее имя пользователя
SHOW STATUS	Статус сервера
SHOW VARIABLES	Переменные конфигурации сервера

Другие расширения