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. }

语法释义

下面是各个参数的释义：

  1. ALTER TABLE tbl_name：表示修改名为 tbl_name 的表。

  2. 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：重命名列。

  3. key_part：表示索引的组成部分，可以使用列名（在创建一个文本列的索引时，你可以为索引指定一个长度，字符长度可变。如果您在创建索引时使用列名且不指定长度，索引将会使用整个列的值作为索引组成部分。这在某些情况下可能导致性能降低，特别是在处理较大文本列或二进制数据列时。对于较小的数据类型，例如整数或日期，通常不需要指定长度）。

  4. index_option：表示索引的选项，例如注释（COMMENT）。

  5. table_options：表示表的选项，如表的注释（COMMENT）。
  6. 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)

限制

  1. 这些子句：CHANGE [COLUMN]，MODIFY [COLUMN]，RENAME COLUMN，ADD [CONSTRAINT [symbol]] PRIMARY KEY，DROP PRIMARY KEY 和 ALTER COLUMN ORDER BY 可以在 ALTER TABLE 语句中自由组合使用，但暂时不支持与其他子句一起使用。
  2. 临时表暂不支持使用 ALTER TABLE 修改表结构。
  3. 使用 CREATE TABLE ... CLUSTER BY... 所建的表，不支持使用 ALTER TABLE 修改表结构。