ALTER TABLE

You are viewing an old version of this article. View the current version here.

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.

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.