ALTER TABLE

ALTER TABLE 用于修改现有数据表结构。

语法说明

ALTER TABLE 用于修改现有数据表结构。

语法结构

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]

alter_option: {
    ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}
  | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}
  | table_options
  | 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] ...
  | ADD [CONSTRAINT] FOREIGN KEY
        [index_name] (col_name,...)
        reference_definition
  | 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
  | 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: {col_name [(length)] | (expr)} [ASC | DESC]
index_option: {
  COMMENT[=]'string'
}
table_options:
    table_option [[,] table_option] ...
table_option: {
  COMMENT [=] 'string'
}

语法释义

下面是各个参数的释义：

  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）。

  7. ALGORITHM [=] {DEFAULT | INSTANT | INPLACE | COPY}：指定 MatrixOne 执行该操作所使用的算法。COPY 会构建表的新副本；INPLACE 原地修改表；INSTANT 仅应用元数据变更；DEFAULT 由 MatrixOne 为该操作选择算法。

  8. LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}：指定操作期间所需的锁级别。NONE 允许并发读写，SHARED 允许并发读但不允许写，EXCLUSIVE 阻止并发访问，DEFAULT 由 MatrixOne 选择锁级别。

ALGORITHM 和 LOCK 子句

ALTER TABLE 接受与 MySQL 兼容的 ALGORITHM 和 LOCK 子句。它们可以单独出现或一起出现，并位于表更改操作之前：

ALTER TABLE tbl_name ALGORITHM=COPY, LOCK=SHARED, ADD COLUMN c INT;

ALGORITHM 接受 DEFAULT、INSTANT、INPLACE 和 COPY。LOCK 接受 DEFAULT、NONE、SHARED 和 EXCLUSIVE。

并非所有组合都合法。重建表的操作（如 ADD COLUMN 和 DROP COLUMN）要求 ALGORITHM=COPY，并拒绝 ALGORITHM=INPLACE 或 ALGORITHM=INSTANT。仅涉及元数据或索引的操作（如 ADD INDEX、DROP INDEX 和 RENAME COLUMN）接受 INPLACE 或 INSTANT。当操作要求 COPY 时，LOCK=NONE 会被拒绝，因为 COPY 需要排他锁。

DROP DATABASE IF EXISTS alter_table_alg_demo;
CREATE DATABASE alter_table_alg_demo;
USE alter_table_alg_demo;

CREATE TABLE t_alg_lock(a INT, b INT);
INSERT INTO t_alg_lock VALUES (1, 2);

ALTER TABLE t_alg_lock ALGORITHM=COPY, ADD COLUMN c1 INT;
ALTER TABLE t_alg_lock ALGORITHM=INPLACE, ADD INDEX idx_s2(a);
ALTER TABLE t_alg_lock LOCK=SHARED, ADD INDEX idx_s4(b);
ALTER TABLE t_alg_lock LOCK=EXCLUSIVE, ADD COLUMN c3 INT;
ALTER TABLE t_alg_lock ALGORITHM=INSTANT, LOCK=DEFAULT, RENAME COLUMN c3 TO c3_new;

DROP DATABASE alter_table_alg_demo;

示例

  • 示例 1：删除外键约束

DROP DATABASE IF EXISTS alter_table_fk_demo;
CREATE DATABASE alter_table_fk_demo;
USE alter_table_fk_demo;

-- 创建 f1 表，包含两个整数列：fa（主键）和 fb（具有唯一约束的键）
CREATE TABLE f1(fa INT PRIMARY KEY, fb INT UNIQUE KEY);
-- 创建 c1 表，包含两个整数列：ca 和 cb
CREATE TABLE c1 (ca INT, cb INT);
-- 为 c1 表添加一个名为 ffa 的外键约束，将 c1 表的 ca 列与 f1 表的 fa 列相关联
ALTER TABLE c1 ADD CONSTRAINT ffa FOREIGN KEY (ca) REFERENCES f1(fa);
-- 向 f1 表插入一条记录：(2, 2)
INSERT INTO f1 VALUES (2, 2);
-- 向 c1 表插入 (1, 1) 会违反外键约束，因为 1 不在 f1 表的 fa 列中
-- Expected-Success: false
INSERT INTO c1 VALUES (1, 1);
-- 向 c1 表插入一条记录：(2, 2)
INSERT INTO c1 VALUES (2, 2);
-- 从 c1 表中选择所有记录，并按 ca 列排序
SELECT ca, cb FROM c1 ORDER BY ca;
-- 从 c1 表中删除名为 ffa 的外键约束
ALTER TABLE c1 DROP FOREIGN KEY ffa;
-- 向 c1 表插入一条记录：(1, 1)
INSERT INTO c1 VALUES (1, 1);
-- 从 c1 表中选择所有记录，并按 ca 列排序
SELECT ca, cb FROM c1 ORDER BY ca;

DROP DATABASE alter_table_fk_demo;

  • 示例 2：添加主键

DROP DATABASE IF EXISTS alter_table_pk_demo;
CREATE DATABASE alter_table_pk_demo;
USE alter_table_pk_demo;

-- 创建一个名为 't1' 的新表，包含列 a、b、c 和 d。列 'a' 的数据类型为 INTEGER，'b' 的数据类型为 CHAR(10)，'c' 的数据类型为 DATE，'d' 的数据类型为 DECIMAL(7,2)。在列 'a' 和 'b' 上添加了一个唯一键。
CREATE TABLE t1(a INTEGER, b CHAR(10), c DATE, d DECIMAL(7,2), UNIQUE KEY(a, b));

-- 向表 't1' 插入三行数据。
INSERT INTO t1 VALUES(1, 'ab', '1980-12-17', 800);
INSERT INTO t1 VALUES(2, 'ac', '1981-02-20', 1600);
INSERT INTO t1 VALUES(3, 'ad', '1981-02-22', 500);

-- 显示表 't1' 中的所有行。
SELECT * FROM t1;

-- 修改表 't1'，在列 'a' 和 'b' 上添加主键 'pk1'。
ALTER TABLE t1 ADD PRIMARY KEY pk1(a, b);

-- 再次查看修改后的表 't1' 的结构。
DESC t1;

-- 添加主键后，再次显示表 't1' 中的所有行。
SELECT * FROM t1;

DROP DATABASE alter_table_pk_demo;

  • 示例 3：修改列名

DROP DATABASE IF EXISTS alter_table_rename_col_demo;
CREATE DATABASE alter_table_rename_col_demo;
USE alter_table_rename_col_demo;

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

INSERT INTO t1 VALUES(1, 'ab');
INSERT INTO t1 VALUES(2, 'ac');
INSERT INTO t1 VALUES(3, 'ad');

SELECT * FROM t1;

-- 修改表 't1'，将列 'a' 的名称改为 'x'，并将数据类型修改为 VARCHAR(20)。
ALTER TABLE t1 CHANGE a x VARCHAR(20);

DESC t1;
SELECT * FROM t1;

DROP DATABASE alter_table_rename_col_demo;

  • 示例 4：表重命名

DROP DATABASE IF EXISTS alter_table_rename_demo;
CREATE DATABASE alter_table_rename_demo;
USE alter_table_rename_demo;

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

SHOW TABLES;

ALTER TABLE t1 RENAME TO t2;

SHOW TABLES;

DROP DATABASE alter_table_rename_demo;

限制

  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 修改表结构。