nnoDB's transportable tablespaces for more
information.
 
IMPORT TABLESPACE
 
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.
 
Progress Reporting
 
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.
 
Aborting ALTER TABLE Operations
 
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.
 
Examples
-------- 
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;
 


URL: https://mariadb.com/kb/en/alter-table/