# CREATE TABLE

## Syntax

```sql
CreateTableStmt ::=
    'CREATE' 'TABLE' IfNotExists TableName ( 
      TableElementList CreateTableSelectOpt | LikeTableWithOrWithoutParen ) OnCommitOpt

IfNotExists ::=
    ('IF' 'NOT' 'EXISTS')?

TableName ::=
    Identifier ('.' Identifier)?
    
TableElementList ::=
    TableElement ( ',' TableElement )*

TableElement ::=
    ColumnDef
|   ColumnIndex
```

 `CREATE TABLE` 语句用于创建一张表。同一个数据库下，表名在必须是唯一的，在同一个数据库下，重复创建同名表，会发生错误。

建表语句中需要定义`table_element`列表。`table_element`分为列描述`ColumnDef`和`Constraint`。OpenMLDB要求`table_element`列表中至少包含一个`ColumnDef`。

### 相关语法元素

#### 列描述ColumnDef（必要）

```SQL
ColumnDef ::=
    ColumnName ( ColumnType ) [ColumnOptionList]

ColumnName
         ::= Identifier ( '.' Identifier ( '.' Identifier )? )?      
         
ColumnType ::=
						'INT' | 'INT32'
						|'SMALLINT' | 'INT16'
						|'BIGINT' | 'INT64'
						|'FLOAT'
						|'DOUBLE'
						|'TIMESTAMP'
						|'DATE'
						|'STRING' | 'VARCHAR'
						
ColumnOptionList
         ::= ColumnOption*	
ColumnOption 
				 ::= ['DEFAULT' DefaultValueExpr ] ['NOT' 'NULL']
				 	  
DefaultValueExpr
				::= int_literal | float_literal | double_literal | string_literal
```

一张表中包含一个或多个列。每一列的列描述`ColumnDef`描述了列名、列类型以及类配置。

- 列名：列在表中的名字。同一张表内的列名必须是唯一的。
- 列类型：列的类型。想要了解OpenMLDB支持的数据类型，可以参考[数据类型](../data_types/reference.md)。
- 列约束配置：
  - `NOT NULL`: 配置列的不允许为空值。
  - `DEFAULT`: 配置列默认值。`NOT NULL`的属性会同时配置`DEFAULT`默认值，这样的话，查入数据时，若没有定义该列的值，会插入默认值。若配置`NOT NULL`属性且没有配置`DEFAULT`值，插入语句中未定义改列值时，OpenMLDB会抛出错误。

##### Example: 创建一张表

将当前数据库设为`db1`，在当前数据库中创建一张表`t1`，包含列`col0`，列类型为STRING

```sql
CREATE DATABASE db1;
-- SUCCEED: Create database successfully

USE db1;
-- SUCCEED: Database changed

CREATE TABLE t1(col0 STRING);
-- SUCCEED: Create successfully

```

指定在数据库`db1`中创建一张表`t1`，包含列`col0`，列类型为STRING

```sql
CREATE TABLE db1.t1 (col0 STRING, col1 int);
-- SUCCEED: Create successfully
desc t1;
 --- ------- --------- ------ --------- 
  #   Field   Type      Null   Default  
 --- ------- --------- ------ --------- 
  1   col0    Varchar   YES             
  2   col1    Int       YES             
 --- ------- --------- ------ --------- 
 --- -------------------- ------ ---- ------ --------------- 
  #   name                 keys   ts   ttl    ttl_type       
 --- -------------------- ------ ---- ------ --------------- 
  1   INDEX_0_1639524201   col0   -    0min   kAbsoluteTime  
 --- -------------------- ------ ---- ------ --------------- 
```

##### Example: 创建一张表，配置列不允许为空NOT NULL

```sql
USE db1;
CREATE TABLE t1 (col0 STRING NOT NULL, col1 int);
-- SUCCEED: Create successfully
```

```sql
desc t1;
 --- ------- --------- ------ --------- 
  #   Field   Type      Null   Default  
 --- ------- --------- ------ --------- 
  1   col0    Varchar   NO              
  2   col1    Int       YES             
 --- ------- --------- ------ --------- 
 --- -------------------- ------ ---- ------ --------------- 
  #   name                 keys   ts   ttl    ttl_type       
 --- -------------------- ------ ---- ------ --------------- 
  1   INDEX_0_1639523978   col0   -    0min   kAbsoluteTime  
 --- -------------------- ------ ---- ------ --------------- 
```

##### Example: 创建一张表，配置列配置默认值

```sql
USE db1;
CREATE TABLE t1 (col0 STRING DEFAULT "NA", col1 int);
-- SUCCEED: Create successfully
```

```sql
desc t1;
--- ------- --------- ------ --------- 
  #   Field   Type      Null   Default  
--- ------- --------- ------ --------- 
  1   col0    Varchar   NO     NA       
  2   col1    Int       YES             
--- ------- --------- ------ --------- 
--- -------------------- ------ ---- ------ --------------- 
  #   name                 keys   ts   ttl    ttl_type       
--- -------------------- ------ ---- ------ --------------- 
  1   INDEX_0_1639524344   col0   -    0min   kAbsoluteTime  
--- -------------------- ------ ---- ------ --------------- 
```

##### Example: 在同一个数据库下重复创建同名表

```sql
USE db1;
CREATE TABLE t1 (col0 STRING NOT NULL, col1 int);
-- SUCCEED: Create successfully
CREATE TABLE t1 (col1 STRING NOT NULL, col1 int);
-- SUCCEED: Create successfully
```

#### 列索引ColumnIndex（可选）

```sql
ColumnIndex
         ::= 'INDEX' IndexName '(' IndexOptionList ')' 
 
IndexOptionList
         ::= IndexOption ( ',' IndexOption )*
IndexOption
         ::=  'KEY' '=' ColumnNameList 
              | 'TS' '=' ColumnName
              | 
              | 'TTL' = int_literal
              | 'REPLICANUM' = int_literal
  
-- IndexKeyOption
IndexKeyOption 
						::= 'KEY' '=' ColumnNameList 
ColumnNameList
				 :: = '(' ColumnName (',' ColumnName)* ')'
-- IndexTsOption         
IndexTsOption 
						::= 'TS' '=' ColumnName
-- IndexTtlTypeOption					
IndexTtlTypeOption
						::= 'TTL_TYPE' '=' TTLType
TTLType ::= 
						'ABSOLUTE'
						| 'LATEST'
						| 'ABSORLAT'
						| 'ABSANDLAT'
   
-- IndexTtlOption
IndexTtlOption
						::= 'TTL' '=' int_literal|interval_literal

interval_literal ::= int_literal 'S'|'D'|'M'|'H'


```

索引可以被数据库搜索引擎用来加速数据的检索。 简单说来，索引就是指向表中数据的指针。配置一个列索引一般需要配置索引key，索引时间列, TTL和TTL_TYPE。其中索引key是必须配置的，其他配置项都为可选。下表列出了列索引配置项：

| 配置项     | 描述                                                         | 用法示例                                                     |
| ---------- | ------------------------------------------------------------ | ------------------------------------------------------------ |
| `KEY`      | 索引列（必选）。OpenMLDB支持单列索引，也支持联合索引。当`KEY`=一列时，配置的是单列索引。当`KEY`=多列时，配置的是这几列的联合索引，具体来说会将几列按顺序拼接成一个字符串作为索引。 | 单列索引：`INDEX(KEY=col1)`<br />联合索引：`INDEX(KEY=(col1, col2))` |
| `TS`       | 索引时间列（可选）。同一个索引上的数据将按照时间索引列排序。当不显式配置`TS`时，使用数据插入的时间戳作为索引时间。 | `INDEX(KEY=col1, TS=std_time)`。索引列为col1,col1相同的数据行按std_time排序。 |
| `TTL_TYPE` | 淘汰规则（可选）。包括：`ABSOLUTE`, `LATEST`, `ABSORLAT`, `ABSANDLAT`这四种类型。当不显式配置`TTL_TYPE`时，默认使用`ABSOLUTE`过期配置。 | 具体用法可以参考“TTL和TTL_TYPE的配置细则”                    |
| `TTL`      | 最大存活时间/条数（）可选。不同的TTL_TYPE有不同的配置方式。当不显式配置`TTL`时，`TTL=0`。`TTL`为0表示不设置淘汰规则，OpenMLDB将不会淘汰记录。 |                                                              |

TTL和TTL_TYPE的配置细则：

| TTL_TYPE    | TTL                                                          | 描述                                                 | 用法示例                                                     |
| ----------- | ------------------------------------------------------------ | ---------------------------------------------------- | ------------------------------------------------------------ |
| `ABSOLUTE`  | TTL的值代表过期时间。配置值为时间段如`100m, 12h, 1d, 365d`。最大可以配置的过期时间为`15768000m`(即30年) | 当记录过期时，会被淘汰。                             | `INDEX(KEY=col1, TS=std_time, TTL_TYPE=absolute, TTL=100m)`<br />OpenMLDB将会删除100分钟之前的数据。 |
| `LATEST`    | TTL的值代表最大存活条数。即同一个索引下面，最大允许存在的数据条数。最大可以配置1000条 | 记录超过最大条数时，会被淘汰。                       | `INDEX(KEY=col1, TS=std_time, TTL_TYPE=LATEST, TTL=10)`。OpenMLDB只会保留最近10条记录，删除以前的记录。 |
| `ABSORLAT`  | 配置过期时间和最大存活条数。配置值是一个2元组，形如`(100m, 10), (1d, 1)`。最大可以配置`(15768000m, 1000)`。 | 当且仅当记录过期**或**记录超过最大条数时，才会淘汰。 | `INDEX(key=c1, ts=c6, ttl=(120min, 100), ttl_type=absorlat)`。当记录超过100条，**或者**当记录过期时，会被淘汰 |
| `ABSANDLAT` | 配置过期时间和最大存活条数。配置值是一个2元组，形如`(100m, 10), (1d, 1)`。最大可以配置`(15768000m, 1000)`。 | 当记录过期**且**记录超过最大条数时，记录会被淘汰。   | `INDEX(key=c1, ts=c6, ttl=(120min, 100), ttl_type=absandlat)`。当记录超过100条，**而且**记录过期时，会被淘汰 |

##### Example: 创建一张带单列索引的表

```sql
USE db1;
CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1));
-- SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---- ------ --------------- 
  #   name                 keys   ts   ttl    ttl_type       
 --- -------------------- ------ ---- ------ --------------- 
  1   INDEX_0_1639524520   col1   -    0min   kAbsoluteTime  
 --- -------------------- ------ ---- ------ --------------- 
```

##### Example: 创建一张带联合列索引的表

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=(col0, col1)));
-- SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ----------- ---- ------ --------------- 
  #   name                 keys        ts   ttl    ttl_type       
 --- -------------------- ----------- ---- ------ --------------- 
  1   INDEX_0_1639524576   col0|col1   -    0min   kAbsoluteTime  
 --- -------------------- ----------- ---- ------ --------------- 

```

##### Example: 创建一张带单列索引+时间列的表

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time));
-- SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---------- ------ --------------- 
  #   name                 keys   ts         ttl    ttl_type       
 --- -------------------- ------ ---------- ------ --------------- 
  1   INDEX_0_1639524645   col0   std_time   0min   kAbsoluteTime  
 --- -------------------- ------ ---------- ------ --------------- 
```

##### Example: 创建一张带单列索引+时间列的TTL type为abusolute表，并配置ttl为30天

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absolute, TTL=30d));
-- SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---------- ---------- --------------- 
  #   name                 keys   ts         ttl        ttl_type       
 --- -------------------- ------ ---------- ---------- --------------- 
  1   INDEX_0_1639524729   col1   std_time   43200min   kAbsoluteTime  
 --- -------------------- ------ ---------- ---------- --------------- 
```

##### Example: 创建一张带单列索引+时间列的TTL type为latest表，并配置ttl为1

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=latest, TTL=1));
-- SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---------- ----- ------------- 
  #   name                 keys   ts         ttl   ttl_type     
 --- -------------------- ------ ---------- ----- ------------- 
  1   INDEX_0_1639524802   col1   std_time   1     kLatestTime  
 --- -------------------- ------ ---------- ----- ------------- 
```

##### Example: 创建一张带单列索引+时间列的TTL type为absANDlat表，并配置过期时间为30天，最大留存条数为10条

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absandlat, TTL=(30d,10)));
-- SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---------- -------------- ------------ 
  #   name                 keys   ts         ttl            ttl_type    
 --- -------------------- ------ ---------- -------------- ------------ 
  1   INDEX_0_1639525038   col1   std_time   43200min&&10   kAbsAndLat  
 --- -------------------- ------ ---------- -------------- ------------ 

```

##### Example: 创建一张带单列索引+时间列的TTL type为absORlat表，并配置过期时间为30天，最大留存条数为10条

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time, TTL_TYPE=absorlat, TTL=(30d,10)));
--SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ --------- 
  #   Field      Type        Null   Default  
 --- ---------- ----------- ------ --------- 
  1   col0       Varchar     YES             
  2   col1       Int         YES             
  3   std_time   Timestamp   YES             
 --- ---------- ----------- ------ --------- 
 --- -------------------- ------ ---------- -------------- ----------- 
  #   name                 keys   ts         ttl            ttl_type   
 --- -------------------- ------ ---------- -------------- ----------- 
  1   INDEX_0_1639525079   col1   std_time   43200min||10   kAbsOrLat  
 --- -------------------- ------ ---------- -------------- ----------- 
```

##### Example: 创建一张多索引的表
```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col0, TS=std_time), INDEX(KEY=col1, TS=std_time));
--SUCCEED: Create successfully

desc t1;
 --- ---------- ----------- ------ ---------
  #   Field      Type        Null   Default
 --- ---------- ----------- ------ ---------
  1   col0       Varchar     YES
  2   col1       Int         YES
  3   std_time   Timestamp   YES
 --- ---------- ----------- ------ ---------
 --- -------------------- ------ ---------- ------ ---------------
  #   name                 keys   ts         ttl    ttl_type
 --- -------------------- ------ ---------- ------ ---------------
  1   INDEX_0_1648692457   col0   std_time   0min   kAbsoluteTime
  2   INDEX_1_1648692457   col1   std_time   0min   kAbsoluteTime
 --- -------------------- ------ ---------- ------ ---------------
```

#### 表属性TableOptions（可选）

```sql
TableOptions
						::= 'OPTIONS' '(' TableOptionItem (',' TableOptionItem)* ')'

TableOptionItem
						::= PartitionNumOption
						    | ReplicaNumOption
						    | DistributeOption
						    | StorageModeOption
								
-- PartitionNum
PartitionNumOption
						::= 'PARTITIONNUM' '=' int_literal
-- ReplicaNumOption
ReplicaNumOption
						::= 'REPLICANUM' '=' int_literal
						
-- DistributeOption				
DistributeOption
						::= 'DISTRIBUTION' '=' DistributionList
DistributionList
						::= DistributionItem (',' DistributionItem)*
DistributionItem
						::= '(' LeaderEndpoint ',' FollowerEndpointList ')'
LeaderEndpoint 
						::= 	Endpoint	
FollowerEndpointList
						::= '[' Endpoint (',' Endpoint)* ']'
Endpoint
				::= string_literals

-- StorageModeOption
StorageModeOption
						::= 'STORAGE_MODE' '=' StorageMode

StorageMode
						::= 'Memory'
						    | 'HDD'
						    | 'SSD'
```



| 配置项            | 描述                                                                                                                                                               | 用法示例                                                                          |
|----------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------|-------------------------------------------------------------------------------|
| `PARTITIONNUM` | 配置表的分区数。OpenMLDB将表分为不同的分区块来存储。分区是OpenMLDB的存储、副本、以及故障恢复相关操作的基本单元。不显式配置时，`PARTITIONNUM`默认值为8。                                                                      | `OPTIONS (PARTITIONNUM=8)`                                                    |
| `REPLICANUM`   | 配置表的副本数。请注意，副本数只有在Cluster OpenMLDB中才可以配置。                                                                                                                        | `OPTIONS (REPLICANUM=3)`                                                      |
| `DISTRIBUTION` | 配置分布式的节点endpoint配置。一般包含一个Leader节点和若干follower节点。`(leader, [follower1, follower2, ..])`。不显式配置是，OpenMLDB会自动的根据环境和节点来配置`DISTRIBUTION`。                               | `DISTRIBUTION = [ ('127.0.0.1:6527', [ '127.0.0.1:6528','127.0.0.1:6529' ])]` |
| `STORAGE_MODE` | 表的存储模式，支持的模式为`Memory`、`HDD`或`SSD`。不显式配置时，默认为`Memory`。<br/>如果需要支持非`Memory`模式的存储模式，`tablet`需要额外的配置选项，具体可参考[tablet配置文件 conf/tablet.flags](../../../deploy/conf.md)。 | `OPTIONS (STORAGE_MODE='HDD')`                                                |

##### 磁盘表（`STORAGE_MODE` == `HDD`|`SSD`）与内存表（`STORAGE_MODE` == `Memory`）区别
- 目前磁盘表不支持GC操作
- 磁盘表插入数据，同一个索引下如果（`key`, `ts`）相同，会覆盖老的数据；内存表则会插入一条新的数据
- 磁盘表不支持`addindex`和`deleteindex`操作，所以创建磁盘表的时候需要定义好所有需要的索引
（`deploy`命令会自动添加需要的索引，所以对于磁盘表，如果创建的时候缺失对应的索引，则`deploy`会失败）

##### Example: 创建一张带表，配置分片数为8，副本数为3，存储模式为HDD

```sql
USE db1;

CREATE TABLE t1 (col0 STRING, col1 int, std_time TIMESTAMP, INDEX(KEY=col1, TS=std_time)) OPTIONS(partitionnum=8, replicanum=3, storage_mode='HDD');
--SUCCEED: Create successfully

DESC t1;
--- ---------- ----------- ------ ----------
#   Field      Type        Null   Default
 --- ---------- ----------- ------ ---------
  1   col0       Varchar     YES
  2   col1       Int         YES
  3   std_time   Timestamp   YES
 --- ---------- ----------- ------ ---------
 --- -------------------- ------ ---------- ------ ---------------
  #   name                 keys   ts         ttl    ttl_type
 --- -------------------- ------ ---------- ------ ---------------
  1   INDEX_0_1651143735   col1   std_time   0min   kAbsoluteTime
 --- -------------------- ------ ---------- ------ ---------------
 --------------
  storage_mode
 --------------
  HDD
 --------------
```

## 相关SQL

[CREATE DATABASE](../ddl/CREATE_DATABASE_STATEMENT.md)

[USE DATABASE](../ddl/USE_DATABASE_STATEMENT.md)