列操作

OceanBase 数据库 MySQL 模式下的列操作包括尾部添加列、非尾部添加列、删除列、重命名列、重排列、修改列类型、管理列的默认值、管理约束、修改自增列值。

添加列

OceanBase 数据库 MySQL 模式下添加列的操作分为 尾部添加列 和 非尾部添加列，下文分别进行介绍。

尾部添加列

在表的尾部添加列的语法如下：

ALTER TABLE table_name ADD COLUMN column_name data_type;

相关参数说明如下：

• table_name：指定待添加列的表的表名。

• column_name：指定待添加列的列名。

• data_type：指定待添加列的数据类型。

此处假设数据库中存在表 tbl1，以在表 tbl1 的尾部添加列 c1 为例介绍如何执行添加列的语句。

1. 查看表 tbl1 的表结构信息

   obclient> DESCRIBE tbl1;
   

   输出如下，可以看出该表中存在 id、name、age 三列。

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

2. 在表的尾部添加列 c1，并指定该列的数据类型为 INT

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT;
   

3. 再次查看表 tbl1 的表结构信息

   obclient> DESCRIBE tbl1;
   

   输出如下，可以看出该表的尾部新增了 c1 列。

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   | c1    | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

非尾部添加列

在表的非尾部添加列的语法如下：

ALTER TABLE table_name 
    ADD COLUMN new_column_name data_type 
    {FIRST | BEFORE | AFTER} column_name;

相关参数说明如下：

• table_name：指定待添加列的表的表名。

• new_column_name：指定待添加列的列名。

• data_type：指定待添加列的数据类型。

• FIRST | BEFORE | AFTER：指定待添加列的位置，可以添加在表首（FIRST）、 column_name 之前（BEFORE）或 column_name 之后（AFTER）。

  注意

  使用 FIRST 将列添加到表首时，无需设置 column_name，否则会执行报错。

• column_name：指定与待添加列位置相关的列名。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此处以在 tbl1 表的表首添加 c1 列，name 列前添加 c2 列，name 列后添加 c3 列为例，介绍如何执行添加列的语句。

1. 在 tbl1 表的表首添加 c1 列，并指定该列的数据类型为 INT

   obclient> ALTER TABLE tbl1 ADD COLUMN c1 INT FIRST;
   

2. 在 tbl1 表的 name 列前添加 c2 列，并指定该列的数据类型为 VARCHAR

   obclient> ALTER TABLE tbl1 ADD COLUMN c2 VARCHAR(50) BEFORE name;
   

3. 在 tbl1 表的 name 列后添加 c3 列，并指定该列的数据类型为 VARCHAR，且约束该列不能为空

   obclient> ALTER TABLE tbl1 ADD COLUMN c3 VARCHAR(25) NOT NULL AFTER name;
   

4. 再次查看 tbl1 表的表结构信息

   obclient> DESCRIBE tbl1;
   

   输出如下，

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | c1    | int(11)     | YES  |     | NULL    |       |
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | c2    | varchar(50) | YES  |     | NULL    |       |
   | name  | varchar(50) | NO   |     | NULL    |       |
   | c3    | varchar(25) | NO   |     | NULL    |       |
   | age   | int(11)     | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

删除列

删除列的语法如下：

ALTER TABLE table_name DROP COLUMN column_name

其中，table_name 指定待删除列所在表的表名；column_name 指定待删除列的列名。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此处以删除 name 列为例介绍如何执行删除列的语句。

obclient> ALTER TABLE tbl1 DROP COLUMN name;

删除后再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构，输出如下，表 tbl1 中已无 name 列。

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

删除多列

语法如下：

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

与删除单列相同，table_name 指定待删除列所在表的表名；column_name 指定待删除列的列名。 假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C4    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
| C5    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

此处以删除 tbl1 表的中 C4、C5 列为例，介绍如何使用 ALTER TABLE table_name DROP COLUMN column_name1, column_name2, ... 删除表中的多列。

obclient> ALTER TABLE tbl1 DROP COLUMN C4, DROP COLUMN C5;

再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构，输出如下，表 tbl1 中已无 C4、C5 列。

+-------+--------------+------+------+---------+-------+
| FIELD | TYPE         | NULL | KEY  | DEFAULT | EXTRA |
+-------+--------------+------+------+---------+-------+
| C1    | NUMBER(30)   | NO   | PRI  | NULL    | NULL  |
| C2    | VARCHAR(50) | YES  | NULL | NULL    | NULL  |
| C3    | NUMBER(30)   | YES  | NULL | NULL    | NULL  |
+-------+--------------+------+------+---------+-------+

清除废弃列

清除废弃列的语法，如下所示：

ALTER TABLE TABLE_NAME FORCE;

此处以清除 tbl1 表的中 C2、C3、C4、C5 列为例，介绍如何清除表中的废弃列。

obclient> ALTER TABLE tbl1 FORCE;

重命名列

重命名列的语法有如下两种：

• 重命名列的同时修改列的数据类型

  ALTER TABLE table_name CHANGE old_col_name new_col_name data_type;
  

  相关参数说明如下：

  • table_name：指定待重命名的列所在表的表名。

  • old_col_name：指定待重命名的列的列名。

  • new_col_name：指定重命名后列的列名。

  • data_type：指定待重命名的列的数据类型，您可指定为当前数据类型，也可指定将该列修改为其他数据类型，支持修改的数据类型可参见下文 修改列类型。

• 仅重命名列

  ALTER TABLE table_name RENAME COLUMN old_col_name TO new_col_name
  

  相关参数说明如下：

  • table_name：指定待重命名的列所在表的表名。

  • old_col_name：指定待重命名的列的列名。

  • new_col_name：指定重命名后列的列名。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此处分别以修改 name 列名为 c2、修改 age 列名为 c3，并修改 age 列数据类型为 VARCHAR 为例介绍如何执行重命名列的语句。

1. 修改 name 列名为 c2

   obclient> ALTER TABLE tbl1 RENAME COLUMN name TO c2;
   

2. 修改 age 列名为 c3，并修改 age 列数据类型为 VARCHAR

   obclient> ALTER TABLE tbl1 CHANGE age c3 VARCHAR(50);
   

3. 再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构

   输出如下，tbl1 表中只有 id、c2、c3 三列，且 c3 列的数据类型为 VARCHAR。

   +-------+-------------+------+-----+---------+-------+
   | Field | Type        | Null | Key | Default | Extra |
   +-------+-------------+------+-----+---------+-------+
   | id    | int(11)     | NO   | PRI | NULL    |       |
   | c2    | varchar(50) | YES  |     | NULL    |       |
   | c3    | varchar(50) | YES  |     | NULL    |       |
   +-------+-------------+------+-----+---------+-------+
   

重排列

重排列的语法如下：

ALTER TABLE table_name 
    MODIFY [COLUMN] column_name data_type 
    {FIRST | BEFORE | AFTER} column_name;

相关参数说明如下：

• table_name：指定待重排列的表的表名。

• column_name：指定待重排列的列名。

• data_type：指定待重排列的数据类型，您可指定为当前数据类型，也可指定将该列修改为其他数据类型，支持修改的数据类型可参见下文 修改列类型。

• FIRST | BEFORE | AFTER：指定待添加列的位置，可以添加在表首（FIRST）、 column_name 之前（BEFORE）或 column_name 之后（AFTER）。

  注意

  使用 FIRST 将列添加到表首时，无需设置 column_name，否则会执行报错。

• column_name：指定与待重排列位置相关的列名。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此处以将 age 列重排列到 name 列之前，并将 age 列数据类型修改为 VARCHAR 为例介绍如何使用重排列语句。

obclient> ALTER TABLE tbl1 MODIFY COLUMN age VARCHAR(50) BEFORE name;

再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构，输出如下，age 列排列在 name 列之前，并且数据类型为 VARCHAR。

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| age   | varchar(50) | YES  |     | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

修改列类型

OceanBase 数据库所支持的列类型的相关转换如下：

• 字符类型列的数据类型转换，包括 CHAR、VARCHAR、TINYTEXT、TEXT 和 LONGTEXT。

• 数值类型列的数据类型转换，包括 TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT。

• 二进制类型的数据类型转换，包括 BINARY、VARBINARY、BLOB、TINYBLOB、MEDIUMBLOB 和 LONGBLOB。

• 带精度的数据类型支持改变精度，包括 VARCHAR、FLOAT、DOUBLE 和 DECIMAL。

• 带精度的数据类型之间的转换，包括 FLOAT、DOUBLE 和 DECIMAL。

• 不同数据类型之间的转换，包括 INT、VARCHAR、DOUBLE、FLOAT 和 DECIMAL。

OceanBase 数据库相关的列类型变更规则，请参考 列类型变更规则。

修改列类型的语法如下：

ALTER TABLE table_name MODIFY [COLUMN] column_name data_type;

相关参数说明如下：

• table_name：指定待修改类型的列所在表的表名。

• column_name：指定待修改类型的列的列名。

• data_type：指定修改后的数据类型。

字符数据类型之间的转换示例

如下所示，创建表 test01：

obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));

以 test01 表为例，结合如下几条示例介绍如何修改字符数据类型列的数据类型并提升长度。

• 修改 test01 表中 c2 列数据类型为 VARCHAR，并指定长度最大为 20 字符

  obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(20);
  

• 修改 test01 表中 c2 列数据类型为 VARCHAR，并指定长度最大为 40 字符

  obclient> ALTER TABLE test01 MODIFY c2 VARCHAR(40);
  

• 修改 test01 表中 c2 列数据类型为 TINYTEXT

  obclient> ALTER TABLE test01 MODIFY c2 TINYTEXT;
  

• 修改 test01 表中 c2 列数据类型为 LONGTEXT

  obclient> ALTER TABLE test01 MODIFY c2 LONGTEXT;
  

• 修改 test01 表中 c3 列长度为 20 字符

  obclient> ALTER TABLE test01 MODIFY c3 CHAR(20);
  

• 修改 test01 表中 c3 列数据类型为 VARCHAR，并指定长度最大为 30 字符

  obclient> ALTER TABLE test01 MODIFY c3 VARCHAR(30);
  

数值数据类型之间的转换示例

整数类型

如下所示，创建表 test02：

obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));

以 test02 表为例，结合如下几条示例介绍如何修改整数类型列的数据类型并提升长度。

• 修改 test02 表中 age 列数据类型为 SMALLINT

  obclient> ALTER TABLE test02 MODIFY age SMALLINT;
  

• 修改 test02 表中 age 列数据类型为 INT

  obclient> ALTER TABLE test02 MODIFY age INT;
  

• 修改 test02 表中 age 列数据类型为 BIGGINT

  obclient> ALTER TABLE test02 MODIFY age BIGGINT;
  

带精度数据类型

如下所示，创建表 test03:

obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));

以 test03 表为例，结合如下几条示例介绍如何修改带精度的数据类型列的数据类型和长度。

• 修改 test03 表中 c2 列可存储数字位数为 5 位

  obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2);
  

• 修改 test03 表中 c2 列数据类型为 DOUBLE，并设置可以存储总共 10 位数，其中小数部分为 4 位

  obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);
  

• 修改 test03 表中 c2 列数据类型为 DOUBLE，并设置可存储数字位数为 5 位，其中小数部分为 2 位

  obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);
  

• 修改 test03 表中 c2 列数据类型为 DECIMAL，并设置可以存储总共 20 位数，其中小数部分为 4 位

  obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
  

二进制类型数据类型的转换示例

如下所示，创建表 test04：

obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));

以 test04 表为例，结合如下几条示例介绍如何修改二进制数据类型列的数据类型和长度。

• 修改 test04 表中 c1 列数据类型为 BLOB

  obclient> ALTER TABLE test04 MODIFY c1 BLOB;
  

• 修改 test04 表中 c1 列数据类型为 BINARY，并设置最多可存储 256 字节的数据

  obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
  

• 修改 test04 表中 c1 列数据类型为 VARCHAR，并指定长度最大为 256 字符

  obclient> ALTER TABLE test04 MODIFY c1 VARCHAR(256);
  

整数型数据与字符型数据的转换示例

如下所示，创建表 test05：

obclient> CREATE TABLE test05 (c1 INT);

1. 执行如下命令，将 test05 表中 c1 列数据类型修改为 VARCHAR，并指定长度最大为 64 字符

   obclient> ALTER TABLE test05 MODIFY c1 VARCHAR(64);
   

2. 执行如下命令，将 test05 表中 c1 列再次修改为 INT 类型

   obclient> ALTER TABLE test05 MODIFY c1 INT;
   

管理列的默认值

OceanBase 数据库 MySQL 模式下可修改、删除列的默认值，本节将分别为您介绍如何操作。

修改列的默认值

未设置的情况下，列的默认值为 NULL，修改列的默认值的语法如下：

ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT const_value;

相关参数说明如下：

• table_name：指定待修改默认值的列所在表的表名。

• column_name：指定待修改默认值的列的列名。

• const_value：指定修改后列的默认值。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

此处以修改 age 列的默认值为 18 为例介绍如何修改列的默认值。

obclient> ALTER TABLE tbl1 ALTER COLUMN age SET DEFAULT 18;

再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构，输出如下，age 列的默认值为 18。

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+

删除列的默认值

删除列的默认值的语法如下：

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

其中，table_name 指定待修改默认值的列所在表的表名；column_name 指定待修改默认值的列的列名。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | 18      |       |
+-------+-------------+------+-----+---------+-------+

此处以删除 age 列的默认值为例介绍如何删除列的默认值。

obclient> ALTER TABLE tbl1 ALTER COLUMN age DROP DEFAULT;

再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构，输出如下，age 列的默认值为 NULL。

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(50) | NO   |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

管理约束

OceanBase 数据库 MySQL 模式下可为表增加列约束，如修改已有列为自增列、设置列是否可为空、指定列的唯一性等，本节将分别为您介绍如何操作。

管理约束的语法如下：

ALTER TABLE table_name 
    MODIFY [COLUMN] column_name data_type
    [AUTO_INCREMENT] 
    [NULL | NOT NULL] 
    [[PRIMARY] KEY] 
    [UNIQUE [KEY]];

相关参数说明如下：

• table_name：指定待增加约束的列所在表的表名。

• column_name：指定待增加约束的列的列名。

• data_type：指定待修改列的数据类型，您可指定为当前数据类型，也可指定将该列修改为其他数据类型，支持修改的数据类型可参见上文 修改列类型。

• AUTO_INCREMENT：指定设置选定列为自增列。

• NULL | NOT NULL：指定设置选定列可以为空（NULL），或不能为空（NOT NULL）。

• [PRIMARY] KEY：指定设置选定列为主键。

• UNIQUE [KEY]：指定设置选定列的唯一性。

假设数据库中存在表 tbl1，tbl1 表结构信息如下所示：

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

1. 将 id 列设置为主键

   obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT PRIMARY KEY;
   

2. 将 id 列设置为自增列

   obclient> ALTER TABLE tbl1 MODIFY COLUMN id INT AUTO_INCREMENT;
   

3. 将 name 列设置为不能为空

   obclient> ALTER TABLE tbl1 MODIFY COLUMN name VARCHAR(50) NOT NULL;
   

4. 将 age 列设置为必须唯一

   obclient> ALTER TABLE tbl1 MODIFY COLUMN age INT UNIQUE;
   

5. 再次执行 DESCRIBE tbl1; 命令查看 tbl1 表的表结构

   +-------+-------------+------+-----+---------+----------------+
   | Field | Type        | Null | Key | Default | Extra          |
   +-------+-------------+------+-----+---------+----------------+
   | id    | int(11)     | NO   | PRI | NULL    | auto_increment |
   | name  | varchar(50) | NO   |     | NULL    |                |
   | age   | int(11)     | YES  | UNI | NULL    |                |
   +-------+-------------+------+-----+---------+----------------+
   

修改自增列值

修改自增列值的语法如下：

ALTER TABLE table_name [SET] AUTO_INCREMENT = next_value;

此处假设数据库中存在表 tbl1，以修改 tbl1 中自增列值为 12 为例介绍如何修改自增列的列值：

obclient> ALTER TABLE tbl1 AUTO_INCREMENT = 12;

修改后，tbl1 表的下一个自增值将设置为 12，插入新记录时，自增列（例如 c1）将从 12 开始递增。

相关文档

ALTER TABLE