Database Character Set and Collation
Every database has a database character set and a database
collation. The CREATE DATABASEALTER DATABASE statements have optional
clauses for specifying the database character set and collation:
and
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]
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
X and COLLATE
Y were specified, then
character set X and collation
Y.
-
If CHARACTER SET
X was specified without
COLLATE, then character set
X and its default collation.
-
If COLLATE Y
was specified without CHARACTER SET, then
the character set associated with
Y and collation
Y.
-
Otherwise, the server character set and server collation.
The database character set and collation are used as default
values if the table character set and collation are not
specified in CREATE TABLE statements. They
have no other purpose.
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.