Skip navigation links

Additional languages
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 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. 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`

Add your own comment.