ALTER TABLE

描述

该语句用来修改已存在的表的结构，例如修改表及表属性、新增列、修改列及属性、删除列等。

语法

ALTER TABLE table_name [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
      table_options
      ADD [COLUMN] col_name column_definition  
          [FIRST | BEFORE | AFTER column_name]
    | ADD [COLUMN] (col_name column_definition, ...)
    | ADD {INDEX | KEY} [index_name]
          [index_type] (key_part,...) [index_option [ index_option ...]
    | ADD [CONSTRAINT [constraint_name]] UNIQUE [INDEX | KEY]
          [index_name] index_desc
    | ADD [CONSTRAINT [constraint_name]] FOREIGN KEY
          [index_name] index_desc
          REFERENCES reference_definition
          [match_action][reference_option [,reference_option ...]]
    | ADD PRIMARY KEY (column_name)
    | ADD CONSTRAINT [constraint_name] CHECK (expression)[[NOT] ENFORCED]
    | ADD PARTITION (range_partition [, range_partition ...])
    | ALTER [COLUMN] column_name {
          SET DEFAULT const_value
        | DROP DEFAULT
      }
    | ALTER {CHECK | CONSTRAINT} symbol [NOT] ENFORCED
    | ALTER INDEX index_name
          [VISIBLE | INVISIBLE]
    | CHANGE [COLUMN] column_name column_definition
    | DROP [COLUMN] column_name [, DROP COLUMN column_name ...]
    | DROP [COLUMN] column_name
    | DROP {PARTITION | SUBPARTITION} partition_name [, partition_name ...]
    | DROP TABLEGROUP
    | DROP FOREIGN KEY fk_name
    | DROP PRIMARY KEY [, ADD PRIMARY KEY (column)]
    | DROP INDEX index_name
    | DROP CHECK constraint_name
    | MODIFY [COLUMN] column_definition
    | RENAME COLUMN old_col_name TO new_col_name
    | RENAME [TO] table_name
    | RENAME {INDEX | KEY} old_index_name TO new_index_name
    | [SET] table_option [ table_option ...]
    | TRUNCATE {PARTITION | SUBPARTITION} partition_name [, partition_name ...]
    | MODIFY PARTITION partition_name ADD SUBPARTITION (add_subpartition_option)
    | FORCE

column_definition:
    column_name data_type
    [DEFAULT const_value] [AUTO_INCREMENT]
    [NULL | NOT NULL] [[PRIMARY] KEY] [UNIQUE [KEY]] comment
    [[FIRST | BEFORE | AFTER column_name]]

index_desc:
   (column_desc [, column_desc ...]) [index_type] [index_option [ index_option ...]]

column_desc:
     column_name [(length)] [ASC | DESC]

match_action:
   MATCH {SIMPLE | FULL | PARTIAL}

reference_option:
   ON {DELETE | UPDATE} {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}

key_part:
    {index_col_name [(length)] | (expr)} [ASC | DESC]

index_type:
    USING BTREE

index_option:
     [GLOBAL | LOCAL]
    | block_size
    | compression
    | STORING(column_name [, column_name ...])
    | comment
    | KEY_BLOCK_SIZE [=] INT_VALUE

table_option:
      TABLEGROUP = tablegroup_name
    | block_size
    |lob_inrow_threshold [=] num
    | compression
    | AUTO_INCREMENT [=] INT_VALUE
    | comment
    | parallel_clause
    | READ {ONLY \| WRITE}
    | KEY_BLOCK_SIZE [=] INT_VALUE
    | TABLE_MODE [=] 'table_mode_value'
    | auto_increment_cache_size [=] INT_VALUE
    | DUPLICATE_SCOPE= 'none | cluster'

parallel_clause:
    {NOPARALLEL | PARALLEL integer}

table_mode_value:
    NORMAL
    | QUEUING
    | MODERATE
    | SUPER
    | EXTREME

partition_options:
    partition_option [partition_option] ...

partition_option:
      PARTITION BY HASH(expression)
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY KEY([column_name [, column_name ...]])
      [subpartition_option] PARTITIONS partition_count
    | PARTITION BY RANGE {(expression) | COLUMNS (column_name [, column_name ...])}
      [subpartition_option] (range_partition [, range_partition ...])

subpartition_option:
      SUBPARTITION BY HASH(expression)
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY KEY(column_name [, column_name ...])
      SUBPARTITIONS subpartition_count
    | SUBPARTITION BY RANGE {(expression) | COLUMNS (column_name [, column_name ...])}
      (range_subpartition [, range_subpartition ...])

range_partition:
    PARTITION partition_name
    VALUES LESS THAN {(expression [, expression ...]) | MAXVALUE}

range_subpartition:
    SUBPARTITION subpartition_name
    VALUES LESS THAN {(expression [, expression ...]) | MAXVALUE}

partition_count | subpartition_count:
    INT_VALUE

add_subpartition_option:
    {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
        [, SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr] ...}
    | {SUBPARTITION subpartition_name VALUES IN list_partition_expr
        [, SUBPARTITION subpartition_name VALUES IN list_partition_expr] ...}

参数说明

table_mode_value

• NORMAL：默认值，表示正常。在该模式下，数据转储后触发合并的概率极低。
• QUEUING：在该模式下，数据转储后触发合并的概率低。
• MODERATE：表示适度。在该模式下，数据转储后触发合并的概率为中等。
• SUPER：表示超级。在该模式下，数据转储后触发合并的概率高。
• EXTREME：表示极端。在该模式下，转储后触发合并的概率较高。

更多有关合并的信息，请参见 自适应合并。

示例

增加和删除列的操作

创建示例表 tbl1，并执行 DESCRIBE 命令查看表信息。

obclient> CREATE TABLE tbl1 (c1 INT(11) PRIMARY KEY,c2 VARCHAR(50));
Query OK, 0 rows affected

obclient> DESCRIBE tbl1;
+-------+------------+----------+--------+---------+-------+
| Field | Type       | Null     | Key    | Default | Extra |
+-------+------------+----------+--------+---------+-------+
| c1    | int(11)    | NO       | PRI    | NULL    |       |    
| c2    | varchar(50)| YES      |        | NULL    |       |   
+-------+------------+----------+--------+---------+-------+

• 增加 c3 列，并执行 DESCRIBE 命令查看表信息。

  obclient> ALTER TABLE tbl1 ADD c3 INT;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |    
  | c2    | varchar(50)| YES      |        | NULL    |       |  
  | c3    | int(11)    | YES      |        | NULL    |       |  
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

• 删除 c3 列，并执行 DESCRIBE 命令查看表信息。

  obclient> ALTER TABLE tbl1 DROP c3;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |    
  | c2    | varchar(50)| YES      |        | NULL    |       |  
  +-------+------------+----------+--------+---------+-------+
  2 row affected
  

• 将表 tbl1 的字段 c2 改名为 c3，并执行 DESCRIBE 命令查看表信息。

  obclient> ALTER TABLE tbl1 CHANGE COLUMN c2 c3 VARCHAR(50);
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c1    | int(11)    | NO       | PRI    | NULL    |       |    
  | c3    | varchar(50)| YES      |        | NULL    |       |  
  +-------+------------+----------+--------+---------+-------+
  2 rows affected
  

• 为表 tbl1 添加 c4 列，并将该列设置为表的第一列，并执行 DESCRIBE 命令查看表信息。

  obclient> ALTER TABLE tbl1 ADD COLUMN c4 INTEGER FIRST;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |  
  | c1    | int(11)    | NO       | PRI    | NULL    |       |    
  | c3    | varchar(50)| YES      |        | NULL    |       |  
  +-------+------------+----------+--------+---------+-------+
  3 row affected
  

• 在 c1 列后添加 c2 列，并执行 DESCRIBE 命令查看表信息。

  obclient> ALTER TABLE tbl1 ADD COLUMN c2 INTEGER AFTER c1;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c4    | int(11)    | YES      |        | NULL    |       |  
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |    
  | c3    | varchar(50)| YES      |        | NULL    |       |  
  +-------+------------+----------+--------+---------+-------+
  4 row affected
  

• 在 c4 列前添加 c5 列，并执行 DESCRIBE 命令查看表信息。

  obclient> ALTER TABLE tbl1 ADD COLUMN c5 INT BEFORE c4;
  Query OK, 1 row affected
  
  obclient> DESCRIBE tbl1;
  +-------+------------+----------+--------+---------+-------+
  | Field | Type       | Null     | Key    | Default | Extra |
  +-------+------------+----------+--------+---------+-------+
  | c5    | int(11)    | YES      |        | NULL    |       |  
  | c4    | int(11)    | YES      |        | NULL    |       |  
  | c1    | int(11)    | NO       | PRI    | NULL    |       |
  | c2    | int(11)    | YES      |        | NULL    |       |    
  | c3    | varchar(50)| YES      |        | NULL    |       |  
  +-------+------------+----------+--------+---------+-------+
  5 row affected
  

• 为表 tbl2 添加外键约束 fk1。当 UPDATE 操作影响与子表相匹配行的父表中键值时，执行 SET NULL 操作。

  obclient> CREATE TABLE tbl2(c1 INT PRIMARY KEY,c2 INT);
  Query OK, 0 row affected
  obclient> CREATE TABLE tbl3(c1 INT PRIMARY KEY,c2 INT);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 ADD CONSTRAINT fk1 FOREIGN KEY (c2) REFERENCES tbl3(c1) ON UPDATE SET NULL;
  Query OK, 0 row affected
  

• 删除 tbl3 表的外键约束 fk1。

  obclient> ALTER TABLE tbl2 DROP FOREIGN KEY fk1;
  Query OK, 0 row affected
  

• 删除 tbl2 表的主键。

  obclient> ALTER TABLE tbl2 DROP PRIMARY KEY;
  Query OK, 0 row affected
  

索引操作

• 将 tbl2 表的索引 ind1 重命名为 ind2。

  obclient> CREATE TABLE tbl2(c1 INT(11) PRIMARY KEY,c2 INT(50));
  Query OK, 0 row affected
  
  obclient> CREATE INDEX ind1 ON tbl2 (c2 ASC);
  Query OK, 0 row affected
  obclient> ALTER TABLE tbl2 RENAME INDEX ind1 TO ind2;
  Query OK, 0 row affected
  

• 在 tbl2 表上创建索引 ind3，引用 c1、c2 列。

  obclient> ALTER TABLE tbl2 ADD INDEX ind3 (c1,c2);
  Query OK, 0 row affected
  

  可以通过 SHOW INDEX 语句查看创建的索引。

  obclient> SHOW INDEX FROM tbl2;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | tbl2  |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind2     |            1 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | tbl2  |          1 | ind3     |            2 | c2          | A         |        NULL | NULL     | NULL   |  YES | BTREE      | available |               | YES     |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  4 rows in set
  

• 删除 tbl2 表上的索引 ind2。

  obclient> ALTER TABLE tbl2 DROP INDEX ind2;
  Query OK, 0 row affected
  

  说明

  在实际运维场景中，您可以通过以上方式实现索引的原子性变更。

• 在 t1_func 上添加 3 个函数索引，其中一个名字是 i2，另外两个由系统自动生成的名称，格式为 functional_index 前缀加编号。

  obclient> ALTER TABLE t1_func ADD INDEX ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX ((c1+1));
  Query OK, 0 rows affected
  obclient> ALTER TABLE t1_func ADD INDEX i2 ((CONCAT(c1,'a')));
  Query OK, 0 rows affected
  obclient> SHOW CREATE TABLE t1_func;
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | t1_func | CREATE TABLE `t1_func` (
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  KEY `i1` (((`c1` + `c2`) < 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL,
  KEY `functional_index_2` ((`c1` + 1)) BLOCK_SIZE 16384 LOCAL,
  KEY `i2` (concat(`c1`,'a')) BLOCK_SIZE 16384 LOCAL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

• 修改索引不可见。

  说明

  当索引设置为不可见后，可以在表结构中进行看到 /*!80000 INVISIBLE */ 标记。

  1. 创建表 tbl4。

     CREATE TABLE tbl4 (col1 INT PRIMARY KEY, col2 INT, col3 INT);
     

  2. 创建索引 idx1_tbl4。

     CREATE INDEX idx1_tbl4 ON tbl4(col2);
     

  3. 修改索引 idx1_tbl4 不可见。

     ALTER TABLE tbl4 ALTER INDEX idx1_tbl4 INVISIBLE;
     

  4. 再次查看表 tbl4 结构。

     SHOW CREATE TABLE tbl4;
     

     返回结果如下：

     +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                               |
     +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     | tbl4  | CREATE TABLE `tbl4` (
       `col1` int(11) NOT NULL,
       `col2` int(11) DEFAULT NULL,
       `col3` int(11) DEFAULT NULL,
       PRIMARY KEY (`col1`),
       KEY `idx1_tbl4` (`col2`) BLOCK_SIZE 16384 LOCAL /*!80000 INVISIBLE */
     ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
     +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
     1 row in set
     

分区操作

• 清除分区表 t_log_part_by_range 的分区 M202001 和 M202002 中的全部数据。

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL  
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range TRUNCATE PARTITION M202001, M202002;
  Query OK, 0 rows affected
  

• 为分区表 t_log_part_by_range 添加分区 M202006。

  obclient> CREATE TABLE t_log_part_by_range (
      log_id      bigint NOT NULL
      , log_value varchar(50)
      , log_date  timestamp NOT NULL  
  ) PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
  (
      PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
      , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
      , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
      , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
      , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t_log_part_by_range ADD PARTITION
           (PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
           );
  

修改并行度

• 修改表 tbl3 的并行度为 2。

  obclient> ALTER TABLE tbl3 PARALLEL 2;
  

列类型相关操作

• 修改表 tbl4 的普通列 col1 为自增列。

  obclient> CREATE TABLE tbl4 (col1 BIGINT(10) NOT NULL,col2 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 MODIFY col1 BIGINT(10) AUTO_INCREMENT;
  Query OK, 0 rows affected
  

• 为表 tbl4 增加主键 col1，之后更改主键为 col2。

  obclient> ALTER TABLE tbl4 ADD PRIMARY KEY (col1);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl4 DROP PRIMARY KEY,ADD PRIMARY KEY (`col2`);
  Query OK, 0 rows affected
  

• 添加 CHECK 约束。

  obclient> CREATE TABLE tbl7(col1 VARCHAR(10),col2 VARCHAR(10));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl7 ADD CONSTRAINT my_check CHECK (col1> col2) ;
  Query OK, 0 rows affected
  

• 删除表 tbl7 的 CHECK 约束。

  obclient> ALTER TABLE tbl7 DROP CHECK my_check;
  Query OK, 0 rows affected
  

• 修改列类型为 NOT NULL。

  obclient> CREATE TABLE dept(  
      deptno           NUMBER(2,0),  
      dname            VARCHAR(14),  
      location         VARCHAR(13),   
      CONSTRAINT pk_dept PRIMARY KEY(deptno)  
  );
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE emp(  
      empno         NUMBER(4,0) NOT NULL,  
      empname       VARCHAR(10) NOT NULL,  
      job           VARCHAR(9) NOT NULL,  
      mgr           NUMBER(4,0) NOT NULL,  
      hiredate      DATE NOT NULL,  
      sal           NUMBER(7,2) DEFAULT NULL,
      comm          NUMBER(7,2) DEFAULT NULL,        
      deptno        NUMBER(2,0) DEFAULT NULL,   
      CONSTRAINT PK_emp PRIMARY KEY (empno),
      CONSTRAINT FK_deptno  FOREIGN KEY (deptno)  REFERENCES dept (deptno)  
  );
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE emp MODIFY deptno NUMBER(2,0) DEFAULT '12' NOT NULL;
  Query OK, 0 rows affected
  

重命名列操作

• RENAME COLUMN 不改变列定义，仅修改列名。如果目标名称在表中已经存在，那么 RENAME COLUMN 执行会报错，但是重命名为原名称则不会报错。

  obclient> CREATE TABLE tbl8 (a INT, b INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO b;
  ERROR 1060 (42S21): Duplicate column name 'b'
  
  obclient> ALTER TABLE tbl8 RENAME COLUMN a TO a;
  Query OK, 0 rows affected
  

• 如果重命名的列上建有索引，RENAME COLUMN 可以正常执行，索引定义会自动级联修改。

  obclient> CREATE TABLE tbl9 (a INT, b INT, index idx_a(a));
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | a           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          1 | idx_a    |            1 | c           | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  1 row in set
  

• 如果重命名列被前缀索引引用，RENAME COLUMN 可以正常执行，前缀索引支持级联修改。

  DROP TABLE tbl9;
  obclient> CREATE TABLE tbl9 (c1 INT PRIMARY KEY, c2 BLOB, c3 INT, INDEX i1 (c2(10)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl9 RENAME COLUMN c2 TO c2_, RENAME COLUMN c1 TO c2_1;
  DESC tbl9;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM tbl9;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible | Expression |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  | tbl9  |          0 | PRIMARY  |            1 | c2_1        | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     | NULL       |
  | tbl9  |          1 | i1       |            1 | c2_         | A         |        NULL | 10       | NULL   | YES  | BTREE      | available |               | YES     | NULL       |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
  2 rows in set
  

• 如果重命名的列上建有外键约束，RENAME COLUMN 可以正常执行，外键约束会自动级联修改。

  obclient> CREATE TABLE tbl10 (a INT PRIMARY KEY);
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl11(b INT, FOREIGN KEY (b) REFERENCES tbl10(a));
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`a`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE =  16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  
  obclient> ALTER TABLE tbl10 RENAME COLUMN a TO c;
  Query OK, 0 rows affected
  
  obclient> SHOW CREATE TABLE tbl11;
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table                                                                                                                                                                                                                                                                                                                                                      |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | tbl11 | CREATE TABLE `tbl11` (
  `b` int(11) DEFAULT NULL,
  CONSTRAINT `tbl11_OBFK_1694681944513338` FOREIGN KEY (`b`) REFERENCES `test`.`tbl10`(`c`) ON UPDATE RESTRICT ON DELETE RESTRICT
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
  +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set
  

OceanBase 数据库在以下场景，不支持修改或者不会自动级联修改：

• 重命名的列被生成列表达式引用，不支持修改列名，执行会报错。

  obclient> CREATE TABLE tbl12(a INT, b INT AS (a + 1), c INT, d INT, CONSTRAINT d_check CHECK(d > 0)) PARTITION BY HASH(c + 1) PARTITIONS 2;
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN a TO e;
  ERROR 3108 (HY000): Column 'a' has a generated column dependency
  

• 重命名的列被分区表达式引用，不支持修改列名，执行会报错。

  obclient> ALTER TABLE tbl12 RENAME COLUMN c TO e;
  ERROR 3855 (HY000): Column 'c' has a partitioning function dependency and cannot be dropped or renamed.
  

• 重命名的列被 CHECK 约束引用，不支持修改列名，执行会报错。

  obclient> ALTER TABLE tbl12 RENAME COLUMN d TO e;
  ERROR 3959 (HY000): Check constraint 'd_check' uses column 'd', hence column cannot be dropped or renamed.
  

• 重命名的列被函数索引引用，不支持修改列名，执行会报错。

  DROP TABLE IF EXISTS tbl12;
  obclient> CREATE TABLE tbl12(i INT, INDEX ((i+1)));
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE tbl12 RENAME COLUMN i TO j;
  ERROR 3837 (HY000): Column 'i' has a functional index dependency and cannot be dropped or renamed.
  

• 重命名的列被视图引用，RENAME COLUMN 执行成功，查询视图会报错，需要用户手动修改视图定义。

  obclient> CREATE TABLE tbl13(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE VIEW v1 AS SELECT a + 1 FROM tbl13;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  Empty set
  
  obclient> ALTER TABLE tbl13 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM v1;
  ERROR 1356 (42S22): View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
  

• 重命名的列被存储过程引用，RENAME COLUMN 执行成功，CALL Procedure 报错，需要用户手动修改。

  obclient> CREATE TABLE tbl14(a INT);
  Query OK, 0 rows affected
  
  obclient> CREATE PROCEDURE proc() SELECT a + 1 FROM tbl14;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  Empty set
  
  obclient> ALTER TABLE tbl14 RENAME COLUMN a TO b;
  Query OK, 0 rows affected
  
  obclient> CALL proc();
  ERROR 1054 (42S22): Unknown column 'a' in 'field list'
  

相关文档

更改表