You can the CREATE DATABASE statement to create a database. In the statement, you can specify default attributes for the database. For example, you can specify a default character set and a 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 = 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.
CHARSET charset_name The character set that the database supports.
COLLATE collation_name The collation that the database supports.
LOCALITY The data nodes that are used to store data for the database.
Note After the storage location of a database is specified by using the LOCALITY syntax, the storage location cannot be modified.
PARTITION_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

  • Use the following statement to create a database that is named test and uses the UTF-8 character set: 
    CREATE DATABASE test PARTITION_MODE=drds default CHARACTER SET UTF8;
Query OK, 1 row affected (0.00 sec)
  • Use the following statement to create a database in a PolarDB-X instance and specify the polardbx-storage-0-master node as the data node of the database: 
    CREATE DATABASE db1 PARTITION_MODE=drds LOCALITY='dn=polardbx-storage-0-master';
    Note
    • If you do not specify a data node when you create the database, the system evenly distributes data in the database to each data node in the instance.
    • The data node of the table shards must be the same as that of the database. Data in different table shards is isolated.
    After the database is created, you can use the following statement to query the data node information: 
    SHOW CREATE DATABASE db1 PARTITION_MODE=drds;
    The following information is returned:
    +----------+------------------------------------------------------------------------+
| DATABASE | CREATE DATABASE                                                        |
+----------+------------------------------------------------------------------------+
| db1      | CREATE DATABASE `db1` /* LOCALITY = "dn=polardbx-storage-0-master" */  |
+----------+------------------------------------------------------------------------+
1 row in set