--source include/have_innodb.inc
|
|
CREATE TABLE t1 (FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, c TEXT, FULLTEXT KEY (c)) ENGINE=InnoDB;
|
INSERT INTO t1 (c) VALUES ("
|
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
|
| 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] ...
|
| 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
|
| ADD PARTITION (partition_definition)
|
| DROP PARTITION 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}
|
|
table_options:
|
table_option [[,] table_option] ...
|
In MariaDB 10.0.2 and later, IF EXISTS and IF NOT EXISTS
|
clauses have been added 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).
|
|
This was done in MDEV-318.
|
|
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/XtraDB 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
|
|
In MariaDB 10.0 and later, 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 has also worked for partitioned tables
|
since MariaDB 10.0.11.
|
|
Online ALTER TABLE is available by executing the following:
|
|
ALTER ONLINE TABLE ...;
|
|
This statement has the following semantics:
|
|
In MariaDB 10.0.12 and later, this statement is equivalent
|
to the following:
|
|
ALTER TABLE ... LOCK=NONE;
|
|
See the LOCK alter specification for more information.
|
|
In MariaDB 10.0.11, this statement is equivalent to the
|
following:
|
|
ALTER TABLE ... ALGORITHM=INPLACE;
|
|
See the ALGORITHM alter specification for more information.
|
|
MariaDB until 10.0.10
|
|
In MariaDB 10.0.10 and before, this statement ensures that
|
the ALTER TABLE statement does not make a copy of the table.
|
|
WAIT/NOWAIT
|
|
Set the lock wait timeout. See WAIT and NOWAIT.
|
|
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 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';
|
|
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).
|
|
MariaDB until 5.3
|
SET DEFAULT: This option is currently implemented only for
|
the 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.
|
|
MariaDB 10.2.1 introduced new ways to define a constraint.
|
|
Note: Before MariaDB 10.2.1, constraint expressions were
|
accepted in syntax, but ignored.
|
|
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
|
|
System-versioned tables was added in MariaDB 10.3.4.
|
|
Add system versioning.
|
|
DROP SYSTEM VERSIONING
|
|
System-versioned tables was added in MariaDB 10.3.4.
|
|
Drop system versioning.
|
|
ADD PERIOD FOR SYSTEM_TIME
|
|
System-versioned tables was added in MariaDB 10.3.4.
|
|
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;
|
|
In MariaDB 10.0 and later, 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. 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.
|
|
EXCHANGE PARTITION
|
|
ALTER TABLE ... EXCHANGE PARTITION was introduced in MariaDB
|
10.0.4
|
|
This is used to exchange the tablespace files between a
|
partition and another 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
|
|
In MariaDB 5.5 and before, ALTER TABLE operations required
|
making a temporary copy of the table, which can be slow for
|
large tables.
|
|
In MariaDB 10.0 and later, 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 was introduced in MariaDB 10.0 as the name
|
for the original ALTER TABLE algorithm.
|
|
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=INPLACE was introduced in MariaDB 10.0.
|
|
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
|
|
In MariaDB 10.0 and later, 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 mysql 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 by
|
removing excessive undo logging (MDEV-11415). This
|
significantly shortens the time it takes to abort a running
|
ALTER TABLE operation.
|
|
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;
|
");
|
|
ALTER TABLE t1 ROW_FORMAT=COMPRESSED;
|
SELECT * FROM t1 WHERE MATCH(c) AGAINST('constraint' WITH QUERY EXPANSION);
|
|
# Cleanup
|
DROP TABLE t1;
|