OceanBase學習之路12|體驗 DDL 新特性（MySQL 模式）

OceanBase 資料庫 MySQL 模式下的 DDL 新特性包括變更主鍵、變更分割槽型別、變更列型別和修改字符集。

變更主鍵

OceanBase 資料庫 MySQL 模式下的變更主鍵操作包括新增主鍵、修改主鍵和刪除主鍵。

新增主鍵

新增主鍵的語法如下：

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

新增主鍵的示例如下：

obclient> CREATE TABLE tbl1(c1 INT,c2 VARCHAR(50));
Query OK, 0 rows affected
obclient> ALTER TABLE tbl1 ADD PRIMARY KEY(c1);
Query OK, 0 rows affected

修改主鍵

修改主鍵的語法如下：

ALTER TABLE table_name DROP PRIMARY KEY,ADD PRIMARY KEY (column_name_list);

修改主鍵的示例如下：

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

刪除主鍵

刪除主鍵的語法如下：

ALTER TABLE table_name DROP PRIMARY KEY;

刪除主鍵的示例如下：

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

變更分割槽型別

OceanBase 資料庫 MySQL 模式下變更分割槽型別的語法如下：

ALTER TABLE table_name PARTITION BY (partition_definitions);

變更分割槽示例

示例 1：將 Hash 分割槽更改為 Key 分割槽方式。

obclient> CREATE TABLE tbl2(c1 INT, c2 DATETIME, PRIMARY KEY(c1, c2)) 
PARTITION BY HASH(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> ALTER TABLE tbl2 PARTITION BY KEY(c1) PARTITIONS 10;
Query OK, 0 rows affected

示例 2：將一級 Hash 分割槽表修改為模板化 Hash+Range 二級分割槽表。

obclient> ALTER TABLE tbl2 
       PARTITION BY HASH(c1)
                SUBPARTITION BY RANGE COLUMNS(c2)
                SUBPARTITION TEMPLATE(
                SUBPARTITION p1 VALUES LESS THAN ('2016-10-10'),
                SUBPARTITION p2 VALUES LESS THAN ('2116-3-30')) PARTITIONS 2;
Query OK, 0 rows affected

示例 3：將 Hash + Range 二級分割槽錶轉為 Range Columns+Range Columns 分割槽。

obclient> ALTER TABLE tbl2
   PARTITION BY RANGE COLUMNS(c1)
    SUBPARTITION BY RANGE COLUMNS(c2)
  (
  PARTITION p0 VALUES LESS THAN (100)
  (SUBPARTITION sp0 VALUES LESS THAN ('2020-01-01')
  ,SUBPARTITION sp1 VALUES LESS THAN  ('2021-01-01')
  ,SUBPARTITION sp2 VALUES LESS THAN ('2022-01-01')
  ,SUBPARTITION sp3 VALUES LESS THAN  ('2023-01-01')
  ),
  PARTITION p1 VALUES LESS THAN (200)
  (SUBPARTITION sp4 VALUES LESS THAN ('2020-01-01')
  ,SUBPARTITION sp5 VALUES LESS THAN  ('2021-01-01')
  ,SUBPARTITION sp6 VALUES LESS THAN ('2022-01-01')
  ,SUBPARTITION sp7 VALUES LESS THAN  ('2023-01-01'))
  );
Query OK, 0 rows affected

變更列型別

OceanBase 資料庫 MySQL 模式下所支援的列型別的相關轉換如下：

• 字元型別列的資料型別轉換，包括  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。

修改列型別的語法如下：

ALTER TABLE table_name MODEFY column_name data_type;

修改列型別的示例

字元資料型別之間的轉換示例

如下示例為修改字元資料型別列的資料型別並提升長度。

obclient> CREATE TABLE test01 (c1 INT PRIMARY KEY, c2 CHAR(10), c3 CHAR(10));
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 VARCHAR(40);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 TINYTEXT;
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C2 LONGTEXT;
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C3 CHAR(20);
Query OK, 0 rows affected
obclient> ALTER TABLE test01 MODIFY C3 VARCHAR(30);
Query OK, 0 rows affected

數值資料型別之間的轉換示例

示例 1：修改整數型別列的資料型別並提升長度。

obclient> CREATE TABLE test02 (id INT PRIMARY KEY, name VARCHAR(10),age TINYINT, description VARCHAR(65525));
Query OK, 0 rows affected
obclient> ALTER TABLE test02 MODIFY age SMALLINT;
Query OK, 0 rows affected
obclient> ALTER TABLE test02 MODIFY age INT;
Query OK, 0 rows affected
obclient> ALTER TABLE test02 MODIFY age BIGGINT;
Query OK, 0 rows affected

示例 2：修改帶精度的資料型別列的資料型別和長度。

obclient> CREATE TABLE test03(c1 INT, c2 FLOAT(8,2), c3 FLOAT(8,2), UNIQUE(c2, c3));
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 FLOAT(5,2);
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(10,4);
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 DOUBLE(5,2);
Query OK, 0 rows affected
obclient> ALTER TABLE test03 MODIFY c2 DECIMAL(20, 4);
Query OK, 0 rows affected

二進位制型別資料型別的轉換示例

obclient> CREATE TABLE test04 (c1 TINYBLOB, c2 BINARY(64));
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 BLOB;
Query OK, 0 rows affected
obclient> ALTER TABLE test04 MODIFY c1 BINARY(256);
Query OK, 0 rows affected
obclient> CREATE TABLE test05 (id INT PRIMARY KEY, name TINYTEXT,age INT, description VARCHAR(65535));
Query OK, 0 rows affected
obclient> ALTER TABLE test05 MODIFY name VARCHAR(256);
Query OK, 0 rows affected

整數型資料與字元型資料的轉換示例

obclient> CREATE TABLE test06 (c1 INT);
Query OK, 0 rows affected
obclient> ALTER TABLE test06 MODIFY c1 VARCHAR(64);
Query OK, 0 rows affected
obclient> CREATE TABLE test07 (c1 VARCHAR(32));
Query OK, 0 rows affected
obclient> ALTER TABLE test07 MODIFY c1 INT;
Query OK, 0 rows affected

修改表或者列的字符集和字元序（COLLATION）

修改表的字符集和字元序

OceanBase 資料庫 MySQL 模式下修改表的字元序和字符集的語法如下：

ALTER TABLE table_name CHARACTER SET = charset_name COLLATE =collate_name;

說明

此語法只修改表所在的 Schema 上的字元序和字符集，只對之後新增加的表起作用。

示例如下：

obclient> CREATE TABLE test_collation (c1 INT PRIMARY KEY, c2 VARCHAR(32), c3 VARCHAR(32), UNIQUE KEY idx_test_collation_c2(c2));
Query OK, 0 rows affected
obclient> ALTER TABLE test_collation CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin;
Query OK, 0 rows affected

修改表中已有資料的字符集和字元序

OceanBase 資料庫 MySQL 模式下修改表中已有資料的字符集和字元序的語法如下：

ALTER TABLE table_name CONVERT TO CHARACTER SET charset_name COLLATE collate_name;

說明

此語法不僅修改了表上已有資料的字符集和字元序，也會修改表所在的 Schema 上的字元序和字符集。

示例如下：

obclient> ALTER TABLE test_collation CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
Query OK, 0 rows affected

修改列的字符集和字元序

OceanBase 資料庫 MySQL 模式下修改列的字元序和字符集的語法如下：

ALTER TABLE table_name MODIFY COLUMN column_name data_type COLLATE collate_name;

示例如下：

obclient> ALTER TABLE test_collation MODIFY COLUMN c2 VARCHAR(32) COLLATE utf8mb4_bin;
Query OK, 0 rows affected