Database-level character sets and collations

Every database is created with a default character set and collation.

Specify the character set and collation of a database

You can execute the CREATE DATABASE statement to specify the character set and collation of a database by using the following syntax:

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 allow you to create databases with different character sets and collations on the same OBServer node.

Here are some examples:

/* Create a latin1 database */
CREATE DATABASE latin1_db CHARSET latin1;

/* Change a utf8 database to a latin1 database */
CREATE DATABASE db CHARSET=utf8mb4;
ALTER DATABASE db CHARSET latin1;

Select the character set and collation of a database

You can select the character set and collation of a database by using the following methods in OceanBase Database:

• If you specify both the CHARACTER SET charset_name and COLLATE collation_name clauses, the character set specified by charset_name and the collation specified by ollation_name are used.

• If you specify only the CHARACTER SET charset_name clause, the character set specified by charset_name and the associated default collation are used. To view the default collation for each character set, execute the SHOW CHARACTER SET statement.

• If you specify only the COLLATE collation_name clause, the collation specified by collation_name and the associated character set are used.

• If you do not specify the CHARACTER SET and COLLATE clauses, the character set and collation for the server are used.

You can specify the default character set and collation of a database by setting the character_set_database and collation_database variables. To view the default character set and collation of a specific database, execute 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

Specify to use the swedish collation of the latin1 character set for the database, where the collation is case-insensitive (indicated by ci).

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