CREATE TABLE建表语法

AnalyticDB for MySQL的存储压缩由系统自动管理，建表时无法通过语法指定压缩算法或压缩级别。如果您想节省存储空间，可以指定STORAGE_POLICY：COLD数据存储在OSS，以节省成本，HOT数据存储在SSD，更注重查询性能。

自增列的值不是顺序递增，也不支持从1开始递增。但自增列的值都是唯一值。

根据分布键值的HASH结果不同，数据被分散到不同的分片上。在分片上，根据分区键值的不同，数据被划分为不同的分区。示意图如下。

• 创建分区表，不是必须手动指定分布键。如果未手动指定分布键，AnalyticDB for MySQL会采纳主键作为分布键。无主键时，自动生成一列__adb_auto_id__作为分布键和主键。

• 创建复制表，不需要指定分布键，但需要指定DISTRIBUTED BY BROADCAST，表示每个存储节点都存储一份全量数据。

变配不会改变集群的分片数（Shard数量）。

执行SQL查询表的分区信息：

SELECT partition_id, --分区名
 row_count, -- 分区总行数
 local_data_size, --分区本地存储所占用空间大小
 index_size, -- 分区的索引大小
 pk_size, --分区的主键索引大小
 remote_data_size --分区的远端存储所占用空间大小
FROM information_schema.kepler_partitions
WHERE schema_name = '$DB'
 AND table_name ='$TABLE' 
 AND partition_id > 0;

创建分区表后，查不到分区信息主要有两个原因：

• 建表时，只是通过定义分区键设置了数据分区的规则，并没有开始创建分区。分区由分区键的键值决定。如果表还没写入数据，分区键值为空，不会创建分区。

• 分区不是实时构建的。当写入的数据BUILD完成后，才能查看到分区信息。

解决方法：

请先写入数据，并等待BUILD任务完成。BUILD任务完成后，可以查看分区信息。

您可以通过查询的过滤条件WHERE <分区键名称> = '<分区键值>'，查询指定分区的数据。不支持类似用法：SELECT * FROM table PARTITION(202304)。

完整使用示例如下。

已创建分区表orders_demo，并按日期order_date分区，建表语句示例如下：

CREATE TABLE orders_demo (
  order_id BIGINT NOT NULL COMMENT '订单ID',
  customer_id INT NOT NULL COMMENT '顾客ID',
  order_status VARCHAR(1) NOT NULL COMMENT '订单状态',
  total_price DECIMAL(15, 2) NOT NULL COMMENT '订单金额',
  order_date DATE NOT NULL COMMENT '订单日期',
  PRIMARY KEY(order_id,order_date)
)
DISTRIBUTED BY HASH(order_id) 
PARTITION BY VALUE(date_format(order_date, '%Y%m')) LIFECYCLE 30 ;

向表中写入10行示例数据，示例如下：

INSERT INTO orders_demo (order_id, customer_id, order_status, total_price, order_date)
VALUES
  (1001, 1, 'C', 150.75, '2023-10-01'),
  (1002, 2, 'P', 200.50, '2023-10-01'),
  (1003, 3, 'S', 99.99, '2023-10-01'),
  (1004, 4, 'C', 300.00, '2023-10-01'),
  (1005, 5, 'P', 450.25, '2023-10-02'),
  (1006, 6, 'S', 120.00, '2023-10-02'),
  (1007, 7, 'C', 80.50, '2023-10-03'),
  (1008, 8, 'P', 600.00, '2023-10-03'),
  (1009, 9, 'S', 250.75, '2023-10-03'),
  (1010, 10, 'C', 199.99, '2023-10-14');

手动Build分区表，完成分区的构建，示例如下：

BUILD TABLE orders_demo;

表满足一定条件后，可以自动完成BUILD。本示例为了更方便地体验后续步骤，选择手动Build表。

查询Build状态。当status字段返回FINISH时，说明orders_demo表已完成Build。

SELECT table_name, schema_name, status FROM INFORMATION_SCHEMA.KEPLER_META_BUILD_TASK WHERE table_name='ORDERS_DEMO';

本示例中，分区键order_date是DATE日期类型，查询2023-10-01分区数据，可以使用如下SQL：

SELECT * FROM orders_demo WHERE order_date='2023-10-01';

假如分区键是DATETIME类型，查询2023-10-01分区数据，查询条件需为WHERE order_date >= "2023-10-01 00:00:00" and order_date < "2023-10-02 00:00:00"。示例如下：

SELECT * FROM orders_demo WHERE order_date >= "2023-10-01 00:00:00" and order_date < "2023-10-02 00:00:00";

如果表设置了分区键，查询时不要求过滤条件中必须包含分区键。但将分区键作为过滤条件可以显著加速查询，因为只需扫描相关分区，而无需遍历整个表，从而大幅提升查询性能。

分区键的数据类型可以是数值类型、日期时间类型或表示数字的字符串类型。除此以外的数据类型，可能导致数据写入报错。

如果写入数据时，遇到报错partition format function error，说明写入分区键的值不符合数据类型的要求。

不能。定义分区键仅支持三种方法：PARTITION BY VALUE（column）、PARTITION BY VALUE(DATE_FORMAT(column,'format'))或PARTITION BY VALUE(FROM_UNIXTIME(column,'format'))。使用其他函数会报错。

通过SHOW CREATE TABLE <table_name>，查看建表语句。建表语句中显示分区表的生命周期。

可能有两个原因：

• 分区刚刚过期，尚未被删除。过期的分区数据不会被立即删除。当该表的BUILD任务完成后，过期的分区数据才会被删除。

• 3.2.1.1以下内核版本中创建的表，LIFECYCLE的定义为一个分片上保留多少个分区。当一个分片上，实际分区数小于LIFECYCLE设定值时，可能出现该现象。在3.2.1.1及以上内核版本中新建的表，不存在该问题。

  例如：

  • 数据分布不均。假设按日期分区，分片1有分区20231201、20231202、依次类推到20231230；分片2有分区20231202、20231203、依次类推到20231231。分片1和分片2上的分区数均为30，没有超过LIFECYCLE的设定值（30），因此分片1和分片2都不会删除分区。查询数据时，可以查到日期为20231201～20231231的数据。

  • 长时间无数据写入。假设按日期分区，分片1已有分区20231201、20231202、20231203和20231204，并且在20231204后该表没有新的分区数据写入。此时分片1仅有4个分区，没有超过LIFECYCLE的设定值（30），因此不会删除分区。在20231231后，仍然可以查到日期为20231201的数据。

不会。分区不是实时构建和清理的。表的某个分区过期后，该表需要完成BUILD，分区才会被清理。

通过SHOW CREATE TABLE查询建表语句中定义的聚集索引。

AnalyticDB for MySQL不支持唯一索引（UNIQUE INDEX）。但AnalyticDB for MySQL的主键索引就是唯一索引，可以确保主键值在表中是唯一的。

不支持多列复合索引。一个普通索引，只能包含一个列，例如INDEX(column1)。

新写入的数据会先进入实时引擎，主键与索引需在BUILD任务完成后才会物化到分区。若空间总览中主键索引、普通索引的大小显示为0，通常是尚未执行或未完成BUILD。可执行BUILD TABLE 表名;，待任务完成后重新查看。

TABLE_PROPERTIES='{"format":"columnstore"}'是固定取值，表示ENGINE中的数据是列存格式。建表时您无需手动指定该属性。

不支持。

ALTER TABLE支持变更以下参数：

• table_name、column_name、column_type、COMMENT

• 增加和删除列（主键列除外）

• 列的默认值

• NOT NULL变更为NULL

• 增加和删除INDEX索引

• 分区函数的日期格式

• 生命周期

• 存储策略

具体用法请参见ALTER TABLE。

其他参数在建表后无法变更。

以下属性建表后不可修改，如需修改请重建表并迁移数据：

• 主键：不能增加、减少或变更主键列。

• 分布键：不能增加、减少或变更分布键列。

• 分区键：不能增加分区键（即不能将非分区表变更为分区表），也不支持增加、减少或修改分区键中的列。

• 存储引擎：不能切换存储引擎（XUANWU和XUANWU_V2之间不能互相切换）。

更多信息，请参见快速建表指南。

一个AnalyticDB for MySQL集群的表数量上限如下：

• 企业版集群：80000/(分片数/预留资源节点数/3)。分片数/预留资源节点数/3向上取整。增加预留资源节点数，可提高内表数量的上限。

• 基础版集群：80000/(分片数/预留资源节点数)。(分片数/预留资源节点数)向上取整。增加预留资源节点数，可提高内表数量的上限。

• 企业版、基础版及湖仓版和数仓版弹性模式集群的外表数量上限：50万。

• 湖仓版集群的内表数量上限：[80000/(分片数/存储预留资源的组数)]*2。一组存储预留资源为24 ACU。假设集群的存储预留资源为48 ACU，则存储预留资源的组数为2。扩容存储预留资源，可提高内表数量的上限。

• 数仓版弹性模式集群的内表数量上限：[80000/(分片数/EIU数量)]*2。分片数/EIU数量向上取整。EIU又叫弹性IO资源。增加EIU数量，可提高内表数量的上限。

• 数仓版预留模式集群（具备1～20个节点组）：80000/(分片数/节点组数量)。分片数/节点组数量向上取整。增加节点组数量，可提高内表数量的上限。

AnalyticDB for MySQL默认的字符集为utf-8，相当于MySQL的utf8mb4字符集，暂不支持其他字符集。

您可以通过SHOW CREATE TABLE db_name.table_name;语句查看该表的DDL语句。若您在DDL语句中未找到ENGINE参数，或者ENGINE参数的取值为XUANWU或XUANWU_V2，则表示该表为内表，反之则为外表。

完整的使用限制请参见使用限制。以下是建表时常见的数量限制：