13.1.8.3 ALTER TABLE 范例
首先创建一个 tablet1
,如下所示:
CREATE TABLE t1 (a INTEGER, b CHAR(10));
要将 table 从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);
要添加名为d
的新TIMESTAMP列:
ALTER TABLE t2 ADD d TIMESTAMP;
在第d
列上添加索引,在a
列上添加UNIQUE
索引:
ALTER TABLE t2 ADD INDEX (d), ADD UNIQUE (a);
要删除第c
列:
ALTER TABLE t2 DROP COLUMN c;
要添加名为c
的新AUTO_INCREMENT
整数列:
ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (c);
因为必须对AUTO_INCREMENT
列进行索引,所以我们对c
进行了索引(作为PRIMARY KEY
),并且由于主键列不能为NULL
,因此我们将c
声明为NOT NULL
。
对于NDBtable,也可以更改用于 table 或列的存储类型。例如,考虑如下所示创建的NDBtable:
mysql> CREATE TABLE t1 (c1 INT) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.27 sec)
要将此 table 转换为基于磁盘的存储,可以使用以下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)
最初创建 table 时不必引用 table 空间;但是,table 空间必须由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 群集磁盘数据 table:
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
使用基于磁盘的存储,因为这是 table 的默认设置(由CREATE TABLE语句中的 table 级STORAGE DISK
子句确定)。但是,列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
table,可以通过在ALTER TABLE之前执行SET INSERT_ID=value
或使用AUTO_INCREMENT=value
table 选项来设置第一个序列号。
对于MyISAM
table,如果不更改AUTO_INCREMENT
列,则序列号不受影响。如果删除AUTO_INCREMENT
列然后添加另一个AUTO_INCREMENT
列,则数字将从 1 开始重新排序。
使用复制时,将AUTO_INCREMENT
列添加到 table 中可能不会在从属服务器和主服务器上产生相同的行 Sequences。发生这种情况的原因是,行的编号 Sequences 取决于用于 table 的特定存储引擎以及行的插入 Sequences。如果在主机和从机上具有相同的 Sequences 很重要,则必须在分配AUTO_INCREMENT
号之前对行进行排序。假设您要向 tablet1
添加AUTO_INCREMENT
列,以下语句将生成一个新 tablet2
,该 table 与t1
相同,但具有AUTO_INCREMENT
列:
CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY)
SELECT * FROM t1 ORDER BY col1, col2;
假定 tablet1
具有列col1
和col2
。
这组语句还将产生一个与t1
相同的新 tablet2
,并增加一个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;
Important
为了保证主服务器和从服务器上的 Sequences 相同,必须在ORDER BY
子句中引用t1
的* all *列。
无论使用哪种方法来创建和填充具有AUTO_INCREMENT
列的副本,最后一步都是删除原始 table,然后重命名该副本:
DROP TABLE t1;
ALTER TABLE t2 RENAME t1;