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:
Notice
|
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:
The following information is returned:SHOW CREATE DATABASE db1 PARTITION_MODE=drds;
+----------+------------------------------------------------------------------------+ | DATABASE | CREATE DATABASE | +----------+------------------------------------------------------------------------+ | db1 | CREATE DATABASE `db1` /* LOCALITY = "dn=polardbx-storage-0-master" */ | +----------+------------------------------------------------------------------------+ 1 row in set