PolarDB:Usage instructions

Syntax

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
| DIST_DB [=] {'Y' | 'N'}
}

Examples

Create a database named test1 and allow multi-master cluster partitioned tables to be created in the database.

CREATE DATABASE test1 DIST_DB='Y';

Database sharding mode

The CREATE TABLE statement is used to create tables. In a database marked as DIST_DB='Y', you can use DBDISTRIBUTION to specify the distribution method of multi-master cluster partitioned tables. When the number of shards is not explicitly defined, the system automatically determines it based on the cluster configuration.

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [dbdistribution_options]

dbdistribution_options:
    DBDISTRIBUTION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY [ALGORITHM={1 | 2}] (column_list)
        | RANGE{(expr) | COLUMNS(column_list)}
        | LIST{(expr) | COLUMNS(column_list)} }
    [(dbdistribution_definition [, dbdistribution_definition] ...)]

dbdistribution_definition:
    DBDISTRIBUTION distribution_name
        [VALUES
            {LESS THAN {(expr | value_list) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

Examples:

• Regular single table

  CREATE database test;
  CREATE TABLE t1(
   id bigint NOT NULL, 
   name varchar(30), 
   PRIMARY key(id)
  );

• Multi-master partitioned tables supporting database sharding mode

  Use a hash function for splitting

  Create a multi-master cluster partitioned table and use the id column for hash splitting. The data shards are distributed across the read/write nodes of the cluster.

  CREATE TABLE dist1(
   id bigint NOT NULL, 
   c1 int, 
   c2 varchar(30), 
   PRIMARY key(id)
  ) dbdistribution BY hash(id);

  LIST sharding

  list is a collection of enumeration value lists, which can meet the requirements of enumeration sharding. In list sharding, DBDISTRIBUTION BY LIST(expr) is used for definition, where expr is the abbreviation of expression, representing the value of a column or an expression based on the column value. expr is required to return an integer value. Then, each shard can be defined by VALUES IN (value_list), where value_list is a comma-separated list of integer values.

  CREATE TABLE t1 (
      id INT NOT NULL,
      store_id INT,
      partition_no INT,
      PRIMARY key(id, partition_no)
  ) DBDISTRIBUTION BY LIST(partition_no) (
    DBDISTRIBUTION a VALUES IN (1),
    DBDISTRIBUTION b VALUES IN (2)
  );

  LIST-COLUMNS sharding

  list column sharding is very similar to list sharding and is defined by using DBDISTRIBUTION BY LIST COLUMNS(column_list), where column_list can be one or more column names instead of expressions. In fact, columns() does not allow expressions other than column names. list columns sharding is no longer limited to integer columns. The applicable types include:

  • All integer types: TINYINT, SMALLINT, MEDIUMINT, INT (INTEGER), and BIGINT.

  • Other numeric types: for example, DECIMAL and FLOAT are not supported.

  • Date types: DATE and DATETIME.

  • String types: CHAR, VARCHAR, BINARY, and VARBINARY. TEXT and BLOB are not supported.

  CREATE TABLE t3 (
      id INT NOT NULL,
      store_id INT,
      partition_no varchar(20),
      PRIMARY key(id, partition_no)
  ) DBDISTRIBUTION BY LIST COLUMNS (partition_no) (
    DBDISTRIBUTION a values in ('id_1'),
    DBDISTRIBUTION b values in ('id_2')
  );

ALTER TABLE

Commonly used MySQL ALTER TABLE syntax is supported. Multi-master cluster partitioned tables do not support modifying distribution rules or some operations that require reorganizing shards. For specific limits, see the development limits in this topic.

CREATE INDEX

Commonly used MySQL CREATE INDEX syntax is supported. When you create a primary key or unique index, the index must contain all distribution keys.