Поиск

Синтаксис ALTER TABLE

ALTER [IGNORE] TABLE имя_ таблицы
а l ter [, спецификацияalter] ... спецификация_а11ег:
ADD [COLUMN] определение_столбца[FIRST | AFTER имя_столбца] I ADD [COLUMN] {определвние_столбца, ...)
I ADD INDEX [имя_индекса] [тип_индекса] {имя_столбца_индекса,...) I ADD [CONSTRAINT [символ]]
PRIMARY KEY [тип_индекса] {имя_столбца_индекса, ...) I ADD [CONSTRAINT [символ]]
UNIQUE [имя_индекса] [тип_индекса] {имя_столбца_индекса,...) I ADD [FULLTEXT|SPATIAL] [имя_индекса] {имя_столбца__индекса,...) | ADD [CONSTRAINT [символ]]
FOREIGN KEY [имя_индекса] [имя_столбца_индекса,...)
[ о пределение__ ссылки]
I ALTER [COLUMN] имя_столбца {SET DEFAULT литерал | DROP DEFAULT} CHANGE [COLUMN] старое_имя_столбца определение_столбца
[FIRST|AFTER имя_столбца]
I MODIFY [COLUMN] определение_столбца [FIRST | AFTER имя_столбца] | DROP [COLUMN] имя_столбца | DROP PRIMARY KEY I DROP INDEX имя_индекса I DROP FOREIGN KEY символ_£к I DISABLE KEYS I ENABLE KEYS
I RENAME [TO] новое_имя_таблицыI ORDER BY имя_столбцаI CONVERT TO CHARACTER SET имя_набора_ символов
[COLLATE имя_порядка_сопоставления] I [DEFAULT] CHARACTER SET имя_набор а_символов
[COLLATE имя_порядка_сопоставления] I DISCARD TABLESPACE IMPORT TABLESPACE I опции_ таблицы
ALTER TABLE позволяет изменить структуру существующей таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы, изменять тип существующих столбцов или переименовывать столбцы и сами таблицы. Можно также изменять комментарии для таблиц и их тип.
Синтаксис многих допустимых изменений в этом операторе похож на аналогичные конструкции оператора CREATE TABLE. См. раздел 6.2.5.
Если вы использовали ALTER TABLE для изменения спецификаций столбцов, но DESCRIBE имя_таблицы показывает, что ваши столбцы не изменились, возможно, MySQL проигнорировал ваши изменения по одной из причин, описанных в разделе Неявные изменения спецификаций столбцов На­пример, если вы пытаетесь изменить тип столбца varchar на CHAR, MySQL будет по-прежнему использовать VARCHAR, если таблица содержит другие столбцы переменной длины.
ALTER TABLE работает, создавая временную копию оригинальной таблицы. Измене­ния производится над копией, затем исходная таблица удаляется, а новая переименовы­вается. В процессе работы ALTER TABLE исходная таблица остается доступной по чтению другим клиентам. Обновления и запись в эту таблицу приостанавливаются до тех пор, пока новая таблица не будет готова, затем автоматически перенаправляются на новую таблицу, без каких-либо потерь.

Помните, что если вы используете любые опции ALTER TABLE за исключением RENAME, MySQL всегда создает временную таблицу, даже если нет строгой необходимо­сти в копировании данных (например, когда вы всего лишь переименовываете столбец). Мы планируем исправить это в будущем, но поскольку ALTER TABLE - оператор, исполь­зуемый нечасто, эта задача не имеет высокого приоритета в наших планах. Для таблиц MyISAM вы можете ускорить процедуру пересоздания индексов (что является самой мед­ленной частью в процессе выполнения этого оператора), присвоив системной перемен­ной myisam_sort_buf fer_size большее значение.

  1. Для использования ALTER TABLE необходимо иметь привилегии ALTER, INSERT и CREATE для таблицы.
  2. IGNORE - это расширение MySQL стандарта SQL. Это слово управляет тем, как ALTER TABLE работает в случае дублирования уникальных ключей в новой таблице. Если IGNORE не указано, копирование прерывается и выполняется откат при обнаружении ошибки дублирования ключей. Если же IGNORE указано, то из строк, дублирующих уникальный ключ, используется только первая. Остальные удаляются.
  3. Вы можете использовать множество конструкций ADD, ALTER, DROP и CHANGE в пределах одного оператора ALTER TABLE. Это также MySQL-расширение стандарта SQL, в котором разрешается только по одному такому предложению в операторе

ALTER TABLE.

  1. CHANGE имя_столбца, DROP имя_столбца и DROP INDEX - это MySQL-расширения стандарта SQL.
  2. MODIFY - это расширение ALTER TABLE от Oracle.

  • Слово COLUMN - совершенно необязательное и может быть опущено.

  1. Если вы используете ALTER TABLE имя_таблицы RENAME TO новое_имя_таблицы без каких-либо опций, MySQL просто переименовывает все файлы, имеющие отношение к таблице имя_таблицы. Нет необходимости создавать временную таблицу. (Вы также можете использовать оператор RENAME TABLE для переименования таблиц. См. раздел Синтаксис RENAME TABLE)
  2. Конструкции определение_столбца используют тот же синтаксис для ADD и CHANGE, что и CREATE TABLE. Следует отметить, что синтаксис включает имя столбца, а не только его тип. См. раздел Синтаксис CREATE TABLE
  3. Вы можете переименовывать столбцы, используя конструкцию CHANGE старое_имя_столбца определение_столбца. Чтобы сделать это, укажите старые и новые имена, а также типы столбцов. Например, чтобы переименовать столбец INTEGER из а в b, можно поступить так:

mysql> ALTER TABLE tl CHANGE a b INTEGER;
Если вы хотите изменить только тип столбца, не меняя имени, синтаксис CHANGE все равно требует указания старого и нового имени столбца, даже если эти имена одинаковы. Например: mysql> ALTER TABLE tl CHANGE b b BIGINT NOT NULL;
Однако, начиная с версии MySQL 3.22.16а, вы также можете использовать MODIFY для изменения типа столбца без его переименования: mysql> ALTER TABLE tl MODIFY b BIGINT NOT NULL;

  1. Если вы используете CHANGE или MODIFY, чтобы укоротить столбец, который используется в ключе индекса частично (например, если индекс построен по первым 10 символам столбца VARCHAR), вы не можете сделать столбец короче, чем количество символов, по которым построен индекс.
  2. В MySQL 3.22 и более поздних версиях вы можете воспользоваться first или after жя_столбца, чтобы добавить новый столбец в указанное место строки таблицы. Поумолчанию столбцы добавляются в конец структуры. Начиная с MySQL 4.0.1, FIRST или AFTER имя^столбца можно также применять в операциях CHANGE и MODIFY.
  3. ALTER COLUMN указывает новое значение по умолчанию для столбцы или удаляет старое. Если старое значение по умолчанию удалено, и столбец может принимать значение NULL, то значением по умолчанию становится NULL. Если столбец не может принимать значение NULL, MySQL присваивает ему значение по умолчанию в зависимости от его типа, как описано в разделе Синтаксис CREATE TABLE
  4. DROP INDEX удаляет индекс. Это расширение стандарта SQL от MySQL. См. раздел Синтаксис DROP INDEX
  5. Если столбец удаляется из структуры таблицы, столбец также удаляются из всех индексов, частью ключа которых он была. Если все столбцы, составляющие ключ индекса, удалены, индекс также удаляется.
  6. Если таблица содержит только один столбец, столбец не может быть удален. Если вы при этом имеете в виду удаление таблицы, используйте вместо этого DROP TABLE.
  7. DROP PRIMARY KEY уничтожает индекс первичного ключа. (До версии MySQL 4.1.2, если таблица не имела первичного ключа, оператор DROP PRIMARY KEY уничтожал первый уникальный индекс таблицы. MySQL помечает первый уникальный ключ как первичный (PRIMARY KEY), если таковой не специфицирован явно. Если вы добавляете таблице UNIQUE INDEX или PRIMARY KEY, он сохраняется перед любым неуникальным индексом, чтобы MySQL мог обнаруживать случаи дублирования ключа как можно раньше.)
  8. ORDER BY позволяет создавать новую таблицу со строками в определенном порядке.Следует отметить, что последовательность строк изменится после выполнения вставок и удалений. Эта опция в основном применима, когда вы знаете, что запросы будут выполняться в основном так, чтобы извлекать строки в определенном порядке. Используя эту опцию после существенных изменений в таблице, вы можете добиться увеличения производительности. В некоторых случаях можно облегчить MySQL задачу сортировки, если данные в таблице будут расположены в определенном порядке по значению столбца, в котором позже их придется извлекать.
  9. Если вы используете ALTER TABLE для таблицы MyISAM, все неуникальные индексы создаются в отдельном пакете (как при выполнении REPAIR TABLE). Это может значительно ускорить выполнение ALTER TABLE, если индексов много. Начиная с MySQL 4.0, это средство может быть активизировано явно. ALTER
    TABLE.. .DISABLE KEYS сообщает MySQL, что нужно прекратить обновление неуникальных индексов в таблице MylSAM. ALTER TABLE.. .ENABLE KEYS затем может быть применен для воссоздания индексов. MySQL делает это по специальному алгоритму, который гораздо быстрее, нежели при вставке строк по одной, поэтому отключение индексов перед операцией пакетной вставки должно дать ощути­мое ускорение.

  • Конструкции foreign key и REFERENCES поддерживаются механизмом хранения InnoDB, который реализует ADD [CONSTRAINT [символ]} FOREIGN KEY (...) REFERENCES... (...). Для других механизмов хранения эти конструкции прини­маются, но игнорируются. Конструкция CHECK распознается, но игнорируется всеми механизмами хранения. См. раздел Синтаксис CREATE TABLE Причина, по которой этот синтак­сис принимается, но игнорируется, связана с обеспечением совместимости, упро­щением переноса кода с других SQL-серверов, и необходимостью запуска прило­жений, которые создают таблицы со ссылками. См. раздел Отличия MySQL от стандартного SQL
  • Начиная с версии MySQL 4.0.13, InnoDB поддерживает применение ALTER TABLE для удаления внешних ключей: ALTER TABLE имя_таблицыDROP FOREIGN KEY символик; ШALTER TABLE игнорирует ОПЦИИ DATA DIRECTORY И INDEX DIRECTORY.
  • Начиная с MySQL 4.1.2 и последующих версий, если вы хотите изменить набор символов для всех символьных столбцов (CHAR, VARCHAR, TEXT) на другой, используйте оператор вроде следующего:
ALTER TABLE имя_таблицы CONVERT TO CHARACTER SET имя_набора_символов;
Это удобно, например, после обновления версии MySQL 4.0.x до MySQL 4.1.x. См. раздел Обновление символьных наборов от версии MySQL4.0

Внимание!
Предыдущая операция преобразует значения столбцов между старым и новым наборами симво­лов. Это не то, что нужно, если у вас есть столбец в одном наборе символов (вроде latin i), но со­держит значения, в действительности использующие какой-то другой, несовместимый набор символов (вроде utf8). В этом случае для каждой такой столбцы потребуется выполнить следую­щие операторы:
ALTER TABLE tl CHANGE cl cl BLOB;
ALTER TABLE tl CHANGE cl cl TEXT CHARACTER SET utf8;
Это работает, поскольку не происходит никакого преобразования столбцов типа BLOB. Для изменения только набора символов по умолчанию для таблицы вос­пользуйтесь следующим оператором:
ALTER TABLE имя_таблицыDEFAULT CHARACTER SET имя_набора_символов;
Слово DEFAULT является необязательным. Набор символов по умолчанию - это тот набор, который применяется для новых столбцов, добавляемых к таблице, если не указывается явно (например, в ALTER TABLE...ADD column).
Внимание!
Начиная с MySQL 4.1.2 и выше, ALTER TABLE.. .DEFAULT CHARACTER SET и ALTER TABLE ... CHARACTER SET эквивалентны и изменяют только набор символов по умолчанию. В выпус­ках MySQL 4.1, предшествующих 4.1.2, ALTER TABLE... DEFAULT CHARACTER SET изменяет набор символов по умолчанию, a ALTER TABLE. .. CHARACTER SET изменяет набор символов по умолчанию и преобразует все столбцы.

  • Для таблицы innoDB, которая была создана в своем собственном табличном пространстве в файле . ibd, этот файл может быть отброшен и импортирован. Чтобы отбросить (discard) файл . ibd, воспользуйтесь следующим оператором:
    ALTER TABLE имя_таблицы DISCARD TABLESPACE;
    Приведенный выше оператор удалит текущий файл . ibd, поэтому сначала убеди­тесь, что у вас есть резервная копия. Попытки обратиться к таблице, когда файл табличного пространства отброшен, приводят к ошибке.
    Чтобы импортировать обратно резервную копию файла . ibd, скопируйте его в ка­талог данных и выполните следующий оператор: ALTER TABLE имя__таблицы IMPORT TABLESPACE;
  • С помощью функции С API mysql_info() можно определить, сколько записей было скопировано и (когда применяется IGNORE) сколько записей было удалено из-за дублирования значений уникальных ключей. Ниже показаны некоторые примеры, демонстрирующие применение оператора ALTER TABLE. Начнем с таблицы tl, созданной следующим образом: mysql> CREATE TABLE tl (a INTEGER,b CHAR(IO));
    Чтобы переименовать таблицу из tl в t2, воспользуйтесь таким оператором: mysql> ALTER TABLE tl RENAME tl;
    Чтобы изменить тип столбца с INTEGER на Tinyint NOT NULL (оставив его имя преж­ним), и чтобы изменить тип столбца b с CHAR (10) на CHAR (20) и переименовать его на с, воспользуйтесь таким оператором:
    mysql> ALTER TABLE t2 MODIFY a TIKYIKT ЮТ NULL, CHANGE b с CHAR(20) ;
    Чтобы добавить новый столбец типа TIMESTAMP с именем d, выполните следующий оператор:
    raysql> ALTER TABLE t2 ADD d TIMESTAMP;
    Чтобы добавить индексы по столбцу d и столбцу а, воспользуйтесь таким оператором: mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a); Чтобы удалить столбец с, выполните такой оператор: mysql> ALTER TABLE t2 DROP COLUMN c;
    Чтобы добавить новый столбец с именем с целого типа, имеющий свойство AUTO_ INCREMENT, воспользуйтесь следующим оператором:
    mysql> ALTER TABLE t2 ADD с INT UNSIGNED NOT NULL AUTO_INCREMENT, -> ADD PRIMARY KEY (c);
    Следует отметить, что мы проиндексировали столбец с (как первичный ключ), по­скольку столбцы AUTO_INCREMENT должны быть проиндексированы. Кроме того, столбец с объявлен как NOT NULL, так как столбцы первичного ключа не могут быть NULL.
    Когда добавляется столбец с атрибутом AUTO_INCREMENT, он автоматически заполня­ется значениями последовательности целых чисел. Для таблиц MyISAM можно указать первое число последовательности, выполнив SET INSERT_ID=значение перед запуском ALTER TABLE, или воспользовавшись опцией таблицы AUTO_INCREMENT=3Ha4eH#e. См. раздел Синтаксис SET

    Для таблиц MylSAM, если вы не изменяете столбец AUTO_INCREMENT, числовая последо­вательность не будет затронута. Если вы удаляете столбец autO_inCREMENT, а затем до­бавляете другой столбец AUTO_INCREMENT, то числа будут перенумерованы, начиная с 1.
    В MySQL 3.23.50 и более поздних версиях InnoDB разрешает добавлять новые огра­ничения внешних ключей к таблице с помощью оператора ALTER TABLE:
    ALTER TABLE имя_таблицы
    ADD [CONSTRAINT символ] FOREIGN KEY [идентификатор] [имя_столбца_индекса, .. .)
    REFERENCES имя_таблицы {имя_столбца_индексаг ...)
    [ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    [ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT}]
    He забудьте сначала создать все необходимые индексы. Используя ALTER TABLE, в
    таблицу можно также добавлять внешние ключи, ссылающиеся на эту же таблицу.
    Начиная с MySQL 4.0.13, InnoDB поддерживает применение ALTER TABLE для удале­ния внешних ключей:
    ALTER TABLE имя_таблицыDROP FOREIGN KEY символ_Ы;
    Если конструкция FOREIGN KEY включает имя CONSTRAINT, когда создается внешний ключ, то вы можете ссылаться на него, чтобы удалить этот внешний ключ. (Имя ограни­чения внешнего ключа может присваиваться, начиная с MySQL 4.0.18.) В противном случае InnoDB генерирует внутреннее значение символ_£к при создании внешнего клю­ча. Чтобы узнать это значение, когда вам понадобится удалить внешний ключ, воспользуйтесь оператором SHOW CREATE table, например:
    mysql> SHOW CREATE TABLE ibtestllc\G
    Table: ibtestllc
    Create Table: CREATE TABLE чibtestllc4 ( 'A1 int(ll) NOT NULL auto_increment, 'D' int(ll) NOT NULL default '0', "B' varchar(200) NOT NULL default ", СЧ varchar(175) default NULL, PRIMARY KEY ('AV D4,'B'), KEY VB* (4B\ X") , KEY NC4 TCN),
    CONSTRAINT ч0_38775ч FOREIGN KEY fA\ 'D') REFERENCES 4ibtestlla4 ГА4, *DV) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT ч0_38776ч FOREIGN KEY CB\ Ч СЧ) REFERENCES 4ibtestlla4 (ЧВ\ * СЧ) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB CHARSET=latinl 1 row in set (0.01 sec)
    mysql> ALTER TABLE ibtestllc DROP FOREIGN KEY 0_38775;
    В версиях MySQL до 3.23.50 операторы ALTER TABLE и CREATE INDEX не должны применяться для таблиц, которые имеют ограничения внешних ключей, либо на которые ссылаются внешние ключи других таблиц. Любой оператор ALTER TABLE удаляет все ограничения внешних ключей, определенные в таблице. Нельзя также применять alter TABLE к таблицам, на которые имеются ссылки. Вместо этого нужно пользоваться DROP

    TABLE и CREATE TABLE для модификации схемы. Когда сервер MySQL выполняет ALTER TABLE, он может скрыто выполнять RENAME TABLE, и это сбивает с толку ограничения внешних ключей, которые ссылаются на таблицу. В MySQL оператор CREATE INDEX об­рабатывается как ALTER TABLE, поэтому вышесказанное касается и этого оператора. См. раздел Проблемы с ALTER TABLE