Синтаксис оператора ALTER TABLE

Данная статья является свободным переводом официальной документации MySQL версии 5.0.

Синтаксис оператора ALTER TABLE

Оператор ALTER TABLE позволяет изменять структуру существующей таблицы. Например, вы можете добавлять или удалять колонки, изменять тип существующих колонок, переименовывать колонки, или саму таблицу. Также можно изменить комментарий к таблице и тип таблицы.

Синтаксис оператора ALTER TABLE во многих случаях подобен синтаксису CREATE TABLE. Для получения подробной информации см. раздел 11.1.5, "CREATE TABLE Syntax".

Некоторые операции могут вызвать предупреждения, если совершать их над таблицей, механизм хранения которой не поддерживает такие операции. Эти предупреждения можно просмотреть с помощью оператора SHOW WARNINGS. См. раздел 11.5.4.28, "SHOW WARNINGS Syntax"

Если вы используете оператор ALTER TABLE для изменения колонки, но оператор DESCRIBE tbl_name показывает, что ваша колонка осталась неизменной, то возможно, что сервер MySQL проигнорировал ваши изменения по одной из причин, описанных в разделе 11.1.5.1, "Silent Column Specification Changes"

В большинстве случаев оператор ALTER TABLE работает, создавая временную копию исходной таблицы. Изменения осуществляются на копии, затем исходная таблица удаляется и новая переименовывается. Пока оператор ALTER TABLE выполняется, исходная таблица остается доступной для чтения другим клиентам. Обновление и запись данных в таблицу задерживаются до тех пор пока новая таблица не будет готова, и затем автоматически направляются к новой таблице без каких-либо уведомлений о неудавшихся изменениях. Временная таблица создается в директории базы данных новой таблицы. Она может отличаться от директории базы данных исходной таблицы, если оператор ALTER TABLE с помощью переименования таблицы переносит её в другую базу данных.

Если вы используете оператор ALTER TABLE tbl_name RENAME TO new_tbl_name без каких-либо других опций, MySQL просто переименует файлы, относящиеся к таблице tbl_name. (Для переименования таблиц вы также можете использовать оператор RENAME TABLE. См. раздел 11.1.9, "RENAME TABLE Syntax".) Права доступа пользователей, относящиеся именно к переименованной таблице, не перенесутся на новое имя. Они должны быть изменены вручную с помощью операторов GRANT и REVOKE.

Если вы используете оператор ALTER TABLE с любыми отличными от RENAME опциями, MySQL всегда создает временную таблицу даже если данные заведомо не требуется копировать (например, когда меняется название колонки). Для таблиц типа MyISAM вы можете ускорить пересоздание индексов (это самая медленная часть в процессе изменения) установкой для системной переменной myisam_sort_buffer_size большей величины.

Информацию о возможных проблемах при использовании оператора ALTER TABLE см. в разделе B.1.7.1. "Problems with ALTER TABLE".

• Для выполнения оператора ALTER TABLE необходимо иметь привелегии ALTER, INSERT и CREATE на изменяемую таблицу.
• Ключевое слово IGNORE - это расширение MySQL к стандарту SQL. Оно определяет как будет работать оператор ALTER TABLE, если в результате изменения таблицы произойдет дублирование уникального ключа или возникнет предупреджение при работе в SQL-режиме strict. Если ключевое слово IGNORE не указано, то при возникновении ошибки копирование прерывается и откатывается назад. Если указано ключевое слово IGNORE, то используется только первая строка среди строк с дублирующимся уникальным ключом, остальные конфликтующие строки удаляются. Некорректные значения приводятся к ближайшему допустимому значению.
• table_option обозначает опции таблицы, которые могут быть использованы в операторе CREATE TABLE, такие как ENGINE, AUTO_INCREMENT или AVG_ROW_LENGTH. (В разделе 12.1.10, "CREATE TABLE Syntax" перечисленны все табличные опции). Однако, оператор ALTER TABLE игнорирует опции DATA DIRECTORY и INDEX DIRECTORY.
  Например, для преобразования механизма хранения таблицы к InnoDB используется следующее выражение:
  ALTER TABLE t1 ENGINE = InnoDB;
  Успешность попытки изменить механизм хранения таблицы будет зависеть от того, доступен ли назначаемый механизм хранения и устанавлен ли режим SQL NO_ENGINE_SUBSTITUTION, как описано в разделе 5.1.7, "Server SQL Modes".
  Начиная с версии MySQL 5.0.23, для предотвращения непреднамеренной потери данных оператор ALTER TABLE не может быть использован для изменения механизма хранения таблицы на MERGE или BLACKHOLE.
  Для изменения значения счетчика AUTO_INCREMENT, используемого для нумерации новых строк, выполните следующее выражение:
  ALTER TABLE t2 AUTO_INCREMENT = value;
  Нельзя установить значение счетчика меньше или равное максимальному значению первичного ключа среди данных, содержащихся в таблице. Если для MyISAM-таблиц устанавливаемое значение меньше или равно текущему максимальному значению в AUTO_INCREMENT колонке, то установленное значение будет на единицу превосходить текущее максимальное значение. Для InnoDB-таблиц можно использовать оператор ALTER TABLE ... AUTO_INCREMENT = value, начиная с версии MySQL 5.0.3, но если устанавливаемое значение меньше, чем текущее максимальное значение в колонке, то сообщение об ошибке не возникнет и текущее значение счетчика останется неизменным.
• Можно использовать несколько конструкций ADD, ALTER, DROP и CHANGE в одном операторе ALTER TABLE, разделяя их запятой. Это расширение MySQL к стандарту SQL, который позволяет только одну конструкцию в операторе ALTER TABLE. Например, для удаления нескольких колонок одной командой, используйте следующее выражение:
  ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
• Конструкции CHANGE col_name, DROP col_name и DROP INDEX — расширение MySQL к стандарту SQL.
• Ключевое слово MODIFY — расширение Oracle оператора ALTER TABLE.
• Ключевое слово COLUMN необязательно и может быть опущено.
• В части column_definition для конструкций ADD и CHANGE используется тот же синтаксис, что и для оператора CREATE TABLE. См. раздел 12.1.10, "CREATE TABLE Syntax".
• Переименовать колонку можно, используя конструкцию CHANGE old_col_name  new_col_name  column_definition. Для этого нужно указать старое и новое имя колонки и её текущие свойства. Например, для переименования INTEGER колонки из a в b используйте следующее выражение:
  ALTER TABLE t1 CHANGE a b INTEGER;
  Если требуется изменить только тип колонки, синтаксис конструкции CHANGE все равно требует старого и нового имен колонки, несмотря на то, что они совпадают. Например:
  ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
  Для изменения только типа колонки без переименования можно использовать конструкцию MODIFY:
  ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
• Если при использовании операций CHANGE или MODIFY уменьшается длина данных для колонки, имеющей индекс, и получившаяся длина меньше, чем длина индекса, MySQL автоматически уменьшает его размер.
• Если с помощью операций CHANGE или MODIFY меняется тип данных, MySQL пытается преобразовать находящиеся в колонке величины к новому типу данных.

  Обратите внимание
  Такое преобразование может привести к искажению данных. Например, при уменьшении длины для строковой колонки данные могут быть обрезаны. Для предотвращения операций по преобразованию данных, результатом которых станет их потеря, необходимо включить SQL-режим "strict" до использования оператора ALTER TABLE (см. раздел 5.1.6, "SQL Modes").

• Для добавления колонки на определенную позицию используются ключевые слова FIRST или AFTER col_name. По умолчанию добавляемая колонка становится последней. Кроме того, ключевые слова FIRST и AFTER можно использовать в операциях CHANGE или MODIFY для изменения положения колонки в таблице.
• Оператор ALTER ... SET DEFAULT или ALTER ... DROP DEFAULT соответственно устанавливает для колонки новую величину по умолчанию или удаляет существующую. Если старая величина по умолчанию удалена и колонка может принимать значение NULL, то новой величиной по умолчанию будет NULL. Если колонка не может принимать значение NULL, MySQL назначит величину по умолчанию, как это описано в разделе 9.1.4, "Data Type Default Values".
• Конструкция DROP INDEX удаляет индекс. Это расширение MySQL к стандарту SQL. См. раздел 11.1.7, "DROP INDEX Syntax". Если вы не уверены в имени индекса, используйте оператор SHOW INDEX FROM tbl_name.
• Если колонка удалена из таблицы, она автоматически удаляется из всех индексов, в которые входит. Если удалены все колонки, входящие в индекс, то этот индекс тоже удаляется.
• Если таблица содержит только одну колонку, то колонка не может быть удалена. Если подразумевается удаление таблицы, нужно использовать оператор DROP TABLE.
• Конструкция DROP PRIMARY KEY удаляет основной индекс. Если основной индекс отсутствует, произойдет ошибка.
  Если в таблицу добавляются первичный или уникальный индекс, то они записываются раньше всех неуникальных индексов. Это делается для того, чтобы MySQL мог определить дублирование ключа как можно раньше.
• Некоторые механизмы хранения позволяют определять тип индекса при его создании. Синтаксис для index_type определен как USING type_name. Подробнее об использовании USING см. раздел 12.1.8, "CREATE INDEX Syntax".
• После выполнения оператора ALTER TABLE, возможно, будет необходимо выполнить оператор ANALYZE TABLE для обновления информации о количестве уникальных значений индекса. См. раздел 12.5.5.18, "SHOW INDEX Syntax".
• Конструкция ORDER BY позволяет задать определенный порядок строк в обновленной таблице. Обратите внимание, что таблица не остается с таким порядком строк после добавлений и удалений. Эта опция полезна, в основном, когда точно известно, что в большинстве случаев строки будут запрашиваться в определенном порядке. Использование этой опции после значительного изменения таблицы может улучшить производительность. В ряде случаев это может облегчить для MySQL сортировку, если таблица хранится с таким порядком строк, который мы потом хотим получить.
• Синтаксис конструкции ORDER BY позволяет указывать несколько колонок для сортировки, после каджой из которых может следовать необязательное ключевое слово ASC или DESC, определяющее порядок сортировки по возрастанию или убыванию соответственно. По умолчанию сортировка производится по возрастанию. Для указания порядка сортировки можно использовать только имена колонок; любые выражения запрещены.
• Конструкция ORDER BY игнорируется для InnoDB-таблиц, имеющих определенный пользователем кластерный индекс (первичный ключ или уникальный индекс с флагом NOT NULL). InnoDB всегда сортирует строки в соответствии с таким индексом, если он присутствует в таблице. То же самое справедливо для BDB таблиц, имеющих первичный ключ.
• При использовании оператора ALTER TABLE для MyISAM-таблиц все неуникальные индексы создаются вместе в рамках специального алгоритма (как при использовании оператора REPAIR TABLE). Такой подход делает оператор ALTER TABLE значительно быстрее при наличии большого количества индексов.
  Такой режим работы может быть явно назначен для MyISAM-таблиц. Оператор ALTER TABLE ... DISABLE KEYS отключает обновление неуникальных индексов. Затем с помощью оператора ALTER TABLE ... ENABLE KEYS можно пересоздать недостающие индексы. MySQL делает это по специальному алгоритму, который работает значительно быстрее, чем если добавлять ключи один за другим, таким образом, отключение ключей перед добавлением большого количества строк может значительно увеличить быстродействие. Использование оператора ALTER TABLE ... DISABLE KEYS требует обладание привилегией INDEX в дополнение к указанным ранее.
  Во время отключения неуникальные индексы игнорируются такими операторами как SELECT и EXPLAIN, которые используют их в иное время.
• Если в результате применения оператора ALTER TABLE к InnoDB-таблице изменились данные в колонках (например, по причине того что они оказались обрезаны), проверка целостности внешних ключей не извещает о возможных нарушениях из-за такого изменения значений.
• Конструкции FOREIGN KEY и REFERENCES поддерживаются только механизмом хранения InnoDB, который поддерживает конструкцию ADD [CONSTRAINT [symbol]] FOREIGN KEY (...) REFERENCES ... (...). См. раздел 13.2.4.4, "FOREIGN KEY Constraints". Для остальных механизмов хранения данные конструкции опознаются, но игнорируются. Конструкция CHECK распознается, но игнорируется всеми механизмами хранения. См. раздел 12.1.10, "CREATE TABLE Syntax". Причины распознования, но игнорирования синтаксических конструкций заключаются в переносимости; это облегчает перенос кода с других SQL серверов и позволяет выполнять приложения, создающие таблицы с внешними ссылками. См. раздел 1.7.5, "MySQL Differences from Standard SQL".

  Обратите внимание
  Механизм хранения InnoDB игнорирует использование конструкции REFERENCES в части определения колонки. Допускается использовать конструкцию REFERENCES только при определении внешнего ключа.

• InnoDB поддерживает использование оператора ALTER TABLE для удаления внешних ключей:
  ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
  Для получение подробной информации см. раздел 13.2.4.4, “FOREIGN KEY Constraints”.
• Нельзя добавлять и удалять внешние ключи одним оператором ALTER TABLE. Для этого нужно использовать отдельные выражения.
• Для таблиц InnoDB, созданных с собственным табличным пространством в .ibd файле, этот файл может быть отключен и импортирован. Для отключения .ibd файла используйте следующее выражение:
  ALTER TABLE tbl_name DISCARD TABLESPACE;
  Эта операция удаляет текущий .ibd файл, поэтому будте уверены, что вы предварительно сделали бекап. Попытка доступа к таблице при отключенном файле табличного пространства приведет к ошибке.
  Для импорта в таблицу бекап файла .ibd, скопируйте его в директорию базы данных и выполните следующее выражение:
  ALTER TABLE tbl_name IMPORT TABLESPACE;
  См. раздел 13.2.2.1, “Using Per-Table Tablespaces”.
• Находящийся в очереди на выполнение оператор INSERT DELAYED отменяется, если оператор ALTER TABLE изменяет структуру таблицы.
• Для изменения кодировки по умолчанию таблицы и всех строковых колонок (CHAR, VARCHAR, TEXT) используйте следующее выражение:
  ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
  Для колонок с типом данных VARCHAR или одним из типов семейства TEXT операция CONVERT TO CHARACTER SET изменит тип данных таким образом, чтобы колонка могла содержать столь же много символов как и до смены кодировки. Например, в колонке типа TEXT два байта отводятся на хранение информации о длине данных в байтах, которая в этом случае не может быть больше 65535. В случае кодировки latin1 для хранения каждого символа требуется один байт, таким образом колонка типа TEXT с кодировкой latin1 может содержать до 65535 символов. Если колонку перекодировать в utf8, то для хранения одного символа потребуется три байта, а для хранения максимально возможного для данного типа количества символов потребуется 3 × 65,535 = 196,605 байт. Данное количество превышает длину данных в байтах, которую может содержать тип данных TEXT, поэтому MySQL преобразует тип данных в MEDIUMTEXT, который является наименьшим из строковых типов, для которого длина данных в байтах может равняться 196,605. Подобным образом колонка типа VARCHAR может быть преобразована в MEDIUMTEXT.
  Для предотвращения смены типов данных как описано выше не используйте операцию CONVERT TO CHARACTER SET. Вместо этого используйте MODIFY для изменения отдельных колонок. Например:
  ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
  ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;
  Если вы указывате CONVERT TO CHARACTER SET binary, колонки типов CHAR, VARCHAR и TEXT преобразуются в соответствующие им бинарные строковые типы (BINARY, VARBINARY, BLOB). Это означает. что колонки больше не имеют кодировки и последующие операции CONVERT TO не применяются к ним.
  Если в качестве charset_name указано значение DEFAULT, будет использована кодировка базы данных.

  Обратите внимание
  Операция CONVERT TO преобразует данные из одной кодировки в другую. Это может привести к нежелательным последствиям, если колонка имеет одну кодировку (например, latin1), а данные реально имеют некоторую другую кодировку, несовместимую с первой (например, utf8). В этом случае для каждой подобной колонки необходимо выполнить следующие команды:

  ALTER TABLE t1 CHANGE c1 c1 BLOB;
  ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

  Причина таких действий заключается в том, что при преобразовании данных в или из бинарного типа, преобразования между кодировками не происходит.

  Для изменения только кодировки по умолчанию таблицы используйте следующее выражение:
  ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
  Ключевое слово DEFAULT необязательно. Кодировка по умочанию таблицы определяет кодировку, которая будет использована при последующем добавлении колонки без явного указания её кодировки (например, с помощью оператора ALTER TABLE ... ADD column).

Используя mysql_info() C API функции, вы можете определить сколько строк было скопировано и (в случае использования ключевого слова IGNORE) сколько строк было удалено вследствии дублирования уникального ключа. См. раздел 20.9.3.35, “mysql_info()”.

Ниже приведены примеры, иллюстрирующие использование оператора ALTER TABLE. Сначала создадим таблицу t1 как показано ниже:

Для переименования таблицы из t1 в t2:

Для изменения колонки a с INTEGER на TINYINT NOT NULL (без переименования) и изменения колонки b с CHAR(10) на CHAR(20), одновременно переименуя её из b в c:

Для добавления новой колонки d типа TIMESTAMP:

Для добавления индекса на колонку d и уникального индекса на колонку a:

Для удаления колонки c:

Для добавления новой целочисленной AUTO_INCREMENT колонки c:

Обратите внимание, что мы сделали колонку c первичным ключом, так как в таблице может быть только одна AUTO_INCREMENT колонка и она должна быть ключом. Вследствии того, что первичный ключ не может принимать NULL значения, мы определили колонку с как NOT NULL.

При добавлении AUTO_INCREMENT колонки она автоматически заполняется последовательными значениями целых чисел. Для MyISAM-таблиц можно указать величину первого значения, выполнив команду SET INSERT_ID=value перед оператором ALTER TABLE или используя табличную опцию AUTO_INCREMENT=value. См. раздел 5.1.4, “Session System Variables”.

В случае MyISAM-таблиц если не происходит смены AUTO_INCREMENT колонки нумерация чисел не меняется. Если сначала удалить AUTO_INCREMENT колонку, а затем создать новую AUTO_INCREMENT колонку, то её заполнение начнется с единицы.

При использовании репликации добавление AUTO_INCREMENT колонки не гарантирует одинаковый порядок строк на мастере и slave. Это поисходит вследствии того, что порядок нумерации строк зависит от особенностей механизма хранения используемого для таблицы и того в каком порядке были добавлены строки в таблицу. Если важно иметь одинаковый порядок строк на мастере и slave, строки должны быть упорядочены до назначения AUTO_INCREMENT номера. Предположим, что вы хотите добавить AUTO_INCREMENT колонку к таблице t1, следующий оператор новую таблицу t2 идентичную t1, но имеющую AUTO_INCREMENT колонку:

Здесь предполагается, что таблица t1 имеет колонки col1 и col2.

Следующие операторы также создают новую таблицу t2 идентичную t1, но имеющую AUTO_INCREMENT колонку:

Обратите внимание
Для гарантии одинакового порядка строк на мастере и slave все колонки таблицы t1 должны быть перечислены в части ORDER BY.

Независимо от метода, используемого для создания и наполнения копии, имеющей AUTO_INCREMENTколонку, заключительный этап совпадает: удаление исходной таблицы и переименование копии: