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

You can 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]

The CHARACTER SET and COLLATE clauses can be used to create databases with different character sets and collations on the same OBServer.

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

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

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

Select the character set and collation for a database

OceanBase Database selects the character set and collation for a database as follows:

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

• If CHARACTER SET charset_name is specified but COLLATE is not, the character set charset_name and its default collation are used. 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 associated with collation_name and the specified collation are used.

• If neither CHARACTER SET nor COLLATE is specified, the server's character set and collation are used.

The default character set and collation for 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 for 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