CREATE DATABASE syntax and parameters - PolarDB

CREATE DATABASE creates a logical database in PolarDB-X. Specify the default character set, collation, storage locality, and partitioning mode when creating the database.

Syntax

create_database_stmt:
    CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] database_name [database_option_list]

database_option_list:
    database_option [database_option ...]

database_option:
    [DEFAULT] {CHARACTER SET | CHARSET} [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name
  | LOCALITY = locality_option}
  | [{PARTITION_MODE|MODE} = partition_mode_option]
  | [DEFAULT_SINGLE = default_single_option]

locality_option:
    'dn=storage_inst_id_list'

storage_inst_id_list:
    storage_inst_id[,storage_inst_id_list]

partition_mode_option:
     {'auto'|'partitioning'}
    |{'drds'|'sharding'}

default_single_option:
    {'on'|'off'}

Usage notes

Standard Edition instances do not support the DBPARTITION BY syntax used to create database shards and table shards.
Only Enterprise Edition instances support creating databases in DRDS mode.

Parameters

Parameter	Description
`database_name`	The name of the database.
`CHARSET charset_name`	The character set of the database.
`COLLATE collation_name`	The collation of the database.
`LOCALITY`	The storage location of the database, specified as one or more data node (DN) IDs. The location cannot be changed after creation. If `LOCALITY` is not specified, PolarDB-X distributes data evenly across all storage nodes.
`MODE`	The partitioning mode of the logical database. Valid values: `auto` and `drds`. See Choose a partitioning mode for details. Default: `drds`.
`DEFAULT_SINGLE`	Specifies whether to distribute single-table data across multiple DNs when no explicit partition definition is given. Valid only for AUTO mode databases. Available for PolarDB-X 5.4.17-16921956 or later. Valid values: `on` and `off`. Default: `off`.

Important

The partitioning mode (MODE) cannot be changed after the database is created.

Choose a partitioning mode

PolarDB-X supports two partitioning modes for logical databases. Choose the mode based on your table creation workflow.

	AUTO mode	DRDS mode
Default	No	Yes (default when `MODE` is not specified)
Table creation syntax	`PARTITION BY HASH`, `PARTITION BY RANGE`, `PARTITION BY LIST`	`DBPARTITION BY`, `TBPARTITION BY`
Cross-node single tables	Supported (enable with `DEFAULT_SINGLE='on'`)	Not supported
Auto-partition by primary key	Supported (enable with `set global auto_partition=true`)	Not supported
Edition requirement	Not restricted to Enterprise Edition	Enterprise Edition only
Reference	CREATE TABLE (AUTO mode)	CREATE TABLE (DRDS mode)

For a detailed explanation of both modes, see Databases in AUTO mode and DRDS mode.

DEFAULT_SINGLE behavior in AUTO mode

DEFAULT_SINGLE controls what happens when you create a table in an AUTO mode database without specifying a partition definition.

`DEFAULT_SINGLE`	Behavior
`off` (default)	A single table is created.
`on`	A single table is created and data is distributed across all data nodes in the instance. See Common table sharding.

Note

If both DEFAULT_SINGLE='on' and MODE='auto' are specified, DEFAULT_SINGLE='on' takes precedence, which disables automatic partitioning by primary key.

Examples

Create a database in AUTO mode with UTF-8

CREATE DATABASE test MODE=auto default CHARACTER SET UTF8;

Query OK, 1 row affected (0.00 sec)

Create a database on a specific storage node

Pin the database to a single storage node using LOCALITY. Table shards in the database are stored on the same node, isolating data by location.

CREATE DATABASE db1 MODE=drds LOCALITY='dn=polardbx-storage-0-master';

Verify the location after creation:

SHOW CREATE DATABASE db1 MODE=drds;

+----------+------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                        |
+----------+------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /* LOCALITY = "dn=polardbx-storage-0-master" */  |
+----------+------------------------------------------------------------------------+
1 row in set