Database-level character set and collation

Each database has a default character set and a default collation.

Specify the character set and collation for a database

Use the CREATE DATABASE statement to specify the character set and collation for a database. The syntax is as follows:

CREATE DATABASE database_name
    [DEFAULT] CHARACTER SET [=] charset_name
    [[DEFAULT] COLLATE [=] collation_name]

ALTER DATABASE database_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

In the same OBServer, you can create databases with different character sets and collations by using the CHARACTER SET and COLLATE clauses.

Here are examples of creating and modifying the character set of a database:

/* Create a database with the Latin-1 character set. */
CREATE DATABASE latin1_db CHARSET latin1;

/* Change the character set of an existing UTF-8 database to Latin-1. */
CREATE DATABASE db CHARSET=utf8mb4;
ALTER DATABASE db CHARSET latin1;

Choose the character set and collation for a database

OceanBase Database chooses the character set and collation for a database based on the following rules:

• If both CHARACTER SET charset_name and COLLATE collation_name are specified, the character set is charset_name and the collation is collation_name.

• If CHARACTER SET charset_name is specified but COLLATE is not, the character set is charset_name and the collation is the default collation for charset_name. To view the default collation for each character set, use the SHOW CHARACTER SET statement.

• If COLLATE collation_name is specified but CHARACTER SET is not, the character set and collation are determined by the association of collation_name.

• If neither CHARACTER SET nor COLLATE is specified, the character set and collation are determined by the server.

The default character set and collation of the default database can be determined by the values of the character_set_database and collation_database system variables. To view the default character set and collation of a specified database, use the following statement:

USE database_name;
SELECT @@character_set_database, @@collation_database;

or:

SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'database_name';

Here is an example:

obclient> SET collation_database = 'latin1_swedish_ci';
Query OK, 0 rows affected