划线反馈

OceanBase 数据库 MySQL 模式下复杂 ALTER TABLE 语句相关问题处理

更新时间：2026-05-21 09:16

适用版本：V4.2.x、V4.3.x内容类型：TechNote

本文介绍 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 及之前版本)。

DDL 诊断增强之实时进度展示

Oracle 模式通过一条 SQL 既加 Lob 列也加 not null 约束后，查询写入报错 4002，获取不到 Lob

OceanBase 数据库