CREATE DATABASE - PolarDB - Alibaba Cloud Documentation Center

You can execute a CREATE DATABASE statement to create a database. When you create the database, you can specify the default attributes of the database, such as the default character set and collation.

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 syntax that is used to create database shards and table shards. Only Enterprise Edition instances allow you to create 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 location of the database. Note After you use the LOCALITY syntax to specify the location of the database, you cannot modify the location.
MODE	The partitioning mode of the logical database. Valid values: auto (default): The database is partitioned in AUTO mode. You can use only the syntax described in CREATE TABLE (AUTO mode), such as PARTITION BY HASH, PARTITION BY RANGE, and PARTITION BY LIST, to create tables. Data of a single table can be distributed across different data nodes. This feature is disabled by default. Add `DEFAULT_SINGLE = 'on'` in the table creation statement to enable this feature. For more information, see Common table sharding. If you do not manually specify the partition definition, the database can be partitioned based on the primary key. This feature is disabled by default. To enable this feature, explicitly specify `set global auto_partition=true`. drds: The database is partitioned in DRDS mode. You can use only the syntax described in CREATE TABLE (DRDS mode), such as DBPARTITION BY and TBPARTITION BY, to create tables. Important If you do not configure the MODE parameter, the default value drds is used. You cannot change the partitioning mode that you specify for a database. For more information about the preceding modes, see Databases in AUTO mode and DRDS mode.
DEFAULT_SINGLE (available for PolarDB-X 5.4.17-16921956 or later)	Specifies whether to create a single table in the database in AUTO mode and distribute the table data across different data nodes (DNs) in the instance. This parameter is valid only for databases in AUTO mode. Valid values: OFF (default): Table data is not distributed across different data nodes. When you create a table in the database in AUTO mode without explicitly specifying the partition definition, a single table is created. ON: Table data is distributed across different data nodes. When you create a table in the database in AUTO mode without explicitly specifying the partition definition, a single table is created and table data is distributed across different data nodes in the instance. For more information, see Common table sharding. Note If both `DEFAULT_SINGLE='ON'` and `MODE='auto'` are specified, `DEFAULT_SINGLE='ON'` prevails, which means automatic partitioning is disabled after you enable the command table sharding feature.

Examples

Create a database named test in AUTO mode and specify the UTF-8 character set:

CREATE DATABASE test MODE=auto default CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)

Create a database on the polardbx-storage-0-master node of a PolarDB-X instance.

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

Note

If you do not specify the location of a database when you create the database, the system evenly distributes the database data across all storage nodes.
Table shards in a database must be stored in the same location as the database. This way, data in the table shards can be isolated.

After you create the database, you can execute the following statement to query the location of the database:

SHOW CREATE DATABASE db1 MODE=drds;

The following results are returned:

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