Добавление столбца

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INSTANT;

INSTANT — это алгоритм по умолчанию в MySQL 9.2.

При добавлении столбца с помощью алгоритма INSTANT применяются следующие ограничения:

В одном операторе ALTER TABLE можно добавить несколько столбцов. Например:

ALTER TABLE t1 ADD COLUMN c2 INT, ADD COLUMN c3 INT, ALGORITHM=INSTANT;

После каждой операции ALTER TABLE ... ALGORITHM=INSTANT, добавляющей один или несколько столбцов, удаляющей один или несколько столбцов или добавляющей и удаляющей один или несколько столбцов в одной операции, создаётся новая версия строки. Столбец INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS отслеживает количество версий строк для таблицы. Значение увеличивается каждый раз, когда столбец добавляется или удаляется мгновенно. Начальное значение равно 0.

mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
        WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                  0 |
+---------+--------------------+

При перестроении таблицы с мгновенно добавленными или удаленными столбцами с помощью операции перестроения таблицы ALTER TABLE или OPTIMIZE TABLE, значение TOTAL_ROW_VERSIONS сбрасывается до 0. Максимальное количество версий строк, разрешенных в MySQL 9.1.0 и более поздних версиях, равно 255, так как каждая версия строки требует дополнительного места для метаданных таблицы. При достижении предела версий строк операции ADD COLUMN и DROP COLUMN, использующие ALGORITHM=INSTANT, отклоняются с сообщением об ошибке, рекомендующим перестроить таблицу с помощью алгоритма COPY или INPLACE.

Ошибка 4080 (HY000): Достигнуто максимальное количество версий строк для таблицы test/t1. Больше нельзя мгновенно добавлять или удалять столбцы. Пожалуйста, используйте COPY/INPLACE.

Следующие столбцы INFORMATION_SCHEMA предоставляют дополнительные метаданные для мгновенно добавленных столбцов. Подробнее см. в описаниях этих столбцов. См. Раздел 28.4.9, «Таблица INFORMATION_SCHEMA INNODB_COLUMNS» и Раздел 28.4.23, «Таблица INFORMATION_SCHEMA INNODB_TABLES».

Одновременные DML-операции запрещены при добавлении столбца. Данные существенно переупорядочиваются, что делает эту операцию дорогостоящей. Минимально требуется ALGORITHM=INPLACE, LOCK=SHARED.

Таблица перестраивается, если для добавления столбца используется ALGORITHM=INPLACE.

Удаление столбца

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INSTANT;

INSTANT — это алгоритм по умолчанию в MySQL 9.2.

При удалении столбца с помощью алгоритма INSTANT применяются следующие ограничения:

В одном операторе ALTER TABLE можно удалить несколько столбцов, например:

ALTER TABLE t1 DROP COLUMN c4, DROP COLUMN c5, ALGORITHM=INSTANT;

Каждый раз, когда столбец добавляется или удаляется с помощью ALGORITHM=INSTANT, создаётся новая версия строки. Столбец INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS отслеживает количество версий строк для таблицы. Значение увеличивается каждый раз, когда столбец добавляется или удаляется мгновенно. Начальное значение равно 0.

mysql>  SELECT NAME, TOTAL_ROW_VERSIONS FROM INFORMATION_SCHEMA.INNODB_TABLES
        WHERE NAME LIKE 'test/t1';
+---------+--------------------+
| NAME    | TOTAL_ROW_VERSIONS |
+---------+--------------------+
| test/t1 |                  0 |
+---------+--------------------+

При перестроении таблицы с мгновенно добавленными или удаленными столбцами с помощью операции перестроения таблицы ALTER TABLE или OPTIMIZE TABLE, значение TOTAL_ROW_VERSIONS сбрасывается до 0. Максимальное количество версий строк, разрешенных в MySQL 9.1.0 и более поздних версиях, равно 255, так как каждая версия строки требует дополнительного места для метаданных таблицы. При достижении предела версий строк операции ADD COLUMN и DROP COLUMN, использующие ALGORITHM=INSTANT, отклоняются с сообщением об ошибке, рекомендующим перестроить таблицу с помощью алгоритма COPY или INPLACE.

Ошибка 4080 (HY000): Достигнуто максимальное количество версий строк для таблицы test/t1. Больше нельзя мгновенно добавлять или удалять столбцы. Пожалуйста, используйте COPY/INPLACE.

Если используется алгоритм, отличный от ALGORITHM=INSTANT, данные существенно переупорядочиваются, что делает эту операцию дорогостоящей.

Переименование столбца

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INSTANT;

Для поддержки одновременных DML-операций, сохраните тот же тип данных и измените только имя столбца.

Если вы сохраняете тот же тип данных и атрибут [NOT] NULL, изменяя только имя столбца, операция всегда может быть выполнена онлайн.

Переименование столбца, на который ссылается другая таблица, разрешено только с помощью ALGORITHM=INPLACE. Если вы используете ALGORITHM=INSTANT, ALGORITHM=COPY или другие условия, из-за которых операция использует эти алгоритмы, операция ALTER TABLE завершается неудачно.

ALGORITHM=INSTANT поддерживает переименование виртуального столбца; ALGORITHM=INPLACE нет.

ALGORITHM=INSTANT и ALGORITHM=INPLACE не поддерживают переименование столбца при добавлении или удалении виртуального столбца в одном операторе. В этом случае поддерживается только ALGORITHM=COPY.

Переупорядочение столбцов

Для переупорядочения столбцов используйте FIRST или AFTER в операциях CHANGE или MODIFY.

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

Данные существенно переупорядочиваются, что делает эту операцию дорогостоящей.

Изменение типа данных столбца

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

Изменение типа данных столбца поддерживается только с помощью ALGORITHM=COPY.

Увеличение размера колонки VARCHAR

ALTER TABLE tbl_name CHANGE COLUMN c1 c1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

Количество байтов длины, необходимых для колонки VARCHAR, должно остаться неизменным. Для колонок VARCHAR размером от 0 до 255 байт требуется один байт длины для кодирования значения. Для колонок VARCHAR размером 256 байт или более требуется два байта длины. В результате, операция ALTER TABLE на месте поддерживает только увеличение размера колонки VARCHAR с 0 до 255 байт или с 256 байт до большего размера. Операция ALTER TABLE на месте не поддерживает увеличение размера колонки VARCHAR с менее чем 256 байт до размера, равного или большего 256 байт. В этом случае количество требуемых байтов длины меняется с 1 на 2, что поддерживается только копированием таблицы (ALGORITHM=COPY). Например, попытка изменить размер колонки VARCHAR для набора символов с одним байтом от VARCHAR(255) до VARCHAR(256) с помощью операции ALTER TABLE на месте возвращает следующую ошибку:

ALTER TABLE tbl_name ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
column type INPLACE. Try ALGORITHM=COPY.

Уменьшение размера колонки VARCHAR с помощью операции ALTER TABLE на месте не поддерживается. Для уменьшения размера колонки VARCHAR требуется копирование таблицы (ALGORITHM=COPY).

Установка значения по умолчанию для колонки

ALTER TABLE tbl_name ALTER COLUMN col SET DEFAULT literal, ALGORITHM=INSTANT;

Изменяет только метаданные таблицы. Значения по умолчанию для колонок хранятся в .

Удаление значения по умолчанию для колонки

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INSTANT;

Изменение значения автоинкремента

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

Изменяет значение, хранящееся в памяти, а не в файле данных.

В распределённой системе с репликацией или фрагментацией иногда требуется сбросить счётчик автоинкремента таблицы до определённого значения. Следующая строка, вставленная в таблицу, использует указанное значение для колонки автоинкремента. Этот метод также может быть использован в среде хранилища данных, где периодически пустые таблицы загружаются заново, а последовательность автоинкремента перезапускается с 1.

Преобразование колонки в NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;

Перестраивает таблицу на месте. Данные существенно переупорядочиваются, что делает операцию дорогостоящей.

Преобразование колонки в NOT NULL

ALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Перестраивает таблицу на месте. Требуется STRICT_ALL_TABLES или STRICT_TRANS_TABLES SQL_MODE для успешного выполнения операции. Операция завершается ошибкой, если колонка содержит NULL-значения. Сервер запрещает изменения в колонках внешних ключей, которые могут привести к нарушению целостности ссылок. См. Раздел 15.1.9, “Выполнение ALTER TABLE”. Данные существенно переупорядочиваются, что делает операцию дорогостоящей.

Изменение определения колонки ENUM или SET

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));
ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INSTANT;

Изменение определения колонки ENUM или SET путем добавления новых элементов перечисления или набора в конец списка допустимых значений может выполняться мгновенно или на месте, если размер хранилища типа данных не изменяется. Например, добавление элемента в колонку SET с 8 элементами изменяет требуемый размер хранения на значение с 1 байта на 2 байта; это требует копирования таблицы. Добавление элементов в середине списка приводит к пересчёту существующих элементов, что требует копирования таблицы.