Create a partitioned table

This topic describes the method of creating a partitioned table and other related items.

Overview

The following types of partitioned tables are supported: RANGE, RANGE COLUMNS, LIST, LIST COLUMNS, HASH, and KEY.

Create a RANGE- or RANGE COLUMNS-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[, column_name column_type])
  PARTITION BY { RANGE (expr(column_name) | column_name)
                | RANGE COLUMNS(column_name [,column_name])
                }
    (
     PARTITION partition_name VALUES LESS THAN(expr)
     [, PARTITION partition_name VALUES LESS THAN (expr )...]
     [, PARTITION partition_name VALUES LESS THAN (MAXVALUE)]
     );

Note

When you create a RANGE-partitioned table, comply with the following rules:

• In MySQL mode, the result of the expr expression in the PARTITION BY RANGE ( expr )clause must be an integer. To use temporal columns for RANGE partitioning, ensure that values in the columns are of the TIMESTAMP data type and use the UNIX_TIMESTAMP function to convert a temporal value to a numerical value. You can also use RANGE COLUMNS partitioning for the same result. In this case, the result of the expression for the partitioning key does not have to be an integer.
• A VALUES LESS THAN clause must be specified for each partition. This clause specifies a non-inclusive upper bound for the partition. Values of the partitioning key equal to or higher than this upper bound are added to the next higher partition.
• All partitions, except the first one, have an implicit lower bound, which is the upper bound of the previous partition.
• A MAXVALUE literal can be defined only for the last partition. MAXVALUE represents a virtual infinite value that is always greater than other possible values for the partitioning key, including the NULL value. If MAXVALUE is specified for the last RANGE partition, you cannot add a new partition.

Parameters

Examples

• Create a RANGE-partitioned table named tbl1_r.

  obclient> CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
         PARTITION BY RANGE(UNIX_TIMESTAMP(log_date))
          (PARTITION M197001 VALUES LESS THAN(UNIX_TIMESTAMP('1970/02/01'))
         , PARTITION M197002 VALUES LESS THAN(UNIX_TIMESTAMP('1970/03/01'))
         , PARTITION M197003 VALUES LESS THAN(UNIX_TIMESTAMP('1970/04/01'))
         , PARTITION M197004 VALUES LESS THAN(UNIX_TIMESTAMP('1970/05/01'))
         , PARTITION M197005 VALUES LESS THAN(UNIX_TIMESTAMP('1970/06/01'))
         , PARTITION M197006 VALUES LESS THAN(UNIX_TIMESTAMP('1970/07/01'))
         , PARTITION M197007 VALUES LESS THAN(UNIX_TIMESTAMP('1970/08/01'))
         , PARTITION M197008 VALUES LESS THAN(UNIX_TIMESTAMP('1970/09/01'))
         , PARTITION M197009 VALUES LESS THAN(UNIX_TIMESTAMP('1970/10/01'))
         , PARTITION M197010 VALUES LESS THAN(UNIX_TIMESTAMP('1970/11/01'))
         , PARTITION M197011 VALUES LESS THAN(UNIX_TIMESTAMP('1970/12/01'))
         , PARTITION M197012 VALUES LESS THAN(UNIX_TIMESTAMP('1971/01/01'))
          );
  Query OK, 0 rows affected
  

  View the tbl1_r partitioned table.

  obclient>show create table tbl1_r\G
  *************************** 1. row ***************************
         Table: tbl1_r
  Create Table: CREATE TABLE `tbl1_r` (
    `log_id` bigint(20) NOT NULL,
    `log_value` varchar(50) DEFAULT NULL,
    `log_date` timestamp NOT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range(UNIX_TIMESTAMP(log_date))
  (partition M197001 values less than (1580486400),
  partition M197002 values less than (1582992000),
  partition M197003 values less than (1585670400),
  partition M197004 values less than (1588262400),
  partition M197005 values less than (1590940800),
  partition M197006 values less than (1593532800),
  partition M197007 values less than (1596211200),
  partition M197008 values less than (1598889600),
  partition M197009 values less than (1601481600),
  partition M197010 values less than (1604160000),
  partition M197011 values less than (1606752000),
  partition M197012 values less than (1609430400))
  1 row in set
  

  Insert data into the tbl1_r table.

  obclient> INSERT INTO tbl1_r VALUES (10,'10','1970/04/01 00:00:00.000000');
  Query OK, 1 row affected
  

  View the partition into which data is inserted.

  obclient> SELECT * FROM tbl1_r partition(M197004);
  +--------+-----------+---------------------+
  | log_id | log_value | log_date            |
  +--------+-----------+---------------------+
  |     10 | 10        | 1970-04-01 00:00:00 |
  +--------+-----------+---------------------+
  1 row in set
  

• Create a RANGE COLUMNS-partitioned table tbl1_log_rc.

  obclient> CREATE TABLE tbl1_log_rc (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date DATE NOT NULL)
         PARTITION BY RANGE COLUMNS(log_date)
          (PARTITION M197001 VALUES LESS THAN('1970/02/01')
         , PARTITION M197002 VALUES LESS THAN('1970/03/01')
         , PARTITION M197003 VALUES LESS THAN('1970/04/01')
         , PARTITION M197004 VALUES LESS THAN('1970/05/01')
         , PARTITION M197005 VALUES LESS THAN('1970/06/01')
         , PARTITION M197006 VALUES LESS THAN('1970/07/01')
         , PARTITION M197007 VALUES LESS THAN('1970/08/01')
         , PARTITION M197008 VALUES LESS THAN('1970/09/01')
         , PARTITION M197009 VALUES LESS THAN('1970/10/01')
         , PARTITION M197010 VALUES LESS THAN('1970/11/01')
         , PARTITION M197011 VALUES LESS THAN('1970/12/01')
         , PARTITION M197012 VALUES LESS THAN('1971/01/01')
         , PARTITION MMAX VALUES LESS THAN MAXVALUE
          );
  Query OK, 0 rows affected
  

  View the tbl1_log_rc partitioned table.

  obclient> show create table tbl1_log_rc\G
  *************************** 1. row ***************************
         Table: tbl1_log_rc
  Create Table: CREATE TABLE `tbl1_log_rc` (
    `log_id` bigint(20) NOT NULL,
    `log_value` varchar(50) DEFAULT NULL,
    `log_date` date NOT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by range columns(log_date)
  (partition M197001 values less than ('1970-02-01'),
  partition M197002 values less than ('1970-03-01'),
  partition M197003 values less than ('1970-04-01'),
  partition M197004 values less than ('1970-05-01'),
  partition M197005 values less than ('1970-06-01'),
  partition M197006 values less than ('1970-07-01'),
  partition M197007 values less than ('1970-08-01'),
  partition M197008 values less than ('1970-09-01'),
  partition M197009 values less than ('1970-10-01'),
  partition M197010 values less than ('1970-11-01'),
  partition M197011 values less than ('1970-12-01'),
  partition M197012 values less than ('1971-01-01'),
  partition MMAX values less than (MAXVALUE))
  1 row in set
  

  Insert data into the tbl1_log_rc table.

  obclient> INSERT INTO tbl1_log_rc VALUES (10,'10','1970/04/01 00:00:00.000000');
  Query OK, 1 row affected
  

  View the partition into which data is inserted.

  obclient> SELECT * FROM tbl1_log_rc partition(M197004);
  +--------+-----------+---------------------+
  | log_id | log_value | log_date            |
  +--------+-----------+---------------------+
  |     10 | 10        | 1970-04-01 00:00:00 |
  +--------+-----------+---------------------+
  1 row in set
  

Create a LIST- or LIST COLUMNS-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { LIST ( expr(column_name) | column_name )
                | LIST COLUMNS ( column_name [,column_name])
                }
    (PARTITION partition_name VALUES IN ( v01 [, v0N])
     [,PARTITION partition_name VALUES IN ( vN1 [, vNN])]
     [,PARTITION partition_name VALUES IN (DEFAULT)]
    );

Notice

When you create a LIST-partitioned table, comply with the following rules:

• The result of the partitioning expression must be an integer.
• The partitioning expression can reference only one column, instead of a list of multiple columns (column vectors).

Parameters

Examples

• Create a LIST-partitioned table named tbl1_l.

  obclient> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
         PARTITION BY LIST(col1)
          (PARTITION p0 VALUES IN (1, 2, 3),
           PARTITION p1 VALUES IN (5, 6),
           PARTITION p2 VALUES IN (DEFAULT)
          );
  Query OK, 0 rows affected
  

  View the tbl1_l partitioned table.

  obclient> show create table tbl1_l\G
  *************************** 1. row ***************************
         Table: tbl1_l
  Create Table: CREATE TABLE `tbl1_l` (
    `col1` bigint(20) NOT NULL,
    `col2` varchar(50) DEFAULT NULL,
    PRIMARY KEY (`col1`)
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list(col1)
  (partition p0 values in (1,2,3),
  partition p1 values in (5,6),
  partition p2 values in (DEFAULT))
  1 row in set
  

  Insert data into the tbl1_l table.

  obclient> INSERT INTO tbl1_l VALUES (1,'1');
  Query OK, 1 row affected
  

  View the partition into which data is inserted.

  obclient> SELECT * FROM tbl1_l partition(p0);
  +------+------+
  | col1 | col2 |
  +------+------+
  |    1 | 1    |
  +------+------+
  1 row in set
  

• Create a LIST COLUMNS-partitioned table.

  obclient> CREATE TABLE tbl1_lc (id INT,partition_id VARCHAR(2))
         PARTITION BY LIST COLUMNS(partition_id)
          (PARTITION p0 VALUES IN ('00','01'),
           PARTITION p1 VALUES IN ('02','03'),
           PARTITION p2 VALUES IN (DEFAULT)
          );
  Query OK, 0 rows affected
  

  View the tbl1_lc partitioned table.

  obclient> show create table tbl1_lc\G
  *************************** 1. row ***************************
         Table: tbl1_lc
  Create Table: CREATE TABLE `tbl1_lc` (
    `id` int(11) DEFAULT NULL,
    `partition_id` varchar(2) DEFAULT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by list columns(partition_id)
  (partition p0 values in ('00','01'),
  partition p1 values in ('02','03'),
  partition p2 values in (DEFAULT))
  1 row in set
  

  Insert data into the tbl1_lc table.

  obclient> INSERT INTO tbl1_lc VALUES (1,'1');
  Query OK, 1 row affected
  

  View the partition into which data is inserted.

  obclient> SELECT * FROM tbl1_lc partition(p2);
  +------+--------------+
  | id   | partition_id |
  +------+--------------+
  |    1 | 1            |
  +------+--------------+
  1 row in set
  

Create a HASH- or KEY-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { HASH(expr) | KEY([column_name][,column_name])}
    PARTITIONS partition_count;

Parameters

Examples

• Create a HASH-partitioned table named tbl1_h.

  obclient> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50))
         PARTITION BY HASH(col1) PARTITIONS 60;
  Query OK, 0 rows affected
  

  View the tbl1_h partitioned table.

  obclient> show create table tbl1_h\G
  *************************** 1. row ***************************
         Table: tbl1_h
  Create Table: CREATE TABLE `tbl1_h` (
    `col1` int(11) DEFAULT NULL,
    `col2` varchar(50) DEFAULT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by hash(col1)
  (partition p0,
  partition p1,
  partition p2,
  partition p3,
  partition p4,
  partition p5,
  partition p6,
  partition p7,
  partition p8,
  partition p9,
  partition p10,
  partition p11,
  partition p12,
  partition p13,
  partition p14,
  partition p15,
  partition p16,
  partition p17,
  partition p18,
  partition p19,
  partition p20,
  partition p21,
  partition p22,
  partition p23,
  partition p24,
  partition p25,
  partition p26,
  partition p27,
  partition p28,
  partition p29,
  partition p30,
  partition p31,
  partition p32,
  partition p33,
  partition p34,
  partition p35,
  partition p36,
  partition p37,
  partition p38,
  partition p39,
  partition p40,
  partition p41,
  partition p42,
  partition p43,
  partition p44,
  partition p45,
  partition p46,
  partition p47,
  partition p48,
  partition p49,
  partition p50,
  partition p51,
  partition p52,
  partition p53,
  partition p54,
  partition p55,
  partition p56,
  partition p57,
  partition p58,
  partition p59)
  1 row in set
  

  Insert data into the tbl1_h table.

  obclient> INSERT INTO tbl1_h VALUES (59,'1');
  Query OK, 1 row affected
  

  View the partition into which data is inserted.

  obclient> SELECT * FROM tbl1_h partition(p59);
  +------+------+
  | col1 | col2 |
  +------+------+
  |   59 | 1    |
  +------+------+
  1 row in set
  

• Create a KEY-partitioned table named tbl1_k.

  obclient> CREATE TABLE tbl1_k(id INT,gmt_create DATETIME,info VARCHAR(20))
         PARTITION BY KEY(id,gmt_create) PARTITIONS 10;
  Query OK, 0 rows affected
  

  View the tbl1_k partitioned table.

  obclient> show create table tbl1_k\G
  *************************** 1. row ***************************
         Table: tbl1_k
  Create Table: CREATE TABLE `tbl1_k` (
    `id` int(11) DEFAULT NULL,
    `gmt_create` datetime DEFAULT NULL,
    `info` varchar(20) DEFAULT NULL
  ) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
   partition by key(id,gmt_create)
  (partition p0,
  partition p1,
  partition p2,
  partition p3,
  partition p4,
  partition p5,
  partition p6,
  partition p7,
  partition p8,
  partition p9)
  1 row in set
  

  Insert data into the tbl1_k table.

  obclient> INSERT INTO tbl1_k VALUES (1,'1971/04/01','1');
  Query OK, 1 row affected
  

  View the partition into which data is inserted.

  obclient> SELECT * FROM tbl1_k partition(p9);
  +------+---------------------+------+
  | id   | gmt_create          | info |
  +------+---------------------+------+
  |    1 | 1971-04-01 00:00:00 | 1    |
  +------+---------------------+------+
  1 row in set
  

Subsequent operations

After creating a partition, you can add, drop, or truncate the partition. For information about partition management, see Manage a partitioned table.