Begin with a table t1
created as shown here:
CREATE TABLE t1 (a INTEGER, b CHAR(10));
To rename the table from t1
to
t2
:
ALTER TABLE t1 RENAME t2;
To change column a
from
INTEGER
to TINYINT NOT
NULL
(leaving the name the same), and to change column
b
from CHAR(10)
to
CHAR(20)
as well as renaming it from
b
to c
:
ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);
To add a new TIMESTAMP
column
named d
:
ALTER TABLE t2 ADD d TIMESTAMP;
To add an index on column d
and a
UNIQUE
index on column a
:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
To remove column c
:
ALTER TABLE t2 DROP COLUMN c;
To add a new AUTO_INCREMENT
integer column
named c
:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (c);
We indexed c
(as a PRIMARY
KEY
) because AUTO_INCREMENT
columns
must be indexed, and we declare c
as
NOT NULL
because primary key columns cannot
be NULL
.
For NDB
tables, it is also possible
to change the storage type used for a table or column. For
example, consider an NDB
table
created as shown here:
mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.27 sec)
To convert this table to disk-based storage, you can use the
following ALTER TABLE
statement:
mysql> ALTER TABLE t1 TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (2.99 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */
ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
It is not necessary that the tablespace was referenced when the
table was originally created; however, the tablespace must be
referenced by the ALTER TABLE
:
mysql> CREATE TABLE t2 (c1 INT) ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.00 sec)
mysql> ALTER TABLE t2 STORAGE DISK;
ERROR 1005 (HY000): Can't create table 'c.#sql-1750_3' (errno: 140)
mysql> ALTER TABLE t2 TABLESPACE ts_1 STORAGE DISK;
Query OK, 0 rows affected (3.42 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t2` (
`c1` int(11) DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */
ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.01 sec)
To change the storage type of an individual column, you can use
ALTER TABLE ... MODIFY [COLUMN]
. For example,
suppose you create an NDB Cluster Disk Data table with two
columns, using this CREATE TABLE
statement:
mysql> CREATE TABLE t3 (c1 INT, c2 INT)
-> TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
Query OK, 0 rows affected (1.34 sec)
To change column c2
from disk-based to
in-memory storage, include a STORAGE MEMORY clause in the column
definition used by the ALTER TABLE statement, as shown here:
mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;
Query OK, 0 rows affected (3.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
You can make an in-memory column into a disk-based column by
using STORAGE DISK
in a similar fashion.
Column c1
uses disk-based storage, since this
is the default for the table (determined by the table-level
STORAGE DISK
clause in the
CREATE TABLE
statement). However,
column c2
uses in-memory storage, as can be
seen here in the output of SHOW CREATE
TABLE
:
mysql> SHOW CREATE TABLE t3\G
*************************** 1. row ***************************
Table: t3
Create Table: CREATE TABLE `t3` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) /*!50120 STORAGE MEMORY */ DEFAULT NULL
) /*!50100 TABLESPACE ts_1 STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.02 sec)
When you add an AUTO_INCREMENT
column, column
values are filled in with sequence numbers automatically. For
MyISAM
tables, you can set the first sequence
number by executing SET
INSERT_ID=
before
value
ALTER TABLE
or by using the
AUTO_INCREMENT=
table option.
value
With MyISAM
tables, if you do not change the
AUTO_INCREMENT
column, the sequence number is
not affected. If you drop an AUTO_INCREMENT
column and then add another AUTO_INCREMENT
column, the numbers are resequenced beginning with 1.
When replication is used, adding an
AUTO_INCREMENT
column to a table might not
produce the same ordering of the rows on the replica and the
source. This occurs because the order in which the rows are
numbered depends on the specific storage engine used for the
table and the order in which the rows were inserted. If it is
important to have the same order on the source and replica, the
rows must be ordered before assigning an
AUTO_INCREMENT
number. Assuming that you want
to add an AUTO_INCREMENT
column to the table
t1
, the following statements produce a new
table t2
identical to t1
but with an AUTO_INCREMENT
column:
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
SELECT * FROM t1 ORDER BY col1, col2;
This assumes that the table t1
has columns
col1
and col2
.
This set of statements also produces a new table
t2
identical to t1
, with
the addition of an AUTO_INCREMENT
column:
CREATE TABLE t2 LIKE t1;
ALTER TABLE t2 ADD id INT AUTO_INCREMENT PRIMARY KEY;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;
To guarantee the same ordering on both source and replica,
all columns of t1
must
be referenced in the ORDER BY
clause.
Regardless of the method used to create and populate the copy
having the AUTO_INCREMENT
column, the final
step is to drop the original table and then rename the copy:
DROP TABLE t1;
ALTER TABLE t2 RENAME t1;