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 Connection methods.

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

Limitations

• In OceanBase Database, the name of each database must be globally unique.

• The database name cannot exceed 128 characters in length.

• The database name can contain letters, digits, underscores (_), and dollar signs ($), but cannot contain only digits.

• 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 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.

  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 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.