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

Задача
Конечно, хорошо уметь получать информацию о структуре таблицы, но что с ней делать дальше?

Решение
Есть множество вариантов: выводить списки столбцов таблицы, создавать элементы веб-формы, формировать предложения ALTER TABLE для изменения столбцов ENUM или SET, и т. д.

Обсуждение
В этом разделе описано несколько способов применения информации о структуре таблицы, предоставляемой MySQL.

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


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

Но что делать, если пользователь оставил поле пустым? Если, например, поле соответствует столбцу таблицы типа CHAR, следует ли установить столбец в значение NULL или в пустую строку? И на такой вопрос можно ответить, проверив структуру таблицы. Определим, допускает ли столбец использование значений NULL. Если да, то установим столбец в NULL; в противном случае – в пустую строку.

Сопоставление типов столбцов элементам веб-страницы
Некоторые типы столбцов, такие как ENUM или SET, естественным образом отображаются на элементы веб-форм:

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

• Столбец SET похож на ENUM, но допускает выбор нескольких значений, что соответствует группе флажков или прокручиваемому списку с возможностью выбора нескольких элементов.

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


Благодаря этому вы можете предложить пользователям список допустимых значений, чтобы они могли осуществить выбор варианта, не вводя ничего с клавиатуры. Ранее в главе рассказывалось о том, как получить метаданные столбцов ENUM и SET.

Добавление элементов в определения столбцов ENUM и SET
Добавление нового элемента в определение столбца ENUM или SET при использовании ALTER TABLE – занятие не из приятных, так как приходится указыватьне только новый элемент, но и все уже существующие. Один из способов выполнения подобной операции с помощью mysqldump и редактора описан в рецепте 8.2. Можно также написать собственную программу, которая сделает за вас б льшую часть работы, используя метаданные столбца. Давайте напишем сценарий на Python, add_element.py, который будет автоматически формировать соответствующее предложение ALTER TABLE для указанного имени таблицы, имени столбца ENUM или SET и значения нового элемента. Предположим, вы хотите добавить в столбец colors таблицы item элемент hot pink (ярко-розовый). Текущая структура таблицы такова:

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:

Сценарий add_element.py использует эту информацию для построения корректного предложения ALTER TABLE и его вывода:

% ./add_element.py item colors "hot pink"
ALTER TABLE item
MODIFY colors
set('chartreuse','mauve','lime green','puce','hot pink')
NULL DEFAULT 'puce';

Данное предложение является выводом add_element.py, который вы затем можете передать программе mysql для незамедлительного исполнения или сохранить в файле:

% ./add_element.py item colors "hot pink" | mysql cookbook
% ./add_element.py item colors "hot pink" > stmt.sql

Второй вариант предпочтительнее, если вы хотите поместить новый элемент не в конец списка значений, где его расположит сценарий add_element.py.

Тогда вы можете сохранить вывод в файле, а затем отредактировать stmt.sql, поместив элемент туда, куда следует, после чего выполнить предложение:

% vi stmt.sql
% mysql cookbook < stmt.sql

Первая часть сценария add_element.py импортирует необходимые модули и проверяет аргументы командной строки.


Все достаточно просто:

#! /usr/bin/python
# add_element.py – вывод предложения ALTER TABLE для столбца ENUM или SET
# (предполагается работа с базой данных cookbook)
import sys
sys.path.insert (0, "/usr/local/apache/lib/python")
import reimport MySQLdb
import Cookbook
if len (sys.argv) != 4:
print "Usage: add_element.py tbl_name col_name new_element"
sys.exit (1)
tbl_name = sys.argv[1]
col_name = sys.argv[2]
elt_val = sys.argv[3]

После соединения с сервером MySQL (код не приводится) необходимо выполнить запрос SHOW COLUMNS для извлечения информации об указанном столбце. Это делает следующий фрагмент кода, проверяя наличие такого столбца в таблице:

cursor = conn.cursor ()
# экранируем символы шаблона SQL в имени столбца для буквального соответствия
esc_col_name = re.sub (r'([%_])', r'\\\1', col_name)
# это *не* использование заполнителя
cursor.execute ("SHOW COLUMNS FROM %s LIKE '%s'" % (tbl_name, esc_col_name))
info = cursor.fetchone ()
cursor.close
if info == None:
print "Could not retrieve information for table %s, column %s" \
% (tbl_name, col_name)
sys.exit (1)

К этому моменту, если предложение SHOW COLUMNS успешно выполнено, выведенная им информация доступна в виде кортежа, хранящегося в переменной info.


Нам понадобится несколько элементов этого кортежа. Наиболее важным является значение типа столбца, в котором есть строка enum(...) или set(...), содержащая текущее определение столбца. Эту информацию можно использовать для того, чтобы определить, действительно ли столбец имеет тип ENUM или SET, затем добавить в строку новый элемент непосредственно перед закрывающей скобкой. Для столбца colors мы хотим заменить:

set('chartreuse','mauve','lime green','puce')
На:
set('chartreuse','mauve','lime green','puce','hot pink')

Кроме того, следует проверить, допускает ли столбец значения NULL и каково значение по умолчанию, чтобы программа могла добавить соответствующую информацию в предложение ALTER TABLE. Код будет таким:

# получаем строку типа столбца, убеждаемся в том, что она начинается с ENUM или SET
type = info[1]
if not re.match ('(enum|set)', type):
print "table %s, column %s is not an ENUM or SET" % (tbl_name, col_name)
sys.exit(1)
# добавляем кавычки, вставляем запятую и новый элемент прямо перед закрывающей скобкой
elt_val = conn.literal (elt_val)type = re.sub ('\)$', ',' + elt_val + ')', type)
# определяем, может ли столбец содержать значения NULL
if info[2] == "YES":
nullable = "NULL"
else:
nullable = "NOT NULL";
# создаем инструкцию DEFAULT (заключаем в кавычки, если только это не значение NULL)
default = "DEFAULT " + conn.literal (info[4])
print "ALTER TABLE %s\n\tMODIFY %s\n\t%s\n\t%s %s;" \
% (tbl_name, col_name, type, nullable, default)

Вот и все.


Получилась рабочая программа по изменению столбца ENUM или SET. Но это только базовая программа, которую можно усовершенствовать несколькими способами:

• Проверьте, не входит ли уже в столбец добавляемое в него значение.

• Разрешите сценарию add_element.py принимать несколько аргументов после имени столбца и одновременно добавлять их в определение таблицы.

• Добавьте опцию, которая указывала бы на то, что заданный элемент необходимо не добавить, а удалить.

• Добавьте опцию, которая заставляла бы сценарий сразу же выполнять предложение ALTER TABLE, вместо того, чтобы выводить его.

• Если вы работаете с более ранней версией MySQL, чем 3.22.16, то используемая сценарием add_element.py конструкция MODIFY имя_столбца не будет распознана. Необходимо отредактировать сценарий так, чтобы он использовал CHANGE имя_столбца. Два приведенных ниже предложения эквиваленты:

ALTER TABLE имя_таблицы MODIFY имя_столбца определение_столбца;
ALTER TABLE имя_таблицы CHANGE имя_столбца имя_столбца определение_столбца;

Сценарий add_element.py использует предложение MODIFY, потому что его синтаксис более понятен.

Извлечение дат в формате не-ISO
MySQL хранит даты в формате ISO 8601 (CCYY-MM-DD), но часто требуется перезаписать значения дат, например при передаче табличных данных в другую программу, которая ожидает поступления дат в другом формате. Можно написать сценарий, который бы извлекал и печатал строки таблицы, используя метаданные для распознавания столбцов DATE, DATETIME и TIMESTAMP, и переформатировал их при помощи DATE_FORMAT() в интересующий вас формат даты.

Преобразование символьных столбцов из типа фиксированной длины в тип переменной длины, и наоборот Столбцы CHAR имеют фиксированную длину, а VARCHAR – переменную. Обычно таблицы, использующие столбцы CHAR, обрабатываются быстрее, но занимают больше места, чем таблицы со столбцами VARCHAR. Чтобы упростить преобразование таблиц для использования столбцов CHAR или VARCHAR, можно обратиться к информации SHOW COLUMNS для формирования предложения ALTER TABLE, выполняющего необходимое преобразование столбцов. Напишем на Python функцию alter_to_char(), создающую предложение для замены всех столбцов VARCHAR на CHAR:

def alter_to_char (conn, tbl_name):
cursor = conn.cursor ()
cursor.execute ("SHOW COLUMNS FROM " + tbl_name)
rows = cursor.fetchall ()
cursor.close ()
str = ""
for info in rows:
col_name = info[0]
type = info[1]
if re.match ('varchar', type): # это столбец VARCHAR
type = re.sub ("var", "", type) # преобразуем его в CHAR
# определяем, допускает ли столбец NULL
if info[2] == "YES":
nullable = "NULL"
else:
nullable = "NOT NULL";
# формируем инструкцию DEFAULT
# (добавляем кавычки, только если значение не NULL)
default = "DEFAULT " + conn.literal (info[4])
# добавляем инструкцию MODIFY
if str != "":
str = str + ",\n\t"
str = str + \
"MODIFY %s %s %s %s" % (col_name, type, nullable, default)
cursor.close ()
if str == "":
return None
return "ALTER TABLE " + tbl_name + "\n\t" + str
Пусть у вас была такая таблица:
CREATE TABLE chartbl
(
c1 VARCHAR(10),
c2 VARCHAR(10) BINARY,
c3 VARCHAR(10) NOT NULL DEFAULT 'abc\'def'
);
Если передать ее имя функции alter_to_varchar(), она вернет следующее
предложение:ALTER TABLE chartbl
MODIFY c1 char(10) NULL DEFAULT NULL,
MODIFY c2 char(10) binary NULL DEFAULT NULL,
MODIFY c3 char(10) NOT NULL DEFAULT 'abc\'def'
Функция, выполняющая преобразование столбцов в обратном направлении
(из CHAR в VARCHAR), будет аналогичной. Вот ее вариант на Perl:
sub alter_to_varchar
{
my ($dbh, $tbl_name) = @_;
my ($sth, $str);
$sth = $dbh->prepare ("SHOW COLUMNS FROM $tbl_name");
$sth->execute ();
while (my @row = $sth->fetchrow_array ())
{
if ($row[1] =~ /^char/) # столбец CHAR
{
$row[1] = "var" . $row[1];
$str .= ",\n\t" if $str;
$str .= "MODIFY $row[0] $row[1]";
$str .= ($row[2] eq "YES" ? "" : " NOT") . " NULL";
$str .= " DEFAULT " . $dbh->quote ($row[4]);
}
}
$str = "ALTER TABLE $tbl_name\n\t$str" if $str;
return ($str);
}

Для полноты картины функция генерирует предложение ALTER TABLE, которое явно преобразует все столбцы типа CHAR в VARCHAR. В действительности достаточно преобразовать всего один такой столбец. MySQL отметит замену типа столбца фиксированной длины на тип переменной длины и автоматически преобразует все остальные столбцы фиксированной длины, имеющие эквивалент с переменной длиной.
Выбор всех столбцов, кроме некоторых

Предположим, что вы хотите извлечь «почти все» столбцы таблицы. Пусть у вас есть таблица image, содержащая столбец типа BLOB с именем data, используемый для хранения изображений, которые могут быть очень большими, и другие столбцы, характеризующие столбец BLOB, такие как идентификатор изображения, его описание и т. д. Очень просто написать запрос SELECT *, извлекающий все столбцы, но если вас интересует только описательная информация об изображениях, но не они сами, то было бы неэффективно загружать соединение с базой данных передачей значений BLOB. Вместо этого хотелось бы выбрать из записи все, кроме столбца data.

К сожалению, нет способа напрямую указать в SQL необходимость выбора «всех столбцов, кроме одного». Необходимо явно назвать все столбцы, кроме data. Но такой запрос достаточно просто построить при помощи информациио структуре таблицы. Извлекаем список столбцов, удаляем тот, который следует исключить из запроса, и формируем запрос SELECT для всех оставшихся столбцов. Покажем, как выполнить такую операцию в PHP, используя написанную ранее в этой главе функцию get_column_names_with_show() для получения имен столбцов таблицы:

$names = get_column_names_with_show ($conn_id, $tbl_name);
$query = "";
# формируем список столбцов для выбора: все, кроме "data"
reset ($names);
while (list ($index, $name) = each ($names))
{
if ($name == "data")
continue;
if ($query != "") # ставим запятые между именами столбцов
$query .= ",";
$query .= $name;
}
$query = "SELECT $query FROM $tbl_name";

Тот же самый код построения запроса на Perl будет несколько короче (и понятнее):

my @names = get_column_names_with_show ($dbh, $tbl_name);
my $query = "SELECT "
. join (",", grep (!/^data$/, @names))
. " FROM $tbl_name";

Какой бы язык вы ни выбрали, результатом будет запрос, извлекающий все столбцы, кроме data. Он будет эффективнее, чем SELECT *, так как не требует передачи по сети значений BLOB. Конечно, в данном случае необходимо дополнительное обращение к серверу для выполнения предложения, извлекающего имена столбцов, так что следует учитывать контекст планируемого использования запроса SELECT. Если вам нужно просто извлечь одну запись, выбор целой строки может оказаться эффективнее, чем совершение дополнительного путешествия на сервер. Но если вы извлекаете много строк, то уменьшение объема трафика, достигаемое за счет пропуска столбцов BLOB, будет важнее, чем дополнительные расходы на запрос о структуре таблицы.

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

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