CREATE DATABASE

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

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