CREATE TABLE

语法

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'

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,...)]

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

参数

注意事项

• 创建表时，AnalyticDB for MySQL集群默认编码格式为utf-8，相当于MySQL中的utf8mb4编码，暂不支持其他编码格式。
• 目前AnalyticDB for MySQL集群支持创建的最大表数目如下所示：
  • 集群版：min（节点组数量*256,10000）。
  • 基础版：
    • T8，500。
    • T16和T32，1500。
    • T52，2500。

示例

• 新建TEST表。

  create table test (
         id bigint auto_increment,
         name varchar,
         value int,
         ts timestamp
  )
  DISTRIBUTED BY HASH(id)                  

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

• 新建CUSTOMER表。

  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 '客户信息表';                   

  CUSTOMER表为普通表，customer_id为分布键，login_time为分区键，login_time、customer_id、phone_num为组合主键。

MySQL语法兼容性说明

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

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

  mysql> create table t (c1 bigint, c2 int, c3 varchar, primary key(c1,c2));
  Query OK, 0 rows affected (2.37 sec)
  mysql> 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 for MySQL将添加一个__adb_auto_id__字段作为主键和DISTRIBUTED BY COLUMN。

  mysql> create table t (c1 bigint, c2 int, c3 varchar);
  Query OK, 0 rows affected (0.50 sec)
  mysql> 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)

冷热数据分层存储

为了降低数据存储成本，同时保证查询性能，将查询频度高的数据称为热数据，存储在SSD介质；将查询频度低的数据称为冷数据，存储在HDD介质。

通过建表语句指定该表是热数据还是冷数据，示例如下：

create table t1
(a int
, b datetime) 
distribute by hash(a) 
partition by value(date_format('%Y%m') lifecycle 30 
storage_policy = HOT|COLD;

可以通过alter table语句变更该表冷热数据存储属性，示例如下：

alter table t1 storage_policy = HOT|COLD;