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
  | 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.

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.