MySQL-инструкция ALTER TABLE
изменяет структуру таблицы. Например, можно добавлять или удалять столбцы, создавать или уничтожать индексы, изменять тип существующих столбцов или переименовывать столбцы или саму таблицу. Также можно изменить такие характеристики, как механизм хранения, используемый для таблицы, или комментарий к таблице.
Синтаксис многих допустимых изменений подобен инструкциям CREATE TABLE
. По этому, в материале будут рассмотрены только особенности и различие синтаксиса ALTER TABLE
.
ALTER TABLE
;PRIMARY KEY
и INDEX
;FOREIGN KEY
;CHECK
;ALTER TABLE
.ALTER TABLE
.ALTER TABLE tbl_name
-- изменение свойств таблицы
RENAME [TO | AS] new_tbl_name
CHARACTER SET=charset_name [COLLATE=collation_name]
CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
-- добавление/изменение/удаление столбцов
-- изменяемой таблицы
ADD COLUMN col_name column_definition [FIRST | AFTER col_name]
CHANGE COLUMN old_col_name new_col_name column_definition
[FIRST | AFTER col_name]
MODIFY COLUMN col_name column_definition
[FIRST | AFTER col_name]
ALTER COLUMN col_name SET DEFAULT literal | (expr)
ALTER COLUMN col_name DROP DEFAULT
RENAME COLUMN old_col_name TO new_col_name
DROP COLUMN col_name
-- добавление/изменение/удаление индексированных
-- столбцов изменяемой таблицы
ADD INDEX [index_name] (col_name1[, col_name2, ... ]) [USING BTREE | HASH]
ADD UNIQUE KEY [index_name] [USING BTREE | HASH]
ADD FULLTEXT | SPATIAL [INDEX] [index_name]
RENAME INDEX old_index_name TO new_index_name
DROP INDEX index_name
ADD PRIMARY KEY reference_definition
DROP PRIMARY KEY
ADD [CONSTRAINT index_name] FOREIGN KEY reference_definition
DROP FOREIGN KEY index_name
-- добавление/изменение/удаление индексированных
-- столбцов изменяемой таблицы
ADD [CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED]
ALTER CHECK check_name [NOT] ENFORCED
DROP CHECK check_name
ALTER TABLE
.tbl_name
: имя изменяемой таблицыnew_tbl_name
: новое уникальное имя изменяемой таблицыcharset_name
: имя кодировки, в которой будут храниться символьные и текстовые типы данных столбцов таблицы. Список доступных кодировок можно посмотреть командой SHOW CHARACTER SET;
. Подробнее в материале "Преобразование типов и кодировки в БД MySQL".collation_name
: имя кодировки, которая будет использоваться при поиске/сравнении/сортировке символьных и текстовых типов данных. Список доступных кодировок можно посмотреть командой SHOW COLLATION;
. Подробнее в материале "Преобразование типов и кодировки в БД MySQL".col_name
: имя столбца изменяемой таблицыcolumn_definition
: определение столбца изменяемой таблицы подобно синтаксису CREATE TABLE
. Подробнее в материале "Создание таблиц CREATE TABLE в БД MySQL".old_col_name
: имя изменяемого столбца new_col_name
: новое уникальное имя изменяемого столбцаliteral
: значение по умолчанию, которое будет принимать столбец, если его значение не указано в инструкции INSERT
при добавлении записи в таблицу. Тип этого значения должен соответствовать значению data_type
.(expr)
- выражение заключенное в скобки. Это выражение, значение которого будет использоваться соответствующим оператором. Обычно выражение содержит ограниченные конструкции. Если выражение будет содержать запрещенные конструкции, то возникает ошибка.col_name1
/col_name2
: строка, уникальное имя столбца/колонки в создаваемой таблице.old_index_name
: имя изменяемого индексаindex_name
: уникальное имя индекса new_index_name
: новое уникальное имя изменяемого индекса check_name
необязательное уникальное имя ограничения, которое используется для проверки вставляемых значений в колонки таблицы. Если оно не задано, то MySQL генерирует его самостоятельно.ALTER TABLE
:Чтобы использовать инструкцию ALTER TABLE
, необходимы привилегии пользователя ALTER
, CREATE
и INSERT
для таблицы. Переименование таблицы требует привилегий ALTER
и DROP
.
После имени таблицы tbl_name
необходимо указать изменения, которые необходимо внести. Если ничего не указано, то инструкция ALTER TABLE
ничего не делает.
Для операторов ADD
и CHANGE
в параметре column_definition
используется тот же синтаксис, что и для инструкции CREATE TABLE
.
Слово COLUMN
является необязательным и может быть опущено, за исключением RENAME COLUMN
(чтобы отличить операцию переименования столбца от операции переименования таблицы RENAME
).
В одном операторе ALTER TABLE
допускается использование нескольких операторов ADD
, ALTER
, DROP
и CHANGE
, разделенных запятыми. Это расширение MySQL для стандартного SQL, которое допускает только одно из каждого предложения для оператора ALTER TABLE
. Например, чтобы удалить несколько столбцов в одном запросе, можно сделать следующее:
ALTER TABLE t2
DROP COLUMN c,
DROP COLUMN d;
Если при использовании ALTER TABLE
для изменения CHARACTER SET
или COLLATE
появляется ошибка повторяющегося ключа, то причина либо в том, что новое COLLATE
сопоставляет два ключа с одним и тем же значением, либо в том, что таблица повреждена. В последнем случае необходимо запустить команду REPAIR TABLE
для таблицы. REPAIR TABLE
работает с таблицами MyISAM, ARCHIVE и CSV.
Если ALTER TABLE
используется для движка InnoDB или MySQL работает под Windows, то ALTER TABLE
разблокирует таблицу, если она заблокирована LOCK TABLE
. Это происходит потому, что InnoDB и Windows не могут удалить таблицу, которая используется.
Инструкции ALTER ... SET DEFAULT ...
или ALTER ... DROP DEFAULT
указывает новое значение по умолчанию для столбца или удаляют старое значение по умолчанию соответственно. Если старое значение по умолчанию удалено, то новое значение по умолчанию будет равно NULL
. Если столбец не может быть NULL
, то MySQL будет пытаться присвоить значение по умолчанию которое возможно для установленного в столбце типа.
Операторы CHANGE
, MODIFY
, RENAME COLUMN
и ALTER
позволяют изменять имена и определения существующих столбцов. Они имеют следующие сравнительные характеристики:
CHANGE
:
MODIFY
или RENAME COLUMN
за счет удобства некоторых операций.FIRST
или AFTER col_name
можно изменить порядок столбцов.MODIFY
:
CHANGE
, чтобы изменить определение столбца, не переименовывая его.FIRST
или AFTER col_name
можно изменить порядок столбцов.RENAME COLUMN
:
CHANGE
, для переименования столбца без изменения его определения.ALTER
:
DEFAULT
.Инструкция CHANGE
- это расширение MySQL для стандартного SQL. MODIFY
и RENAME COLUMN
- это расширения MySQL для совместимости с Oracle.
Для изменения имени И определения столбца, лучше использовать оператор CHANGE
, который позволяет менять все за один запрос. Например, чтобы переименовать столбец с a
на b
и изменить его определение INT NOT NULL
для использования типа данных BIGINT
, сохранив при этом атрибут NOT NULL
, нужно сделать следующее:
ALTER TABLE t1
CHANGE a b BIGINT NOT NULL;
Чтобы изменить ТОЛЬКО определение столбца, можно использовать CHANGE
или MODIFY
. Синтаксис CHANGE
требует дважды указывать одно и то же имя столбца, чтобы оставить его без изменений. Например, чтобы изменить определение столбца b
, нужно сделать следующее:
ALTER TABLE t1
CHANGE b b INT NOT NULL;
Оператор MODIFY
более удобен для изменения определения без изменения имени:
ALTER TABLE t1
MODIFY b INT NOT NULL;
Чтобы ТОЛЬКО переименовать столбец, а определение оставить прежним, можно использовать CHANGE
или RENAME COLUMN
. С оператором CHANGE
синтаксис требует повторного указания определения столбца (которое столбец имеет в настоящее время). Например, чтобы переименовать столбец b
, который имеет определение INT NOT NULL
на a
с тем же определением, нужно сделать следующее:
ALTER TABLE t1
CHANGE b a INT NOT NULL;
С оператором RENAME COLUMN
удобнее изменять имя столбца без изменения его определения:
ALTER TABLE t1 RENAME COLUMN b TO a;
Для изменений определения столбца с помощью CHANGE
или MODIFY
определение должно включать тип данных и все атрибуты, которые должны применяться к новому столбцу, кроме атрибутов индекса, таких как PRIMARY KEY
или UNIQUE
. Атрибуты, присутствующие в исходном определении, но не указанные для нового определения, не переносятся. Предположим, что столбец col1
определен как INT UNSIGNED DEFAULT 1 COMMENT 'столбец'
. Пробуем изменить столбец, намереваясь изменить только INT
на BIGINT
:
ALTER TABLE t1
MODIFY col1 BIGINT;
Этот запрос изменяет тип данных с INT
на BIGINT
, но также удаляет атрибуты UNSIGNED
, DEFAULT
и COMMENT
. Чтобы сохранить их, инструкция должна включать их явно:
ALTER TABLE t1
MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'столбец';
Для изменения типа данных с помощью CHANGE
или MODIFY
MySQL пытается преобразовать существующие значения столбца в новый тип настолько хорошо, насколько это возможно.
Предупреждение. Это преобразование может привести к изменению данных. Например, если сократить строковый столбец, то значения могут быть усечены. Чтобы предотвратить потерю информации, если преобразование в новый тип приведет к изменению данных, необходимо включить строгий режим SQL.
Если использовать CHANGE
или MODIFY
для уменьшения размера хранимых данных в столбце, для которого существует индекс, а результирующая длина столбца меньше, чем длина индекса, то MySQL автоматически укорачивает индекс.
Для столбцов, переименованных CHANGE
или RENAME COLUMN
, MySQL не переименовывает автоматически ссылки на эти столбцы, которые используются в представлениях и/или сохраненных функциях и процедурах. Все эти ссылки необходимо изменить вручную.
Чтобы изменить порядок столбцов в таблице, необходимо в операциях CHANGE
или MODIFY
использовать атрибуты FIRST
и AFTER col
.
PRIMARY KEY
и INDEX
.Инструкция DROP PRIMARY KEY
удаляет первичный ключ. Если первичного ключа нет, то возникает ошибка.
Если включена системная переменная sql_require_primary_key
, попытка удалить первичный ключ приведет к ошибке.
Если в таблицу добавлен UNIQUE INDEX
или PRIMARY KEY
, то MySQL сохранит его перед любым неуникальным индексом, чтобы разрешить обнаружение повторяющихся ключей как можно раньше.
Инструкция DROP INDEX
удаляет индекс. Это расширение MySQL для стандартного SQL. Чтобы посмотреть имена индексов таблицы, необходимо использовать команду SHOW INDEX FROM tbl_name
.
Некоторые механизмы хранения позволяют указать тип индекса при их создании. Синтаксис USING BTREE | HASH
.
Инструкция RENAME INDEX old_index_name TO new_index_name
переименовывает индекс. Это расширение MySQL для стандартного SQL. Содержимое таблицы остается неизменным. Параметр old_index_name
должен быть именем существующего индекса в таблице, который не удаляется тем же оператором ALTER TABLE
. Параметр new_index_name
это новое имя индекса, которое не может дублировать имя индекса в результирующей таблице после применения изменений. Ни одно из имен индексов не может иметь имя PRIMARY
.
Если вы используете ALTER TABLE для таблицы MyISAM, все неуникальные индексы создаются в отдельном пакете (как для REPAIR TABLE). Это должно сделать ALTER TABLE намного быстрее, когда у вас много индексов.
После операций по изменению индексов может потребоваться запустить команду ANALYZE TABLE
для обновления информации о количестве элементов индекса.
FOREIGN KEY
.Для ALTER TABLE
, в отличие от CREATE TABLE
, инструкция ADD FOREIGN KEY
игнорирует index_name
, если оно задано, и использует автоматически сгенерированное имя внешнего ключа. В качестве обходного пути, чтобы указать имя внешнего ключа, необходимо дополнительно указать оператор CONSTRAINT index_name
:
ALTER TABLE t1
ADD CONSTRAINT index_name FOREIGN KEY (....) ...
Важно. MySQL молча игнорирует встроенные спецификации
REFERENCES
, где ссылки определены как часть спецификации столбца. MySQL принимает только предложенияREFERENCES
, определенные как часть отдельной спецификацииFOREIGN KEY
.
CHECK
.До версии MySQL 8.0.16 (узнать версию SELECT VERSION();
) оператор ALTER TABLE
допускал только ограниченную версию синтаксиса: ADD CHECK (expr)
, которая анализировалась и игнорировалась.
Начиная с MySQL 8.0.16, оператор ALTER TABLE
позволяет добавлять, удалять или изменять ограничения CHECK
.
Добавление нового ограничения CHECK
. Значение элементов синтаксиса такое же, как и для CREATE TABLE
:
ALTER TABLE tbl_name
ADD [CONSTRAINT check_name] CHECK (expr) [[NOT] ENFORCED];
Удаление существующего ограничения CHECK
с именем check_name
:
ALTER TABLE tbl_name
DROP CHECK check_name;
Включение ENFORCED
или выключение NOT ENFORCED
проверки ограничения CHECK
с именем check_name
:
ALTER TABLE tbl_name
ALTER CHECK check_name [NOT] ENFORCED;
Предложения DROP CHECK
и ALTER CHECK
являются расширениями MySQL для стандартного SQL.
Начиная с MySQL 8.0.19 (узнать версию SELECT VERSION();
), ALTER TABLE
допускает более общий (и стандартный для SQL) синтаксис для удаления и изменения существующих ограничений любого типа, где тип ограничения определяется по имени ограничения:
Удаление существующего ограничения с именем check_name
:
ALTER TABLE tbl_name
DROP CONSTRAINT check_name;
Если включена системная переменная sql_require_primary_key
, то попытка удалить первичный ключ приведет к ошибке.
Включение/выключение проверки ограничения CHECK
с именем check_name
:
ALTER TABLE tbl_name
ALTER CONSTRAINT symbol [NOT] ENFORCED;
Если изменение таблицы приводит к нарушению принудительного ограничения CHECK, то возникает ошибка, и таблица не изменяется. Примеры операций, при которых возникает ошибка:
AUTO_INCREMENT
к столбцу, используемому в ограничении CHECK
.CHECK
или применить непринудительное ограничение CHECK
, для которых существующие строки нарушают условие ограничения.CHECK
относится только к одному столбцу, то удаление столбца автоматически удаляет ограничение.Чтобы изменить набор символов (кодировку) таблицы по умолчанию и все столбцы символов CHAR
, VARCHAR
, TEXT
на новый набор символов (кодировку), нужно использовать следующий запрос:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
Запрос также изменяет сопоставление COLLATE
всех столбцов символов. Если не указать предложение COLLATE
, то оператор CHARACTER SET
использует сопоставление по умолчанию для указанного набора символов (кодировки). Если это сопоставление не подходит для предполагаемого использования таблицы (например, если оно изменится с сопоставления с учетом регистра на сопоставление без учета регистра), то необходимо указать сопоставление COLLATE
явно.
Для столбца с типом данных VARCHAR
или одним из типов TEXT
команда CONVERT TO CHARACTER SET
изменяет тип данных по мере необходимости. Такое изменение необходимо, чтобы новый столбец был достаточно длинным для хранения такого же количества символов, как исходный столбец. Например, столбец TEXT
имеет два байта длины, которые хранят длину значений в байтах в столбце, максимум до 65 535. Для столбца latin1 TEXT
каждому символу требуется один байт, поэтому столбец может хранить до 65 535 символов. Если столбец преобразован в utf8
, то для каждого символа может потребоваться до трех байтов, при максимально возможной длине 3 × 65535 = 196605 байт. Эта длина не умещается в байтах длины столбца TEXT
, поэтому MySQL преобразует тип данных в MDIUMTEXT
, который является наименьшим строковым типом, для которого байты длины могут записывать значение 196 605. Точно так же столбец VARCHAR
может быть преобразован в MEDIOMTEXT
.
Чтобы избежать только что описанных изменений типа данных, не используйте CONVERT TO CHARACTER SET
. Вместо этого, для изменения отдельных столбцов нужно использовать оператор MODIFY
. Например:
ALTER TABLE t
MODIFY colText_latin1 TEXT CHARACTER SET utf8;
ALTER TABLE t
MODIFY colVarchar_latin1 VARCHAR(M) CHARACTER SET utf8;
Предупреждение. Операция
CONVERT TO
преобразует значения столбца между исходным и именованным наборами символов. Это не то, что обычно нужно. Например, если есть столбец с одной кодировкой (например, latin1), но сохраненные значения фактически используют другую, несовместимую кодировку (например, utf8), то в этом случае необходимо сделать следующее для каждого такого столбца:ALTER TABLE t1 CHANGE c1 c1 BLOB; ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;Причина, по которой это работает, заключается в том, что при преобразовании в тип
BLOB
или изBLOB
- преобразования не происходит.
Чтобы изменить только набор символов (кодировку) по умолчанию для таблицы, необходимо использовать запрос:
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;
Оператор DEFAULT
не является обязательным. Набор символов (кодировка) по умолчанию - это набор символов, который используется для хранения данных символьных и текстовых столбцов, если столбец добавляется позже (например, с помощью ALTER TABLE ... ADD column
).
Если включена системная переменная foreign_key_checks
(это настройкой по умолчанию), то преобразование набора символов не разрешено для таблиц, содержащих столбец, используемый в FOREIGN KEY
. Обходной путь заключается в отключении foreign_key_checks
перед выполнением преобразования набора символов. Необходимо выполнить преобразование в обеих таблицах, участвующих в ограничении внешнего ключа, прежде чем включить foreign_key_checks
. Если включить foreign_key_checks
после преобразования только одной из таблиц, то операция ON DELETE CASCADE
или ON UPDATE CASCADE
может повредить данные в ссылочной таблице из-за неявного преобразования, которое происходит во время этих операций.
ALTER TABLE
.Начнем с таблицы t1
, созданной, как показано здесь:
CREATE TABLE t1 (a INTEGER, b CHAR(10));
Переименуем таблицу с t1
на t2
:
ALTER TABLE t1 RENAME t2;
Изменим столбец a
с INTEGER
на TINYINT NOT NULL
(оставив имя прежним) и изменим столбец b
с CHAR(10)
на CHAR(20)
, а также переименуем его с b
на c
:
ALTER TABLE t2
MODIFY a TINYINT NOT NULL,
CHANGE b c CHAR(20);
Добавим новый столбец TIMESTAMP
с именем d
:
ALTER TABLE t2 ADD d TIMESTAMP;
Добавим индекс для столбца d
и индекс UNIQUE
для столбца a
:
ALTER TABLE t2
ADD INDEX (d),
ADD UNIQUE (a);
Удалим столбец c
:
ALTER TABLE t2 DROP COLUMN c;
Добавим новый целочисленный столбец AUTO_INCREMENT
с именем c
:
ALTER TABLE t2
ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (c);
Запрос проиндексировал столбец c
(как PRIMARY KEY
), т.к. столбцы AUTO_INCREMENT
должны быть индексированными. Также в запросе объявлен столбец c
как NOT NULL
, т.к. первичные ключи не могут быть NULL
.
Когда добавляется столбец AUTO_INCREMENT
, значения столбцов автоматически заполняются порядковыми номерами. Для таблиц MyISAM можно установить первый порядковый номер, выполнив SET INSERT_ID=value
перед ALTER TABLE
или используя параметр таблицы AUTO_INCREMENT=value
.
В таблицах MyISAM, если не изменить столбец AUTO_INCREMENT
, то порядковый номер не изменится. Если удалить столбец AUTO_INCREMENT
, а затем добавить еще один AUTO_INCREMENT
, то порядок чисел изменится, начиная с 1.
При использовании репликации, добавление столбца AUTO_INCREMENT
в таблицу может привести к неодинаковому порядку строк в копии и источнике. Это происходит потому, что порядок, в котором нумеруются строки, зависит от конкретного механизма хранения, используемого для таблицы, и порядка, в котором были вставлены строки. Если важно иметь одинаковый порядок в источнике и копии, то строки должны быть упорядочены до присвоения номера AUTO_INCREMENT
. Предполагая, что нужно добавить столбец AUTO_INCREMENT
в таблицу t1
, следующие операторы создают новую таблицу t2
, идентичную t1
, но со столбцом AUTO_INCREMENT
:
CREATE TABLE t2 (
id INT AUTO_INCREMENT PRIMARY KEY
) SELECT * FROM t1 ORDER BY col1, col2;
Этот набор операторов предполагает, что таблица t1
имеет столбцы col1
и col2
.
Следующий набор инструкций также создает новую таблицу t2
, идентичную t1
, с добавлением столбца AUTO_INCREMENT
:
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2
ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
Важно. Чтобы гарантировать одинаковый порядок как в источнике, так и в копии, все столбцы
t1
должны быть указаны в оператореORDER BY
.
Независимо от метода, использованного для создания и заполнения копии со столбцом AUTO_INCREMENT
, последний шаг - удалить исходную таблицу, а затем переименовать копию:
DROP TABLE t1;
ALTER TABLE t2 RENAME t1;