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 | 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 VERSIONING 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' | CLUSTERING={YES| NO} table_options: table_option [[,] table_option] ...
In MariaDB 10.0.2 and later, 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.
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.
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
.
Executing the ALTER TABLE
statement generally requires at least the ALTER
privilege for the table or the database..
If you are renaming a table, then it also requires the DROP
, CREATE
and INSERT
privileges for the table or the database as well.
In MariaDB 10.0 and later, online DDL is supported with the ALGORITHM
and LOCK
clauses.
See InnoDB Online DDL Overview for more information on online DDL with InnoDB.
ALTER ONLINE TABLE
ALTER ONLINE TABLE has also worked for partitioned tables since MariaDB 10.0.11.
Online ALTER TABLE
is available by executing the following:
ALTER ONLINE TABLE ...;
This statement has the following semantics:
In MariaDB 10.0.12 and later, this statement is equivalent to the following:
ALTER TABLE ... LOCK=NONE;
See the LOCK
alter specification for more information.
In MariaDB 10.0.11, this statement is equivalent to the following:
ALTER TABLE ... ALGORITHM=INPLACE;
See the ALGORITHM
alter specification for more information.
In MariaDB 10.0.10 and before, this statement ensures that the ALTER TABLE
statement does not make a copy of the table.
Set the lock wait timeout. See WAIT and NOWAIT.
See CREATE TABLE: Column Definitions for information about column definitions.
See CREATE TABLE: Index Definitions for information about index definitions.
The CREATE INDEX
and DROP INDEX
statements can also be used to add or remove an index.
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.
See CREATE TABLE: Table Options for information about table options.
... 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 [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.
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 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.
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;
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;
This lets you change column options.
CREATE TABLE t1 (a INT UNSIGNED AUTO_INCREMENT, b varchar(50), PRIMARY KEY(a)); ALTER TABLE t1 ALTER b SET DEFAULT 'hello';
Add a primary key.
For PRIMARY KEY
indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY
.
See Getting Started with Indexes: Primary Key for more information.
Drop a primary key.
For PRIMARY KEY
indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY
.
See Getting Started with Indexes: Primary Key for more information.
Add a foreign key.
For FOREIGN KEY
indexes, a reference definition must be provided.
For FOREIGN KEY
indexes, you can specify a name for the constraint, using the CONSTRAINT
keyword. That name will be used in error messages.
First, you have to specify the name of the target (parent) table and a column or a column list which must be indexed and whose values must match to the foreign key's values. The MATCH
clause is accepted to improve the compatibility with other DBMS's, but has no meaning in MariaDB. The ON DELETE
and ON UPDATE
clauses specify what must be done when a DELETE
(or a REPLACE
) statements attempts to delete a referenced row from the parent table, and when an UPDATE
statement attempts to modify the referenced foreign key columns in a parent table row, respectively. The following options are allowed:
RESTRICT
: The delete/update operation is not performed. The statement terminates with a 1451 error (SQLSTATE '2300'). NO ACTION
: Synonym for RESTRICT
. CASCADE
: The delete/update operation is performed in both tables. SET NULL
: The update or delete goes ahead in the parent table, and the corresponding foreign key fields in the child table are set to NULL
. (They must not be defined as NOT NULL
for this to succeed). SET DEFAULT
: This option is currently implemented only for the PBXT storage engine, which is disabled by default and no longer maintained. It sets the child table's foreign key fields to their DEFAULT
values when the referenced parent table key entries are updated or deleted. If either clause is omitted, the default behavior for the omitted clause is RESTRICT
.
See Foreign Keys for more information.
Drop a foreign key.
See Foreign Keys for more information.
Add a plain index.
Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT
or SPATIAL
indexes.
See Getting Started with Indexes: Plain Indexes for more information.
Drop a plain index.
Plain indexes are regular indexes that are not unique, and are not acting as a primary key or a foreign key. They are also not the "specialized" FULLTEXT
or SPATIAL
indexes.
See Getting Started with Indexes: Plain Indexes for more information.
Add a unique index.
The UNIQUE
keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index.
For UNIQUE
indexes, you can specify a name for the constraint, using the CONSTRAINT
keyword. That name will be used in error messages.
See Getting Started with Indexes: Unique Index for more information.
Drop a unique index.
The UNIQUE
keyword means that the index will not accept duplicated values, except for NULLs. An error will raise if you try to insert duplicate values in a UNIQUE index.
For UNIQUE
indexes, you can specify a name for the constraint, using the CONSTRAINT
keyword. That name will be used in error messages.
See Getting Started with Indexes: Unique Index for more information.
Add a FULLTEXT
index.
See Full-Text Indexes for more information.
Drop a FULLTEXT
index.
See Full-Text Indexes for more information.
Add a SPATIAL index.
See SPATIAL INDEX for more information.
Drop a SPATIAL index.
See SPATIAL INDEX for more information.
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.
Renames the table. See also RENAME TABLE
.
Modifies the table adding a constraint on a particular column or columns.
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
for UNIQUE
and FOREIGN KEY
constraints was introduced in MariaDB 10.2.22 and MariaDB 10.3.13.
DROP CONSTRAINT
for CHECK
constraints was introduced in MariaDB 10.2.1
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-versioned tables was added in MariaDB 10.3.4.
Add system versioning.
System-versioned tables was added in MariaDB 10.3.4.
Drop system versioning.
System-versioned tables was added in MariaDB 10.3.4.
ALTER TABLE ... FORCE
can force MariaDB to re-build the table.
In MariaDB 5.5 and before, this could only be done by setting the ENGINE
table option to its old value. For example, for an InnoDB table, one could execute the following:
ALTER TABLE tab_name ENGINE = InnoDB;
In MariaDB 10.0 and later, the FORCE
option can be used instead. For example, :
ALTER TABLE tab_name FORCE;
With InnoDB, the table rebuild will only reclaim unused space (i.e. the space previously used for deleted rows) if the innodb_file_per_table
system variable is set to ON
. If the system variable is OFF
, then the space will not be reclaimed, but it will be-re-used for new data that's later added.
ALTER TABLE ... EXCHANGE PARTITION
was introduced in MariaDB 10.0.4
This is used to exchange the tablespace files between a partition and another table.
See copying InnoDB's transportable tablespaces for more information.
This is used to discard an InnoDB table's tablespace.
See copying InnoDB's transportable tablespaces for more information.
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
In MariaDB 5.5 and before, ALTER TABLE
operations required making a temporary copy of the table, which can be slow for large tables.
In MariaDB 10.0 and later, the ALTER TABLE
statement supports the ALGORITHM
clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE
supports several different algorithms. An algorithm can be explicitly chosen for an ALTER TABLE
operation by setting the ALGORITHM
clause. The supported values are:
ALGORITHM=DEFAULT
- This implies the default behavior for the specific statement, such as if no ALGORITHM
clause is specified. ALGORITHM=COPY
ALGORITHM=INPLACE
ALGORITHM=NOCOPY
- This was added in MariaDB 10.3.7. ALGORITHM=INSTANT
- This was added in MariaDB 10.3.7. See InnoDB Online DDL Overview: ALGORITHM for information on how the ALGORITHM
clause affects InnoDB.
ALGORITHM=DEFAULT
The default behavior, which occurs if ALGORITHM=DEFAULT
is specified, or if ALGORITHM
is not specified at all, usually only makes a copy if the operation doesn't support being done in-place at all. In this case, the most efficient available algorithm will usually be used.
However, in MariaDB 10.3.6 and before, if the value of the old_alter_table
system variable is set to ON
, then the default behavior is to perform ALTER TABLE
operations by making a copy of the table using the old algorithm.
In MariaDB 10.3.7 and later, the old_alter_table
system variable is deprecated. Instead, the alter_algorithm
system variable defines the default algorithm for ALTER TABLE
operations.
ALGORITHM=COPY
ALGORITHM=COPY
was introduced in MariaDB 10.0 as the name for the original ALTER TABLE
algorithm.
When ALGORITHM=COPY
is set, MariaDB essentially does the following operations:
-- Create a temporary table with the new definition CREATE TEMPORARY TABLE tmp_tab ( ... ); -- Copy the data from the original table INSERT INTO tmp_tab SELECT * FROM original_tab; -- Drop the original table DROP TABLE original_tab; -- Rename the temporary table, so that it replaces the original one RENAME TABLE tmp_tab TO original_tab;
This algorithm is very inefficient, but it is generic, so it works for all storage engines.
If ALGORITHM=COPY
is specified, then the copy algorithm will be used even if it is not necessary. This can result in a lengthy table copy. If multiple ALTER TABLE
operations are required that each require the table to be rebuilt, then it is best to specify all operations in a single ALTER TABLE
statement, so that the table is only rebuilt once.
ALGORITHM=INPLACE
ALGORITHM=INPLACE
was introduced in MariaDB 10.0.
ALGORITHM=COPY
can be incredibly slow, because the whole table has to be copied and rebuilt. ALGORITHM=INPLACE
was introduced as a way to avoid this by performing operations in-place and avoiding the table copy and rebuild, when possible.
When ALGORITHM=INPLACE
is set, the underlying storage engine uses optimizations to perform the operation while avoiding the table copy and rebuild. However, INPLACE
is a bit of a misnomer, since some operations may still require the table to be rebuilt for some storage engines. Regardless, several operations can be performed without a full copy of the table for some storage engines.
A more accurate name would have been ALGORITHM=ENGINE
, where ENGINE
refers to an "engine-specific" algorithm.
If an ALTER TABLE
operation supports ALGORITHM=INPLACE
, then it can be performed using optimizations by the underlying storage engine, but it may rebuilt.
See InnoDB Online DDL Operations with ALGORITHM=INPLACE for more.
ALGORITHM=NOCOPY
ALGORITHM=NOCOPY
was introduced in MariaDB 10.3.7.
ALGORITHM=INPLACE
can sometimes be surprisingly slow in instances where it has to rebuild the clustered index, because when the clustered index has to be rebuilt, the whole table has to be rebuilt. ALGORITHM=NOCOPY
was introduced as a way to avoid this.
If an ALTER TABLE
operation supports ALGORITHM=NOCOPY
, then it can be performed without rebuilding the clustered index.
If ALGORITHM=NOCOPY
is specified for an ALTER TABLE
operation that does not support ALGORITHM=NOCOPY
, then an error will be raised. In this case, raising an error is preferable, if the alternative is for the operation to rebuild the clustered index, and perform unexpectedly slowly.
See InnoDB Online DDL Operations with ALGORITHM=NOCOPY for more.
ALGORITHM=INSTANT
ALGORITHM=INSTANT
was introduced in MariaDB 10.3.7.
ALGORITHM=INPLACE
can sometimes be surprisingly slow in instances where it has to modify data files. ALGORITHM=INSTANT
was introduced as a way to avoid this.
If an ALTER TABLE
operation supports ALGORITHM=INSTANT
, then it can be performed without modifying any data files.
If ALGORITHM=INSTANT
is specified for an ALTER TABLE
operation that does not support ALGORITHM=INSTANT
, then an error will be raised. In this case, raising an error is preferable, if the alternative is for the operation to modify data files, and perform unexpectedly slowly.
See InnoDB Online DDL Operations with ALGORITHM=INSTANT for more.
LOCK
In MariaDB 10.0 and later, the ALTER TABLE
statement supports the LOCK
clause. This clause is one of the clauses that is used to implement online DDL. ALTER TABLE
supports several different locking strategies. A locking strategy can be explicitly chosen for an ALTER TABLE
operation by setting the LOCK
clause. The supported values are:
DEFAULT
: Acquire the least restrictive lock on the table that is supported for the specific operation. Permit the maximum amount of concurrency that is supported for the specific operation. NONE
: Acquire no lock on the table. Permit all concurrent DML. If this locking strategy is not permitted for an operation, then an error is raised. SHARED
: Acquire a read lock on the table. Permit read-only concurrent DML. If this locking strategy is not permitted for an operation, then an error is raised. EXCLUSIVE
: Acquire a write lock on the table. Do not permit concurrent DML. Different storage engines support different locking strategies for different operations. If a specific locking strategy is chosen for an ALTER TABLE
operation, and that table's storage engine does not support that locking strategy for that specific operation, then an error will be raised.
If the LOCK
clause is not explicitly set, then the operation uses LOCK=DEFAULT
.
ALTER ONLINE TABLE
is equivalent to LOCK=NONE
. Therefore, the ALTER ONLINE TABLE
statement can be used to ensure that your ALTER TABLE
operation allows all concurrent DML.
See InnoDB Online DDL Overview: LOCK for information on how the LOCK
clause affects InnoDB.
MariaDB provides progress reporting for ALTER TABLE
statement for clients that support the new progress reporting protocol. For example, if you were using the mysql
client, then the progress report might look like this::
ALTER TABLE test ENGINE=Aria; Stage: 1 of 2 'copy to tmp table' 46% of stage
The progress report is also shown in the output of the SHOW PROCESSLIST
statement and in the contents of the information_schema.PROCESSLIST
table.
See Progress Reporting for more information.
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.
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.
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;
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/alter-table/