Create a database

This topic describes the definition of databases, as well as the prerequisites, limitations, considerations, and examples for creating a database by using an SQL statement.

About databases

In OceanBase Cloud, a database contains tables, indexes, and metadata of database objects. Do not use a default database, for example, the test database, in a production environment.

Prerequisites

Before you create a database, make sure that:

• You have deployed an OceanBase cluster and created a MySQL-compatible tenant.

• You have connected to the MySQL-compatible tenant of OceanBase Cloud.

• You have the CREATE privilege. If you do not have the required privileges, contact the administrator to obtain the privileges.

Limitations

• In OceanBase Cloud, the name of each database must be globally unique.
• A database name cannot exceed 128 characters in length.
• A database name can contain only letters, digits, underscores (_), dollar signs ($), and Chinese characters.
• A database name cannot contain reserved keywords.

Considerations

• We recommend that you give a database a meaningful name that reflects its purpose and content as much as possible. For example, you can name a database in the application ID_sub-application name (optional)_db format.
• We recommend that you create a database and related users as the root user and grant only required privileges to ensure the security and controllability of the database.
• When you create a database, specify an appropriate default character set and collation to ensure proper storage and sorting of data. To adapt to long-term development of your business, we recommend that you use the utf8mb4 character set to ensure storage of the majority of characters.
• Database names that contain only digits must be enclosed by backticks (`). However, we recommend that you do not use such database names because they are meaningless and the backticks (`) can increase complexity and cause confusion in queries.

Create a database by using a statement

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.
• Migrate existing data to the database as needed.