On this page
10.3.3 Database Character Set and Collation
Every database has a database character set and a database collation. The CREATE DATABASE
and ALTER DATABASE
statements have optional clauses for specifying the database character set and collation:
CREATE DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
ALTER DATABASE db_name
[[DEFAULT] CHARACTER SET charset_name]
[[DEFAULT] COLLATE collation_name]
The keyword SCHEMA
can be used instead of DATABASE
.
All database options are stored in a text file named db.opt
that can be found in the database directory.
The CHARACTER SET
and COLLATE
clauses make it possible to create databases with different character sets and collations on the same MySQL server.
Example:
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL chooses the database character set and database collation in the following manner:
If both
CHARACTER SET
andcharset_name
COLLATE
are specified, character setcollation_name
charset_name
and collationcollation_name
are used.If
CHARACTER SET
is specified withoutcharset_name
COLLATE
, character setcharset_name
and its default collation are used. To see the default collation for each character set, use theSHOW CHARACTER SET
statement or query theINFORMATION_SCHEMA
CHARACTER_SETS
table.If
COLLATE
is specified withoutcollation_name
CHARACTER SET
, the character set associated withcollation_name
and collationcollation_name
are used.Otherwise (neither
CHARACTER SET
norCOLLATE
is specified), the server character set and server collation are used.
The character set and collation for the default database can be determined from the values of the character_set_database
and collation_database
system variables. The server sets these variables whenever the default database changes. If there is no default database, the variables have the same value as the corresponding server-level system variables, character_set_server
and collation_server
.
To see the default character set and collation for a given database, use these statements:
USE db_name;
SELECT @@character_set_database, @@collation_database;
Alternatively, to display the values without changing the default database:
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'db_name';
The database character set and collation affect these aspects of server operation:
For
CREATE TABLE
statements, the database character set and collation are used as default values for table definitions if the table character set and collation are not specified. To override this, provide explicitCHARACTER SET
andCOLLATE
table options.For
LOAD DATA
statements that include noCHARACTER SET
clause, the server uses the character set indicated by thecharacter_set_database
system variable to interpret the information in the file. To override this, provide an explicitCHARACTER SET
clause.For stored routines (procedures and functions), the database character set and collation in effect at routine creation time are used as the character set and collation of character data parameters for which the declaration includes no
CHARACTER SET
or aCOLLATE
attribute. To override this, provideCHARACTER SET
andCOLLATE
explicitly.