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 | 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 REPLAIR 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 supports this. (At least MyISAM and Aria supoorts this). This can be used to speed up inserts into empty tables.
ENABLE KEYS
will enable all disabled keys.
Re-building a table
By specifying a table option with its old value, the table will not change, but the statement forces MariaDB to re-build the table. For example, for an InnoDB table, one can use:
ALTER TABLE tab_name ENGINE = InnoDB;
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
In MariaDB 5.3, you can use the ALTER ONLINE TABLE to ensure that your ALTER TABLE is instant; If it can't be done instantly you will get an error:
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
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
.