8.1.2.1. OceanBase-Mysql

8.1.2.1.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 t1ss(id int PRIMARY KEY ,
    col1 timestamp(6) DEFAULT current_timestamp ,
    col2 timestamp(6) DEFAULT sysdate,
    col3 date DEFAULT current_timestamp ,
    col4 date DEFAULT sysdate,
    col5 timestamp(6) DEFAULT systimestamp
);

-- 转换后OceanBase-MySQL：
CREATE TABLE t1ss (id bigint PRIMARY KEY,col1 timestamp(6) DEFAULT current_timestamp(6),col2 timestamp(6) DEFAULT current_timestamp(6),col3 datetime DEFAULT current_timestamp(0),col4 datetime DEFAULT current_timestamp(0),col5 timestamp(6) DEFAULT current_timestamp(6));

警告

针对 OceanBase-MySQL-4.2.1 相关版本：

创建表的列的默认表达式支持范围

支持以下写法：

timestamp(n)/data default current_timestamp

timestamp(n)/data default sysdate

timestamp(n) default systimestamp

转换到目标库后，默认值函数的精度会根据列精度 n 自动调整。需要注意，可能与源库 Oracle 中的时间精度存在差异。

表列默认值如果是函数或表达式，转换后会直接去除，导致没有默认值功能，DML时需要指定列值：
- date，timestamp 类型，若默认值为current_date,current_timestamp，sysdate,localtimestamp 则不去除
- to_char函数去除，支持TO_CHAR毫秒’FF6’格式，被用于insert、update、select场景
char类型支持有如下规制：
- char不带精度或者带精度，但精度范围在1到256，不转换
- char带精度，精度范围在256到2000，转换成varchar类型，并带上相同的精度
- char超过2000，报错
PARTITION BY RANGE时，若分区建为number类型的整型（小数为0），会转换为int或bigint 类型
- precision <= 9 类型转换为int
- 10 <= precision <=18 类型转换为bigint
- precision > 18 报错
DEFAULT expression, expression支持被小括号包裹
DEFAULT expression, 如果该列的类型是number(n,0), 则支持格式为 yyyy-mm-dd 和 yyyy/mm/dd 的表达式， yyyy, mm, dd 必须是正整数
列类型为number, 且未指定精度，则转换为 decimal(38,30),支持最大8位整数
列类型为rowid, 则转换为 char(18)
当列约束同时存在 ``DEFAULT NULL NOT NULL`` 时，会去除 ``DEFAULT NULL``
对于clob类型不支持默认值，会直接去除默认值。DML时需要指定列值
不支持子分区、hash分区、多分区键和list分区number类型字段为分区键

8.1.2.1.2. 创建索引

语法

CREAT INDEX index_name on table_name(index_expr [, index_expr ] ...)

index_expr: column_name | function

描述

创建索引。

当目标端为 OceanBase-MySQL 4.2 时，统一SQL支持通过配置项 unisql.obmysql.index.max.key.length 控制索引键总长度预算，默认值为 15000。当索引键总长度超过该预算时，统一SQL会根据列类型自动调整索引定义：

普通索引：对字符类型列自动补充前缀长度，构造可执行的前缀索引；
复合索引：按总长度预算对多个字符列分配前缀长度，优先保留较短列的完整长度；
PRIMARY KEY / UNIQUE 约束：若总长度超限，会按目标端兼容规则调整索引长度；其中 UNIQUE 约束在必要时可转换为 UNIQUE INDEX 形式以支持前缀长度。

当前实现方案依赖元数据的获取；如果无法获取元数据，则该功能不可用。目前已知分区表暂不支持元数据获取，因此该功能对分区表不可用。

若需要保持旧版 16384 bytes 的兼容阈值，可显式设置：

EXEC SET unisql.obmysql.index.max.key.length = 16384

示例

-- 创建表
create index i_key1_1 on t1(key1);
create index i_key1_1 on t1(to_number(key1));

-- 转换后OceanBase-MySQL：
CREATE INDEX i_key1_1 ON t1 (key1);
CREATE INDEX i_key1_1 ON t1 ((CAST(key1 AS DECIMAL(65, 30))));

-- 转换前Oracle SQL：索引键总长度超过默认预算 15000 bytes
CREATE TABLE t50155_index_key_length_t3 (
    c1 VARCHAR2(4000),
    c2 VARCHAR2(4000),
    c3 VARCHAR2(4000)
);
CREATE INDEX idx_t3_composite ON t50155_index_key_length_t3 (c1, c2, c3);

-- 转换后OceanBase-MySQL：自动为超长字符列分配前缀长度
CREATE TABLE `t50155_index_key_length_t3` (
    `c1` varchar(4000),
    `c2` varchar(4000),
    `c3` varchar(4000)
);
CREATE INDEX `idx_t3_composite` ON `t50155_index_key_length_t3` (`c1`(1250), `c2`(1250), `c3`(1250));