本文介绍 OceanBase 数据库 MySQL 模式下复杂 ALTER TABLE 语句相关问题处理，如通过 ALTER TABLE 语句同时修改列的多个属性或同时修改多个列等。
关于修改列类型，详情可以参见：OceanBase 数据库 MySQL 模式列类型变更规则。
详细说明
测试环境为 OceanBase 数据库 V4.2.1 BP9 版本，同时也在 OceanBase 数据库 V4.2.5/V4.3.4 版本中进行了测试，有同样现象。
Case 1 同时修改一列的列名和约束
简化测试 case 如下，在将列名改为大写的同时，增加 NOT NULL 约束。
CREATE TABLE test_alter1 (`c1` varchar(10));
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
相关报错如下。
obclient [test]> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): change column name and alter constraint in single statement not supported
解决方案一：
将 ALTER TABLE 语句拆为两个，每次仅修改一处属性或约束。
ALTER TABLE test_alter1 MODIFY COLUMN `c1` varchar(10) NOT NULL;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10);
解决方案二：
手动执行一个 ALTER TABLE 语句，仅修改一处属性或约束，然后再执行原 ALTER TABLE 语句。
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) ;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
或
ALTER TABLE test_alter1 MODIFY COLUMN `c1` varchar(10) NOT NULL;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
详细测试 case 如下。
CREATE TABLE test_alter1 (`c1` varchar(10));
DESC test_alter1;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
-- 测试解决方案一
DESC test_alter1;
ALTER TABLE test_alter1 MODIFY COLUMN `c1` varchar(10) NOT NULL;
DESC test_alter1;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10);
DESC test_alter1;
-- 测试解决方案二中的第一种方式
CREATE TABLE test_alter11 (`c1` varchar(10));
DESC test_alter11;
ALTER TABLE test_alter11 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter11;
ALTER TABLE test_alter11 MODIFY COLUMN `C1` varchar(10) ;
DESC test_alter11;
ALTER TABLE test_alter11 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter11;
-- 测试解决方案二中的第二种方式
CREATE TABLE test_alter12 (`c1` varchar(10));
DESC test_alter12;
ALTER TABLE test_alter12 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter12;
ALTER TABLE test_alter12 MODIFY COLUMN `c1` varchar(10) NOT NULL;
DESC test_alter12;
ALTER TABLE test_alter12 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter12;
测试结果如下。
Server version: OceanBase 4.2.1.9 (r109010022024101120-45c3c04b7bd9a2070784456c39484ce45e9f9d2c) (Built Oct 11 2024 20:36:32)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> CREATE TABLE test_alter1 (`c1` varchar(10));
Query OK, 0 rows affected (0.138 sec)
obclient [test]> DESC test_alter1;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): change column name and alter constraint in single statement not supported
obclient [test]>
obclient [test]> -- 测试解决方案一
Query OK, 0 rows affected (0.001 sec)
obclient [test]> DESC test_alter1;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter1 MODIFY COLUMN `c1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.564 sec)
obclient [test]> DESC test_alter1;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10);
Query OK, 0 rows affected (0.132 sec)
obclient [test]> DESC test_alter1;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> -- 测试解决方案二中的第一种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter11 (`c1` varchar(10));
Query OK, 0 rows affected (0.138 sec)
obclient [test]> DESC test_alter11;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter11 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): change column name and alter constraint in single statement not supported
obclient [test]> DESC test_alter11;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter11 MODIFY COLUMN `C1` varchar(10) ;
Query OK, 0 rows affected (0.134 sec)
obclient [test]> DESC test_alter11;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.008 sec)
obclient [test]> ALTER TABLE test_alter11 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.568 sec)
obclient [test]> DESC test_alter11;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.013 sec)
obclient [test]> -- 测试解决方案二中的第二种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter12 (`c1` varchar(10));
Query OK, 0 rows affected (0.138 sec)
obclient [test]> DESC test_alter12;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter12 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): change column name and alter constraint in single statement not supported
obclient [test]> DESC test_alter12;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter12 MODIFY COLUMN `c1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.570 sec)
obclient [test]> DESC test_alter12;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter12 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.131 sec)
obclient [test]> DESC test_alter12;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.011 sec)
Case 2 同时修改一列的列名及列类型和约束
简化测试 case 如下，在将列名改为大写和修改列类型的同时，增加 NOT NULL 约束。
CREATE TABLE test_alter2 (`c1` char(10));
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;
相关报错如下。
obclient [test]> ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): alter column and alter constraint in single statement not supported
解决方案一：
将 ALTER TABLE 语句拆为两个，单独修改约束。
ALTER TABLE test_alter2 MODIFY COLUMN `c1` char(10) NOT NULL;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10);
解决方案二：
手动执行一个 ALTER TABLE 语句，仅修改属性或约束，然后再执行原 ALTER TABLE 语句。
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) ;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;
或
ALTER TABLE test_alter2 MODIFY COLUMN `c1` char(10) NOT NULL;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;
详细测试 case 如下。
CREATE TABLE test_alter2 (`c1` char(10));
DESC test_alter2;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;
-- 测试解决方案一
DESC test_alter2;
ALTER TABLE test_alter2 MODIFY COLUMN `c1` char(10) NOT NULL;
DESC test_alter2;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10);
DESC test_alter2;
-- 测试解决方案二中的第一种方式
CREATE TABLE test_alter21 (`c1` char(10));
DESC test_alter21;
ALTER TABLE test_alter21 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter21;
ALTER TABLE test_alter21 MODIFY COLUMN `C1` varchar(10) ;
DESC test_alter21;
ALTER TABLE test_alter21 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter21;
-- 测试解决方案二中的第二种方式
CREATE TABLE test_alter22 (`c1` char(10));
DESC test_alter22;
ALTER TABLE test_alter22 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter22;
ALTER TABLE test_alter22 MODIFY COLUMN `c1` char(10) NOT NULL;
DESC test_alter22;
ALTER TABLE test_alter22 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter22;
测试结果如下。
Server version: OceanBase 4.2.1.9 (r109010022024101120-45c3c04b7bd9a2070784456c39484ce45e9f9d2c) (Built Oct 11 2024 20:36:32)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> CREATE TABLE test_alter2 (`c1` char(10));
Query OK, 0 rows affected (0.140 sec)
obclient [test]> DESC test_alter2;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.010 sec)
obclient [test]> ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): alter column and alter constraint in single statement not supported
obclient [test]> -- 测试解决方案一
Query OK, 0 rows affected (0.000 sec)
obclient [test]> DESC test_alter2;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter2 MODIFY COLUMN `c1` char(10) NOT NULL;
Query OK, 0 rows affected (0.572 sec)
obclient [test]> DESC test_alter2;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | NO | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.010 sec)
obclient [test]> ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10);
Query OK, 0 rows affected (1.097 sec)
obclient [test]> DESC test_alter2;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.012 sec)
obclient [test]> -- 测试解决方案二中的第一种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter21 (`c1` char(10));
Query OK, 0 rows affected (0.136 sec)
obclient [test]> DESC test_alter21;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter21 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): alter column and alter constraint in single statement not supported
obclient [test]> DESC test_alter21;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter21 MODIFY COLUMN `C1` varchar(10) ;
Query OK, 0 rows affected (1.196 sec)
obclient [test]> DESC test_alter21;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter21 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.571 sec)
obclient [test]> DESC test_alter21;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.010 sec)
obclient [test]> -- 测试解决方案二中的第二种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter22 (`c1` char(10));
Query OK, 0 rows affected (0.133 sec)
obclient [test]> DESC test_alter22;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter22 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ERROR 1235 (0A000): alter column and alter constraint in single statement not supported
obclient [test]> DESC test_alter22;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter22 MODIFY COLUMN `c1` char(10) NOT NULL;
Query OK, 0 rows affected (0.571 sec)
obclient [test]> DESC test_alter22;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | NO | | NULL | |
+-------+----------+------+------+---------+-------+
1 row in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter22 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (1.305 sec)
obclient [test]> DESC test_alter22;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
1 row in set (0.009 sec)
Case 3 同时修改一列的列名及约束和另一列的列类型
简化测试 case 如下，在将一列的列名改为大写并增加 NOT NULL 约束的同时，修改另一列的列类型。
CREATE TABLE test_alter3 (`c1` varchar(10),`C2` char(10));
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
相关报错如下。
obclient [test]> ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
ERROR 1235 (0A000): table redefinition and alter constraint in single statement not supported
解决方案一：
将 ALTER TABLE 语句拆为多个，每次仅修改一处属性或约束。
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10);
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ALTER TABLE test_alter3 MODIFY COLUMN `C2` varchar(10);
解决方案二：
手动执行多个 ALTER TABLE 语句，修改属性或约束，然后再执行原 ALTER TABLE 语句。
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10);
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL;
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
或
ALTER TABLE test_alter3 MODIFY COLUMN `C2` varchar(10);
ALTER TABLE test_alter3 MODIFY COLUMN `c1` varchar(10) NOT NULL;
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
详细测试 case 如下。
CREATE TABLE test_alter3 (`c1` varchar(10),`C2` char(10));
DESC test_alter3;
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
-- 测试解决方案一
DESC test_alter3;
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10);
DESC test_alter3;
ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter3;
ALTER TABLE test_alter3 MODIFY COLUMN `C2` varchar(10);
DESC test_alter3;
-- 测试解决方案二中的第一种方式
CREATE TABLE test_alter31 (`c1` varchar(10),`C2` char(10));
DESC test_alter31;
ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
DESC test_alter31;
ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10);
DESC test_alter31;
ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10) NOT NULL;
DESC test_alter31;
ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
DESC test_alter31;
-- 测试解决方案二中的第二种方式
CREATE TABLE test_alter32 (`c1` varchar(10),`C2` char(10));
DESC test_alter32;
ALTER TABLE test_alter32 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
DESC test_alter32;
ALTER TABLE test_alter32 MODIFY COLUMN `C2` varchar(10);
DESC test_alter32;
ALTER TABLE test_alter32 MODIFY COLUMN `c1` varchar(10) NOT NULL;
DESC test_alter32;
ALTER TABLE test_alter32 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
DESC test_alter32;
测试结果如下。
Server version: OceanBase 4.2.1.9 (r109010022024101120-45c3c04b7bd9a2070784456c39484ce45e9f9d2c) (Built Oct 11 2024 20:36:32)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> CREATE TABLE test_alter3 (`c1` varchar(10),`C2` char(10));
Query OK, 0 rows affected (0.141 sec)
obclient [test]> DESC test_alter3;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
ERROR 1235 (0A000): table redefinition and alter constraint in single statement not supported
obclient [test]> -- 测试解决方案一
Query OK, 0 rows affected (0.000 sec)
obclient [test]> DESC test_alter3;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.004 sec)
obclient [test]> ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10);
Query OK, 0 rows affected (0.136 sec)
obclient [test]> DESC test_alter3;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter3 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.570 sec)
obclient [test]> DESC test_alter3;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter3 MODIFY COLUMN `C2` varchar(10);
Query OK, 0 rows affected (1.307 sec)
obclient [test]> DESC test_alter3;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
| C2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> -- 测试解决方案二中的第一种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter31 (`c1` varchar(10),`C2` char(10));
Query OK, 0 rows affected (0.134 sec)
obclient [test]> DESC test_alter31;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.010 sec)
obclient [test]> ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
ERROR 1235 (0A000): table redefinition and alter constraint in single statement not supported
obclient [test]> DESC test_alter31;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10);
Query OK, 0 rows affected (0.132 sec)
obclient [test]> DESC test_alter31;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.567 sec)
obclient [test]> DESC test_alter31;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter31 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
Query OK, 0 rows affected (1.211 sec)
obclient [test]> DESC test_alter31;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
| C2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> -- 测试解决方案二中的第二种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter32 (`c1` varchar(10),`C2` char(10));
Query OK, 0 rows affected (0.139 sec)
obclient [test]> DESC test_alter32;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter32 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
ERROR 1235 (0A000): table redefinition and alter constraint in single statement not supported
obclient [test]> DESC test_alter32;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.004 sec)
obclient [test]> ALTER TABLE test_alter32 MODIFY COLUMN `C2` varchar(10);
Query OK, 0 rows affected (1.091 sec)
obclient [test]> DESC test_alter32;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | YES | | NULL | |
| C2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter32 MODIFY COLUMN `c1` varchar(10) NOT NULL;
Query OK, 0 rows affected (0.571 sec)
obclient [test]> DESC test_alter32;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| C2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter32 MODIFY COLUMN `C1` varchar(10) NOT NULL, MODIFY COLUMN `C2` varchar(10);
Query OK, 0 rows affected (0.135 sec)
obclient [test]> DESC test_alter32;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| C1 | varchar(10) | NO | | NULL | |
| C2 | varchar(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
Case 4 同时修改两列的列类型和约束
简化测试 case 如下，同时修改两列的列类型和约束。
CREATE TABLE test_alter4 (c1 char(10), c2 char(10));
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
相关报错如下。
obclient [test]> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
ERROR 1235 (0A000): Add/modify not null constraint together with other DDLs not supported
解决方案一：
将 ALTER TABLE 语句拆为多个，每次仅修改一处属性或约束。
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL;
ALTER TABLE test_alter4 MODIFY COLUMN c2 varchar(10) NOT NULL;
解决方案二：
手动执行一个 ALTER TABLE 语句，然后再执行原 ALTER TABLE 语句。
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL;
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
或
ALTER TABLE test_alter4 MODIFY COLUMN c2 varchar(10) NOT NULL;
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
详细测试 case 如下。
CREATE TABLE test_alter4 (c1 char(10), c2 char(10));
DESC test_alter4;
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
-- 测试解决方案一
DESC test_alter4;
ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL;
DESC test_alter4;
ALTER TABLE test_alter4 MODIFY COLUMN c2 varchar(10) NOT NULL;
DESC test_alter4;
-- 测试解决方案二中的第一种方式
CREATE TABLE test_alter41 (c1 char(10), c2 char(10));
DESC test_alter41;
ALTER TABLE test_alter41 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
DESC test_alter41;
ALTER TABLE test_alter41 MODIFY COLUMN c1 varchar(10) NOT NULL;
DESC test_alter41;
ALTER TABLE test_alter41 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
DESC test_alter41;
-- 测试解决方案二中的第二种方式
CREATE TABLE test_alter42 (c1 char(10), c2 char(10));
DESC test_alter42;
ALTER TABLE test_alter42 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
DESC test_alter42;
ALTER TABLE test_alter42 MODIFY COLUMN c2 varchar(10) NOT NULL;
DESC test_alter42;
ALTER TABLE test_alter42 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
DESC test_alter42;
测试结果如下。
Server version: OceanBase 4.2.1.9 (r109010022024101120-45c3c04b7bd9a2070784456c39484ce45e9f9d2c) (Built Oct 11 2024 20:36:32)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> CREATE TABLE test_alter4 (c1 char(10), c2 char(10));
Query OK, 0 rows affected (0.134 sec)
obclient [test]> DESC test_alter4;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
2 rows in set (0.010 sec)
obclient [test]> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
ERROR 1235 (0A000): Add/modify not null constraint together with other DDLs not supported
obclient [test]> -- 测试解决方案一
Query OK, 0 rows affected (0.000 sec)
obclient [test]> DESC test_alter4;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
2 rows in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL;
Query OK, 0 rows affected (1.306 sec)
obclient [test]> DESC test_alter4;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| c2 | char(10) | YES | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter4 MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (1.532 sec)
obclient [test]> DESC test_alter4;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| c2 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> -- 测试解决方案二中的第一种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter41 (c1 char(10), c2 char(10));
Query OK, 0 rows affected (0.135 sec)
obclient [test]> DESC test_alter41;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
2 rows in set (0.010 sec)
obclient [test]> ALTER TABLE test_alter41 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (0.897 sec)
obclient [test]> DESC test_alter41;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| c2 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.002 sec)
obclient [test]> ALTER TABLE test_alter4 MODIFY COLUMN c1 varchar(10) NOT NULL;
Query OK, 0 rows affected (0.146 sec)
obclient [test]> DESC test_alter41;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| c2 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.002 sec)
obclient [test]> ALTER TABLE test_alter41 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (0.140 sec)
obclient [test]> DESC test_alter41;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| c2 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.002 sec)
obclient [test]> -- 测试解决方案二中的第二种方式
Query OK, 0 rows affected (0.000 sec)
obclient [test]> CREATE TABLE test_alter42 (c1 char(10), c2 char(10));
Query OK, 0 rows affected (0.139 sec)
obclient [test]> DESC test_alter42;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
2 rows in set (0.009 sec)
obclient [test]> ALTER TABLE test_alter42 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
ERROR 1235 (0A000): Add/modify not null constraint together with other DDLs not supported
obclient [test]> DESC test_alter42;
+-------+----------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | char(10) | YES | | NULL | |
+-------+----------+------+------+---------+-------+
2 rows in set (0.003 sec)
obclient [test]> ALTER TABLE test_alter42 MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (1.403 sec)
obclient [test]> DESC test_alter42;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | char(10) | YES | | NULL | |
| c2 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.008 sec)
obclient [test]> ALTER TABLE test_alter42 MODIFY COLUMN c1 varchar(10) NOT NULL, MODIFY COLUMN c2 varchar(10) NOT NULL;
Query OK, 0 rows affected (1.437 sec)
obclient [test]> DESC test_alter42;
+-------+-------------+------+------+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+------+---------+-------+
| c1 | varchar(10) | NO | | NULL | |
| c2 | varchar(10) | NO | | NULL | |
+-------+-------------+------+------+---------+-------+
2 rows in set (0.009 sec)
Case 5 修改有 prefix 索引的列
简化测试 case 如下，表列上有 prefix 索引。
CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));
ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
相关报错如下。
obclient [test]> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
ERROR 1235 (0A000): Alter column that the prefix index column depends on not supported
解决方案一：
如索引列宽度较小，可以考虑先删除旧的 prefix 索引，然后创建新的索引。
ALTER TABLE test_alter5 DROP INDEX idx_c1;
ALTER TABLE test_alter5 ADD KEY idx_c1 (c1);
解决方案二：
备份表数据后，重新创建表，然后再导入表数据。
测试结果如下。
Server version: OceanBase 4.2.1.9 (r109010022024101120-45c3c04b7bd9a2070784456c39484ce45e9f9d2c) (Built Oct 11 2024 20:36:32)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [test]> CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));
Query OK, 0 rows affected (0.184 sec)
obclient [test]> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
ERROR 1235 (0A000): Alter column that the prefix index column depends on not supported
obclient [test]> ALTER TABLE test_alter5 DROP INDEX idx_c1;
Query OK, 0 rows affected (0.784 sec)
obclient [test]> ALTER TABLE test_alter5 ADD KEY idx_c1 (c1);
Query OK, 0 rows affected (0.834 sec)
obclient [test]> ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);
Query OK, 0 rows affected (1.925 sec)
影响租户
影响 OceanBase 数据库中的 MySQL 租户，对于 SYS 租户和 Oracle 租户无影响。
适用版本
OceanBase 数据库 V4.x (OceanBase 数据库 V4.2.1 BP9/V4.2.5/V4.3.4 及之前版本)。
