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
.
The CHARACTER SET
and
COLLATE
clauses make it possible to create
databases with different character sets and collations on the
same MySQL server.
Database options are stored in the data dictionary and can be
examined by checking the Information Schema
SCHEMATA
table.
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.