The Geek Diary

How to Change the Default Character Set and Collation for a Database in MySQL

by

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.

You May Also Like