如何在AnalyticDBMySQL使用CREATE TABLE创建内表_云原生数据仓库AnalyticDB MySQL版-阿里云帮助中心

云原生数据仓库AnalyticDB MySQL版支持通过CREATE TABLE创建表，也支持通过CTAS将查询到的数据写入新表中。

语法

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [storage_policy]
   [block_size]
   [engine]
   [rt_engine]
   [table_properties]
[AS] query_expression
   COMMENT 'string'

column_attributes:
   [DEFAULT default_expr]
   [AUTO_INCREMENT]

column_constraints:
   [{NOT NULL|NULL} ]
   [PRIMARY KEY]

table_constraints:
   [{INDEX|KEY} [index_name] (column_name,...)]
   [PRIMARY KEY [index_name] (column_name,...)]
   [CLUSTERED KEY [index_name] (column_name,...)]
   [[CONSTRAINT [symbol]] FOREIGN KEY  (fk_col_name) REFERENCES tbl_name (pk_col_name)]

table_attribute:
   DISTRIBUTED BY HASH(column_name,...) | DISTRIBUTED BY BROADCAST

partition_options:
  PARTITION BY 
        {VALUE(column_name) | VALUE(date_format(column_name, ?))}
  LIFECYCLE N

storage_policy:
  STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

block_size:
  BLOCK_SIZE= VALUE

engine:
  ENGINE= 'XUANWU'

rt_engine：
  RT_ENGINE= 'COLUMNSTORE'

table_properties:
  TABLE_PROPERTIES = '{"format":"columnstore"}'

参数

参数	说明
`table_name`	表名。表名以字母或下划线（_）开头，可包含字母、数字以及下划线（_），长度为1到127个字符。支持`db_name.table_name`格式，区分不同数据库下相同名字的表。
`column_name`	列名。列名以字母或下划线（_）开头，可包含字母、数字以及下划线（_），长度为1到127个字符。
`column_type`	要添加的列的数据类型。 AnalyticDB MySQL版支持的数据类型，请参见基础数据类型。
`column_attributes`	`DEFAULT default_expr`：设置列的默认值，`DEFAULT`为无变量表达式，例如`current_timestamp`。如果未指定默认值，则列的默认值为`NULL`。 `AUTO_INCREMENT`：定义自增列，可选项。自增列的数据类型必须是`BIGINT`类型，AnalyticDB MySQL版为自增列提供唯一值，但自增列的值不是顺序递增。不支持设置`AUTO_INCREMENT`从1开始。
`column_constraints`	`NOT NULL`\|`NULL`：定义了`NOT NULL`的列不允许值为`NULL`；定义了`NULL`（默认值）的列允许值为`NULL`。 `PRIMARY KEY`：定义主键。如果有多个主键，语法为`PRIMARY KEY(column_name [, ... ])`。
`table_constraints`	说明云原生数据仓库AnalyticDB MySQL版不支持创建唯一索引。 `INDEX\|KEY`：定义索引。 AnalyticDB MySQL版默认为表创建全索引，一般情况下无须手动创建索引。如果您指定了索引，则AnalyticDB MySQL版不会自动创建索引。 `PRIMARY KEY`：定义主键索引。只有定义过主键的表支持DELETE和UPDATE操作。主键中必须包含分布键和分区键，建议将分区键和分布键放在组合主键的前部。 `CLUSTERED KEY`：聚集索引，定义表中的排序列，聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序。例如，`clustered key col5_col6_cls_index(col5,col6)`定义了`col5 col6`的聚集索引，`col5 col6`和`col6 col5`是不同的聚集索引。聚集索引会将该列或者多列进行排序，保证与该列相同或者相近的数据存储在磁盘的相同或相近位置。当以聚集列做为查询条件时，查询结果存储在磁盘的相同位置，这样可以减少磁盘的IO，提高查询性能。如何判断是否需要聚集索引：查询一定会携带的字段可以作为聚集索引。例如，SaaS类应用中，用户通常只访问自己的数据，用户ID可以定义为聚集索引，保证数据的局部性，提升数据查询性能。聚集列有以下限制：每张表中只支持创建一个聚集列索引。由于聚集索引会进行全表排序，导致数据写入性能下降、CPU占用较高，因此一般不建议使用聚集索引。定义了聚集索引列的表，通过`SHOW CREATE TABLE`查询建表语句时，不会展示`CLUSTERED KEY`信息。
`table_attribute`	`DISTRIBUTED BY HASH(column_name,...)`：在普通表中定义表的分布键，按照`column_name`的HASH值进行分片。 AnalyticDB MySQL版支持将多个字段作为分布键。 AnalyticDB MySQL版不支持修改分布键。如果需要修改分布键，请参见更改分区键/分布键。 `DISTRIBUTED BY BROADCAST`：定义维度表，维度表会在集群的每个节点存储一份数据，因此建议维度表的数据量不宜太大。
`partition_options`	`PARTITION BY`：普通表中定义分区。通过`LIFECYCLE N`方式实现表生命周期管理，即对分区进行排序，超出`N`的分区将被过滤掉。可以通过如下语句，查询表的分区信息： `SELECT shard_name, table_name, partition_id, row_count FROM INFORMATION_SCHEMA.KEPLER_CSTORE_PARTITION_XIHE_MERGED WHERE table_name like upper("%中文字符改成表名%") ORDER BY row_count DESC;` 例如，`PARTITION BY VALUE(column_name)`表示使用`column_name`的值来做分区，`PARTITION BY VALUE(DATE_FORMAT(column_name, '%Y%m%d'))`表示将`column_name`格式化为类似`20190101`的日期格式做分区。`LIFECYCLE 365`表示每个节点最多保留的分区个数为365，即如果数据保存天数为365天，则第366天写入数据后，系统会自动删除第1天写入的数据。说明 AnalyticDB MySQL版集群的节点数说明：数仓版（3.0）预留模式集群版集群的节点数=集群列表页显示的节点组个数。数仓版（3.0）弹性模式集群版（新版）集群的节点数=EIU数。湖仓版（3.0）集群的节点数=存储预留资源的组数（一组存储预留资源约等于24 ACU）。 AnalyticDB MySQL版不支持修改分区键。如果需要修改分区键，请参见更改分区键/分布键。说明二级分区不是整张表级，而是每个Shard级别。如果数据分布不均，则会导致整张表保留的二级保存分区数大于N。二级分区不是实时清理的，是后台异步任务清理的。当您使用`PARTITION BY`指定二级分区时，必须定义`LIFECYCLE N`，否则会报错。若不指定二级分区，数据不会被清理。
storage_policy	说明目前仅弹性模式集群版（新版）实例支持冷热数据分层存储功能。 `STORAGE_POLICY`：指定热（HOT）、冷（COLD）、或混合（MIXED）的存储策略。默认值为HOT。不同存储策略下数据读写性能不同，存储成本不同。为了降低数据存储成本，同时还要保证查询性能，您可以选择将查询频度高的数据（称为热数据）存储在SSD介质；将查询频度低的数据（称为冷数据）存储在OSS上。根据业务需求，您还可以按表粒度、表的二级分区粒度独立选择冷、热存储介质。例如，指定这个表数据全部存储在SSD，或者全部存储在OSS，或者指定这个表的一部分二级分区存储在SSD，另一部分二级分区存储在OSS。 HOT、COLD、MIXED大小写兼容。 HOT: 所有分区都在SSD。 COLD: 所有分区都在OSS。 MIXED: 部分分区在SSD，部分分区在OSS，需要通过`hot_partition_count`指定存在SSD上的分区的数量。
hot_partition_count=N	指定MIXED存储策略时热分区的个数。表示按分区键的值的大小倒序排列，最大N个分区为热分区，其他分区为冷分区。 N为非零正整数。指定MIXED策略时，必须同时指定热分区的个数；其他策略禁止指定`hot_partition_count=N`。
block_size	指定列式存储中每个block存储的Value的个数，也是最小的IO单元，默认值取值说明：弹性模式集群版（新版）且计算资源为32核以下（不包括32核）的集群block_size默认值为8192。维度表的block_size默认值为4096。其它情况下block_size默认值为32760。当block_size为32760，在`SHOW CREATE TABLE <table_name>;`时，block_size不做显示。调大或调小会使得每次IO读取的Value个数变大或变小，具体产生的影响需要结合查询特征，例如点查询时，若block_size较大，存储读block的效率会降低，此时可以适当调小block_size。重要若不熟悉列式存储原理，建议不要进行更改。
engine	AnalyticDB MySQL版内表的存储引擎类型，固定取值为`XUANWU`。关于玄武存储引擎的详细信息，请参见玄武分析型存储。
rt_engine	指定实时存储引擎类型。取值为COLUMNSTORE。当表的实时存储引擎为COLUMNSTORE时，`SHOW CREATE TABLE`时不会展示RT_ENGINE信息。
table_properties	指定AnalyticDB MySQL版内表全量数据的格式为列存，固定取值为`{"format":"columnstore"}`。说明 3.1.9.5以下内核版本的集群，如果在创建内表时显式指定了`engine=XUANWU`，则需同时显示指定`"format":"columnstore`，否则建表会失败。

注意事项

创建表时，AnalyticDB MySQL版集群默认编码格式为utf-8，相当于MySQL中的utf8mb4编码，暂不支持其他编码格式。
AnalyticDB MySQL版不支持unsigned约束。
目前AnalyticDB MySQL版集群支持创建的最大表数量如下。
- 每个预留模式集群（具备1～20个节点组）中表数量的最大值：80000/(Shard个数/节点组数)张。
- 每个弹性模式集群中内表数量的最大值：[80000/(Shard个数/EIU个数)]*2张。
- 每个弹性模式集群中外表数量的最大值：50万张。
说明
- 执行SQL语句查询Shard个数：SELECT count(1) FROM information_schema.kepler_meta_shards;。
- 公式中的Shard个数/节点组数和Shard个数/EIU个数向上取整。
- 扩容节点组数量可以增加预留模式集群表数量的最大值，扩容节点组请参见数仓版扩缩容与跨规格变配。
- 扩容EIU数量可以增加弹性集群内表数量的最大值，扩容EIU请参见弹性IO资源（EIU）扩容。

示例

新建普通表test，id为自增列，分布键为id，按照id值进行HASH分区。

CREATE TABLE test (
       id bigint auto_increment,
       name varchar,
       value int,
       ts timestamp
)
DISTRIBUTED BY HASH(id);

新建普通表customer， customer_id为分布键，login_time为分区键，并按login_time排序，数据保存天数为30天，则第31天写入数据后，系统会自动删除第1天写入的数据，login_time、customer_id、phone_num为组合主键。

CREATE TABLE customer (
customer_id bigint NOT NULL COMMENT '顾客ID',
customer_name varchar NOT NULL COMMENT '顾客姓名',
phone_num bigint NOT NULL COMMENT '电话',
city_name varchar NOT NULL COMMENT '所属城市',
sex int NOT NULL COMMENT '性别',
id_number varchar NOT NULL COMMENT '身份证号码',
home_address varchar NOT NULL COMMENT '家庭住址',
office_address varchar NOT NULL COMMENT '办公地址',
age int NOT NULL COMMENT '年龄',
login_time timestamp NOT NULL COMMENT '登录时间',
PRIMARY KEY (login_time,customer_id，phone_num)
 )
DISTRIBUTED BY HASH(customer_id)
PARTITION BY VALUE(DATE_FORMAT(login_time, '%Y%m%d')) LIFECYCLE 30
COMMENT '客户信息表';

新增一个名为store_returns的表，通过使用外键语法FOREIGN KEY将sr_item_sk列和customer表的主键列customer_id关联起来。

CREATE TABLE store_returns
(
  sr_sale_id bigint,
  sr_store_sk bigint,
  sr_item_sk bigint NOT NULL,
  FOREIGN KEY (sr_item_sk) REFERENCES item (customer_id)
);

MySQL语法兼容性说明

AnalyticDB MySQL版标准建表语法中必须包含DISTRIBUTED BY ...，而MySQL建表语法中没有DISTRIBUTED BY ...。AnalyticDB MySQL版默认兼容MySQL建表语法，您可以根据实际情况通过以下两种方式处理DISTRIBUTED BY ...不兼容问题。

如果MySQL表含有主键，AnalyticDB MySQL版默认将主键作为DISTRIBUTED BY COLUMN。

CREATE TABLE t (c1 bigint, c2 int, c3 varchar, PRIMARY KEY(c1,c2));

SHOW CREATE TABLE t;

+-------+---------------------------------------------------------------------------+
| Table | Create Table                                                              |
+-------+---------------------------------------------------------------------------+
| t     | Create Table `t` (
 `c1` bigint,
 `c2` int,
 `c3` varchar,
 primary key (c1,c2)
) DISTRIBUTED BY HASH(`c1`,`c2`) INDEX_ALL='Y' |
+-------+----------------------------------------------------------------------------+
1 row in set (0.04 sec)

如果MySQL表不含主键，AnalyticDB MySQL版将添加一个__adb_auto_id__字段作为主键和DISTRIBUTED BY COLUMN。

CREATE TABLE t (c1 bigint, c2 int, c3 varchar);

SHOW CREATE TABLE t;

+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                 |
+-------+----------------------------------------------------------------------------------------------+
| t     | Create Table `t` (
 `c1` bigint,
 `c2` int,
 `c3` varchar,
 `__adb_auto_id__` bigint AUTO_INCREMENT,
 primary key (__adb_auto_id__)
) DISTRIBUTED BY HASH(`__adb_auto_id__`) INDEX_ALL='Y' |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)

创建表时指定冷热数据存储策略

说明

目前仅弹性模式集群版（新版）实例支持冷热数据分层存储功能。

语法

CREATE TABLE [IF NOT EXISTS] table_name
  ({column_name column_type [column_attributes] [ column_constraints ] [COMMENT 'string']
  | table_constraints}
   [, ... ]  )
   table_attribute
   [partition_options]
   [storage_policy]
   [AS] query_expression
   COMMENT 'string'
   
storage_policy:
   STORAGE_POLICY= 'HOT'|'COLD'|'MIXED' [hot_partition_count=N]

示例

创建表时指定冷（COLD）存储策略

CREATE TABLE test_table (
 L_ORDERKEY bigint NOT NULL,
 L_LINENUMBER int NOT NULL,
 L_SHIPDATE date NOT NULL,
 dummy varchar,
 primary key (l_orderkey,l_linenumber,l_shipdate)
) DISTRIBUTE BY HASH(l_orderkey) 
PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
STORAGE_POLICY='COLD';

创建表时指定热（HOT）存储策略

CREATE TABLE test_table (
 L_ORDERKEY bigint NOT NULL,
 L_LINENUMBER int NOT NULL,
 L_SHIPDATE date NOT NULL,
 dummy varchar,
 primary key (l_orderkey,l_linenumber,l_shipdate)
) DISTRIBUTE BY HASH(l_orderkey) 
PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
STORAGE_POLICY='HOT';

创建表时指定混合（MIXED）存储策略，同时指定热分区数量为16个

CREATE TABLE test_table (
 L_ORDERKEY bigint NOT NULL,
 L_LINENUMBER int NOT NULL,
 L_SHIPDATE date NOT NULL,
 dummy varchar,
 primary key (l_orderkey,l_linenumber,l_shipdate)
) DISTRIBUTE BY HASH(l_orderkey) 
PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) LIFECYCLE 200 INDEX_ALL='Y' 
STORAGE_POLICY='MIXED' HOT_PARTITION_COUNT=16;

创建向量索引表

创建向量索引表的语法、参数说明以及示例，请参见创建向量索引。