关于表结构的修改

SQL新手 2022-12-08

1090

本文主要介绍在 OceanBase 数据库修改表名、修改列名、修改列类型、同时修改列名和列类型、普通列修改为自增列、增加列、删除列、添加主键、修改主键、删除主键、添加外键、删除外键、添加 CHECK 约束、修改表分区。

说明
有关修改表结构的详细信息，请参见 ALTER TABLE。

前提条件

当前用户需要具有 ALTER TABLE 权限。

示例 1

创建示例表 tbl1。

obclient> CREATE TABLE tbl1(col1 INT,col2 INT,col3 VARCHAR(50));
Query OK, 0 rows affected

示例 1-1 修改表名

将表 tbl1 的名称改为 test_tbl1。

obclient> ALTER TABLE tbl1 RENAME test_tbl1;
Query OK, 0 rows affected

示例 1-2 修改列名

将 test_tbl1 表中列 col3 的名称改成 name。

obclient> ALTER TABLE test_tbl1 CHANGE col3 name VARCHAR(50);
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col1  | int(11)     | YES  |     | NULL    |       |
| col2  | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set

示例 1-3 修改列类型

将 test_tbl1 表中列 col2 的数据类型从 INT(11) 转换为 VARCHAR(128)。

obclient> ALTER TABLE test_tbl1 MODIFY col2 VARCHAR(128);
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | int(11)      | YES  |     | NULL    |       |
| col2  | varchar(128) | YES  |     | NULL    |       |
| name  | varchar(50)  | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set

说明
有关列类型变更规则的详细信息，请参见列类型变更规则。

示例 1-4 同时修改列名和列类型

将 test_tbl1 表中的 name 列名改成 col3 并把列类型改为 LONGTEXT。

obclient> ALTER TABLE test_tbl1 CHANGE name col3 LONGTEXT;
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | int(11)      | YES  |     | NULL    |       |
| col2  | varchar(128) | YES  |     | NULL    |       |
| col3  | longtext     | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set

示例 1-5 普通列修改为自增列

将表 test_tbl1 的 col1 列修改为自增列。

obclient> ALTER TABLE test_tbl1 MODIFY col1 INT AUTO_INCREMENT;
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| col1  | int(11)      | NO   |     | NULL    | auto_increment |
| col2  | varchar(128) | YES  |     | NULL    |                |
| col3  | longtext     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

示例 1-6 增加列

在 test_tbl1 表中，增加 col4 列。

obclient> ALTER TABLE test_tbl1 ADD col4 char(1);
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| col1  | int(11)      | NO   |     | NULL    | auto_increment |
| col2  | varchar(128) | YES  |     | NULL    |                |
| col3  | longtext     | YES  |     | NULL    |                |
| col4  | char(1)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
4 rows in set

示例 1-7 删除列

说明
OceanBase 数据库支持删除表中的普通列和含索引的列，但不允许删除主键列。如果要删除主键列，可以先删除主键，然后再删除列。

示例 1-7-1 删除普通列

在 test_tbl1 表中，删除 col4 列。

obclient> ALTER TABLE test_tbl1 DROP col4;
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| col1  | int(11)      | NO   |     | NULL    | auto_increment |
| col2  | varchar(128) | YES  |     | NULL    |                |
| col3  | longtext     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

示例 1-7-2 删除表中含索引的列

obclient> CREATE TABLE test_tbl2 (col1 INT,col2 INT,col3 INT,PRIMARY KEY(col1),INDEX(col2,col3));
Query OK, 0 rows affected

obclient> DESC test_tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   | PRI | NULL    |       |
| col2  | int(11) | YES  | MUL | NULL    |       |
| col3  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set

#删除 `col2` 列
obclient> ALTER TABLE test_tbl2 DROP col2;
Query OK, 0 rows affected

obclient> DESC test_tbl2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col1  | int(11) | NO   | PRI | NULL    |       |
| col3  | int(11) | YES  | MUL | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set

示例 1-8 添加主键

将 test_tbl1 表中列 col1 设置为主键。

obclient> ALTER TABLE test_tbl1 ADD PRIMARY KEY (col1);
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| col1  | int(11)      | NO   | PRI | NULL    | auto_increment |
| col2  | varchar(128) | YES  |     | NULL    |                |
| col3  | longtext     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

示例 1-9 修改主键

将表 test_tbl1 的主键改为 col2。

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

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| col1  | int(11)      | NO   |     | NULL    | auto_increment |
| col2  | varchar(128) | NO   | PRI | NULL    |                |
| col3  | longtext     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

示例 1-10 删除主键

说明
OceanBase 数据库 MySQL 模式中，以下情况不允许删除主键：
该表是包含外键信息的父表。
该表是子表，但主键列包含外键引用列。

示例

删除表 test_tbl1 的主键。

obclient> ALTER TABLE test_tbl1 DROP PRIMARY KEY;
Query OK, 0 rows affected

obclient> DESC test_tbl1;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| col1  | int(11)      | NO   |     | NULL    | auto_increment |
| col2  | varchar(128) | NO   |     | NULL    |                |
| col3  | longtext     | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set

示例 1-11 添加外键

创建表 tbl1 和 tbl2，然后为表 tbl1 的 col2 列添加外键 fk1_tbl1。

obclient> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 INT(50));
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 INT(11) PRIMARY KEY,col2 INT(50));
Query OK, 0 rows affected

obclient> ALTER TABLE tbl1 ADD CONSTRAINT fk1_tbl1 FOREIGN KEY (col2) REFERENCES tbl2(col1);
Query OK, 0 rows affected

示例 1-12 删除外键

删除表 tbl1 的外键约束 fk1_tbl1。

obclient> ALTER TABLE tbl1 DROP FOREIGN KEY fk1_tbl1;
Query OK, 0 rows affected

示例 1-13 添加 CHECK 约束

为表 tbl1 的 col1 添加 CHECK 约束，col1 的列值大于 0。

obclient> ALTER TABLE test_tbl1 MODIFY col1 INT CHECK(col1>0);
Query OK, 0 rows affected

示例 2 修改表分区

OceanBase 数据库支持以下表的分区操作：

非分区表转换为分区表，可以将非分区表转换成为一级分区表和二级分区表，包括模版二级分区表和非模版二级分区表。
修改分区表的分区方式，例如：将一级分区表修改为二级分区表。

有关修改表分区的详细信息，请参见修改分区规则。

示例 2-1 将非分区表转换为一级分区表

创建非分区表 tbl3，然后将表 tbl3 修改为 Hash 类型的一级分区表。

obclient> CREATE TABLE tbl3(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl3 PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected

示例 2-2 将非分区表转换为二级分区表

创建非分区表 tbl4，然后将表 tbl4 修改为 Hash-Range 类型的模板化二级分区表。

obclient> CREATE TABLE tbl4(col1 INT,col2 INT);
Query OK, 0 rows affected

obclient> ALTER TABLE tbl4 PARTITION BY HASH(col1)
      SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE(
      SUBPARTITION sp1 VALUES LESS THAN (2020),
      SUBPARTITION sp2 VALUES LESS THAN (2024))
      PARTITIONS 5;
Query OK, 0 rows affected

示例 2-3 将一级分区表转换为二级分区表

创建 Hash 类型的一级分区表 tbl5 后，然后将表 tbl5 修改为 Hash-Range 类型的模板化二级分区表。

obclient> CREATE TABLE tbl5(col1 INT,col2 INT) PARTITION BY HASH(col1) PARTITIONS 5;
Query OK, 0 rows affected

obclient> ALTER TABLE tbl5 PARTITION BY HASH(col1)
      SUBPARTITION BY RANGE COLUMNS(col2)
      SUBPARTITION TEMPLATE(
      SUBPARTITION sp1 VALUES LESS THAN(2022),
      SUBPARTITION sp2 VALUES LESS THAN(2024))
      PARTITIONS 10;
Query OK, 0 rows affected

分区表主键约束 test 外键 oceanbase

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者