6.5.4 Синтаксис оператора ALTER TABLE
ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]
alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
или ADD [COLUMN] (create_definition, create_definition,...)
или ADD INDEX [index_name] (index_col_name,...)
или ADD PRIMARY KEY (index_col_name,...)
или ADD UNIQUE [index_name] (index_col_name,...)
или ADD FULLTEXT [index_name] (index_col_name,...)
или ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
[reference_definition]
или ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
или CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
или MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
или DROP [COLUMN] col_name
или DROP PRIMARY KEY
или DROP INDEX index_name
или DISABLE KEYS
или ENABLE KEYS
или RENAME [TO] new_tbl_name
или ORDER BY col
или table_options
Оператор ALTER TABLE обеспечивает возможность изменять структуру
существующей таблицы. Например, можно добавлять или удалять столбцы,
создавать или уничтожать индексы или переименовывать столбцы либо саму
таблицу. Можно также изменять комментарий для таблицы и ее тип.
See section 6.5.3 Синтаксис оператора CREATE TABLE.
Если оператор ALTER TABLE используется для изменения определения типа
столбца, но DESCRIBE tbl_name показывает, что столбец не изменился, то,
возможно, MySQL игнорирует данную модификацию по одной из причин,
описанных в разделе section 6.5.3.1 Молчаливые изменения определений столбцов. Например, при
попытке изменить столбец VARCHAR на CHAR MySQL будет продолжать
использовать VARCHAR, если данная таблица содержит другие столбцы с
переменной длиной.
Оператор ALTER TABLE во время работы создает временную копию исходной
таблицы. Требуемое изменение выполняется на копии, затем исходная таблица
удаляется, а новая переименовывается. Так делается для того, чтобы в новую
таблицу автоматически попадали все обновления кроме неудавшихся. Во время
выполнения ALTER TABLE исходная таблица доступна для чтения другими
клиентами. Операции обновления и записи в этой таблице приостанавливаются,
пока не будет готова новая таблица.
Следует отметить, что при использовании любой другой опции для ALTER
TABLE кроме RENAME, MySQL всегда будет создавать временную таблицу, даже
если данные, строго говоря, и не нуждаются в копировании (например, при
изменении имени столбца). Мы планируем исправить это в будущем, однако,
поскольку ALTER TABLE выполняется не так часто, мы (разработчики MySQL)
не считаем эту задачу первоочередной. Для таблиц MyISAM можно увеличить
скорость воссоздания индексной части (что является наиболее медленной
частью в процессе восстановления таблицы) путем установки переменной
myisam_sort_buffer_size достаточно большого значения.
-
Для использования оператора
ALTER TABLE необходимы привилегии ALTER,
INSERT и CREATE для данной таблицы.
-
Опция
IGNORE является расширением MySQL по отношению к ANSI SQL92.
Она управляет работой ALTER TABLE при наличии дубликатов уникальных
ключей в новой таблице. Если опция IGNORE не задана, то для данной
копии процесс прерывается и происходит откат назад. Если IGNORE
указывается, тогда для строк с дубликатами уникальных ключей только
первая строка используется, а остальные удаляются.
-
Можно запустить несколько выражений
ADD, ALTER, DROP и CHANGE в одной
команде ALTER TABLE. Это является расширением MySQL по отношению к
ANSI SQL92, где допускается только одно выражение из упомянутых в
одной команде ALTER TABLE.
-
Опции
CHANGE col_name, DROP col_name и DROP INDEX также являются
расширениями MySQL по отношению к ANSI SQL92.
-
Опция
MODIFY представляет собой расширение Oracle для команды ALTER
TABLE.
-
Необязательное слово
COLUMN представляет собой ``белый шум'' и может
быть опущено.
-
При использовании
ALTER TABLE имя_таблицы RENAME TO новое_имя без
каких-либо других опций MySQL просто переименовывает файлы,
соответствующие заданной таблице. В этом случае нет
необходимости создавать временную таблицу. See section 6.5.5 Синтаксис оператора RENAME TABLE.
-
В выражении
create_definition для ADD и CHANGE используется тот же
синтаксис, что и для CREATE TABLE. Следует учитывать, что этот
синтаксис включает имя столбца, а не просто его тип. See section 6.5.3 Синтаксис оператора CREATE TABLE.
-
Столбец можно переименовывать, используя выражение
CHANGE имя_столбца
create_definition. Чтобы сделать это, необходимо указать старое и
новое имена столбца и его тип в настоящее время. Например, чтобы
переименовать столбец INTEGER из a в b, можно сделать следующее:
mysql> ALTER TABLE t1 CHANGE a b INTEGER;
При изменении типа столбца, но не его имени синтаксис выражения CHANGE все
равно требует указания обоих имен столбца, даже если они одинаковы.
Например:
mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
Однако начиная с версии MySQL 3.22.16a можно также использовать выражение
MODIFY для изменения типа столбца без переименовывания его:
mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
-
При использовании
CHANGE или MODIFY для того, чтобы уменьшить длину
столбца, по части которого построен индекс (например, индекс по первым
10 символам столбца VARCHAR), нельзя сделать столбец короче, чем число
проиндексированных символов.
-
При изменении типа столбца с использованием
CHANGE или MODIFY MySQL
пытается преобразовать данные в новый тип как можно корректнее.
-
В версии MySQL 3.22 и более поздних можно использовать
FIRST или ADD
... AFTER имя_столбца для добавления столбца на заданную позицию внутри
табличной строки. По умолчанию столбец добавляется в конце. Начиная с
версии MySQL 4.0.1, можно также использовать ключевые слова FIRST и
AFTER в опциях CHANGE или MODIFY.
-
Опция
ALTER COLUMN задает для столбца новое значение по умолчанию
или удаляет старое. Если старое значение по умолчанию удаляется и
данный столбец может принимать значение NULL, то новое значение по
умолчанию будет NULL. Если столбец не может быть NULL, то MySQL
назначает значение по умолчанию так, как описано в разделе section 6.5.3 Синтаксис оператора CREATE TABLE.
-
Опция
DROP INDEX удаляет индекс. Это является расширением MySQL по
отношению к ANSI SQL92. See section 6.5.8 Синтаксис оператора DROP INDEX.
-
Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из
любого индекса, в который они входят как часть. Если все столбцы,
составляющие индекс, удаляются, то данный индекс также удаляется.
-
Если таблица содержит только один столбец, то этот столбец не может
быть удален. Вместо этого можно удалить данную таблицу, используя
команду
DROP TABLE.
-
Опция
DROP PRIMARY KEY удаляет первичный индекс. Если такого
индекса в данной таблице не существует, то удаляется первый индекс
UNIQUE в этой таблице. (MySQL отмечает первый уникальный ключ UNIQUE
как первичный ключ PRIMARY KEY, если никакой другой первичный ключ
PRIMARY KEY не был явно указан). При добавлении UNIQUE INDEX или
PRIMARY KEY в таблицу они хранятся перед остальными неуникальными
ключами, чтобы можно было определить дублирующиеся ключи как можно
раньше.
-
Опция
ORDER BY позволяет создавать новую таблицу со строками,
размещенными в заданном порядке. Следует учитывать, что созданная
таблица не будет сохранять этот порядок строк после операций вставки и
удаления. В некоторых случаях такая возможность может облегчить
операцию сортировки в MySQL, если таблица имеет такое расположение
столбцов, которое вы хотели бы иметь в дальнейшем. Эта опция в
основном полезна, если заранее известен определенный порядок, в
котором преимущественно будут запрашиваться строки. Использование
данной опции после значительных преобразований таблицы дает
возможность получить более высокую производительность.
-
При использовании команды
ALTER TABLE для таблиц MyISAM все
неуникальные индексы создаются в отдельном пакете (подобно REPAIR).
Благодаря этому команда ALTER TABLE при наличии нескольких индексов
будет работать быстрее.
-
Начиная с MySQL 4.0, вышеуказанная возможность может быть
активизирована явным образом. Команда
ALTER TABLE ... DISABLE KEYS
блокирует в MySQL обновление неуникальных индексов для таблиц MyISAM.
После этого можно применить команду ALTER TABLE ... ENABLE KEYS для
воссоздания недостающих индексов. Так как MySQL делает это с помощью
специального алгоритма, который намного быстрее в сравнении со
вставкой ключей один за другим, блокировка ключей может дать
существенное ускорение на больших массивах вставок.
-
Применяя функцию C API
mysql_info(), можно определить, сколько
записей было скопировано, а также (при использовании IGNORE) - сколько
записей было удалено из-за дублирования значений уникальных ключей.
-
Выражения
FOREIGN KEY, CHECK и REFERENCES фактически ничего не
делают. Они введены только из соображений совместимости, чтобы
облегчить перенос кода с других серверов SQL и запуск приложений,
создающих таблицы со ссылками. See section 1.9.4 Отличия MySQL от ANSI SQL92.
Ниже приводятся примеры, показывающие некоторые случаи употребления
команды ALTER TABLE. Пример начинается с таблицы t1, которая создается
следующим образом:
mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));
Для того чтобы переименовать таблицу из t1 в t2:
mysql> ALTER TABLE t1 RENAME t2;
Для того чтобы изменить тип столбца с INTEGER на TINYINT NOT NULL
(оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с
переименованием его с b на c:
mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
Для того чтобы добавить новый столбец TIMESTAMP с именем d:
mysql> ALTER TABLE t2 ADD d TIMESTAMP;
Для того чтобы добавить индекс к столбцу d и сделать столбец a первичным
ключом:
mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);
Для того чтобы удалить столбец c:
mysql> ALTER TABLE t2 DROP COLUMN c;
Для того чтобы добавить новый числовой столбец AUTO_INCREMENT с именем c:
mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD INDEX (c);
Заметьте, что столбец c индексируется, так как столбцы AUTO_INCREMENT
должны быть индексированы, кроме того, столбец c объявляется как NOT
NULL, поскольку индексированные столбцы не могут быть NULL.
При добавлении столбца AUTO_INCREMENT значения этого столбца автоматически
заполняются последовательными номерами (при добавлении записей). Первый
номер последовательности можно установить путем выполнения команды SET
INSERT_ID=# перед ALTER TABLE или использования табличной опции
AUTO_INCREMENT = #. See section 5.5.6 Синтаксис команды SET.
Если столбец AUTO_INCREMENT для таблиц MyISAM, не изменяется, то номер
последовательности остается прежним. При удалении столбца AUTO_INCREMENT и
последующем добавлении другого столбца AUTO_INCREMENT номера будут
начинаться снова с 1.
See section A.6.1 Проблемы с ALTER TABLE.
