Create a database

This topic describes how to create a database with some examples.

Overview

In OceanBase Database, a database includes several tables, indexes, and metadata information about database objects. It is advisable to avoid using the default database, such as the test database, in a production environment.

Prerequisites

Before creating a database, make sure that:

• You have deployed an OceanBase cluster and created a MySQL tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.

• You have connected to the MySQL tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Overview of connection methods.

• You have the CREATE privilege. For information about how to view your privileges, see View user privileges. If you do not have the privilege, contact the administrator. For more information about how to grant privileges, see Grant direct privileges.

Limitations

• In OceanBase Database, the name of each database must be globally unique.
• The database name is limited to 128 characters in length.
• The database name can only contain uppercase and lowercase letters, numbers, underscores, dollar signs, and Chinese characters.
• Avoid using reserved keywords as a database name. For more information about the reserved keywords in OceanBase Database's MySQL mode, see Reserved keywords.

Considerations

• We recommend that you give the database a meaningful name that reflects its purpose and content as much as possible. For example, use application_identifier_sub-application_name (optional)_db as the database name.

• We recommend that you use the root user to create the database and related users, and assign only necessary privileges to ensure the security and controllability of the database.

• When creating a database, ensure that appropriate default character set and collation are set to ensure proper storage and sorting of data. To accommodate the long-term development of the business, it is recommended to use the utf8mb4 character set encoding when creating a database to ensure it can store the majority of characters.

• While it is possible to create a database name using purely numeric characters by enclosing them with backticks (`), this practice is generally not recommended. Database names consisting purely numbers lack clear significance and require the use of backticks (`) for querying, which can lead to unnecessary complexity and potential confusion.

  For more information about the character sets supported by OceanBase Database, see Database-level character sets and collations.

Create a database by using a statement

You can use the CREATE DATABASE statement to create a database.

The syntax of the CREATE DATABASE statement is as follows:

CREATE DATABASE [IF NOT EXISTS] database_name [database_option_list];

where:

• database_name specifies the name of the database.
• database_option_list is a list of options used to set the characteristics, behavior, and properties of the database, such as character set and collation.

Examples

Example 1: Create a database and specify its character set

Create a database named test_db and set its character set to utf8mb4.

obclient [(none)]> CREATE DATABASE test_db DEFAULT CHARACTER SET utf8mb4;

Example 2: Create a read-only database

Create a read-only database named test_ro_db.

obclient [(none)]> CREATE DATABASE test_ro_db READ ONLY;

Example 3: Create a database that supports read and write operations

Create a database named test_rw_db that supports read and write operations.

obclient [(none)]> CREATE DATABASE test_rw_db READ WRITE;

What to do next

After you create a database, you can perform the following operations:

• Create required tables in the database and define the schemas and fields of the tables. For more information, see Create a table.
• Migrate existing data to the database as needed. For more information, see Data migration overview.

References

• For more information about how to view database information, see View databases.
• For more information about how to modify a database, see Modify a database.
• For more information about how to drop a database, see Drop a database.