Every database has a default character set and a default collation.
Specify the character set and collation of 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]
The CHARACTER SET and COLLATE clauses can be used to create a database with different character sets and collations on the same OBServer.
The following example shows how to create and modify a database character set:
/*Directly create a Latin-1 database.*/
CREATE DATABASE latin1_db CHARSET latin1;
/*Convert an existing UTF-8 database to a Latin-1 database.*/
CREATE DATABASE db CHARSET=utf8mb4;
ALTER DATABASE db CHARSET latin1;
Select the character set and collation of a database
OceanBase AI Database selects the database's character set and collation in the following manner:
If both
CHARACTER SET charset_nameandCOLLATE collation_nameare specified, the character setcharset_nameand collationcollation_nameare used.If
CHARACTER SET charset_nameis specified butCOLLATEis not, the character setcharset_nameand its default collation are used. To view the default collation for each character set, use theSHOW CHARACTER SETstatement.If
COLLATE collation_nameis specified butCHARACTER SETis not, the character set associated withcollation_nameand the specified collation are used.If neither
CHARACTER SETnorCOLLATEis specified, the server's character set and collation are used.
The default character set and collation of a 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