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]
   [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]

参数

• table_name：表名，以字母或下划线（_）开头，可包含字母、数字以及下划线（_），长度为1到127个字符。

  支持db_name.table_name格式，区分不同数据库下相同名字的表。

• column_name：列名，以字母或下划线（_）开头，可包含字母、数字以及下划线（_），长度为1到127个字符。

• column_type：要添加的列的数据类型，AnalyticDB for MySQL支持的数据类型请参见数据类型。

• column_attributes

  • DEFAULT default_expr：设置列的默认值，DEFAULT为无变量表达式，例如current_timestamp。如果未指定默认值，则列的默认值为NULL。

  • AUTO_INCREMENT：定义自增列，可选项。

    自增列的数据类型必须是bigint类型，AnalyticDB for MySQL为自增列提供唯一值，但自增列的值不是顺序递增。

• column_constraints

  • NOT NULL|NULL：定义了NOT NULL的列不允许值为NULL；定义了NULL（默认值）的列允许值为NULL。

  • PRIMARY KEY ：定义主键。如果有多个主键，语法为PRIMARY KEY(column_name [, ... ])。

• table_constraints

  • INDEX|KEY 倒排索引。AnalyticDB for MySQL默认为表创建全索引，一般情况下无须手动创建索引。

  • PRIMARY KEY：主键索引。

    • 只有定义过主键的表支持DELETE和UPDATE操作。

    • 主键中必须包含分区键，建议把分区键放到组合主键之前。

  • CLUSTERED KEY：聚集索引，定义表中的排序列，聚集索引中键值的逻辑顺序决定了表中相应行的物理顺序，每个表仅支持创建一个聚集索引。

    例如，clustered key col5_col6_cls_index(col5,col6) 定义了col5 col6的聚集索引，col5 col6和col6 col5是不同的聚集索引。

• DISTRIBUTED BY HASH(column_name,...)： 在普通表中定义表的一级分区键，按照column_name的HASH值进行分区。

  AnalyticDB for MySQL支持将多个字段作为分区键。

• DISTRIBUTED BY BROADCAST：用于定义维度表，维度表会在集群的每个节点存储一份数据，因此建议维度表的数据量不宜太大。

• partition_options：普通表中定义分区。AanlyticDB for MySQL通过LIFECYCLE N方式实现表生命周期管理，即对分区进行排序，超出N的分区将被过滤掉。

  例如，PARTITION BY VALUE(column_name) 表示使用column_name的值来做二级分区值，PARTITION BY YEARMONTHDAY(column_name) 表示将column_name格式化为类似20190101的日期格式做二级分区值。LIFECYCLE 365表示每个节点最多保留的分区个数为365，即如果数据保存天数为365天，则第366天写入数据后，系统会自动删除第1天写入的数据。

注意事项

• 创建表时，AnalyticDB for MySQL集群默认编码格式为utf-8，相当于MySQL中的utf8mp4编码，暂不支持其他编码格式。

• 目前AnalyticDB for MySQL集群支持创建的最大表数目是节点组数目*256。

示例

• 新建TEST表。

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

  TEST为一级分区表，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(YEARMONTHDAY(login_time)) LIFECYCLE 30
    COMMENT '客户信息表';

  CUSTOMER表为二级分区表，先按照customer_id进行一级分区，再按照login_time进行二级分区，主键为login_time、customer_id、phone_num。