Оператор ALTER TABLE

Оператор alter table в СУБД MySQL обладает достаточно универсальными возможностями. Мы уже демонстрировали несколько вариантов его использования (создание и удаление индексов, преобразование таблицы из одного формата в другой). В этом разделе будут продемонстрированы и другие способности этого оператора. Полный синтаксис оператора ALTER table приведен в приложении Г, Синтаксис SQL .

Оператор alter table может пригодиться в том случае, когда становится очевидно, что старая структура таблицы уже не соответствует текущим задачам. Появилась какая-то дополнительная информация, или таблица содержит данные, ставшие уже избыточными. Существующие столбцы оказались слишком малы, или опыт работы с таблицей показал, что они непредусмотрительно были объявлены слишком большими и для экономии пространства и оптимизации запросов их требуется уменьшить. Возможно, просто была допущена ошибка в имени столбца при создании таблицы с помощью оператора create table. Вот несколько таких ситуаций.

Вы ведете на Web-сервере анкету и сохраняете результаты каждого опроса как запись таблицы. Затем вы решили модифицировать анкету и добавить в нее несколько вопросов. Для этого необходимо добавить столбцы в таблицу для того, чтобы сохранять новые вопросы.

Предположим, что вы участвуете в научном проекте. Каждому эксперименту вы присваиваете номер и сохраняете его в столбце с параметром auto iNCREMENT. По всем оценкам, исследования должны длиться достаточно долго. Количество регистрирующих записей должно составлять около 50000. Вы совершенно справедливо приняли решение для хранения номеров опытов выбрать тип unsigned smallint, который может хранить до 65535 значений. Однако по полученным результатам проект был признан успешным и его финансирование возобновилось. По предварительным оценкам может быть сделано еще 50000 записей. Необходимо изменить имеющийся тип столбца на более вместительный.

Размеры столбцов могут меняться и в другую сторону. Предположим, был создан столбец типа char (255), но в процессе работы с таблицей оказалось, что длина значений в этом столбце не превышает 100 символов. Длину столбца можно уменьшить.

Синтаксис оператора alter table имеет следующий вид:

ALTER TABLE tbl name action, ...

Спецификатором action определяется операция, которая будет произведена над таблицей. Таким образом, в СУБД MySQL бьш расширен диапазон действий оператора ALTER table. Это удобно и уменьшает ра-

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

Ниже приведены новые возможности оператора alter table.

Переименование таблицы. Это просто, достаточно указать новое и старое имя:

ALTER TABLE tbl name RENAME AS new tbl name

Для СУБД MySQL 3.23, в которой впервые появились временные таблицы, переименование временных таблиц в имена, которые уже существуют в базе данных, прячет существующие таблицы данных на время существования временных таблиц. Аналогично этому, при создании временной таблицы прячется таблица с тем же самым именем.

Модификация типа столбца. Тип столбца меняется с помощью предложений change или modify В операторе alter table. Предположим, что столбец в таблице my tbl имеет тип smallint unsigned, И появилась необходимость изменить его на meduim unsigned. Это можно сделать с помощью одной из двух команд:

ALTER TABLE tbl name MODIFY i MEDIUMINT UNSIGNED ALTER TABLE tbl name CHANGE i i MEDIUMINT UNSIGNED

Почему BO втором случае столбец назван дважды? Потому, что операция change, в отличие от операции modify, не только меняет тип данных, но и переименовывает столбец. По желанию можно не только изменить тип данных столбца, но и изменить имя столбца с i, скажем, на j:

ALTER TABLE tbl name CHANGE i j MEDIUMINT UNSIGNED

Тут важно понять семантику этого оператора. Сначала указывается имя столбца, который будет меняться, а затем дается полностью новое объявление столбца, включая опять же его имя. Имя обязательно указьшается, даже если оно является старым именем столбца.

Существенной причиной для изменения типа столбца может служить необходимость оптимизировать объединения, сравнивающие столбцы из разных таблиц. Операция сравнения занимает меньше времени, если столбцы однотипные. Предположим, вы запустили запрос:

SELECT ... FROM tl, t2 WHERE tl.name = t2.name

Очевидно, что если столбец tl.name имеет тип CHAR(10), а столбец t2 . name - CHAR (15), запрос будет обрабатываться медленнее, чем, если бы оба столбца имели тип CHAR (15). Изменим длину tl. name с помощью одного из следующих операторов:

ALTER TABLE tl MODIFY name CHAR(15) ALTER TABLE tl CHANGE name name CHAR(15)

в версиях до 3.22 были возможны объединения только по столбцам одного и того же типа. В СУБД MySQL версии 3.22 и выше стало возможно делать объединения по столбцам разного типа. Но эффективнее все же работают объединения одинаковых типов данных.

Преобразование таблиц из таблиц с переменной длиной строки в таблицы с фиксированной длиной строки. Предположим, что мы имеем таблицу chartbl со столбцами типа varchar. Как ее оптимизировать? (Таблицы со строками фиксированной длины обычно обрабатываются быстрее, чем таблицы со строками переменной длины.) Создадим такую таблицу:

CREATE TABLE chartbl {name VARCHAR(40), address VARCHAR(80))

Сложность этой процедуры состоит в том, что изменять все столбцы нужно одновременно, используя один оператор alter table. Если изменить только один или несколько столбцов, СУБД MySQL решит , что таблица по-прежнему имеет строки переменной длины и преобразует char-столбцы обратно в varchar для того, чтобы сэкономить пространство. Такое изменение структуры можно осуществить только оператором вида:

ALTER TABLE chartbl MODIFY name CHAR(40), MODIFY address CHAR(80)

Теперь запрос describe покажет, что таблица содержит только столбцы char. Это наглядно демонстрирует необходимость поддержки множественных операций с помощью оператора alter table.

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

Преобразование таблиц из таблиц с фиксированной длиной строки в таблицы с переменной длиной строки. Да, все запросы к таблице chartbl работают быстрее, когда она имеет строки фиксированной длины, но при этом занимает больше, чем хотелось бы, места. Итак, мы решили преобразовать ее в первоначальное состояние. Преобразование таблицы в этом направлении значительно проще. Достаточно преобразовать тип только одного столбца в тип varchar, и СУБД MySQL автоматически преобразует тип оставшихся столбов из char в varchar:

ALTER TABLE chartbl MODIFY name VARCHAR(40) ALTER TABLE chartbl MODIFY address VARCHAR{80)