Syntax
------

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] ...

Description
-----------

ALTER TABLE enables you to change the structure of an existing table. For
example, you can add or delete columns, create or destroy indexes, change the
type of existing columns, or rename columns or the table itself. You can also
change the comment for the table and the storage engine of the table.

If another connection is using the table, a metadata lock is active, and this
statement will wait until the lock is released. This is also true for
non-transactional tables.

When adding a UNIQUE index on a column (or a set of columns) which have
duplicated values, an error will be produced and the statement will be
stopped. To suppress the error and force the creation of UNIQUE indexes,
discarding duplicates, the IGNORE option can be specified. This can be useful
if a column (or a set of columns) should be UNIQUE but it contains duplicate
values; however, this technique provides no control on which rows are
preserved and which are deleted. Also, note that IGNORE is accepted but
ignored in ALTER TABLE ... EXCHANGE PARTITION statements.

This statement can also be used to rename a table. For details see RENAME
TABLE.

When an index is created, the storage engine may use a configurable buffer in
the process. Incrementing the buffer speeds up the index creation. Aria and
MyISAM allocate a buffer whose size is defined by aria_sort_buffer_size or
myisam_sort_buffer_size, also used for REPAIR TABLE. InnoDB allocates three
buffers whose size is defined by innodb_sort_buffer_size.

Privileges
----------

Executing the ALTER TABLE statement generally requires at least the ALTER
privilege for the table or the database..

If you are renaming a table, then it also requires the DROP, CREATE and INSERT
privileges for the table or the database as well.

Online DDL
----------

Online DDL is supported with the ALGORITHM and LOCK clauses.

See InnoDB Online DDL Overview for more information on online DDL with InnoDB.

ALTER ONLINE TABLE
------------------

ALTER ONLINE TABLE also works for partitioned tables.

Online ALTER TABLE is available by executing the following:

ALTER ONLINE TABLE ...;

This statement has the following semantics:

This statement is equivalent to the following:

ALTER TABLE ... LOCK=NONE;

See the LOCK alter specification for more information.

This statement is equivalent to the following:

ALTER TABLE ... ALGORITHM=INPLACE;

See the ALGORITHM alter specification for more information.

WAIT/NOWAIT
-----------

Set the lock wait timeout. See WAIT and NOWAIT.

IF EXISTS
---------

The IF EXISTS and IF NOT EXISTS clauses are available for the following:

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]
When IF EXISTS and IF NOT EXISTS are used in clauses, queries will not report
errors when the condition is triggered for that clause. A warning with the
same message text will be issued and the ALTER will move on to the next clause
in the statement (or end if finished).

MariaDB starting with 10.5.2
----------------------------
If this is directive is used after ALTER ... TABLE, one will not get an error
if the table doesn't exist.

Column Definitions
------------------

See CREATE TABLE: Column Definitions for information about column definitions.

Index Definitions
-----------------

See CREATE TABLE: Index Definitions for information about index definitions.

The CREATE INDEX and DROP INDEX statements can also be used to add or remove
an index.

Character Sets and Collations
-----------------------------

CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
[DEFAULT] CHARACTER SET [=] charset_name
[DEFAULT] COLLATE [=] collation_name
See Setting Character Sets and Collations for details on setting the character
sets and collations.

Alter Specifications
--------------------

Table Options
-------------

See CREATE TABLE: Table Options for information about table options.

ADD COLUMN
----------

... ADD COLUMN [IF NOT EXISTS]  (col_name column_definition,...)
Adds a column to the table. The syntax is the same as in CREATE TABLE. If you
are using IF NOT_EXISTS the column will not be added if it was not there
already. This is very useful when doing scripts to modify tables.

The FIRST and AFTER clauses affect the physical order of columns in the
datafile. Use FIRST to add a column in the first (leftmost) position, or AFTER
followed by a column name to add the new column in any other position. Note
that, nowadays, the physical position of a column is usually irrelevant.

See also Instant ADD COLUMN for InnoDB.

DROP COLUMN
-----------

... DROP COLUMN [IF EXISTS] col_name [CASCADE|RESTRICT]
Drops the column from the table. If you are using IF EXISTS you will not get
an error if the column didn't exist. If the column is part of any index, the
column will be dropped from them, except if you add a new column with
identical name at the same time. The index will be dropped if all columns from
the index were dropped. If the column was used in a view or trigger, you will
get an error next time the view or trigger is accessed. Dropping a column that
is part of a multi-column UNIQUE constraint is not permitted. For example:

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

The reason is that dropping column a would result in the new constraint that
all values in column b be unique. In order to drop the column, an explicit
DROP PRIMARY KEY and ADD PRIMARY KEY would be required. Up until MariaDB
10.2.7, the column was dropped and the additional constraint applied,
resulting in the following structure:

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    |       |
+-------+---------+------+-----+---------+-------+

MariaDB starting with 10.4.0
----------------------------
MariaDB 10.4.0 supports instant DROP COLUMN. DROP COLUMN of an indexed column
would imply DROP INDEX (and in the case of a non-UNIQUE multi-column index,
possibly ADD INDEX). These will not be allowed with ALGORITHM=INSTANT, but
unlike before, they can be allowed with ALGORITHM=NOCOPY

RESTRICT and CASCADE are allowed to make porting from other database systems
easier. In MariaDB, they do nothing.

MODIFY COLUMN
-------------

Allows you to modify the type of a column. The column will be at the same
place as the original column and all indexes on the column will be kept. Note
that when modifying column, you should specify all attributes for the new
column.

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

CHANGE COLUMN
-------------

Works like MODIFY COLUMN except that you can also change the name of the
column. The column will be at the same place as the original column and all
index on the column will be kept.

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

ALTER COLUMN
------------

This lets you change column options.

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
----------------

MariaDB starting with 10.5.2
----------------------------
From MariaDB 10.5.2, it is possible to rename an index using the RENAME INDEX
(or RENAME KEY) syntax, for example:

ALTER TABLE t1 RENAME INDEX i_old TO i_new;

RENAME COLUMN
-------------

MariaDB starting with 10.5.2
----------------------------
From MariaDB 10.5.2, it is possible to rename a column using the RENAME COLUMN
syntax, for example:

ALTER TABLE t1 RENAME COLUMN c_old TO c_new;

ADD PRIMARY KEY
---------------

Add a primary key.

For PRIMARY KEY indexes, you can specify a name for the index, but it is
silently ignored, and the name of the index is always PRIMARY.

See Getting Started with Indexes: Primary Key for more information.

DROP PRIMARY KEY
----------------

Drop a primary key.

For PRIMARY KEY indexes, you can specify a name for the index, but it is
silently ignored, and the name of the index is always PRIMARY.

See Getting Started with Indexes: Primary Key for more information.

ADD FOREIGN KEY
---------------

Add a foreign key.

For FOREIGN KEY indexes, a reference definition must be provided.

For FOREIGN KEY indexes, you can specify a name for the constraint, using the
CONSTRAINT keyword. That name will be used in error messages.

First, you have to specify the name of the target (parent) table and a column
or a column list which must be indexed and whose values must match to the
foreign key's values. The MATCH clause is accepted to improve the
compatibility with other DBMS's, but has no meaning in MariaDB. The ON DELETE
and ON UPDATE clauses specify what must be done when a DELETE (or a REPLACE)
statements attempts to delete a referenced row from the parent table, and when
an UPDATE statement attempts to modify the referenced foreign key columns in a
parent table row, respectively. The following options are allowed:

* RESTRICT: The delete/update operation is not performed.  The statement
terminates with a 1451 error (SQLSTATE '2300').
* NO ACTION: Synonym for RESTRICT.
* CASCADE: The delete/update operation is performed in both tables.
* SET NULL: The update or delete goes ahead in the parent table, and the
corresponding foreign key fields in the child table are set to NULL.  (They
must not be defined as NOT NULL for this to succeed).
* SET DEFAULT: This option is implemented only for the legacy PBXT storage
engine, which is disabled by default and no longer maintained. It sets the
child table's foreign key fields to their DEFAULT values when the referenced
parent table key entries are updated or deleted.

If either clause is omitted, the default behavior for the omitted clause is
RESTRICT.

See Foreign Keys for more information.

DROP FOREIGN KEY
----------------

Drop a foreign key.

See Foreign Keys for more information.

ADD INDEX
---------

Add a plain index.

Plain indexes are regular indexes that are not unique, and are not acting as a
primary key or a foreign key. They are also not the "specialized" FULLTEXT or
SPATIAL indexes.

See Getting Started with Indexes: Plain Indexes for more information.

DROP INDEX
----------

Drop a plain index.

Plain indexes are regular indexes that are not unique, and are not acting as a
primary key or a foreign key. They are also not the "specialized" FULLTEXT or
SPATIAL indexes.

See Getting Started with Indexes: Plain Indexes for more information.

ADD UNIQUE INDEX
----------------

Add a unique index.

The UNIQUE keyword means that the index will not accept duplicated values,
except for NULLs. An error will raise if you try to insert duplicate values in
a UNIQUE index.

For UNIQUE indexes, you can specify a name for the constraint, using the
CONSTRAINT keyword. That name will be used in error messages.

See Getting Started with Indexes: Unique Index for more information.

DROP UNIQUE INDEX
-----------------

Drop a unique index.

The UNIQUE keyword means that the index will not accept duplicated values,
except for NULLs. An error will raise if you try to insert duplicate values in
a UNIQUE index.

For UNIQUE indexes, you can specify a name for the constraint, using the
CONSTRAINT keyword. That name will be used in error messages.

See Getting Started with Indexes: Unique Index for more information.

ADD FULLTEXT INDEX
------------------

Add a FULLTEXT index.

See Full-Text Indexes for more information.

DROP FULLTEXT INDEX
-------------------

Drop a FULLTEXT index.

See Full-Text Indexes for more information.

ADD SPATIAL INDEX
-----------------

Add a SPATIAL index.

See SPATIAL INDEX for more information.

DROP SPATIAL INDEX
------------------

Drop a SPATIAL index.

See SPATIAL INDEX for more information.

ENABLE/ DISABLE KEYS
--------------------

DISABLE KEYS will disable all non unique keys for the table for storage
engines that support this (at least MyISAM and Aria). This can be used to
speed up inserts into empty tables.

ENABLE KEYS will enable all disabled keys.

RENAME TO
---------

Renames the table. See also RENAME TABLE.

ADD CONSTRAINT
--------------

Modifies the table adding a constraint on a particular column or columns.

ALTER TABLE table_name 
ADD CONSTRAINT [constraint_name] CHECK(expression);
Before a row is inserted or updated, all constraints are evaluated in the
order they are defined. If any constraint fails, then the row will not be
updated. One can use most deterministic functions in a constraint, including
UDF's.

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);

The constraint_name is optional. If you don't provide one in the ALTER TABLE
statement, MariaDB auto-generates a name for you. This is done so that you can
remove it later using DROP CONSTRAINT clause.

You can disable all constraint expression checks by setting the variable
check_constraint_checks to OFF. You may find this useful when loading a table
that violates some constraints that you want to later find and fix in SQL.

To view constraints on a table, query 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
---------------

DROP CONSTRAINT for UNIQUE and FOREIGN KEY constraints was introduced in
MariaDB 10.2.22 and MariaDB 10.3.13.

DROP CONSTRAINT for CHECK constraints was introduced in MariaDB 10.2.1

Modifies the table, removing the given constraint.

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

When you add a constraint to a table, whether through a CREATE TABLE or ALTER
TABLE...ADD CONSTRAINT statement, you can either set a constraint_name
yourself, or allow MariaDB to auto-generate one for you. To view constraints
on a table, query information_schema.TABLE_CONSTRAINTS. For instance,

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           |
+-----------------+----------------+-----------------+

To remove a constraint from the table, issue an ALTER TABLE...DROP CONSTRAINT
statement. For example,

ALTER TABLE t DROP CONSTRAINT is_unique;

ADD SYSTEM VERSIONING
---------------------

Add system versioning. See System-versioned tables.

DROP SYSTEM VERSIONING
----------------------

Drop system versioning. See System-versioned tables.

ADD PERIOD FOR SYSTEM_TIME
--------------------------

See System-versioned tables.

FORCE
-----

ALTER TABLE ... FORCE can force MariaDB to re-build the table.

In MariaDB 5.5 and before, this could only be done by setting the ENGINE table
option to its old value. For example, for an InnoDB table, one could execute
the following:

ALTER TABLE tab_name ENGINE = InnoDB;

The FORCE option can be used instead. For example, :

ALTER TABLE tab_name FORCE;

With InnoDB, the table rebuild will only reclaim unused space (i.e. the space
previously used for deleted rows) if the innodb_file_per_table system variable
is set to ON (the default). If the system variable is OFF, then the space will
not be reclaimed, but it will be-re-used for new data that's later added.

CONVERT TABLE / CONVERT PARTITION
---------------------------------

CONVERT TABLE and CONVERT PARTITION was introduced in MariaDB 10.7.1.

CONVERT PARTITION can be used to remove a partition from a table and make this
an ordinary table. For example:

ALTER TABLE partitioned_table CONVERT PARTITION part1 TO TABLE normal_table;

CONVERT PARTITION will take an existing table and move this to another table
as its own partition with a specified partition definition. For example the
following moves normal_table to a partition of partitioned_table with a
definition that its values, based on the PARTITION BY of the
partitioned_table, are less than 12345.

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

EXCHANGE PARTITION
------------------

This is used to exchange the contents of a partition with another table.

This is performed by swapping the tablespaces of the partition with the other
table.

See copying InnoDB's transportable tablespaces for more information.

DISCARD TABLESPACE
------------------

This is used to discard an InnoDB table's tablespace.

See copying InnoDB's transportable tablespaces for more information.

IMPORT TABLESPACE
-----------------

This is used to import an InnoDB table's tablespace. The tablespace should
have been copied from its original server after executing FLUSH TABLES FOR
EXPORT.

See copying InnoDB's transportable tablespaces for more information.

ALTER TABLE ... IMPORT only applies to InnoDB tables. Most other popular
storage engines, such as Aria and MyISAM, will recognize their data files as
soon as they've been placed in the proper directory under the datadir, and no
special DDL is required to import them.

ALGORITHM
---------

The ALTER TABLE statement supports the ALGORITHM clause. This clause is one of
the clauses that is used to implement online DDL. ALTER TABLE supports several
different algorithms. An algorithm can be explicitly chosen for an ALTER TABLE
operation by setting the ALGORITHM clause. The supported values are:

* ALGORITHM=DEFAULT - This implies the default behavior for the specific
statement, such as if no ALGORITHM clause is specified.
* ALGORITHM=COPY
* ALGORITHM=INPLACE
* ALGORITHM=NOCOPY - This was added in MariaDB 10.3.7.
* ALGORITHM=INSTANT - This was added in MariaDB 10.3.7.

See InnoDB Online DDL Overview: ALGORITHM for information on how the ALGORITHM
clause affects InnoDB.

ALGORITHM=DEFAULT
-----------------

The default behavior, which occurs if ALGORITHM=DEFAULT is specified, or if
ALGORITHM is not specified at all, usually only makes a copy if the operation
doesn't support being done in-place at all. In this case, the most efficient
available algorithm will usually be used.

However, in MariaDB 10.3.6 and before, if the value of the old_alter_table
system variable is set to ON, then the default behavior is to perform ALTER
TABLE operations by making a copy of the table using the old algorithm.

In MariaDB 10.3.7 and later, the old_alter_table system variable is
deprecated. Instead, the alter_algorithm system variable defines the default
algorithm for ALTER TABLE operations.

ALGORITHM=COPY
--------------

ALGORITHM=COPY is the name for the original ALTER TABLE algorithm from early
MariaDB versions.

When ALGORITHM=COPY is set, MariaDB essentially does the following operations:

-- 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;

This algorithm is very inefficient, but it is generic, so it works for all
storage engines.

If ALGORITHM=COPY is specified, then the copy algorithm will be used even if
it is not necessary. This can result in a lengthy table copy. If multiple
ALTER TABLE operations are required that each require the table to be rebuilt,
then it is best to specify all operations in a single ALTER TABLE statement,
so that the table is only rebuilt once.

ALGORITHM=INPLACE
-----------------

ALGORITHM=COPY can be incredibly slow, because the whole table has to be
copied and rebuilt. ALGORITHM=INPLACE was introduced as a way to avoid this by
performing operations in-place and avoiding the table copy and rebuild, when
possible.

When ALGORITHM=INPLACE is set, the underlying storage engine uses
optimizations to perform the operation while avoiding the table copy and
rebuild. However, INPLACE is a bit of a misnomer, since some operations may
still require the table to be rebuilt for some storage engines. Regardless,
several operations can be performed without a full copy of the table for some
storage engines.

A more accurate name would have been ALGORITHM=ENGINE, where ENGINE refers to
an "engine-specific" algorithm.

If an ALTER TABLE operation supports ALGORITHM=INPLACE, then it can be
performed using optimizations by the underlying storage engine, but it may
rebuilt.

See InnoDB Online DDL Operations with ALGORITHM=INPLACE for more.

ALGORITHM=NOCOPY
----------------

ALGORITHM=NOCOPY was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has
to rebuild the clustered index, because when the clustered index has to be
rebuilt, the whole table has to be rebuilt. ALGORITHM=NOCOPY was introduced as
a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=NOCOPY, then it can be
performed without rebuilding the clustered index.

If ALGORITHM=NOCOPY is specified for an ALTER TABLE operation that does not
support ALGORITHM=NOCOPY, then an error will be raised. In this case, raising
an error is preferable, if the alternative is for the operation to rebuild the
clustered index, and perform unexpectedly slowly.

See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for more.

ALGORITHM=INSTANT
-----------------

ALGORITHM=INSTANT was introduced in MariaDB 10.3.7.

ALGORITHM=INPLACE can sometimes be surprisingly slow in instances where it has
to modify data files. ALGORITHM=INSTANT was introduced as a way to avoid this.

If an ALTER TABLE operation supports ALGORITHM=INSTANT, then it can be
performed without modifying any data files.

If ALGORITHM=INSTANT is specified for an ALTER TABLE operation that does not
support ALGORITHM=INSTANT, then an error will be raised. In this case, raising
an error is preferable, if the alternative is for the operation to modify data
files, and perform unexpectedly slowly.

See InnoDB Online DDL Operations with ALGORITHM=INSTANT for more.

LOCK
----

The ALTER TABLE statement supports the LOCK clause. This clause is one of the
clauses that is used to implement online DDL. ALTER TABLE supports several
different locking strategies. A locking strategy can be explicitly chosen for
an ALTER TABLE operation by setting the LOCK clause. The supported values are:

* DEFAULT: Acquire the least restrictive lock on the table that is supported
for the specific operation. Permit the maximum amount of concurrency that is
supported for the specific operation.
* NONE: Acquire no lock on the table. Permit all concurrent DML. If this
locking strategy is not permitted for an operation, then an error is raised.
* SHARED: Acquire a read lock on the table. Permit read-only concurrent DML.
If this locking strategy is not permitted for an operation, then an error is
raised.
* EXCLUSIVE: Acquire a write lock on the table. Do not permit concurrent DML.

Different storage engines support different locking strategies for different
operations. If a specific locking strategy is chosen for an ALTER TABLE
operation, and that table's storage engine does not support that locking
strategy for that specific operation, then an error will be raised.

If the LOCK clause is not explicitly set, then the operation uses LOCK=DEFAULT.

ALTER ONLINE TABLE is equivalent to LOCK=NONE. Therefore, the ALTER ONLINE
TABLE statement can be used to ensure that your ALTER TABLE operation allows
all concurrent DML.

See InnoDB Online DDL Overview: LOCK for information on how the LOCK clause
affects InnoDB.

Progress Reporting
------------------

MariaDB provides progress reporting for ALTER TABLE statement for clients that
support the new progress reporting protocol. For example, if you were using
the mariadb client, then the progress report might look like this::

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

The progress report is also shown in the output of the SHOW PROCESSLIST
statement and in the contents of the information_schema.PROCESSLIST table.

See Progress Reporting for more information.

Aborting ALTER TABLE Operations
-------------------------------

If an ALTER TABLE operation is being performed and the connection is killed,
the changes will be rolled back in a controlled manner. The rollback can be a
slow operation as the time it takes is relative to how far the operation has
progressed.

Aborting ALTER TABLE ... ALGORITHM=COPY was made faster in MariaDB 10.2.13 by
removing excessive undo logging (MDEV-11415). This significantly shortened the
time it takes to abort a running ALTER TABLE operation, compared with earlier
releases.

Atomic ALTER TABLE
------------------

MariaDB starting with 10.6.1
----------------------------
From MariaDB 10.6, ALTER TABLE is atomic for most engines, including InnoDB,
MyRocks, MyISAM and Aria (MDEV-25180). This means that if there is a crash
(server down or power outage) during an ALTER TABLE operation, after recovery,
either the old table and associated triggers and status will be intact, or the
new table will be active.

In older MariaDB versions one could get leftover #sql-alter..',
'#sql-backup..' or 'table_name.frm˝' files if the system crashed during the
ALTER TABLE operation.

See Atomic DDL for more information.

Replication
-----------

MariaDB starting with 10.8.1
----------------------------
Before MariaDB 10.8.1, ALTER TABLE got fully executed on the primary first,
and only then was it replicated and started executing on replicas. From
MariaDB 10.8.1, ALTER TABLE gains an option to replicate sooner and begin
executing on replicas when it merely starts executing on the primary, not when
it finishes. This way the replication lag caused by a heavy ALTER TABLE can be
completely eliminated (MDEV-11675).

Examples
--------

Adding a new column:

ALTER TABLE t1 ADD x INT;

Dropping a column:

ALTER TABLE t1 DROP x;

Modifying the type of a column:

ALTER TABLE t1 MODIFY x bigint unsigned;

Changing the name and type of a column:

ALTER TABLE t1 CHANGE a b bigint unsigned auto_increment;

Combining multiple clauses in a single ALTER TABLE statement, separated by
commas:

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

Changing the storage engine and adding a comment:

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

Rebuilding the table (the previous example will also rebuild the table if it
was already InnoDB):

ALTER TABLE t1 FORCE;

Dropping an index:

ALTER TABLE rooms DROP INDEX u;

Adding a unique index:

ALTER TABLE rooms ADD UNIQUE INDEX u(room_number);

From MariaDB 10.5.3, adding a primary key for an application-time period table
with a WITHOUT OVERLAPS constraint:

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

From MariaDB 10.8.1, ALTER query can be replicated faster with the setting of


prior the ALTER query. Binlog would contain two event groups

| 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 |

of which the first one gets delivered to replicas before ALTER is taken to
actual execution on the primary.

URL: https://mariadb.com/kb/en/alter-table/