Re: Changing charset ang language

[Russell Dyer]

As of MySQL 4.1.1, you can specify the character set and collation to use by default for tables in a database. These database characteristics are stored in a <tt>db.opt</tt> file in the database directory (e.g., /Program Files/MySQL/MySQL Server 4.1/Data/) on the file system. If the file exists, the contents will look something like this:

default-character-set=latin2
default-collation=latin2_bin

While you're at it, you should probably set the default collation to match the default character set. I'm suggesting that you use the binary version of latin2 so that sorting will be case-sensitive. You could edit this file with a text editor, or you could enter an SQL statement like this:

ALTER DATABASE database1
DEFAULT CHARACTER SET latin2
DEFAULT COLLATE latin2_bin;

Notice that there are no commas between these options and this is one statement. When creating databases, you would enter the CREATE DATABASE statement like this to set the default character set and collation:

CREATE DATABASE database2
DEFAULT CHARACTER SET latin2
DEFAULT COLLATE latin2_bin;

Use this next statement to see what the defaults are for a particular database:

SHOW CREATE DATABASE database1;

After you set the database with the defaults that you want, you can then use the ALTER TABLE statement to change the character set and collation for specific tables if you think that you need to do this:

ALTER TABLE table1
CONVERT TO CHARACTER SET latin2
COLLATE latin2_bin;

Use this next statement to see what the defaults are for a table:

SHOW CREATE TABLE table1;

Russell Dyer

Author of "MySQL in a Nutshell" (O'Reilly 2005).