---
title: OceanBase 数据库 MySQL 模式下复杂 ALTER TABLE 语句相关问题处理-OceanBase数据库使用指南
description: 了解OceanBase数据库在实际应用中关于OceanBase 数据库 MySQL 模式下复杂 ALTER TABLE 语句相关问题处理相关的常见问题和使用技巧，帮助您快速解决OceanBase 数据库 MySQL 模式下复杂 ALTER TABLE 语句相关问题处理的难题。
---
切换语言

- 简体中文
- English

划线反馈

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

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

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

本文介绍 OceanBase 数据库 MySQL 模式下复杂 `ALTER TABLE` 语句相关问题处理，如通过 `ALTER TABLE` 语句同时修改列的多个属性或同时修改多个列等。

关于修改列类型，详情可以参见：[OceanBase 数据库 MySQL 模式列类型变更规则](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000000221444)。

## 详细说明

测试环境为 OceanBase 数据库 V4.2.1 BP9 版本，同时也在 OceanBase 数据库 V4.2.5/V4.3.4 版本中进行了测试，有同样现象。

### Case 1 同时修改一列的列名和约束

简化测试 case 如下，在将列名改为大写的同时，增加 NOT NULL 约束。

```shell
CREATE TABLE test_alter1 (`c1` varchar(10));
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;

```

相关报错如下。

```shell
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` 语句拆为两个，每次仅修改一处属性或约束。

```shell
ALTER TABLE test_alter1 MODIFY COLUMN `c1` varchar(10) NOT NULL;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10);

```

**解决方案二：**

手动执行一个 `ALTER TABLE` 语句，仅修改一处属性或约束，然后再执行原 `ALTER TABLE` 语句。

```shell
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) ;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;

```

或

```shell
ALTER TABLE test_alter1 MODIFY COLUMN `c1` varchar(10) NOT NULL;
ALTER TABLE test_alter1 MODIFY COLUMN `C1` varchar(10) NOT NULL;

```

详细测试 case 如下。

```shell
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;

```

测试结果如下。

```shell
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]> -- 测试解决方案二中的第一种方式
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` 约束。

```shell
CREATE TABLE test_alter2 (`c1` char(10));
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;

```

相关报错如下。

```shell
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` 语句拆为两个，单独修改约束。

```shell
ALTER TABLE test_alter2 MODIFY COLUMN `c1` char(10) NOT NULL;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10);

```

手动执行一个 `ALTER TABLE` 语句，仅修改属性或约束，然后再执行原 `ALTER TABLE` 语句。

```shell
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) ;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;

```

或

```shell
ALTER TABLE test_alter2 MODIFY COLUMN `c1` char(10) NOT NULL;
ALTER TABLE test_alter2 MODIFY COLUMN `C1` varchar(10) NOT NULL;

```

```shell
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;

```

测试结果如下。

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]> 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]> 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` 约束的同时，修改另一列的列类型。

```shell
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);

```

相关报错如下。

```shell
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` 语句拆为多个，每次仅修改一处属性或约束。

```shell
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` 语句。

```shell
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);

```

或

```shell
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);

```

```shell
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;

```

测试结果如下。

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]> 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]> 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]> 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)

```

### Case 4 同时修改两列的列类型和约束

简化测试 case 如下，同时修改两列的列类型和约束。

```shell
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;

```

相关报错如下。

```shell
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

```

```shell
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` 语句。

```shell
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;

```

或

```shell
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;

```

```shell
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;

```

测试结果如下。

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]> 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]> 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 索引。

```shell
CREATE TABLE test_alter5 (c1 char(20), KEY idx_c1(c1(10)));
ALTER TABLE test_alter5 MODIFY COLUMN c1 varchar(20);

```

相关报错如下。

```shell
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 索引，然后创建新的索引。

```shell
ALTER TABLE test_alter5 DROP INDEX idx_c1;
ALTER TABLE test_alter5 ADD KEY idx_c1 (c1);

```

备份表数据后，重新创建表，然后再导入表数据。

测试结果如下。

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

Previous

[DDL 诊断增强之实时进度展示](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000568678)

Next

[Oracle 模式通过一条 SQL 既加 Lob 列也加 not null 约束后，查询写入报错 4002，获取不到 Lob](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000001387573) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线