3.2.5.3. OceanBase-Mysql
3.2.5.3.1. 创建表
警告
创建表暂不支持最后添加主键约束。
语法
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [partition_option] [on_commit_option]
CREATE [GLOBAL TEMPORARY] TABLE table_name
(table_definition_list) [partition_option] [AS] select;
table_definition_list:
table_definition [, table_definition ...]
table_definition:
column_definition
| INDEX [index_name] index_desc
| [CONSTRAINT [constraint_name]] [UNIQUE] (column_definition_list) [USING INDEX index_option_list]
| [CONSTRAINT [constraint_name]] FOREIGN KEY (column_name, column_name ...) references_clause constraint_state
| [CONSTRAINT [constraint_name]] CHECK(expression) constraint_state
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
[VISIBLE|INVISIBLE]
{
[DEFAULT expression]
[NULL | NOT NULL]
[CONSTRAINT [constraint_name] [PRIMARY] KEY] [UNIQUE [KEY]]
[CONSTRAINT [constraint_name] CHECK(expression)]
[CONSTRAINT [constraint_name] references_clause]
|
[GENERATED ALWAYS] AS (expression) [VIRTUAL]
[NULL | NOT NULL] [UNIQUE KEY] [[PRIMARY] KEY] [UNIQUE LOWER_KEY] [COMMENT string]
}
references_clause:
REFERENCES table_name [ (column_name, column_name ...) ] [ON DELETE {CASCADE}]
index_desc:
(column_desc_list) [index_option_list]
column_desc_list:
column_desc [, column_desc ...]
column_desc:
column_name [ASC | DESC][NULL LAST | NULL FIRST]
partition_option:
PARTITION BY HASH(column_name_list)
[subpartition_option] hash_partition_define
| PARTITION BY RANGE (column_name_list)
[subpartition_option] (range_partition_list)
| PARTITION BY LIST (column_name_list)
[subpartition_option] (list_partition_list)
on_commit_option:
ON COMMIT DELETE ROWS
| ON COMMIT PRESERVE ROWS
- 描述
- 该语句用来在数据库中创建一张新表，DEFAULT expression可支持使用关键字函数、字符串、常数。
示例
-- 转换前Oracle SQL:
CREATE table unisql_create_table_test(
DBID_ NUMERIC(19) NOT NULL,
PROCESS_KEY VARCHAR2(128) NOT NULL,
BIZ_VERSION VARCHAR2(32) NOT NULL,
PROCESS_NAME VARCHAR2(128) NOT NULL,
PROCESS_FLAG VARCHAR2(1) NOT NULL,
PROCESS_CLASS VARCHAR2(128) NOT NULL,
IGNORE_EXIST_USER VARCHAR2(1) default 0 NOT NULL,
FORM_NAME VARCHAR2(128),
FORM_TYPE VARCHAR2(128),
FORM_URL VARCHAR2(128),
FORM_UUID VARCHAR2(32),
IMPORT_TIME TIMESTAMP,
HANDLE_TIME TIMESTAMP,
STATE_ VARCHAR2(20) default 0,
ERRORMSG CLOB,
constraint UK_IMPORT_PRO UNIQUE (PROCESS_KEY,BIZ_VERSION,PROCESS_NAME),
constraint UK_PROCESS_BIZ UNIQUE (PROCESS_KEY,BIZ_VERSION),
constraint UK_IMPORT_PRO_NAME UNIQUE (PROCESS_NAME,BIZ_VERSION)
);
-- 转换后OceanBase-MySQL：
create table `unisql_create_table_test` (
`DBID_` decimal(19) not null,
`PROCESS_KEY` varchar(128) not null,
`BIZ_VERSION` varchar(32) not null,
`PROCESS_NAME` varchar(128) not null,
`PROCESS_FLAG` varchar(1) not null,
`PROCESS_CLASS` varchar(128) not null,
`IGNORE_EXIST_USER` varchar(1) default 0 not null,
`FORM_NAME` varchar(128),
`FORM_TYPE` varchar(128),
`FORM_URL` varchar(128),
`FORM_UUID` varchar(32),
`IMPORT_TIME` timestamp(6),
`HANDLE_TIME` timestamp(6),
`STATE_` varchar(20) default 0,
`ERRORMSG` longtext,
unique `UK_IMPORT_PRO`(`PROCESS_KEY`,
`BIZ_VERSION`,
`PROCESS_NAME`),
unique `UK_PROCESS_BIZ`(`PROCESS_KEY`,
`BIZ_VERSION`),
unique `UK_IMPORT_PRO_NAME`(`PROCESS_NAME`,
`BIZ_VERSION`));
-- 转换前Oracle SQL:
CREATE GLOBAL TEMPORARY TABLE unisql_create_table_on_option_test(
d_SMALLINT SMALLINT ,
d_INTEGER INTEGER,
d_INT INT ,
d_LONG LONG ,
d_NUMBER NUMBER(10,2),
d_DECIMAL DECIMAL(10,2),
d_CHAR CHAR(60),
d_CHARACTER CHARACTER(60) ,
d_VARCHAR VARCHAR(60),
d_VARCHAR2 VARCHAR2(60),
d_DATE DATE,
d_TIMESTAMP TIMESTAMP(6),
d_TIMESTAMP2 TIMESTAMP(6) WITH TIME ZONE,
d_BLOB BLOB,
d_CLOB CLOB
) ON COMMIT PRESERVE ROWS;
-- 转换后OceanBase-MySQL：
create temporary table `unisql_create_table_on_option_test` (
`d_SMALLINT` bigint,
`d_INTEGER` bigint,
`d_INT` bigint,
`d_LONG` mediumtext,
`d_NUMBER` decimal(10,
2),
`d_DECIMAL` decimal(10,
2),
`d_CHAR` char(60),
`d_CHARACTER` char(60),
`d_VARCHAR` varchar(60),
`d_VARCHAR2` varchar(60),
`d_DATE` datetime,
`d_TIMESTAMP` timestamp(6),
`d_TIMESTAMP2` timestamp(6),
`d_BLOB` longblob,
`d_CLOB` longtext);
3.2.5.3.2. 新增表约束
语法
ALTER TABLE table_name
ADD [CONSTRAINT [constraint_name]] UNIQUE (column_name [, column_name ]...)
| ADD [CONSTRAINT [constraint_name]] CHECK (expr);
[disable | enable]
- 描述
- 该语句用于新增约束
示例
-- 创建表
CREATE TABLE unisql_cn_test(a int,b int,c int,d varchar2(10),e varchar2(10));
-- 测试主键或者唯一约束
-- 转换前Oracle SQL:
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a);
ALTER TABLE unisql_cn_test ADD CONSTRAINT cn_name_a UNIQUE (a,b);
-- 转换后TDSQL-MySQL：
ALTER TABLE `unisql_cn_test` ADD UNIQUE `cn_name_a`(`a`);
ALTER TABLE `unisql_cn_test` ADD UNIQUE `cn_name_a`(`a`, `b`);
3.2.5.3.3. 删除表约束
语法
ALTER TABLE TABLE_NAME DROP PRIMARY KEY
OceanBase-MySQL不支持这种用法
3.2.5.3.4. 修改列的属性
语法
ALTER TABLE table_name MODIFY [COLUMN] column_definition
column_definition_list:
column_definition [, column_definition ...]
column_definition:
column_name data_type
- 描述
- 该语句用于修改列属性
警告
OceaBase-MySQL不允许修改非字符类型和修改列定义，仅支持增加特定字符数据类型（VARCHAR、VARBINARY、CHAR、DECIMAL等）的长度。
示例
DROP TABLE unisql_modify_column_test;
CREATE TABLE unisql_modify_column_test(a int,d number);
SELECT * FROM unisql_modify_column_test;
-- 转换前Oracle SQL:
ALTER TABLE unisql_modify_column_test MODIFY (d number(10,2));
-- 转换后OceaBase-MySQL：
ALTER TABLE `unisql_modify_column_test` MODIFY COLUMN `d` decimal(10,2);
3.2.5.3.5. 修改表名
语法
RENAME TABLE_NAME TO NEW_TABLE_NAME;
- 描述
- 该语句用于修改表名
示例
CREATE TABLE unisql_rename_table_test(id int,name varchar(10));
-- 转换前Oracle SQL:
RENAME unisql_rename_table_test TO unisql_rename_table_test_new;
-- 转换后TDSQL-MySQL：
RENAME TABLE `unisql_rename_table_test` TO `unisql_rename_table_test_new`;