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

The CREATE DATABASE statement is used 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 using the CHARACTER SET and COLLATE clauses.

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

/* 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;

Choose 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';

Examples

In the following example, the collation for the current database is set to swedish_ci, which specifies the swedish collation for the latin1 character set and is case-insensitive (ci indicates case-insensitive).

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