Работа с INFORMATION_SCHEMA. Генерация sql команд.

Использование таблиц INFORMATION_SCHEMA для генерации sql команд.

Еще один пример использования данных из INFORMATION_SCHEMA – генерация sql команд. Предположим, мы хотим сделать копию таблиц какой-либо базы данных, добавив к именам таблиц окончание “_backup”.

shell>mysql -uroot -p –silent –skip-column-names -e
“SELECT CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME,
‘_backup LIKE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dev’;”

В результате получим следующие sql команды на стандартный вывод (stdout):

shell>CREATE TABLE dev.departments_backup LIKE dev.departments;
CREATE TABLE dev.documents_backup LIKE dev.documents;

CREATE TABLE dev.users_backup LIKE dev.users;

Опция “–silent” используется для того, чтобы убрать из вывода форматирование (это форматирование делает похожим вывод на таблицу).
Опция  –skip-column-names” используется для того, чтобы убрать из вывода заголовки столбцов.

С помощью символа pipe ‘|’ можно перенаправить полученные sql команды для исполнения в mysql.

shell>mysql -uroot -p –silent –skip-column-names -e
“SELECT CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME,
‘_backup LIKE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘dev’;” | mysql -uroot -pPASSWORD

Работа с INFORMATION_SCHEMA. Генерация shell-скриптов.

Использование таблиц INFORMATION_SCHEMA для генерации shell-скрипта.

Еще один полезный прием, который необходимо знать администраторам MySQL сервера, – генерация shell-скриптов на основе данных, из таблиц INFORMATION_SCHEMA. Полученный shell скрипт может быть запущен из командной строки. Это можно использовать, например, для создания скриптов резервного копирования каких-либо таблиц или баз данных.

Рассмотрим, конкретный пример. Предположим, нам нужно сделать скрипт для выгрузки из базы данных двух таблиц с именами: USERS и DEPARTMENTS.
Использую команду SELECT с выводом результатов во внешний файл и функциию CONCAT для объединения строк, мы получим нужный нам скрипт.

#mysql> select concat(“mysqldump -uroot -p123456 “,
  -> table_schema,” “,table_name, ” >> “,table_schema, “.sql”)
  -> from tables where table_name in (‘users’,’departments’)
  -> into outfile ‘/tmp/mydump.sh’;
Query OK, 4 rows affected (0.02 sec)

Теперь посмотрим, что в итоге у нас получилось:

#cat /tmp/mydump.sh
mysqldump -uroot -p123456 dev USERS >> dev.sql
mysqldump -uroot -p123456 dev DEPARTMENTS >> dev.sql
mysqldump -uroot -p123456 test USERS >> test.sql
mysqldump -uroot -p123456 test DEPARTMENTS >> test.sql

Таблицы USERS и DEPARTMENTS есть в двух базах данных dev и test. Если запустить полученный скрипт, эти таблицы будут выгружены с помощью mysqldump и записаны в файлы dev.sql и test.sql. Эти файлы можно при необходимости загрузить в любую базу данных.

Использование команды SELECT для работы с INFORMATION_SCHEMA

Пример использования команды SELECT для работы с INFORMATION_SCHEMA.

Как было сказано ранее, информацию из базы данных INFORMATION_SCHEMA можно получить с помощью команды SELECT, обращаясь напрямую к ее таблицам. Это значит, что применимы любые полезные особенности этой команды.

Т.е. в команде SELECT могут быть указаны или использованы:

  • конкретные имена таблиц и их столбцов, из которых необходимо получить данные;
  • условие WHERE для получения информации с указанием необходимых условий выбора;
  • группировку и сортировку результатов выбора с помощью опций GROUP BY и ORDER BY;
  • объединение результатов с помощью JOIN и UNION;
  • подзапросы (subqueries).

Результаты, полученные из INFORMATION_SCHEMA с помощью команды SELECT, могут быть записаны в другую таблицу с помощью команд CREATE TABLE … SELECT или INSERT … SELECT. В каких случаях это может быть полезно, рассмотрим позднее.

Примеры использования:

1. Показать имена таблиц и используемый для них storage engines в конкретной базе данных:

select table_name, engine from information_schema.tables where table_schema=’test_db’;

2. Найти все таблицы, которые имеют столбец типа “SET”:

select table_schema, table_name, column_name from information_schema.columns where data_type = ‘set’;

3. Показать сортировку по умолчанию для каждой кодировки:

select character_set_name,collation_name from information_schema.collations where is_default=’Yes’;

4. Показать количество таблиц в каждой базе данных:

select table_schema, count(*) from information_schema.tables group by table_schema;

 
Таблицы в базе данных INFORMATION_SCHEMA read only (только для чтения). Они не могут быть модифицированы командами INSERT, DELETE, UPDATE. Если мы попытаемся, к примеру, удалить что-то из какой-то таблицы, сервер вернет ошибку:

mysql> delete from information_schema.tables;
ERROR 1044 (42000): Access denied for user ‘root’@’localhost’ to database ‘information_schema’