MySQL Manual
8 National Character Sets and Unicode in MySQL 4.1
- 8.3 Determining The Default Character Set And Collation
- 8.3.1 Server Character Set and Collation
- 8.3.2 Database Character Set and Collation
- 8.3.3 Table Character Set and Collation
- 8.3.4 Column Character Set and Collation
- 8.3.5 Examples of Character Set and Collation Assignment
- 8.3.6 Connection Character Sets and Collations
- 8.3.7 Character String Literal Character Set and Collation
- 8.3.8
COLLATEClause in Various Parts of an SQL Query - 8.3.9
COLLATEClause Precedence - 8.3.10
BINARYOperator - 8.3.11 Some Special Cases Where the Collation Determination is Tricky
- 8.3.12 Collations Must Be for the Right Character Set
- 8.3.13 An example of the Effect of Collation
8.3.2 Database Character Set and Collation
Every database has a database character set and a database
collation, which may not be null. The CREATE DATABASE and ALTER
DATABASE commands now have optional clauses for specifying the
database character set and collation:
CREATE DATABASE db_name
[CHARACTER SET character_set_name [COLLATE collation_name]]
ALTER DATABASE db_name
[CHARACTER SET character_set_name [COLLATE collation_name]]
Example:
CREATE DATABASE db_name CHARACTER SET latin1 COLLATE latin1_swedish_ci;
MySQL chooses the database character set and database collation thus:
- If both
CHARACTER SET XandCOLLATE Ywere specified, then character setXand collationY. - If
CHARACTER SET Xwas specified withoutCOLLATE, then character setXand its default collation. - Otherwise, the server character set and server collation.
MySQL's CREATE DATABASE ... CHARACTER SET ... syntax is
analogous to the standard-SQL CREATE SCHEMA ... CHARACTER SET ...
syntax. Because of this, it is possible to create databases with
different character sets and collations, on the same MySQL
server.
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.
