Получение информации о структуре таблицы

Задача
Вы хотите узнать, как определена таблица.

Решение
Есть несколько способов сделать это, начиная с предложений, непосредственно возвращающих такую информацию, и заканчивая метаданными запроса к таблице.

Обсуждение
Наличие информации о структуре таблицы позволяет ответить на вопросы:

«Какие столбцы и каких типов содержит таблица?» и «Каковы разрешенные значения для столбца ENUM или SET?». В MySQL есть ряд способов узнать, какова структура таблицы:

• Использовать предложение SHOW COLUMNS.

• Использовать запрос SELECT, выбирающий столбцы из таблицы, затем исследовать метаданные запроса на предмет информации о каждом столбце.

• Использовать программу командной строки mysqldump или предложение SHOW CREATE TABLE для получения предложения CREATE TABLE, выводящего структуру таблицы.

В следующих разделах показано, как запрашивать информацию о таблице MySQL каждым из упомянутых способов. Для работы с примерами необходимо создать таблицу item, в которой перечислены идентификаторы изделий, их названия и цвета, имеющиеся в наличии:

CREATE TABLE item
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(20),
colors SET('chartreuse','mauve','lime green','puce') DEFAULT 'puce',
PRIMARY KEY (id)
);

Получение структуры таблицы с помощью предложения SHOW COLUMNS
Предложение SHOW COLUMNS формирует одну строку вывода для каждого столбца таблицы, при этом каждая строка содержит несколько элементов информации о соответствующем столбце.


Я советовал бы вам опробовать предложение SHOW COLUMNS на нескольких собственных таблицах, чтобы получитьпредставление о том, какой вывод оно генерирует для различных типов столбцов. Рассмотрим пример вывода SHOW COLUMNS для таблицы item. (Обратите внимание на использование \G в качестве признака конца предложения для формирования «вертикального» вывода; дело в том, что строки вывода
SHOW COLUMNS часто бывают настолько длинными, что переносятся на следующую строку, и их сложно читать.)

mysql> SHOW COLUMNS FROM item\G
*************************** 1. row ***************************
Field: id
Type: int(10) unsigned
Null:
Key: PRI
Default: NULL
Extra: auto_increment
*************************** 2. row ***************************
Field: name
Type: char(20)
Null: YES
Key:
Default: NULL
Extra:
*************************** 3. row ***************************
Field: colors
Type: set('chartreuse','mauve','lime green','puce')
Null: YES
Key:
Default: puce
Extra:
Предложение выводит информацию следующим образом:
Field
Имя столбца.
Type
Тип столбца.
Null
YES, если столбец допускает значения NULL, иначе – пробел.

Key
Информация о том, индексирован ли столбец.

Default
Значение по умолчанию.

Extra
Дополнительная информация.

Формат SHOW COLUMNS может несколько меняться, но перечисленные значения доступны всегда.


В вашей версии MySQL может отображаться еще какая-то информация.Вывод SHOW COLUMNS удобно использовать в программах. Важно знать, что предложение SHOW подобно SELECT в том, что оно возвращает результирующее множество, поэтому для обработки запроса SHOW вы создаете предложение и организуете цикл для извлечения строк. Приведем для иллюстрации функцию PHP, которая принимает имя таблицы в качестве аргумента и использует SHOW COLUMNS для получения списка имен столбцов таблиц:

function get_column_names_with_show ($conn_id, $tbl_name)
{
$query = "SHOW COLUMNS FROM $tbl_name";
if (!($result_id = mysql_query ($query, $conn_id)))
return (FALSE);
$names = array(); # создать пустой массив
# первое значение каждой строки вывода – это имя столбца
while (list ($name) = mysql_fetch_row ($result_id))
$names[] = $name; # добавить имя в конец массива
mysql_free_result ($result_id);
return ($names);
}

Возвращенный функцией массив содержит имена столбцов в том же порядке, в котором столбцы присутствуют в таблице. Заметьте, что get_column_names_with_show() не принимает аргумент для указания имени базы данных.

В этом нет необходимости, так как MySQL понимает полные ссылки на таблицы в формате db_name.tbl_name.


Для обращения к таблице базы данных, отличной от текущей, просто передайте аргумент $tbl_name, содержащий имя базы данных:

$names = get_column_names_with_show ($conn_id, "some_db.some_tbl");

Аналогичная функция Python выглядит так:

def get_column_names_with_show (conn, tbl_name):
names = []
cursor = conn.cursor ()
cursor.execute ("SHOW COLUMNS FROM " + tbl_name)
rows = cursor.fetchall ()
cursor.close ()
for row in rows:
names.append (row[0])
return (names)

В DBI операция тривиальна, так как selectcol_arrayref() возвращает непосредственно первый столбец результата запроса:

sub get_column_names_with_show
{
my ($dbh, $tbl_name) = @_;
my $ref = $dbh->selectcol_arrayref ("SHOW COLUMNS FROM $tbl_name");
return (defined ($ref) ? @{$ref} : ());
}

Если нужны сведения только об одном столбце, используйте инструкцию LIKE с именем интересующего вас столбца:

mysql> SHOW COLUMNS FROM item LIKE 'colors'\G
*************************** 1. row ***************************
Field: colors
Type: set('chartreuse','mauve','lime green','puce')
Null: YES
Key:
Default: puce
Extra:

Обратите внимание на кавычки, в которые заключено имя столбца, следующего за ключевым словом LIKE. Кавычки необходимы, так как имя – это не настоящее имя столбца, а строковый шаблон SQL. Строка интерпретируется так же, как для оператора LIKE инструкции WHERE предложения SELECT. Предложение SHOW COLUMNS выводит информацию для всех столбцов, имена которых соответствуют образцу. Если указать буквальное имя столбца, то строка будет соответствовать только этому имени, и SHOW COLUMNS выведет данные только по нужному столбцу.

Однако неосторожных здесь ожидает ловушка. Если имена столбцов содержат специальные символы SQL (% или _), и вы хотите установить им буквальное соответствие, необходимо экранировать такие символы в строке образца при помощи обратного слэша, иначе другие имена тоже будут восприниматься как совпадающие с шаблоном. Символ % нечасто используется в именах столбцов, а вот символ подчеркивания _ весьма распространен, так
что вы вполне можете столкнуться с описанной проблемой. Предположим, что у вас есть таблица, содержащая результаты измерений уровня углекислого газа в столбце co_2 и вычисления тригонометрических функций косинуса и котангенса в столбцах cos1, cos2, cot1 и cot2. Если вы хотите получить информацию только о столбце co_2, то не следует использовать запрос:

SHOW COLUMNS FROM имя_таблицы LIKE 'co_2';

Символ _ в поиске по образцу означает «соответствие любому символу», поэтому запрос вернул бы строки co_2, cos2 и cot2. Чтобы получить только co_2, изменим команду SHOW:

SHOW COLUMNS FROM имя_таблицы LIKE 'co\_2';

В программе можно использовать возможности языка API для экранирования символов шаблона SQL перед передачей имен столбцов в запрос SHOW. Например, в Perl, PHP и Python можно использовать такие выражения:

Perl:
$name =~ s/([%_])/\\$1/g;
PHP:
$name = ereg_replace ("([%_])", "\\\\1", $name);

В Python импортируйте модуль re, затем выполните:
name = re.sub (r'([%_])', r'\\\1', name)

Если вам покажется, что выражения содержат слишком много символов обратного слэша, вспомните о том, что транслятор языка API сам интерпретирует такие символы и перед выполнением сравнения с образцом «снимает верхний слой». Чтобы получить в результирующем множестве буквальный символ обратного слэша, необходимо продублировать его в шаблоне. В PHP появляется еще один уровень, так как здесь свою лепту вносит еще и обработчик шаблона.

В Java необходимо выбрать библиотеку класса регулярного выражения. В следующем примере используется библиотека ORO, доступная по адресу jakarta. apache.org, которая содержит классы, эмулирующие регулярные выражения Perl5:

import org.apache.oro.text.perl.*;
Perl5Util util = new Perl5Util ();
name = util.substitute ("s/([_%])/\\\\$1/g", name);

Необходимость экранирования символов % и _ для буквального соответствия значению LIKE относится и к другим формам предложения SHOW, допускающим использование образцов в инструкции LIKE (SHOW TABLES, SHOW DATABASES и SHOW VARIABLES).

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

Метаданные доступны для любого запроса, поэтому их можно специально получить для столбцов указанной таблицы. Например, результирующее множество запроса SELECT * FROM имя_таблицы будет содержать метаданные каждого столбца таблицы имя_таблицы. Однако если вас интересуют только метаданные, но не сами данные таблицы, разумным желанием будет минимизировать размер результирующего множества для генерирования минимального сетевого трафика. Для того чтобы обеспечить пустоту результирующего множества, просто добавим в запрос заведомо ложную инструкцию WHERE:

SELECT * FROM имя_таблицы WHERE 1 = 0

Несмотря на то что такой запрос не выбирает строк, он совершенно корректен; MySQL обработает его и получит метаданные, из которых можно извлечь любую необходимую информацию. Например, ранее в этом разделе мы писали функцию PHP get_column_names_with_show(), получавшую перечень имен столбцов с помощью предложения SHOW COLUMNS. Функцию можно переделать так, чтобы она получала имена столбцов из метаданных столбца, вместо того чтобы выдавать запрос SELECT и вызывать mysql_fetch_field():

function get_column_names_with_meta ($conn_id, $tbl_name)
{
$query = "SELECT * FROM $tbl_name WHERE 1 = 0";
if (!($result_id = mysql_query ($query, $conn_id)))
return (FALSE);
$names = array(); # создать пустой массив
for ($i = 0; $i < mysql_num_fields ($result_id); $i++)
{
if ($field = mysql_fetch_field ($result_id, $i))
$names[] = $field->name; # добавить имя в конец массива
}
mysql_free_result ($result_id);
return ($names);
}

Аналогичная функция Perl будет проще. DBI организует метаданные в массивы, так что нужно просто обратиться к ссылке $sth->{NAME} на массив имен столбцов. Единственная хитрость заключается в том, что необходимо создать копию массива перед вызовом finish(), так как finish() уничтожает метаданные и делает недоступным массив NAME:

sub get_column_names_with_meta
{
my ($dbh, $tbl_name) = @_;
my ($sth, @names);
$sth = $dbh->prepare ("SELECT * FROM $tbl_name WHERE 1=0");
$sth->execute ();
@names = @{$sth->{NAME}}; # создать копию; finish() разрушает метаданные
$sth->finish (); # освободить результирующее множество
return (@names);
}

Без труда можно преобразовать функции, получающие имена столбцов, к более общему виду, чтобы указать, какие именно метаданные извлекать. Давайте назовем эти функции get_column_info() и добавим параметр для указания типа информации. В PHP функция будет такой:

function get_column_info ($conn_id, $tbl_name, $info_type)
{
$query = "SELECT * FROM $tbl_name WHERE 1 = 0";
if (!($result_id = mysql_query ($query, $conn_id)))
return (FALSE);
$info = array(); # создать пустой массивfor ($i = 0; $i < mysql_num_fields ($result_id); $i++)
{
if ($field = mysql_fetch_field ($result_id, $i))
$info[] = $field->$info_type; # добавить информацию в массив
}
mysql_free_result ($result_id);
return ($info);
}

Чтобы использовать функцию, вызовите ее следующим образом:

$names = get_column_info ($conn_id, "item", "name");
$types = get_column_info ($conn_id, "item", "type");
$numeric = get_column_info ($conn_id, "item", "numeric");
Версия на Perl выглядит так:
sub get_column_info
{
my ($dbh, $tbl_name, $info_type) = @_;
my ($sth, @info);
$sth = $dbh->prepare ("SELECT * FROM $tbl_name WHERE 1=0");
$sth->execute ();
@info = @{$sth->{$info_type}}; # создать копию; finish() разрушает метаданные
$sth->finish (); # освободить результирующее множество
return (@info);
}

И вызывается так:
my @names = get_column_info ($dbh, "item", "NAME");
my @types = get_column_info ($dbh, "item", "mysql_type_name");
my @numeric = get_column_info ($dbh, "item", "mysql_is_num");

Работая с get_column_info(), помните, что функцию нельзя использовать для получения ширины вывода столбцов таблицы. (То есть она бесполезна для значений mysql_max_length в Perl и значений max_length в PHP.) Если используются метаданные столбца, полученные из предложения SELECT, ширина вывода столбца будет соответствовать ширине значений, фактически присутствующих в результирующем множестве. Если речь идет о запросе SELECT ...

WHERE 1=0, то его результирующее множество пусто, и для всех столбцов ширина вывода будет равна 0!

Получение структуры таблицы с помощью предложения CREATE TABLE

Третий способ получения от MySQL информации о структуре таблицы – выполнение в командной строке mysqldump --no-data для формирования предложения CREATE TABLE, которое показывает структуру таблицы. Рассмотрим один пример. Опция --no-data указывает программе mysqldump, что не нужно выгружать данные из таблицы, --all задает вывод всех опций предложения CREATE TABLE, а --quote-names означает заключение в кавычки названий, содержащих специальные символы.1 Можно опустить опцию --all или
--quote-names, если они для вас не важны.

% mysqldump --no-data --all --quote-names cookbook item
# MySQL dump 8.16
#
# Host: localhost Database: cookbook
#--------------------------------------------------------
# Server version 3.23.46-log
#
# Table structure for table 'item'
#
CREATE TABLE `item` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` char(20) default NULL,
`colors` set('chartreuse','mauve','lime green','puce') default 'puce',
PRIMARY KEY (`id`)
) TYPE=MyISAM;

Если вы работаете с версией MySQL 3.23.20 или выше, то можете получить ту же информацию при помощи предложения SHOW CREATE TABLE:

mysql> SHOW CREATE TABLE item;
+------+----------------+
| Table | Create Table |
+------+----------------+

| item | CREATE TABLE `item` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` char(20) default NULL,
`colors` set('chartreuse','mauve','lime green','puce') default 'puce',
PRIMARY KEY (`id`)
) TYPE=MyISAM |
+-------+--------------+

Вывод информативен и удобен для восприятия, так как информация о столбцах выводится в формате, аналогичном используемому при первичном создании таблицы. Явно показана структура индекса, чего не обеспечивают другие методы. Однако вы, наверное, согласитесь с тем, что этот способ проверки структуры таблицы больше подходит для визуального контроля, чемдля использования внутри программы. Информация выводится не в обычном формате строка-столбец, поэтому ее трудно анализировать. Кроме того, формат может несколько меняться в результате усовершенствования предложения CREATE TABLE, которые случаются периодически по мере расширения возможностей MySQL.

Конечно, это не значит, что вывод SHOW CREATE TABLE не подлежит использованию в программах. Например, он применяется для создания полной копии таблицы, включающей все ее индексы.

Оцените статью: (0 голосов)
0 5 0

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