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;