我的书签
添加书签
移除书签ALTER TABLE
语法说明
ALTER TABLE 用于修改现有数据表结构。
语法结构
1. ALTER TABLE tbl_name
2. [alter_option [, alter_option] ...]
4. alter_option: {
5. table_options
6. | ADD [COLUMN] col_name column_definition
7. [FIRST | AFTER col_name]
8. | ADD [COLUMN] (col_name column_definition,...)
9. | ADD {[INDEX | KEY] [index_name]
10. [index_option] ...
11. | ADD [CONSTRAINT] UNIQUE [INDEX | KEY]
12. [index_name][index_option] ...
13. | ADD [CONSTRAINT] FOREIGN KEY
14. [index_name] (col_name,...)
15. reference_definition
16. | ADD [CONSTRAINT [symbol]] PRIMARY KEY
17. [index_type] (key_part,...)
18. | CHANGE [COLUMN] old_col_name new_col_name column_definition
19. [FIRST | AFTER col_name]
20. | ALTER INDEX index_name {VISIBLE | INVISIBLE}
21. | DROP [COLUMN] col_name
22. | DROP {INDEX | KEY} index_name
23. | DROP FOREIGN KEY fk_symbol
24. | DROP PRIMARY KEY
25. | RENAME [TO | AS] new_tbl_name
26. | MODIFY [COLUMN] col_name column_definition
27. [FIRST | AFTER col_name]
28. | RENAME COLUMN old_col_name TO new_col_name
29. }
31. key_part: {col_name [(length)] | (expr)} [ASC | DESC]
32. index_option: {
33. COMMENT[=]'string'
34. }
35. table_options:
36. table_option [[,] table_option] ...
37. table_option: {
38. COMMENT [=] 'string'
39. }
语法释义
下面是各个参数的释义：
ALTER TABLE tbl_name：表示修改名为tbl_name的表。-
alter_option：表示可以执行一个或多个更改选项，用逗号分隔。 -
table_options：用于设置或修改表的选项，例如表的注释（COMMENT）。 ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]：在表中添加一个新列，可以指定新列插入的位置（在某列之前或之后）。ADD [COLUMN] (col_name column_definition,...)：同时添加多个新列。ADD {[INDEX | KEY] [index_name] [index_option] ...：添加一个索引，可以指定索引名和索引选项（例如，注释）。ADD [CONSTRAINT] UNIQUE [INDEX | KEY] [index_name][index_option] ...：添加一个 UNIQUE 约束或 UNIQUE 索引。ADD [CONSTRAINT] FOREIGN KEY [index_name] (col_name,...) reference_definition：添加一个 FOREIGN KEY 约束。ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (key_part,...)：添加主键约束。CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name]：修改列定义、列名与顺序。ALTER INDEX index_name {VISIBLE | INVISIBLE}：更改索引的可见性。DROP [COLUMN] col_name：删除一个列。DROP {INDEX | KEY} index_name：删除一个索引。DROP FOREIGN KEY fk_symbol：删除一个 FOREIGN KEY 约束。DROP PRIMARY KEY：删除主键。RENAME [TO | AS] new_tbl_name：重命名整个表。MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]：修改列定义与顺序。RENAME COLUMN old_col_name TO new_col_name：重命名列。key_part：表示索引的组成部分，可以使用列名（在创建一个文本列的索引时，你可以为索引指定一个长度，字符长度可变。如果您在创建索引时使用列名且不指定长度，索引将会使用整个列的值作为索引组成部分。这在某些情况下可能导致性能降低，特别是在处理较大文本列或二进制数据列时。对于较小的数据类型，例如整数或日期，通常不需要指定长度）。index_option：表示索引的选项，例如注释（COMMENT）。table_options：表示表的选项，如表的注释（COMMENT）。table_option：具体的表选项，例如注释（COMMENT）。
示例
- 示例 1：
1. -- 创建 f1 表，包含两个整数列：fa（主键）和 fb（具有唯一约束的键）
2. CREATE TABLE f1(fa INT PRIMARY KEY, fb INT UNIQUE KEY);
3. -- 创建 c1 表，包含两个整数列：ca 和 cb
4. CREATE TABLE c1 (ca INT, cb INT);
5. -- 为 c1 表添加一个名为 ffa 的外键约束，将 c1 表的 ca 列与 f1 表的 fa 列相关联
6. ALTER TABLE c1 ADD CONSTRAINT ffa FOREIGN KEY (ca) REFERENCES f1(fa);
7. -- 向 f1 表插入一条记录：(2, 2)
8. INSERT INTO f1 VALUES (2, 2);
9. -- 向 c1 表插入一条记录：(1, 1)
10. INSERT INTO c1 VALUES (1, 1);
11. -- 向 c1 表插入一条记录：(2, 2)
12. INSERT INTO c1 VALUES (2, 2);
13. -- 从 c1 表中选择所有记录，并按 ca 列排序
14. mysql> select ca, cb from c1 order by ca;
15. +------+------+
16. | ca | cb |
17. +------+------+
18. | 2 | 2 |
19. +------+------+
20. 1 row in set (0.01 sec)
21. -- 从 c1 表中删除名为 ffa 的外键约束
22. ALTER TABLE c1 DROP FOREIGN KEY ffa;
23. -- 向 c1 表插入一条记录：(1, 1)
24. INSERT INTO c1 VALUES (1, 1);
25. -- 从 c1 表中选择所有记录，并按 ca 列排序
26. mysql> select ca, cb from c1 order by ca;
27. +------+------+
28. | ca | cb |
29. +------+------+
30. | 1 | 1 |
31. | 2 | 2 |
32. +------+------+
33. 2 rows in set (0.01 sec)
- 示例 2：
1. -- 创建一个名为 't1' 的新表，包含列 a、b、c 和 d。列 'a' 的数据类型为 INTEGER，'b' 的数据类型为 CHAR(10)，'c' 的数据类型为 DATE，'d' 的数据类型为 DECIMAL(7,2)。在列 'a' 和 'b' 上添加了一个唯一键。
2. CREATE TABLE t1(a INTEGER, b CHAR(10), c DATE, d DECIMAL(7,2), UNIQUE KEY(a, b));
4. -- 查看表 't1' 的结构。
5. mysql> desc t1;
6. +-------+--------------+------+------+---------+-------+---------+
7. | Field | Type | Null | Key | Default | Extra | Comment |
8. +-------+--------------+------+------+---------+-------+---------+
9. | a | INT(32) | YES | | NULL | | |
10. | b | CHAR(10) | YES | | NULL | | |
11. | c | DATE(0) | YES | | NULL | | |
12. | d | DECIMAL64(7) | YES | | NULL | | |
13. +-------+--------------+------+------+---------+-------+---------+
14. 4 rows in set (0.01 sec)
16. -- 向表 't1' 插入三行数据。
17. INSERT INTO t1 VALUES(1, 'ab', '1980-12-17', 800);
18. INSERT INTO t1 VALUES(2, 'ac', '1981-02-20', 1600);
19. INSERT INTO t1 VALUES(3, 'ad', '1981-02-22', 500);
21. -- 显示表 't1' 中的所有行。
22. mysql> select * from t1;
23. +------+------+------------+---------+
24. | a | b | c | d |
25. +------+------+------------+---------+
26. | 1 | ab | 1980-12-17 | 800.00 |
27. | 2 | ac | 1981-02-20 | 1600.00 |
28. | 3 | ad | 1981-02-22 | 500.00 |
29. +------+------+------------+---------+
30. 3 rows in set (0.01 sec)
32. -- 修改表 't1'，在列 'a' 和 'b' 上添加主键 'pk1'。
33. mysql> alter table t1 add primary key pk1(a, b);
34. Query OK, 0 rows affected (0.02 sec)
36. -- 再次查看修改后的表 't1' 的结构。
37. mysql> desc t1;
38. +-------+--------------+------+------+---------+-------+---------+
39. | Field | Type | Null | Key | Default | Extra | Comment |
40. +-------+--------------+------+------+---------+-------+---------+
41. | a | INT(32) | NO | PRI | null | | |
42. | b | CHAR(10) | NO | PRI | null | | |
43. | c | DATE(0) | YES | | null | | |
44. | d | DECIMAL64(7) | YES | | null | | |
45. +-------+--------------+------+------+---------+-------+---------+
46. 4 rows in set (0.01 sec)
48. -- 添加主键后，再次显示表 't1' 中的所有行。
49. mysql> select * from t1;
50. +------+------+------------+---------+
51. | a | b | c | d |
52. +------+------+------------+---------+
53. | 1 | ab | 1980-12-17 | 800.00 |
54. | 2 | ac | 1981-02-20 | 1600.00 |
55. | 3 | ad | 1981-02-22 | 500.00 |
56. +------+------+------------+---------+
57. 3 rows in set (0.00 sec)
- 示例 3：
1. CREATE TABLE t1 (a INTEGER PRIMARY KEY, b CHAR(10));
2. mysql> desc t1;
3. +-------+----------+------+------+---------+-------+---------+
4. | Field | Type | Null | Key | Default | Extra | Comment |
5. +-------+----------+------+------+---------+-------+---------+
6. | a | INT(32) | NO | PRI | NULL | | |
7. | b | CHAR(10) | YES | | NULL | | |
8. +-------+----------+------+------+---------+-------+---------+
9. 2 rows in set (0.01 sec)
11. insert into t1 values(1, 'ab');
12. insert into t1 values(2, 'ac');
13. insert into t1 values(3, 'ad');
15. mysql> select * from t1;
16. +------+------+
17. | a | b |
18. +------+------+
19. | 1 | ab |
20. | 2 | ac |
21. | 3 | ad |
22. +------+------+
23. 3 rows in set (0.01 sec)
25. -- 修改表 't1'，将列 'a' 的名称改为 'x'，并将数据类型修改为 VARCHAR(20)。
26. mysql> alter table t1 change a x VARCHAR(20);
27. Query OK, 0 rows affected (0.01 sec)
29. mysql> desc t1;
30. +-------+-------------+------+------+---------+-------+---------+
31. | Field | Type | Null | Key | Default | Extra | Comment |
32. +-------+-------------+------+------+---------+-------+---------+
33. | x | VARCHAR(20) | NO | PRI | null | | |
34. | b | CHAR(10) | YES | | null | | |
35. +-------+-------------+------+------+---------+-------+---------+
36. 2 rows in set (0.01 sec)
38. mysql> select * from t1;
39. +------+------+
40. | x | b |
41. +------+------+
42. | 1 | ab |
43. | 2 | ac |
44. | 3 | ad |
45. +------+------+
46. 3 rows in set (0.00 sec)
限制
- 这些子句：
CHANGE [COLUMN]，MODIFY [COLUMN]，RENAME COLUMN，ADD [CONSTRAINT [symbol]] PRIMARY KEY，DROP PRIMARY KEY和ALTER COLUMN ORDER BY可以在 ALTER TABLE 语句中自由组合使用，但暂时不支持与其他子句一起使用。 - 临时表暂不支持使用
ALTER TABLE修改表结构。 - 使用
CREATE TABLE ... CLUSTER BY...所建的表，不支持使用ALTER TABLE修改表结构。
