开发指南—DDL语句—分区表语法—CREATE TABLE

CREATE [PARTITION] TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
    [table_options]
    [table_partition_definition]
create_definition:
    col_name column_definition
  | mysql_create_definition
  | [UNIQUE] GLOBAL INDEX index_name [index_type] (index_sharding_col_name,...)
      [global_secondary_index_option]
      [index_option] ...
index_sharding_col_name:
    col_name [(length)] [ASC | DESC]
index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
index_type:
    USING {BTREE | HASH}
# 全局二级索引相关
global_secondary_index_option:
    [COVERING (col_name,...)]
    [partition_options]
# 分区表类型定义
table_partition_definition:
    single
  | broadcast
  | partition_options
# 分区策略定义
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE{({column_name | partition_func(column_name)}) 
        | RANGE COLUMNS(column_list)}
        | LIST{({column_name | partition_func(column_name)}) 
        | LIST COLUMNS(column_list)} }
    partition_list_spec
# 分区函数定义
partition_func：
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH
# 分区列表定义
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list
# Hash / Key 分区表列定义
hash_partition_list:
  PARTITIONS partition_count
# Range / Range Columns 分区表列定义
range_partition_list:
    range_partition [, range_partition ...]
range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]
# List / List Columns 分区表列定义
list_partition_list:
    list_partition [, list_partition ...]
list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]
partition_spec_options:
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string']
        [{CHARSET | CHARACTER SET} [=] charset]
        [COLLATE [=] collation]
        [TABLEGROUP [=] table_group_id]
        [LOCALITY [=] locality_option]
locality_option:
    'dn=storage_inst_id_list'
storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]

CREATE PARTITION TABLE auto_part_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
);

CREATE TABLE single_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) SINGLE;

CREATE TABLE broadcast_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30), 
 primary key(id)
) BROADCAST;

CREATE TABLE hash_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
partition by  hash(bid) 
partitions 8;

CREATE TABLE hash_tbl_todays(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY HASH(TO_DAYS(birthday)) 
PARTITIONS 8;

CREATE TABLE key_tbl(
 id bigint not null auto_increment, 
 bid int, 
 name varchar(30),
 birthday datetime not null,
 primary key(id)
) 
PARTITION BY KEY(id, birthday) 
PARTITIONS 8;

CREATE TABLE orders(
 id int, 
 order_time datetime not null) 
PARTITION BY RANGE(to_days(order_time)) 
(
  PARTITION p1 VALUES LESS THAN (to_days('2021-01-01')),
  PARTITION p2 VALUES LESS THAN (to_days('2021-04-01')),
  PARTITION p3 VALUES LESS THAN (to_days('2021-07-01')),
  PARTITION p4 VALUES LESS THAN (to_days('2021-10-01')),
  PARTITION p5 VALUES LESS THAN (to_days('2022-01-01'))
);

CREATE TABLE orders(
 order_id int, 
 order_time datetime not null) 
PARTITION BY range columns(order_id,order_time) 
(
  PARTITION p1 VALUES LESS THAN (10000,'2021-01-01'),
  PARTITION p2 VALUES LESS THAN (20000,'2021-01-01'),
  PARTITION p3 VALUES LESS THAN (30000,'2021-01-01'),
  PARTITION p4 VALUES LESS THAN (40000,'2021-01-01'),
  PARTITION p5 VALUES LESS THAN (50000,'2021-01-01')
);

CREATE TABLE orders(
 id int, 
 order_region varchar(64),
 order_time datetime not null) 
PARTITION BY LIST(YEAR(order_time)) 
(
  PARTITION p1 VALUES IN (1990,1991,1992,1993,1994,1995,1996,1997,1998,1999),
  PARTITION p2 VALUES IN (2000,2001,2002,2003,2004,2005,2006,2007,2008,2009),
  PARTITION p3 VALUES IN (2010,2011,2012,2013,2014,2015,2016,2017,2018,2019)
);

CREATE TABLE orders(
 id int, 
 order_region varchar(64),
 order_time datetime not null) 
PARTITION BY LIST COLUMNS(order_region) 
(
  PARTITION p1 VALUES IN ('Hangzhou', 'Shenzhen'),
  PARTITION p2 VALUES IN ('Beijing', 'Shanghai'),
  PARTITION p3 VALUES IN ('Qingdao')
);