ALTER TABLE

Синтаксис

ALTER [ONLINE] [IGNORE] TABLE [IF EXISTS] tbl_name
    [WAIT n | NOWAIT]
    alter_specification [, alter_specification] ...
alter_specification:
    table_option ...
  | ADD [COLUMN] [IF NOT EXISTS] col_name column_definition
        [FIRST | AFTER col_name ]
  | ADD [COLUMN] [IF NOT EXISTS] (col_name column_definition,...)
  | ADD {INDEX|KEY} [IF NOT EXISTS] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        UNIQUE [INDEX|KEY] [index_name]
        [index_type] (index_col_name,...) [index_option] ...
  | ADD FULLTEXT [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD SPATIAL [INDEX|KEY] [index_name]
        (index_col_name,...) [index_option] ...
  | ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [IF NOT EXISTS] [index_name] (index_col_name,...)
        reference_definition
  | ADD PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)
  | ALTER [COLUMN] col_name SET DEFAULT literal | (expression)
  | ALTER [COLUMN] col_name DROP DEFAULT
  | ALTER {INDEX|KEY} index_name [NOT] INVISIBLE
  | CHANGE [COLUMN] [IF EXISTS] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] [IF EXISTS] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] [IF EXISTS] col_name [RESTRICT|CASCADE]
  | DROP PRIMARY KEY
  | DROP {INDEX|KEY} [IF EXISTS] index_name
  | DROP FOREIGN KEY [IF EXISTS] fk_symbol
  | DROP CONSTRAINT [IF EXISTS] constraint_name
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO] new_tbl_name
  | ORDER BY col_name [, col_name] ...
  | RENAME COLUMN old_col_name TO new_col_name
  | RENAME {INDEX|KEY} old_index_name TO new_index_name
  | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
  | [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | DISCARD TABLESPACE
  | IMPORT TABLESPACE
  | ALGORITHM [=] {DEFAULT|INPLACE|COPY|NOCOPY|INSTANT}
  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
  | FORCE
  | partition_options
  | CONVERT TABLE normal_table TO partition_definition
  | CONVERT PARTITION partition_name TO TABLE tbl_name
  | ADD PARTITION [IF NOT EXISTS] (partition_definition)
  | DROP PARTITION [IF EXISTS] partition_names
  | COALESCE PARTITION number
  | REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
  | ANALYZE PARTITION partition_names
  | CHECK PARTITION partition_names
  | OPTIMIZE PARTITION partition_names
  | REBUILD PARTITION partition_names
  | REPAIR PARTITION partition_names
  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name
  | REMOVE PARTITIONING
  | ADD SYSTEM VERSIONING
  | DROP SYSTEM VERSIONING
index_col_name:
    col_name [(length)] [ASC | DESC]
index_type:
    USING {BTREE | HASH | RTREE}
index_option:
    [ KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | CLUSTERING={YES| NO} ]
  [ IGNORED | NOT IGNORED ]
table_options:
    table_option [[,] table_option] ...

Описание

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

Если другая соединение использует таблицу, метаданные блокировки активны, и данное заявление будет ожидать, пока блокировка не будет освобождена. Это также относится к таблицам, не использующим транзакции.

При добавлении UNIQUE индекса в столбец (или набор столбцов) с дублированными значениями, будет выдано сообщение об ошибке, и выполнение запроса будет остановлено. Чтобы подавить ошибку и принудительно создать UNIQUE индексы, игнорируя дубликаты, можно использовать опцию IGNORE. Это может быть полезно, если столбец (или набор столбцов) должен быть уникальным, но содержит дубликаты; однако такой метод не предоставляет управления над тем, какие строки сохраняются, а какие удаляются. Также обратите внимание, что IGNORE принимается, но игнорируется в ALTER TABLE ... EXCHANGE PARTITION запросах.

Данное заявление также может быть использовано для переименования таблицы. Подробности см. в разделе RENAME TABLE.

При создании индекса движок хранения может использовать настраиваемый буфер. Увеличение размера буфера ускоряет создание индекса. Aria и MyISAM выделяют буфер, размер которого определяется aria_sort_buffer_size или myisam_sort_buffer_size, также используемые для REPAIR TABLE. InnoDB выделяет три буфера, размер которых определяется innodb_sort_buffer_size.

Права доступа

Для выполнения ALTER TABLE запроса, как правило, необходимо как минимум право ALTER для таблицы или базы данных.

Если вы переименовываете таблицу, то также потребуются права DROP, CREATE и INSERT для таблицы или базы данных.

Online DDL

Поддерживается с помощью ALGORITHM и LOCK клаузов.

См. Обзор онлайн DDL для InnoDB для получения дополнительной информации о онлайн DDL с InnoDB.

ALTER ONLINE TABLE

ALTER ONLINE TABLE также работает для разнесенных таблиц.

Онлайн ALTER TABLE доступен путем выполнения следующего:

ALTER ONLINE TABLE ...;

Это заявление имеет следующие семантики:

Это заявление эквивалентно следующему:

ALTER TABLE ... LOCK=NONE;

См. спецификацию изменения LOCK для получения дополнительной информации.

Это заявление эквивалентно следующему:

ALTER TABLE ... ALGORITHM=INPLACE;

См. спецификацию изменения ALGORITHM для получения дополнительной информации.

WAIT/NOWAIT

Устанавливает тайм-аут ожидания блокировки. См. WAIT и NOWAIT.

IF EXISTS

Ключевые слова IF EXISTS и IF NOT EXISTS доступны для следующих:

ADD COLUMN       [IF NOT EXISTS]
ADD INDEX        [IF NOT EXISTS]
ADD FOREIGN KEY  [IF NOT EXISTS]
ADD PARTITION    [IF NOT EXISTS]
CREATE INDEX     [IF NOT EXISTS]
DROP COLUMN      [IF EXISTS]
DROP INDEX       [IF EXISTS]
DROP FOREIGN KEY [IF EXISTS]
DROP PARTITION   [IF EXISTS]
CHANGE COLUMN    [IF EXISTS]
MODIFY COLUMN    [IF EXISTS]
DROP INDEX       [IF EXISTS]

Когда IF EXISTS и IF NOT EXISTS используются в клаузах, запросы не будут сообщать об ошибках при срабатывании условия для этой клаузы. Будет выдано предупреждение с таким же текстом сообщения, и ALTER перейдет к следующей клаузе в запросе (или завершится, если она последняя).

Определения столбцов

См. CREATE TABLE: Определения столбцов для информации об определениях столбцов.

Определения индексов

См. CREATE TABLE: Определения индексов для информации об определениях индексов.

Команды CREATE INDEX и DROP INDEX также могут использоваться для добавления или удаления индекса.

Наборы символов и сортировки

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name

См. Установка наборов символов и сортировок для получения подробностей о настройке наборов символов и сортировок.

Спецификации ALTER

Параметры таблицы

См. CREATE TABLE: Параметры таблицы для получения информации о параметрах таблицы.

ADD COLUMN

... ADD COLUMN [IF NOT EXISTS]  (col_name column_definition,...)

Добавляет столбец в таблицу. Синтаксис такой же, как в CREATE TABLE. Если вы используете IF NOT_EXISTS, столбец не будет добавлен, если его уже нет. Это очень полезно при выполнении скриптов для изменения таблиц.

Ключевые слова FIRST и AFTER влияют на физический порядок столбцов в файле данных. Используйте FIRST для добавления столбца в первую (самую левую) позицию, или AFTER с последующим именем столбца для добавления нового столбца в любую другую позицию. Обратите внимание, что в настоящее время физическое расположение столбца обычно не имеет значения.

См. также Быстрое добавление столбца для InnoDB.

DROP COLUMN

... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]

Удаляет столбец из таблицы. Если вы используете IF EXISTS, ошибка не будет выведена, если столбца не существовало. Если столбец является частью любого индекса, он будет удалён из этих индексов, за исключением случая добавления нового столбца с идентичным именем одновременно. Индекс будет удалён, если все столбцы из индекса были удалены. Если столбец использовался в представлении или триггере, при следующем обращении к представлению или триггеру будет выдана ошибка. Удаление столбца, являющегося частью многостолбцового UNIQUE ограничения, не разрешено. Например:

CREATE TABLE a (
  a int,
  b int,
  primary key (a,b)
);

ALTER TABLE x DROP COLUMN a;
[42000][1072] Key column 'A' doesn't exist in table

Причина в том, что удаление столбца a приведёт к новому ограничению, требующему, чтобы все значения в столбце b были уникальными. Для удаления столбца необходимо явно использовать DROP PRIMARY KEY и ADD PRIMARY KEY. До MariaDB 10.2.7 столбец удалялся, а дополнительное ограничение применялось, что приводило к следующей структуре:

ALTER TABLE x DROP COLUMN a;
Query OK, 0 rows affected (0.46 sec)

DESC x;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| b     | int(11) | NO   | PRI | NULL    |       |
+-------+---------+------+-----+---------+-------+

RESTRICT и CASCADE разрешены для облегчения переноса из других систем баз данных. В MariaDB они ничего не делают.

MODIFY COLUMN

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

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY((a));
ALTER TABLE t1 MODIFY a BIGINT UNSIGNED AUTO_INCREMENT;

CHANGE COLUMN

Работает как MODIFY COLUMN с возможностью также изменить имя столбца. Столбец будет находиться на том же месте, что и исходный столбец, и все индексы на столбце будут сохранены.

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, PRIMARY KEY(a));
ALTER TABLE t1 CHANGE a b BIGINT UNSIGNED AUTO_INCREMENT;

ALTER COLUMN

Это позволяет изменить параметры столбца.

CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, b varchar(50), PRIMARY KEY(a));
ALTER TABLE t1 ALTER b SET DEFAULT 'hello';

RENAME INDEX/KEY

ALTER TABLE t1 RENAME INDEX i_old TO i_new;

RENAME COLUMN

ALTER TABLE t1 RENAME COLUMN c_old TO c_new;

ADD PRIMARY KEY

Добавляет первичный ключ.

Для PRIMARY KEY индексов можно указать имя индекса, но оно игнорируется, и имя индекса всегда PRIMARY.

См. Начало работы с индексами: Первичный ключ для получения дополнительной информации.

DROP PRIMARY KEY

Удаляет первичный ключ.

Для PRIMARY KEY индексов можно указать имя индекса, но оно игнорируется, и имя индекса всегда PRIMARY.

См. Начало работы с индексами: Первичный ключ для получения дополнительной информации.

ADD FOREIGN KEY

Добавляет внешний ключ.

Для FOREIGN KEY индексов необходимо указать определение ссылки.

Для FOREIGN KEY индексов можно указать имя ограничения, используя ключевое слово CONSTRAINT. Это имя будет использоваться в сообщениях об ошибках.

Сначала нужно указать имя целевой (родительской) таблицы и столбец или список столбцов, которые должны быть индексированы и значения которых должны соответствовать значениям внешнего ключа. Запрос MATCH поддерживается для улучшения совместимости с другими СУБД, но не имеет смысла в MariaDB. Запросы ON DELETE и ON UPDATE определяют, что нужно сделать, когда операция DELETE (или операция REPLACE) пытается удалить связанную строку из родительской таблицы и когда операция UPDATE пытается изменить связанные столбцы внешнего ключа в строке родительской таблицы соответственно. Допустимы следующие варианты:

• RESTRICT: Операция удаления/обновления не выполняется. Запрос завершается ошибкой 1451 (SQLSTATE '2300').
• NO ACTION: Синоним для RESTRICT.
• CASCADE: Операция удаления/обновления выполняется в обеих таблицах.
• SET NULL: Обновление или удаление в родительской таблице выполняется, а соответствующие поля внешнего ключа в дочерней таблице устанавливаются в значение NULL. (Эти поля не должны быть определены как NOT NULL для успешного выполнения).
• SET DEFAULT: Этот параметр реализован только для хранилища PBXT, которое отключено по умолчанию и больше не поддерживается. Он устанавливает поля внешнего ключа дочерней таблицы в их значения DEFAULT при обновлении или удалении записей ключа родительской таблицы.

Если какой-либо из этих пунктов опущен, поведение по умолчанию для опущенного пункта — RESTRICT.

См. Внешние ключи для получения дополнительной информации.

УДАЛЕНИЕ ВНЕШНЕГО КЛЮЧА

Удалить внешний ключ.

См. Внешние ключи для получения дополнительной информации.

ДОБАВИТЬ ИНДЕКС

Добавить обычный индекс.

Обычные индексы — это обычные индексы, которые не уникальны и не используются в качестве первичного или внешнего ключа. Они также не являются «специализированными» индексами FULLTEXT или SPATIAL.

См. Начало работы с индексами: Обычные индексы для получения дополнительной информации.

УДАЛИТЬ ИНДЕКС

Удалить обычный индекс.

Обычные индексы — это обычные индексы, которые не уникальны и не используются в качестве первичного или внешнего ключа. Они также не являются «специализированными» индексами FULLTEXT или SPATIAL.

См. Начало работы с индексами: Обычные индексы для получения дополнительной информации.

ДОБАВИТЬ УНИКАЛЬНЫЙ ИНДЕКС

Добавить уникальный индекс.

Ключевое слово UNIQUE означает, что индекс не будет принимать дублирующиеся значения, за исключением NULL. Возникнет ошибка, если вы попытаетесь вставить дублирующиеся значения в уникальный индекс.

Для UNIQUE индексов можно указать имя ограничения, используя ключевое слово CONSTRAINT. Это имя будет использоваться в сообщениях об ошибках.

См. Начало работы с индексами: Уникальный индекс для получения дополнительной информации.

УДАЛИТЬ УНИКАЛЬНЫЙ ИНДЕКС

Удалить уникальный индекс.

Ключевое слово UNIQUE означает, что индекс не будет принимать дублирующиеся значения, за исключением NULL. Возникнет ошибка, если вы попытаетесь вставить дублирующиеся значения в уникальный индекс.

Для UNIQUE индексов можно указать имя ограничения, используя ключевое слово CONSTRAINT. Это имя будет использоваться в сообщениях об ошибках.

См. Начало работы с индексами: Уникальный индекс для получения дополнительной информации.

ДОБАВИТЬ ПОЛНОТЕКСТОВЫЙ ИНДЕКС

Добавить FULLTEXT индекс.

См. Полные текстовые индексы для получения дополнительной информации.

УДАЛИТЬ ПОЛНОТЕКСТОВЫЙ ИНДЕКС

Удалить FULLTEXT индекс.

См. Полные текстовые индексы для получения дополнительной информации.

ДОБАВИТЬ ПРОСТРАНСТВЕННЫЙ ИНДЕКС

Добавить ПРОСТРАНСТВЕННЫЙ индекс.

См. ПРОСТРАНСТВЕННЫЙ ИНДЕКС для получения дополнительной информации.

УДАЛИТЬ ПРОСТРАНСТВЕННЫЙ ИНДЕКС

Удалить ПРОСТРАНСТВЕННЫЙ индекс.

См. ПРОСТРАНСТВЕННЫЙ ИНДЕКС для получения дополнительной информации.

ВКЛЮЧЕНИЕ/ВЫКЛЮЧЕНИЕ КЛЮЧЕЙ

DISABLE KEYS отключит все не уникальные ключи для таблицы для хранилищ, которые это поддерживают (по крайней мере MyISAM и Aria). Это может быть использовано для ускорения вставок в пустые таблицы.

ENABLE KEYS включит все отключенные ключи.

ПЕРЕИМЕНОВАТЬ В

Переименовывает таблицу. См. также ПЕРЕИМЕНОВАТЬ ТАБЛИЦУ.

ДОБАВИТЬ ОГРАНИЧЕНИЕ

Изменяет таблицу, добавляя ограничение на определенный столбец или столбцы.

ALTER TABLE table_name 
ADD CONSTRAINT [constraint_name] CHECK(expression);

Перед вставкой или обновлением строки все ограничения оцениваются в порядке их определения. Если какое-либо ограничение не выполняется, то строка не будет обновлена. В ограничении можно использовать большинство детерминированных функций, включая UDF.

CREATE TABLE account_ledger (
	id INT PRIMARY KEY AUTO_INCREMENT,
	transaction_name VARCHAR(100),
	credit_account VARCHAR(100),
	credit_amount INT,
	debit_account VARCHAR(100),
	debit_amount INT);

ALTER TABLE account_ledger 
ADD CONSTRAINT is_balanced 
    CHECK((debit_amount + credit_amount) = 0);

constraint_name необязательно. Если вы не укажете его в операторе ALTER TABLE, MariaDB автоматически сгенерирует для вас имя. Это сделано для того, чтобы вы могли удалить его позже с помощью оператора УДАЛИТЬ ОГРАНИЧЕНИЕ.

Вы можете отключить проверку выражений ограничений, установив переменную check_constraint_checks в значение OFF. Это может быть полезно при загрузке таблицы, нарушающей некоторые ограничения, которые вы хотите найти и исправить позже в SQL.

Чтобы просмотреть ограничения таблицы, запросите information_schema.TABLE_CONSTRAINTS:

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'account_ledger';

+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME     | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| is_balanced     | account_ledger | CHECK           |
+-----------------+----------------+-----------------+

УДАЛИТЬ ОГРАНИЧЕНИЕ

DROP CONSTRAINT для UNIQUE и FOREIGN KEY ограничений было введено в MariaDB 10.2.22 и MariaDB 10.3.13.

DROP CONSTRAINT для CHECK ограничений было введено в MariaDB 10.2.1

Изменяет таблицу, удаляя указанное ограничение.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

При добавлении ограничения к таблице, будь то через оператор СОЗДАТЬ ТАБЛИЦУ или ALTER TABLE...ADD CONSTRAINT, вы можете задать constraint_name самостоятельно или позволить MariaDB сгенерировать его автоматически. Чтобы просмотреть ограничения таблицы, запросите information_schema.TABLE_CONSTRAINTS. Например,

CREATE TABLE t (
   a INT,
   b INT,
   c INT,
   CONSTRAINT CHECK(a > b),
   CONSTRAINT check_equals CHECK(a = c)); 

SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE 
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 't';

+-----------------+----------------+-----------------+
| CONSTRAINT_NAME | TABLE_NAME     | CONSTRAINT_TYPE |
+-----------------+----------------+-----------------+
| check_equals    | t              | CHECK           |
| CONSTRAINT_1    | t              | CHECK           |
+-----------------+----------------+-----------------+

Чтобы удалить ограничение из таблицы, выполните оператор ALTER TABLE...DROP CONSTRAINT. Например,

ALTER TABLE t DROP CONSTRAINT is_unique;

ДОБАВИТЬ СИСТЕМНОЕ ВЕРСИОНИРОВАНИЕ

Добавить системное версионирование. См. Системно-версионированные таблицы.

УДАЛИТЬ СИСТЕМНОЕ ВЕРСИОНИРОВАНИЕ

Удалить системное версионирование. См. Системно-версионированные таблицы.

ДОБАВИТЬ ПЕРИОД ДЛЯ SYSTEM_TIME

См. Системно-версионированные таблицы.

ВЫНУЖДЕННОЕ

ALTER TABLE ... FORCE может заставить MariaDB перестроить таблицу.

В MariaDB 5.5 и ранее это можно было сделать, только установив опцию таблицы ДВИЖОК в её старое значение. Например, для таблицы InnoDB можно выполнить следующее:

ALTER TABLE tab_name ENGINE = InnoDB;

Вместо этого можно использовать опцию FORCE. Например, :

ALTER TABLE tab_name FORCE;

В InnoDB перестроение таблицы будет извлекать только неиспользуемое пространство (т.е. пространство, ранее используемое для строк, удаленных) только если переменная системы innodb_file_per_table установлена в значение ON (значение по умолчанию). Если переменная системы имеет значение OFF, то пространство не будет освобождено, но оно будет использовано для новых данных, которые позже будут добавлены.

ПРЕОБРАЗОВАТЬ ТАБЛИЦУ / ПРЕОБРАЗОВАТЬ РАЗДЕЛ

CONVERT TABLE и CONVERT PARTITION было введено в MariaDB 10.7.1.

CONVERT PARTITION может быть использовано для удаления раздела из таблицы и преобразования её в обычную таблицу. Например:

ALTER TABLE partitioned_table CONVERT PARTITION part1 TO TABLE normal_table;

CONVERT PARTITION возьмет существующую таблицу и переместит её в другую таблицу как свой собственный раздел со специфицированным определением раздела. Например, следующее перемещает normal_table в раздел partitioned_table с определением, что его значения, основанные на PARTITION BY partitioned_table, меньше 12345.

ALTER TABLE partitioned_table CONVERT TABLE normal_table TO PARTITION part1 VALUES LESS THAN (12345);

ОБМЕН РАЗДЕЛАМИ

Это используется для обмена содержимым раздела с другой таблицей.

Это выполняется путем обмена областями таблиц раздела с другой таблицей.

См. копирование переносимых областей таблиц InnoDB для получения дополнительной информации.

ОТКАЗАТЬСЯ ОТ ОБЛАСТИ ТАБЛИЦЫ

Это используется для отказа от области таблицы InnoDB.

См. копирование переносимых областей таблиц InnoDB для получения дополнительной информации.

ИМПОРТИРОВАТЬ ОБЛАСТЬ ТАБЛИЦЫ

Это используется для импорта области таблицы InnoDB. Область таблицы должна быть скопирована с исходного сервера после выполнения FLUSH TABLES FOR EXPORT.

См. копирование переносимых областей таблиц InnoDB для получения дополнительной информации.

ALTER TABLE ... IMPORT применяется только к таблицам InnoDB. Большинство других популярных движков хранения, таких как Aria и MyISAM, распознают свои файлы данных сразу после размещения их в соответствующем каталоге под datadir, и для их импорта не требуется специальный DDL.

АЛГОРИТМ

Заявление ALTER TABLE поддерживает предложение ALGORITHM. Это одно из предложений, используемых для реализации онлайн-DDL. ALTER TABLE поддерживает несколько различных алгоритмов. Алгоритм можно явно выбрать для операции ALTER TABLE путём задания предложения ALGORITHM. Поддерживаемые значения:

• ALGORITHM=DEFAULT - Это подразумевает стандартное поведение для конкретного заявления, например, если предложение ALGORITHM не указано.
• ALGORITHM=COPY
• ALGORITHM=INPLACE
• ALGORITHM=NOCOPY - Это было добавлено в MariaDB 10.3.7.
• ALGORITHM=INSTANT - Это было добавлено в MariaDB 10.3.7.

См. Обзор онлайн-DDL InnoDB: АЛГОРИТМ для информации о том, как предложение ALGORITHM влияет на InnoDB.

ALGORITHM=DEFAULT

По умолчанию, если указано ALGORITHM=DEFAULT или если ALGORITHM не указано совсем, обычно делается копия, если операция вообще не поддерживает выполнение на месте. В этом случае обычно используется наиболее эффективный доступный алгоритм.

Однако в MariaDB 10.3.6 и ранее, если значение системной переменной old_alter_table установлено в ON, то по умолчанию операции ALTER TABLE выполняются путём создания копии таблицы с использованием старого алгоритма.

В MariaDB 10.3.7 и более поздних версиях системная переменная old_alter_table устарела. Вместо этого системная переменная alter_algorithm определяет алгоритм по умолчанию для операций ALTER TABLE.

ALGORITHM=COPY

ALGORITHM=COPY - название оригинального алгоритма ALTER TABLE из ранних версий MariaDB.

Когда ALGORITHM=COPY установлено, MariaDB фактически выполняет следующие операции:

-- Create a temporary table with the new definition
CREATE TEMPORARY TABLE tmp_tab (
...
);

-- Copy the data from the original table
INSERT INTO tmp_tab
   SELECT * FROM original_tab;

-- Drop the original table
DROP TABLE original_tab;

-- Rename the temporary table, so that it replaces the original one
RENAME TABLE tmp_tab TO original_tab;

Этот алгоритм очень неэффективен, но он универсален, поэтому он работает для всех движков хранения.

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

ALGORITHM=INPLACE

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

Когда ALGORITHM=INPLACE установлено, подлежащий движок хранения использует оптимизации для выполнения операции, избегая копирования и перестроения таблицы. Однако INPLACE немного вводящее в заблуждение, так как для некоторых движков хранения некоторые операции все равно могут потребовать перестройки таблицы. Независимо от этого, для некоторых движков хранения несколько операций можно выполнить без полной копии таблицы.

Более точное название было бы ALGORITHM=ENGINE, где ENGINE относится к «алгоритму, специфичному для движка».

Если операция ALTER TABLE поддерживает ALGORITHM=INPLACE, то она может выполняться с помощью оптимизаций подлежащего движка хранения, но может быть перестроена.

См. Операции InnoDB Online DDL с ALGORITHM=INPLACE для получения более подробной информации.

ALGORITHM=NOCOPY

ALGORITHM=NOCOPY была добавлена в MariaDB 10.3.7.

ALGORITHM=INPLACE иногда может быть неожиданно медленной в тех случаях, когда ей приходится перестраивать кластеризованный индекс, так как при перестройке кластеризованного индекса вся таблица должна быть перестроена. ALGORITHM=NOCOPY была добавлена для того, чтобы избежать этого.

Если операция ALTER TABLE поддерживает ALGORITHM=NOCOPY, то она может выполняться без перестройки кластеризованного индекса.

Если ALGORITHM=NOCOPY указано для операции ALTER TABLE, которая не поддерживает ALGORITHM=NOCOPY, будет выведено сообщение об ошибке. В этом случае предпочтительнее выдать ошибку, чем позволить операции перестроить кластеризованный индекс и выполняться неожиданно медленно.

См. Операции InnoDB Online DDL с ALGORITHM=NOCOPY для получения более подробной информации.

ALGORITHM=INSTANT

ALGORITHM=INSTANT была добавлена в MariaDB 10.3.7.

ALGORITHM=INPLACE может быть неожиданно медленной в ситуациях, когда необходимо изменить файлы данных. ALGORITHM=INSTANT была добавлена, чтобы избежать этого.

Если операция ALTER TABLE поддерживает ALGORITHM=INSTANT, то она может выполняться без изменения каких-либо файлов данных.

Если ALGORITHM=INSTANT указано для операции ALTER TABLE, которая не поддерживает ALGORITHM=INSTANT, будет выведено сообщение об ошибке. В этом случае предпочтительнее выдать ошибку, чем позволить операции изменить файлы данных и выполняться неожиданно медленно.

См. Операции InnoDB Online DDL с ALGORITHM=INSTANT для получения более подробной информации.

БЛОКИРОВКА

Заявление ALTER TABLE поддерживает предложение LOCK. Это одно из предложений, используемых для реализации онлайн-DDL. ALTER TABLE поддерживает несколько различных стратегий блокировки. Стратегию блокировки можно явно выбрать для операции ALTER TABLE путём задания предложения LOCK. Поддерживаемые значения:

• DEFAULT: Захватить наименее жёсткую блокировку таблицы, которая поддерживается для конкретной операции. Разрешить максимальное количество одновременности, поддерживаемое для конкретной операции.
• NONE: Не захватывать блокировку таблицы. Разрешить все одновременные DML. Если эта стратегия блокировки не разрешена для операции, то выдается ошибка.
• SHARED: Захватить блокировку чтения на таблице. Разрешить только чтение одновременных DML. Если эта стратегия блокировки не разрешена для операции, то выдается ошибка.
• EXCLUSIVE: Захватить блокировку записи на таблице. Не разрешать одновременные DML.

Разные движки хранения поддерживают разные стратегии блокировки для разных операций. Если для операции ALTER TABLE выбрана определённая стратегия блокировки, а движок хранения этой таблицы не поддерживает эту стратегию блокировки для этой конкретной операции, будет выведена ошибка.

Если предложение LOCK не указано явно, операция использует LOCK=DEFAULT.

ALTER ONLINE TABLE эквивалентно LOCK=NONE. Таким образом, оператор ALTER ONLINE TABLE можно использовать для обеспечения того, чтобы ваша операция ALTER TABLE позволяла все одновременные DML.

См. Обзор онлайн-DDL InnoDB: БЛОКИРОВКА для получения информации о том, как предложение LOCK влияет на InnoDB.

Отчёт о ходе выполнения

MariaDB предоставляет отчёт о ходе выполнения оператора ALTER TABLE для клиентов, которые поддерживают новый протокол отчёта о ходе выполнения. Например, если вы использовали клиент mariadb, то отчёт о ходе выполнения мог выглядеть следующим образом::

ALTER TABLE test ENGINE=Aria;
Stage: 1 of 2 'copy to tmp table'    46% of stage

Отчёт о ходе выполнения также отображается в выводе оператора SHOW PROCESSLIST и в содержании таблицы information_schema.PROCESSLIST.

См. Отчёт о ходе выполнения для получения дополнительной информации.

Прерывание операций ALTER TABLE

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

Прерывание ALTER TABLE ... ALGORITHM=COPY было ускорено в MariaDB 10.2.13 путём удаления избыточного протоколирования отката (MDEV-11415). Это значительно сократило время прерывания выполняющейся операции ALTER TABLE по сравнению с предыдущими выпусками.

Атомарное ALTER TABLE

Репликация

Примеры

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

ALTER TABLE t1 ADD x INT;

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

ALTER TABLE t1 DROP x;

Изменение типа столбца:

ALTER TABLE t1 MODIFY x bigint unsigned;

Изменение имени и типа столбца:

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

Комбинирование нескольких положений в одном операторе ALTER TABLE, разделенных запятыми:

ALTER TABLE t1 DROP x, ADD x2 INT,  CHANGE y y2 INT;

Изменение движка хранения и добавление комментария:

ALTER TABLE t1 
  ENGINE = InnoDB 
  COMMENT = 'First of three tables containing usage info';

Перестроение таблицы (предыдущий пример также перестроит таблицу, если она уже InnoDB):

ALTER TABLE t1 FORCE;

Удаление индекса:

ALTER TABLE rooms DROP INDEX u;

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

ALTER TABLE rooms ADD UNIQUE INDEX u(room_number);

Начиная с MariaDB 10.5.3, добавление первичного ключа для таблицы периодов активности приложения с ограничением WITHOUT OVERLAPS:

ALTER TABLE rooms ADD PRIMARY KEY(room_number, p WITHOUT OVERLAPS);

Начиная с MariaDB 10.8.1, операция ALTER может реплицироваться быстрее с установкой

SET @@SESSION.binlog_alter_two_phase = true;

перед операцией ALTER. Бинарный журнал будет содержать две группы событий

| master-bin.000001 | 495 | Gtid              |         1 |         537 | GTID 0-1-2 START ALTER                                        |
| master-bin.000001 | 537 | Query             |         1 |         655 | use `test`; alter table t add column b int, algorithm=inplace |
| master-bin.000001 | 655 | Gtid              |         1 |         700 | GTID 0-1-3 COMMIT ALTER id=2                                  |
| master-bin.000001 | 700 | Query             |         1 |         835 | use `test`; alter table t add column b int, algorithm=inplace |

из которых первая передается репликам до фактического выполнения ALTER на первичном сервере.

См. также

• CREATE TABLE
• DROP TABLE
• Наборы символов и сортировки
• SHOW CREATE TABLE
• Быстрое добавление столбца для InnoDB