ALTER TABLE示例

首先t1创建一个表，如下所示：

CREATE TABLE t1 (a INTEGER, b CHAR(10));

要将表从重命名t1为t2：

ALTER TABLE t1 RENAME t2;

若要更改列a从INTEGER给TINYINT NOT NULL（名字一样），并更改列b从CHAR(10)到CHAR(20)以及来自重命名b到c：

ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);

要添加新TIMESTAMP列d：

ALTER TABLE t2 ADD d TIMESTAMP;

要在列上添加索引d并UNIQUE在列上添加索引a：

ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);

删除列c：

ALTER TABLE t2 DROP COLUMN c;

要添加一个新的AUTO_INCREMENT整数列c：

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (c);

我们之所以建立索引c（因为PRIMARY KEY）是因为AUTO_INCREMENT必须对列进行索引，而我们声明c为，NOT NULL因为主键列不能为NULL。

对于NDB表，也可以更改用于表或列的存储类型。例如，考虑NDB如下所示创建的表：

mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.27 sec)

要将此表转换为基于磁盘的存储，可以使用以下ALTER TABLE语句：

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)

最初创建表时不必引用表空间；但是，表空间必须由引用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)

要更改单个列的存储类型，可以使用ALTER TABLE ... MODIFY[COLUMN]。例如，假设您使用以下CREATE TABLE语句创建具有两列的NDB群集磁盘数据表：

mysql> CREATE TABLE t3 (c1 INT, c2 INT)
->     TABLESPACE ts_1 STORAGE DISK ENGINE NDB;
Query OK, 0 rows affected (1.34 sec)

要将列c2从基于磁盘的存储更改为内存中的存储，请在ALTER TABLE语句使用的列定义中包含STORAGE MEMORY子句，如下所示：

mysql> ALTER TABLE t3 MODIFY c2 INT STORAGE MEMORY;
Query OK, 0 rows affected (3.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

您可以通过STORAGE DISK类似的方式将内存中的列创建为基于磁盘的列。

列c1使用基于磁盘的存储，因为这是表的默认设置（由STORAGE DISK语句中的表级子句确定CREATE TABLE）。但是，列c2使用内存存储，如在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)

添加AUTO_INCREMENT列时，列值会自动用序列号填充。对于MyISAM表，可以通过在执行之前或使用表选项来设置第一个序列号。SET INSERT_ID=valueALTER TABLEAUTO_INCREMENT=value

对于MyISAM表，如果不更改AUTO_INCREMENT列，则序列号不受影响。如果先删除一AUTO_INCREMENT列然后再添加另一AUTO_INCREMENT列，则数字将从1开始重新排序。

使用复制时，将一AUTO_INCREMENT列添加到表中可能不会在从属服务器和主服务器上产生相同的行顺序。发生这种情况的原因是，行的编号顺序取决于用于表的特定存储引擎以及行的插入顺序。如果在主站和从站上具有相同的顺序很重要，则在分配AUTO_INCREMENT编号之前必须对行进行排序。假设要向AUTO_INCREMENT表中添加一列t1，以下语句将生成一个t2与表相同t1但带有AUTO_INCREMENT列的新表：

CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
SELECT * FROM t1 ORDER BY col1, col2;

假设该表t1包含列col1和col2。

这组语句还将产生一个t2与相同的新表t1，并增加一AUTO_INCREMENT列：

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;

重要
为了保证主机和从机上的顺序相同，必须在子句中引用的所有列。t1ORDER BY

无论使用哪种方法来创建和填充具有该AUTO_INCREMENT列的副本，最后一步都是删除原始表，然后重命名副本：

DROP TABLE t1;
ALTER TABLE t2 RENAME t1;