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]

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'}

Parameters

Parameter Description
database_name The name of the database for which you want to modify attributes by executing a CREATE DATABASE statement. If you do not specify this parameter, the attributes of the current default database are modified.
CHARSET charset_name The character set of the database you want to create.
COLLATE collation_name The collation of the database that you want to create.
LOCALITY The location where you want to create the database.
Note After the storage location of a database is specified by using the LOCALITY syntax, the storage location cannot be modified.
MODE
The partitioning mode of the logical database. Valid values:
  • auto/partitioning: The database is partitioned by using the automatic partitioning mode.
    • If you do not manually specify the partition definition, the database is partitioned based on the primary key.
    • On databases that are partitioned by using this mode, only the latest version of the CREATE TABLE syntaxes can be used to create tables. Examples: PARTITION BY HASH, PARTITION BY RANGE, and PARTITION BY LIST.
  • drds/sharding: The database is partitioned by using the DRDS mode. On databases that are partitioned by using this mode, only the CREATE TABLE syntaxes that are dedicated to this mode can be used to create tables. Examples: DBPARTITION BY and TBPARTITION BY.
Notice
  • If you do not specify the value of this parameter, the default value drds/sharding is used.
  • After you specify a partitioning mode for a database, the partitioning mode cannot be changed.

Examples

  • Create a database named test and set the character set to UTF-8. 
    CREATE DATABASE test default CHARACTER SET=UTF8 mode=auto;
Query OK, 1 row affected (3.36 sec)
  • Create a database on the polardbx-storage-0-master node of a PolarDB-X instance. 
    CREATE DATABASE db1 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.
    • A database and table shards in the database must be stored in the same location. 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;
    The following similar results are returned:
    +----------+------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                        |
+----------+------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /* LOCALITY = "dn=polardbx-storage-0-master" */  |
+----------+------------------------------------------------------------------------+
1 row in set