该语句用于在 OceanBase 数据库中创建新表。

格式

CREATE TABLE [IF NOT EXISTS] tblname 
    (create_definition,...)  
    [table_options]  
    [partition_options];  
CREATE TABLE [IF NOT EXISTS] tblname
    LIKE oldtblname
create_definition:
    colname column_definition
    | PRIMARY KEY (index_col_name [, index_col_name...]) [index_type] [index_options]... 
    | {INDEX|KEY} [indexname] (index_col_name,...) [index_type] [index_options]... 
    | UNIQUE [INDEX|KEY] [indexname] (index_col_name,...) [index_type]  [index_options]...
    | FULLTEXT [INDEX|KEY] [indexname] (index_col_name,...) CTXCAT(index_col_name,...) [index_options]...
column_definition:
    data_type [NOT NULL | NULL] [DEFAULT defaultvalue] [AUTO_INCREMENT] [UNIQUE [KEY]] 
    | [[PRIMARY] KEY] [COMMENT ‘string’]       
    | [data_type] [GENERATED ALWAYS] AS (expression)      
    [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]      
    [NOT NULL | NULL] [[PRIMARY] KEY]
data_type:  
    TINYINT[(length)] [UNSIGNED] [ZEROFILL]   
    | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]   
    | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]   
    | INT[(length)] [UNSIGNED] [ZEROFILL]   
    | INTEGER[(length)] [UNSIGNED] [ZEROFILL]   
    | BIGINT[(length)] [UNSIGNED] [ZEROFILL]   
    | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]   
    | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]   
    | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]   
    | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]   
    | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]   
    | DATE  
    | TIME[(fsp)]   
    | TIMESTAMP[(fsp)]   
    | DATETIME[(fsp)]   
    | YEAR  
    | CHAR[(length)]       [CHARACTER SET charsetname] [COLLATE collationname]   
    | VARCHAR(length)      [CHARACTER SET charsetname] [COLLATE collationname]   
    | BINARY[(length)]   
    | VARBINARY(length)
index_col_name:
      colname [(length)] [ASC | DESC]
index_type:
       USING BTREE
index_options:
       index_option [index_option…] 
index_option: 
    GLOBAL [LOCAL]     
    |COMMENT 'string'    
    |COMPRESSION [=] {NONE | LZ4_1.0 | ZSTD_1.0 | SNAPPY_1.0 | ZLIB_1.0}    
    |BLOCK_SIZE [=] size    
    |STORING(columname_list)
    |VISIBLE [INVISIBLE]
columname_list:
     colname [, colname…]
table_options:
     table_option [table_option]...
table_option: 
    [DEFAULT] {CHARACTER SET| CHARSET} [=] charsetname
    | [DEFAULT] COLLATE [=] collationname
    | COMMENT [=] ’string’ 
    | COMPRESSION [=] {NONE | LZ4_1.0 | ZSTD_1.0 | SNAPPY_1.0 | ZLIB_1.0}
    | EXPIRE_INFO [=] expr
    | REPLICA_NUM [=] num
    | TABLE_ID [=] id
    | BLOCK_SIZE [=] size
    | USE_BLOOM_FILTER [=] {True | False}
    | STEP_MERGE_NUM [=] num     
    | TABLEGROUP [=] ‘tablegroupname’
    | PRIMARY_ZONE [=] zonelist
    | AUTO_INCREMENT [=] num| PCTFREE [=] integer     
    | LOCALITY [=] locality
partition_options:
     PARTITION BY
       HASH(expr)
       |KEY(column_list)
       PARTITIONS num
       [partition_definition ...]
partition_definition:
  COMMENT [=] 'string'

说明

注意： OceanBase内部数据以b树为索引，按照 Primary Key 排序。OceanBase 1.0 支持创建只有主键列的表，也可以不指定主键，系统会自动生成。

CREATE TABLE 支持 UNIQUE约束；暂不支持创建临时表，暂不支持CHECK约束；不支持创建表的同时从其他表导入功能。

• 使用 IF NOT EXISTS 时，即使创建的表已经存在，也不会报错，如果不指定时，则会报错。

• NOT NULL、DEFAULT、AUTO_INCREMENT 用于列的完整性约束。

• **table_option** 内容请参见表选项，各子句间用“,”隔开。

• index_option 中，可以指定GLOBAL、LOCAL关键字，表述全局或局部索引。默认是 GLOBAL　index。VISIBLE 和 INVISIBLE 关键字，表述可见索引或不可见索引，可见及不可见是对优化器而言的。创建带有Parition的表时index一定要加LOCAL关键字。如果没有加，系统将报错。

表选项

示例

1. 执行以下命令，创建数据库表。

CREATE TABLE test (c1 int primary key, c2 VARCHAR(50)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';

或者也可以执行以下命令来创建数据库表。

CREATE TABLE test (c1 int, c2 VARCHAR(50), primary key(c1)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';

1. 执行以下命令查看表信息。

SHOW tables;
DESCRIBE test;

示例结果如下：

报错

• 语法错误时报 ERROR 1064 (42000): You have an error in your SQL syntax；

• 表名重复，报 ERROR 1050 (42S01): Table 'test' already exists；

• 表名超出限定长度，报 ERROR 1059 (42000): Identifier name 'XXXX' is too long；

• 创建表时，分区名重复，报 ERROR 1517 (HY000): Duplicate partition name XX

mysql> create table employeestest(id int) partition by range(id) (partition dpname values less than (10), partition dPname values less than (20));
ERROR 1517 (HY000): Duplicate partition name 'dPname'

创建生成列

OceanBase支持创建生成列特性，在创建生成列的时候需要定义生成列依赖的表达式，生成列的值来源于被定义的表达式计算结果的值，因此在向表中插入数据的时候，不能为生成列指定要插入的值。

下面的示例将为我们展示生成列的使用，在统计姓名的表格中，我们一般需要分别统计 first name（名）和 lastname（姓），同时我们又希望能够查询到全名，而全名刚好是 first_name·last_name 的结果，这个时候就可以使用生成列特性。

CREATE TABLE t1(
               first_name varchar(100),
               last_name varchar(100),
               full_name varchar(100) as (concat(first_name, '·', last_name))
);
INSERT INTO t1 (first_name, last_name) values('Tom', 'Lee'), ('Peter', 'Bush');

然后查询表中的结果得到如下所示信息：

select * from t1;
+-----------------+---------------+----------------+
| first_name      | last_name     | full_name      |
+-----------------+---------------+----------------+
| Tom             | Lee           | Tom·Lee        |
| Peter           | Bush          | Peter·Bush     |
+-----------------+---------------+----------------+

生成列的定义如下：

col_name [data_type] [GENERATED ALWAYS] AS (expression)
  [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment]
  [[NOT] NULL] [[PRIMARY] KEY]

AS (expression)表示该列是一个生成列，并且expression被用来计算生成列的值，GENERATED ALWAYS关键字可以不用指定，也可以指定该关键字，使得生成列的创建语句变得更加清晰自然。

VIRTUAL or STORED关键字用来指定生成列的值在表中是如何存储的，不同的存储方式可以应用于不同的生成列使用场景中：

• VIRTUAL:生成列的值不会被存储，而是在生成列被查询访问到的时候进行计算，虚拟列不占用存储空间。

• STORED:生成列的值在insert或者updated时被计算，并且会物化到存储中，除了生成列的值是利用表达式计算出来的外，其它的特性和普通的列并没什么区别。

如果在创建生成列的时候没有指定VIRTUAL或者STORED关键字，那么OB默认创建VIRTUAL形式的生成列，其它的关键字用来指定列的其它属性，例如NULL属性，KEY属性，在OB目前并不支持VIRTUAL生成列作为主键。

表达式的计算可能会依赖当前session中一些变量信息，例如常量字符串的参与运算需要依赖session中的charset和collationtype，timestamp类型计算需要依赖当前的timezone信息，生成列表达式的计算所依赖的session中变量信息为创建生成列时所依赖的session变量信息。

除此之外，生成列的定义还要遵循以下规则的约束：

• 生成列表达式(包括操作符以及函数)的结果必须是确定值，确定值的定义是在给定的charset以及collation type或者timezone信息下，一个表达式多次执行的结果是相同的，例如这些表达式就不满足这样的约定：random(), now()

• 生成列表达式中不能包含子查询，变量或者系统参数以及存储过程函数

• 生成列的定义能够依赖其它生成列，但必须是前面已经定义过的生成列

如果生成列在INSERT、REPLACE、UPDATE语句中现实指定了对应的值，那么只能是DEFAULT，其它的常量或者表达式将报错：

OceanBase (root@test)> create table t1(a int primary key, b int, c int as (a+b));
Query OK, 0 rows affected (0.23 sec)
OceanBase (root@test)> insert into t1 values(1, 1, default);
Query OK, 1 row affected (0.02 sec);
OceanBase (root@test)> update t1 set a=2, c=default;        
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0
OceanBase (root@test)> select * from t1;
+---+---------+--------+
| a | b       | c      |
+---+---------+--------+
| 2 | 1       | 3      |
+---+---------+--------+
1 row in set (0.01 sec) 
OceanBase (root@test)> insert into t1 values(1, 1, 1);      
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't1' is not allowed
OceanBase (root@test)> update t1 set a=2, c=3;        
ERROR 3105 (HY000): The value specified for generated column 'c' in table 't1' is not allowed

为了跟 mysql 兼容，我们允许生成列的创建语法有 NOT NULL 约束，但是生成列上的 NOT NULL 约束并不生效，我们允许为生成列定义数据类型，如果生成列的表达式的结果类型和生成列的数据类型不相同，将发生表达式的值向列类型的转换。如果生成列没有指定数据类型，那么我们会使用表达式的推导类型作为生成列的数据类型，因此我们推荐不为生成列指定数据类型。