MySQL / 8. Изменение таблицы с помощью предложения ALTER TABLE

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

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

Решение
Используйте MODIFY или CHANGE: MODIFY проще, но не умеет изменять название столбца, а CHANGE несколько непонятнее, зато может изменить и имя, и определение.

Обсуждение
Чтобы изменить определение столбца, используйте предложение MODIFY или ключевое слово CHANGE.1 MODIFY имеет более простой формат: укажите имя столбца, затем его новое определение. Например, изменим тип столбца с CHAR(1) на CHAR(10):

ALTER TABLE mytbl MODIFY c CHAR(10);

Синтаксис CHANGE несколько другой. После ключевого слова CHANGE указывается имя столбца, который следует изменить, затем его новое определение, включающее новое имя. Второе имя столбца необходимо, так как CHANGE позволяет изменить и имя столбца, а не только его определение. Например, чтобы изменить тип столбца i с INT на BIGINT и одновременно переименовать его в j, запишем предложение так:

ALTER TABLE mytbl CHANGE i j BIGINT;

Если теперь попробовать выполнить CHANGE для преобразования j из BIGINT обратно в INT без изменения имени, предложение может показаться немного странным:

ALTER TABLE mytbl CHANGE j j INT;

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

Особенно важно помнить об этом тем, чья версия MySQL еще не допускает использования MODIFY. Любое предложение ALTER TABLE с конструкцией MODIFY имя_столбца может быть заменено на аналог, использующий CHANGE имя_столбца имя_столбца. То есть два следующих предложения эквивалентны:

ALTER TABLE имя_таблицы MODIFY имя_столбца ... ;
ALTER TABLE имя_таблицы CHANGE имя_столбца имя_столбца ... ;

Было бы очень удобно использовать такую форму предложения ALTER TABLE, которая бы только переименовывала столбец, не требуя повторного ввода его определения. Особенно такая возможность пригодилась бы при работе со столбцами ENUM и SET, содержащими много значений. Но, к сожалению, такого предложения не существует, что усложняет работу с упомянутыми столбцами при использовании предложения ALTER TABLE. Предположим, что вы добавляете в mytbl столбец e типа ENUM, имеющий несколько членов:

ALTER TABLE mytbl ADD e
ENUM('hardware','software','books','office supplies',
'telecommunications','furniture','utilities',
'shipping','tax');

Если вы захотите изменить название столбца с e на e2, то для задания нового имени используете CHANGE. Но придется повторить и определение столбца:

ALTER TABLE mytbl CHANGE e e2
ENUM('hardware','software','books','office supplies',
'telecommunications','furniture','utilities',
'shipping','tax');

Да… Не хотелось бы все это набирать. Ручной ввод корректного предложения ALTER TABLE для таблиц такого вида утомителен, не говоря уже о том, что подвержен ошибкам. Чтобы избежать повторного ввода определения, запишите текущее определение в файл и редактируйте его для получения соответствующего предложения ALTER TABLE:

• Запустите mysqldump для получения предложения CREATE TABLE, содержащего определение таблицы:

% mysqldump --no-data cookbook mytbl > test.txt

В файле test.txt должно присутствовать такое определение:

CREATE TABLE mytbl (
c char(10) default NULL,j bigint(20) NOT NULL default '100',
e enum('hardware','software','books','office supplies','telecommunications',
'furniture','utilities','shipping','tax') default NULL
) TYPE=MyISAM;

Опция --no-data указывает mysqldump на то, что не нужно делать дамп данных таблицы, необходимо только получить определение таблицы.

• Отредактируйте файл test.txt, удалив из него все, кроме определения столбца e:

e enum('hardware','software','books','office supplies','telecommunications',
'furniture','utilities','shipping','tax') default NULL

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

ALTER TABLE mytbl CHANGE e e2
enum('hardware','software','books','office supplies','telecommunications',
'furniture','utilities','shipping','tax') default NULL;

• Сохраните файл test.txt, выйдите из редактора и передайте test.txt как командный файл в mysql:

% mysql cookbook < test.txt

Конечно, для простых столбцов легче ввести предложение ALTER TABLE вручную, а не выполнять всю эту процедуру. Но для столбцов ENUM и SET с длинными и неизящными определениями часто бывает разумнее использовать редактор для создания командного файла mysql из вывода mysqldump. Этот же прием можно применять для упрощения изменения порядка элементов столбцов ENUM и SET или для добавления и удаления членов из определения столбца.

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

Статьи по MySQL на эту тему:

Добавление и удаление индексов
Изменение значения столбца по умолчанию
Изменение типа таблицы
Использование предложения ALTER TABLE для нормализации таблицы
Переименование таблицы