Skip navigation links
9.1.3.3 Table Character Set and Collation »
Section Navigation      [Toggle]

9.1.3.2. 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 X and COLLATE Y are specified, character set X and collation Y are used.

  • If CHARACTER SET X is specified without COLLATE, character set X and its default collation are used. To see the default collation for each character set, use the SHOW COLLATION statement.

  • If COLLATE Y is specified without CHARACTER SET, the character set associated with Y and collation Y are used.

  • Otherwise, the server character set and server collation are used.

The database character set and collation are used as default values for table definitions if the table character set and collation are not specified in CREATE TABLE statements. The database character set also is used by LOAD DATA INFILE. The character set and collation have no other purposes.

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.


User Comments

Posted by Shuichi Tamagawa on March 23 2006 9:40am[Delete] [Edit]

character_set_database also affects the data which is loaded into table using LOAD DATA command.

Posted by Are you mortal? Then prepare to die. on March 2 2007 6:20pm[Delete] [Edit]

To discover the character set and collation of a database, just

USE your_database_of_interest;


and type,

show variables like "character_set_database";
show variables like "collation_database";


Easy when you know how eh?




Posted by H. Poort on September 10 2007 8:29am[Delete] [Edit]

To show the current character set use this query:

SHOW CREATE DATABASE `DB_NAME`

Posted by Jonathan Watt on May 25 2009 3:16pm[Delete] [Edit]

It's not at all clear what difference it makes to specify DEFAULT or to omit it. So if you specify DEFAULT then that charset becomes the default for tables for which you don't specify an explicit charset. But what if you omit DEFAULT? Does that prevent tables from using a different charset from the database? This should really be explained above.

Add your own comment.