Why are there different levels of MySQL collation/charsets?

Question

Looking at the MySQL server variables the server and database collation are set to latin1_swedish_ci but the collaction_connection is utf8_general_ci. Additionally, the collation/charset is set at many levels: server, database, table and column. You also have the charset of the PHP MySQL connection to consider.

My question is fourfold:

1. Why are there different levels of MySQL collation/charsets? Is it so you can mix up your character sets to suit your needs? Correct me if I'm wrong but utf8 seems to be the best character set for general use so why is it that most of the LAMP setups I've used have latin1 as default?

2. Should you always ensure your PHP connection matches the charset of the database you're working on?

3. If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?

4. If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

Many thanks.

EDIT:

Regarding the comment:

“You can use either, the former one only to set results charset and the latter one to set either PHP internal encoding for use with mysqli_real_escape_string and results encoding.”

I thought you aren't meant to use real_escape_string and SET NAMES together. See: http://www.php.net/manual/en/mysqlinfo.concepts.charset.php

Answer 1

QUESTION #1

Why are there different levels of MySQL collation/charsets?

ANSWER TO QUESTION #1

There are two good reasons for different character sets and collations

Reason #1 : Disk Space

When you run this query

SELECT
    maxlen,
    GROUP_CONCAT(CHARACTER_SET_NAME) CharSets,
    COUNT(1) CharSetCount
FROM information_schema.character_sets
GROUP BY maxlen\G

You get this:

mysql> SELECT
    ->     maxlen,
    ->     GROUP_CONCAT(CHARACTER_SET_NAME) CharSets,
    ->     COUNT(1) CharSetCount
    -> FROM information_schema.character_sets
    -> GROUP BY maxlen\G
*************************** 1. row ***************************
      maxlen: 1
    CharSets: cp1257,cp850,binary,koi8r,latin2,ascii,tis620,koi8u,greek,armscii8,keybcs2,macroman,latin7,cp1251,cp1256,dec8,hp8,geostd8,latin1,swe7,hebrew,cp1250,latin5,cp866,macce,cp852
CharSetCount: 26
*************************** 2. row ***************************
      maxlen: 2
    CharSets: big5,cp932,sjis,gbk,ucs2,euckr,gb2312
CharSetCount: 7
*************************** 3. row ***************************
      maxlen: 3
    CharSets: eucjpms,ujis,utf8
CharSetCount: 3
*************************** 4. row ***************************
      maxlen: 4
    CharSets: utf16,utf32,utf8mb4
CharSetCount: 3
4 rows in set (0.00 sec)

mysql>

Some character sets have a Maximum Length of 1 byte to represent a character. Other need more. Give this information, you may want to refrain from using the eucjpms, ujis, utf8, utf16, utf32, utf8mb4 character sets so that VARCHAR and TEXT data takes less space on disk.

Reason #2 : Internationalization

Characters Sets Each Come With One or More Collations to cover a variety of Languages

When you run this query

SELECT
    A.CHARACTER_SET_NAME,
    GROUP_CONCAT(COLLATION_NAME) Collations,
    COUNT(1) CollationCount
FROM
    information_schema.character_sets A
    INNER JOIN information_schema.collations B
    USING (CHARACTER_SET_NAME)
GROUP BY A.CHARACTER_SET_NAME\G

You will see that some Characters Sets have with multiple collations for Different Parts of Europe. Chinese, Japanese, Greek, and parts of Asia Minor and Scandinavia are also available.

QUESTION #2

Should you always ensure your PHP connection matches the charset of the database you're working on?

ANSWER TO QUESTION #2

SCENARIO

You are driving at 3:00 AM. You are the only driver on the road. You come to an intersection. You have the red light.

Question : Do you stop or go through the red light?

Answer : Depends on the neighborhood

• Safe neighborhood ?
  • Some abide by the law, stop at the red, and wait for green.
  • Some chance it and go through
• Bad neighborhood or new to the area ?
  • Some abide by the law, stop at the red, and wait for green AT THE RISK OF A CARJACKING
  • Some chance it and go through to AVOID OR REDUCE RISK OF A CARJACKING
  • Assume the worst and find another route

How does this apply?

You should err on the side of caution. You should always check the charset beforehand because you do not know the neighborhood (client program, internet browser) the PHP connection will be entering and if there is a risk of a carjacking (putting invalid data into the database, requesting too much data for retrieval).

QUESTION #3

If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?

ANSWER TO QUESTION #3

By all means

QUESTION #4

If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

ANSWER TO QUESTION #4

You may have to shift character sets with the DB Session. Here are the settings that can be changed at the session level:

• character_set_client
• character_set_connection
• character_set_database
• character_set_filesystem
• character_set_results

Please set these carefully before reading from and writing to the database. It would also be wise to store the character set name and collation in the same table you will be accessing.

Answer 2

Actually the connection charset should not necessarily be the same as the table charset. MySQL will automatically convert data from the table charset to the connection charset.

So if you establish the connection charset to UTF-8 you should get the correct data from MySQL whatever charset you have on your tables.

But keep in mind that for example converting from latin1 to UTF-8 is not the same as converting from UTF-8 to latin1. In the last case you can loose some characters that have no corresponding values in latin1.

So using UTF-8 everywhere is definitely the best choise.

Answer 3

I have for only last three.
And it would be rather clarifications than answers

2. Should you always ensure your PHP connection matches the charset of the database you're working on?

It depends. Actually you need to set PHP connection charset only if you are using some marginal encodings like GBK. With UTF-8 you can have it as default latin1 with no problem.
But what you really need to set obligatory, is results charset. It have to match charset of the HTML page you are building.

3. If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?

It doesn't actually matter if you have different tables charset as Mysql will recode all of them to encoding you set for the aforementioned results charset.
You can use either, the former one only to set results charset and the latter one to set either PHP internal encoding for use with mysqli_real_escape_string and results encoding.

4. If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

Same as above.