Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using MySQL 5.5 and when I show variables about charset, I have

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Should I need to change the character_set_database and character_set_server to utf8?

share|improve this question
add comment

1 Answer 1

Think about it:

  • You are storing data in the database as latin1
  • You are data is handled internally by mysqld as latin1

If data coming from the OS or from the connection is utf8, how is mysqld going to treat it?

Rather than guessing or hoping for the best, you could change the incoming character set behavior. With the exception of information_schema and mysql, take all your databases and set the default character set to utf8:

ALTER DATABASE dbname CHARACTER SET utf8;

If you have a specific colllation to go with it, do this:

ALTER DATABASE dbname COLLATE 'utf8_general_ci';

Here are the collations to choose from:

mysql> select * from information_schema.collations where CHARACTER_SET_NAME = 'utf8';
+--------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME     | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------+--------------------+-----+------------+-------------+---------+
| utf8_general_ci    | utf8               |  33 | Yes        | Yes         |       1 |
| utf8_bin           | utf8               |  83 |            | Yes         |       1 |
| utf8_unicode_ci    | utf8               | 192 |            | Yes         |       8 |
| utf8_icelandic_ci  | utf8               | 193 |            | Yes         |       8 |
| utf8_latvian_ci    | utf8               | 194 |            | Yes         |       8 |
| utf8_romanian_ci   | utf8               | 195 |            | Yes         |       8 |
| utf8_slovenian_ci  | utf8               | 196 |            | Yes         |       8 |
| utf8_polish_ci     | utf8               | 197 |            | Yes         |       8 |
| utf8_estonian_ci   | utf8               | 198 |            | Yes         |       8 |
| utf8_spanish_ci    | utf8               | 199 |            | Yes         |       8 |
| utf8_swedish_ci    | utf8               | 200 |            | Yes         |       8 |
| utf8_turkish_ci    | utf8               | 201 |            | Yes         |       8 |
| utf8_czech_ci      | utf8               | 202 |            | Yes         |       8 |
| utf8_danish_ci     | utf8               | 203 |            | Yes         |       8 |
| utf8_lithuanian_ci | utf8               | 204 |            | Yes         |       8 |
| utf8_slovak_ci     | utf8               | 205 |            | Yes         |       8 |
| utf8_spanish2_ci   | utf8               | 206 |            | Yes         |       8 |
| utf8_roman_ci      | utf8               | 207 |            | Yes         |       8 |
| utf8_persian_ci    | utf8               | 208 |            | Yes         |       8 |
| utf8_esperanto_ci  | utf8               | 209 |            | Yes         |       8 |
| utf8_hungarian_ci  | utf8               | 210 |            | Yes         |       8 |
| utf8_sinhala_ci    | utf8               | 211 |            | Yes         |       8 |
+--------------------+--------------------+-----+------------+-------------+---------+
22 rows in set (0.03 sec)

You could also run

mysql> show collation where charset='utf8';
+--------------------+---------+-----+---------+----------+---------+
| Collation          | Charset | Id  | Default | Compiled | Sortlen |
+--------------------+---------+-----+---------+----------+---------+
| utf8_general_ci    | utf8    |  33 | Yes     | Yes      |       1 |
| utf8_bin           | utf8    |  83 |         | Yes      |       1 |
| utf8_unicode_ci    | utf8    | 192 |         | Yes      |       8 |
| utf8_icelandic_ci  | utf8    | 193 |         | Yes      |       8 |
| utf8_latvian_ci    | utf8    | 194 |         | Yes      |       8 |
| utf8_romanian_ci   | utf8    | 195 |         | Yes      |       8 |
| utf8_slovenian_ci  | utf8    | 196 |         | Yes      |       8 |
| utf8_polish_ci     | utf8    | 197 |         | Yes      |       8 |
| utf8_estonian_ci   | utf8    | 198 |         | Yes      |       8 |
| utf8_spanish_ci    | utf8    | 199 |         | Yes      |       8 |
| utf8_swedish_ci    | utf8    | 200 |         | Yes      |       8 |
| utf8_turkish_ci    | utf8    | 201 |         | Yes      |       8 |
| utf8_czech_ci      | utf8    | 202 |         | Yes      |       8 |
| utf8_danish_ci     | utf8    | 203 |         | Yes      |       8 |
| utf8_lithuanian_ci | utf8    | 204 |         | Yes      |       8 |
| utf8_slovak_ci     | utf8    | 205 |         | Yes      |       8 |
| utf8_spanish2_ci   | utf8    | 206 |         | Yes      |       8 |
| utf8_roman_ci      | utf8    | 207 |         | Yes      |       8 |
| utf8_persian_ci    | utf8    | 208 |         | Yes      |       8 |
| utf8_esperanto_ci  | utf8    | 209 |         | Yes      |       8 |
| utf8_hungarian_ci  | utf8    | 210 |         | Yes      |       8 |
| utf8_sinhala_ci    | utf8    | 211 |         | Yes      |       8 |
+--------------------+---------+-----+---------+----------+---------+
22 rows in set (0.00 sec)

mysql>

To see the individual charset of a database run this:

mysql> show create database sample;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| sample   | CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

As for the settings, you could try this:

Add the lines to my.cnf

[mysqld]
character_set_database=utf8
character_set_server=utf8

then restart mysql

I discussed this back on Aug 01, 2011 : Character Set Encoding in a Table

CAVEAT (For MySQL DB Servers in Windows)

These commands

ALTER DATABASE dbname CHARACTER SET utf8;
ALTER DATABASE dbname COLLATE 'utf8_general_ci';

do not work in the Windows Version of MySQL because of the way Windows locks files. The file needed is called db.opt which is located in the database subfolder in datadir.

You may have to do the following:

  • mysqldump that database (no database create info, just table creation and INSERTs)
  • drop that database
  • create database with the specific charset and collation
  • reload in the dump into it

EPILOGUE

No matter what you do, please perform any changes on a Dev/Staging Server to see if you get the desired effects

UPDATE 2012-12-05 11:00 EDT

Your Questions

Should I really change it?

To guarantee the proper treatment of the data, you may want to make sure you have apples-to-apples. Data prepared as one charset and loading it into a table with the database possibly aligning the data as if it sees another charset would probably not display the data with the charset mysqld sees when retrieved and sent back to a DB Connection. Try loading the database on a Dev/Staging Server and experiment with setting default charsets.

Why some defaults use utf8 but some default use latin1 ?

This would depend on the OS version of the MySQL Binary. Windows versions may have latin1 while Linux Versions may use utf8.

share|improve this answer
    
Hi, thanks for your detailed reply. Actually my questions is not about how to change it, but... 1. Should I really change it? 2. Why some defaults use utf8 but some default use latin1 –  Yoga Dec 5 '12 at 15:40
    
(thanks again first) This would depend on the OS version of the MySQL Binary. <-- I mean when I am on Ubuntu, why the default is mixed between latin1 and utf8, e.g. character_set_connection is utf8 but character_set_database is latin1 –  Yoga Dec 7 '12 at 4:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.