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 相关版本：

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

   支持以下写法：

   • timestamp(n)/data default current_timestamp

   • timestamp(n)/data default sysdate

   • timestamp(n) default systimestamp

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