You can specify the character set and collation to use by default for tables in a database.
Checking the current character set and collation
To determine the default character set and collation for a database, enter this SQL statement:
mysql> SHOW CREATE DATABASE database1; *************************** 1. row *************************** Database: database1 Create Database: CREATE DATABASE `database1` /*!40100 DEFAULT CHARACTER SET latin1 */
You can also obtain this information from the INFORMATION_SCHEMA using a query such as this:
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='database1'; *************************** 1. row *************************** SCHEMA_NAME: database1 DEFAULT_CHARACTER_SET_NAME: latin1 DEFAULT_COLLATION_NAME: latin1_swedish_ci
The database characteristics are stored in a db.opt file in the database directory on the file system. If the file exists, the contents will look something like this:
default-character-set=latin2 default-collation=latin2_bin
You can create or edit this file with a text editor, but it’s preferable to use the related SQL statements discussed in the next section.
Changing the character set and collation
When creating new databases, you would enter the CREATE DATABASE statement like this to set the default character set and collation:
CREATE DATABASE database1 DEFAULT CHARACTER SET latin2 DEFAULT COLLATE latin2_bin;
Notice that there are no commas between these options and this is one statement. To change the default settings for an existing database, enter an SQL statement like the following:
ALTER DATABASE database1 DEFAULT CHARACTER SET latin2 DEFAULT COLLATE latin2_bin;
Note that changing the default character set and collation for a database will have no effect on tables that already exist in that database. Also, the DEFAULT COLLATE of the database is ignored if a CHARACTER SET clause is supplied when creating tables in the database, even if the CHARACTER SET specified for the table is the same as the CHARACTER SET specified for the database. For example:
CREATE DATABASE database2 CHARACTER SET cp1257 COLLATE cp1257_bin; CREATE TABLE database2.table1 (column1 VARCHAR(16)) CHARACTER SET cp1257; SHOW CREATE TABLE database2.table1 \G *************************** 1. row *************************** Table: table1 Create Table: CREATE TABLE `table1` ( `column1` varchar(16) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=cp1257
No COLLATE clause is specified in the output of SHOW CREATE TABLE because the default collation of the database is ignored when a COLLATE clause is specified explicitly in the CREATE TABLE statement.