ALTER TABLE
Syntax
ALTER [ONLINE] [IGNORE] TABLE tbl_name alter_specification [, alter_specification] ... alter_specification: table_option ... | ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name ] | ADD [COLUMN] (col_name column_definition,...) | ADD {INDEX|KEY} [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 [index_name] (index_col_name,...) reference_definition | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name] | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name] | DROP [COLUMN] col_name | DROP PRIMARY KEY | DROP {INDEX|KEY} index_name | DROP FOREIGN KEY fk_symbol | 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 [COLLATE [=] collation_name] | DISCARD TABLESPACE | IMPORT TABLESPACE | ALGORITHM [=] {DEFAULT|INPLACE|COPY} | 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 | REMOVE PARTITIONING column_definition: data_type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string'] [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}] [STORAGE {DISK|MEMORY|DEFAULT}] | data_type [GENERATED ALWAYS] AS ( <expression> ) {VIRTUAL | PERSISTENT} [UNIQUE] [UNIQUE KEY] [COMMENT 'string'] 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' 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]
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 type 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.
CREATE INDEX
and DROP INDEX
can also be used to add or remove an index.
Character sets and collations
See Setting Character Sets and Collations for details on setting the character sets and collations.
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.
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.
When does ALTER TABLE copy all data?
MariaDB and MySQL have always had a very rich ALTER TABLE; you can do all changes you need with one command. One downside with this has been that for most cases ALTER TABLE does a full copy of the table, which can take a long time if the table is large.
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.
In MariaDB 5.3, you can use the ALTER ONLINE TABLE
to ensure that your
ALTER TABLE
is instant; since MariaDB 10.0 the ALGORITHM
clause exists. If its value is ALGORITHM=COPY
, the copy algorithm will be used even if it is not necessary, If ALGORITHM=INPLACE
but the operation necessarily requires a copy, an error is issued:
create table t1 (a int, e enum ('red','green')); alter online table t1 modify e enum('red','green','blue'); -> Query OK, 0 rows affected (0.11 sec) -> Records: 0 Duplicates: 0 Warnings: 0 ALTER ONLINE TABLE t1 ADD c int; -> ERROR 1656 (HY000): Can't execute the given 'ALTER' command as online
The default behavior (ALGORITHM=DEFAULT
is specified, or ALGORITHM
is not specified) depends on the value of the old_alter_table
system variable: if it is ON
, a copy of the table is made using a pre-MySQL 5.0 algorithm. Otherwise, a copy is made only if necessary.
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
.
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 table re-creation.
Progress Reports
In 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 information_schema.processlist
.