ALTER TABLE
Syntax
ALTER [ONLINE] [IGNORE] TABLE 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|
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|
ENCRYPTED [=] {YES | NO}|
ENCRYPTION_KEY_ID [=] value|
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 VERSIONINGindex_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] ... (see CREATE TABLE options)
MariaDB starting with 10.0.2
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]
Contents
- Syntax
- Description
- Index Type
- Character Sets and Collations
- WAIT/NOWAIT
- ADD COLUMN
- DROP COLUMN
- MODIFY COLUMN
- CHANGE COLUMN
- ENABLE/ DISABLE KEYS
- ADD CONSTRAINT
- DROP CONSTRAINT
- SYSTEM VERSIONING
- ENGINE/FORCE For Re-building a Table
- EXCHANGE PARTITION
- DISCARD TABLESPACE
- IMPORT TABLESPACE
- ALGORITHM
- LOCK
- Progress Reports
- Privileges
- Aborting ALTER TABLE Operations
- Examples
- See Also
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.
MariaDB starting with 10.0.4
ALTER TABLE ... EXCHANGE PARTITION
was introduced in MariaDB 10.0.4
MariaDB starting with 10.2.1
DEFAULT
with expressions and DROP CONSTRAINT
was introduced in MariaDB 10.2.1
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.
For descriptions of the table options, see CREATE TABLE
.
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
.
Index Type
See Storage Engine Index Types for details on permitted index_types for each storage engine.
CLUSTERING={YES|NO}
is only valid for Tokudb.
CREATE INDEX and DROP INDEX 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.
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.
MariaDB starting with 10.2.8
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;
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.
ADD CONSTRAINT
Modifies the table adding a constraint on a particular column or columns.
MariaDB starting with 10.2.1
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
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;
SYSTEM VERSIONING
MariaDB starting with 10.3.4
See System-versioned tables for a detailed description of the ADD SYSTEM VERSIONING
, DROP SYSTEM VERSIONING
and ADD PERIOD FOR SYSTEM_TIME
clauses.
ENGINE/FORCE For Re-building a Table
ALTER TABLE
can force MariaDB to re-build the table. Before MariaDB 10.0, this was only done by setting the ENGINE
option to its old value. With MariaDB 10.0, the FORCE
option can be used. For example, for an InnoDB table, one can use:
ALTER TABLE tab_name ENGINE = InnoDB;
And, since MariaDB 10.0, this is equivalent to:
ALTER TABLE tab_name FORCE;
With InnoDB, ALTER TABLE will reclaim unused space (i.e. the space previously used for deleted rows) only if innodb_file_per_table is ON
. If the variable is OFF
, the space will not be reclaimed after an ALTER TABLE, but will be-re-used for new data that's later added.
EXCHANGE PARTITION
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
Before MariaDB 10.0, ALTER TABLE operations required making a temporary copy of the table, which can be slow for large tables. From MariaDB 10.0, many ALTER TABLE operations can be performed using in-place operations, without making a temporary copy of the table.
Over time, more operations have been made possible without a full copy. Here is a list of the actions that can be performed without having to copy the table:
- Changing a column name
- Changing display length of a integer like INT(2) -> INT(3)
- Changing a table comment
- Adding a new enum option last to a list
- Renaming a table
If multiple operations are needed, and they require the rebuilding of a table, it is convenient to group them in a single ALTER TABLE, so that only one rebuilding is done.
The ALGORITHM
clause, which has existed since MariaDB 10.0, can be used to exert some control over this behavior. Up until MariaDB 10.3.7, there were three possible values:
- ALGORITHM=DEFAULT (the default behavior if no clause is specified)
- ALGORITHM=COPY
- ALGORITHM=INPLACE
With ALGORITHM=COPY
, the copy algorithm will be used even if it is not necessary. This can result in a lengthy table copy.
With ALGORITHM=INPLACE
, an engine-specific technique for creating indexes more efficiently will be used. Concurrent modification to the table may be possible. If the operation is not natively supported by the storage engine, an error is instead issued:
CREATE TABLE t1 (a INT, e ENUM ('red','green')); ALTER TABLE t1 MODIFY e ENUM('red','green','blue'), ALGORITHM=INPLACE; -> Query OK, 0 rows affected (0.11 sec) -> Records: 0 Duplicates: 0 Warnings: 0 ALTER TABLE t1 MODIFY a INT UNSIGNED, ALGORITHM=INPLACE; -> ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.
The default behavior (ALGORITHM=DEFAULT
, or ALGORITHM
is not specified) usually only makes a copy when necessary. This can change if the value of the old_alter_table system variable is set to ON
(OFF
is default), in which case a copy of the table is made using a pre-MySQL 5.1 algorithm.
MariaDB 10.3.7 introduced two new options:
- ALGORITHM=NOCOPY
- ALGORITHM=INSTANT
Since ALGORITHM=INPLACE
can sometimes be surprisingly slow in instances where it has to modify data or rebuild the clustered index, the two new options were introduced to rather return an error than perform unexpectedly slowly.
ALGORITHM=INSTANT
will refuse any operation that must modify any data files.
ALGORITHM=NOCOPY
will refuse any operation that would rebuild the clustered index (and the whole table).
With respect to the allowed operations, ALGORITHM=INSTANT
is a subset of ALGORITHM=NOCOPY
which is a subset of ALGORITHM=INPLACE
which is a subset of ALGORITHM=COPY
.
Examples
The following will not perform any data file change (MDEV-11369 instant ADD COLUMN):
ALTER TABLE t ADD COLUMN b INT, ALGORITHM=INSTANT;
The following will change data files (ADD INDEX), but not rebuild the table:
ALTER TABLE t ADD INDEX(b), ALGORITHM=NOCOPY;
The following will change data files (not instant operation):
--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t DROP INDEX b, ALGORITHM=INSTANT; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t ADD INDEX(b), ALGORITHM=INSTANT;
All of the above should be allowed with any other ALGORITHM. With ALGORITHM=DEFAULT or with no ALGORITHM, the most efficient available algorithm will be used.
MariaDB starting with 10.0.11
Online ALTER TABLE has also worked for partitioned tables since MariaDB 10.0.11.
LOCK
Depending on the required operation and the used storage engine, different lock strategies can be used for ALTER TABLE
. In some cases no locks are required, other times only read locks are required, while sometimes write locks are required. The LOCK
clause can be used to require a certain lock strategy. It will be used if possible (even if you specify a strategy which is more restrictive than necessary), but if the required operation on the storage engine needs a more restrictive strategy, an error is issued. Allowed values for the LOCK
clause are:
DEFAULT
: The maximum allowed concurrency level is used.NONE
: No lock is acquired or an error is issued.SHARED
: A read lock is acquired or an error is issued.EXCLUSIVE
: A write lock is acquired.
A missing LOCK
clause is equivalent to LOCK=DEFAULT
.
Alternatively you can use ALTER ONLINE TABLE
to ensure that your ALTER TABLE
does not block concurrent operations (takes no locks). It is equivalent to LOCK=NONE
.
The INPLACE
algorithm sometimes requires temporary files. These files are created in the temporary directory, specified in the tmpdir server system variable.
Note that, if the COPY algorithm is used, the current values of innodb_file_per_table and innodb_file_format apply during the InnoDB tables re-creation.
Progress Reports
From MariaDB 5.3 you can get progress reports for ALTER TABLE
in clients
which support the new progress reporting protocol. From the mysql client:
ALTER TABLE test ENGINE=Aria; Stage: 1 of 2 'copy to tmp table' 46% of stage
The progress report is also shown in SHOW PROCESSLIST and the information_schema.processlist table.
Privileges
ALTER TABLE requires at least the ALTER privilege. Renaming a table requires the DROP, CREATE and INSERT privileges as well.
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.
MariaDB starting with 10.2.13
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;
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:
ALTER TABLE t1 ENGINE = InnoDB;
Rebuilding the table (the previous example will also rebuild the table if it was already InnoDB):
ALTER TABLE t1 FORCE;