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: drds: The database is partitioned in DRDS mode. You can use only the CREATE TABLE syntax dedicated to this mode, such as DBPARTITION BY and TBPARTITION BY, to create tables. auto: The database is partitioned in AUTO mode. If you do not manually specify the partition definition, the database is partitioned based on the primary key. You can use only the CREATE TABLE syntax dedicated to this mode, such as DBPARTITION BY HASH, DBPARTITION BY RANGE, and DBPARTITION BY LIST, 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: on: 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 DNs in the instance. off: When you create a table in the database in AUTO mode without explicitly specifying the partition definition, the database is partitioned based on the primary key. The same also applies when this parameter is left unspecified.

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