Troubleshooting issues with MySQL Character Sets

1 .Troubleshooting issues with MySQL Character Sets March, 9, 2017 Sveta Smirnova 

2 .Table of Contents ∙Let’s Meet! ∙Character Set Support in MySQL ∙It Is Simple! ∙Typical Errors ∙Effective Troubleshooting 2 

3 .Let’s Meet! 

4 .My Name is Света 4 

5 .Russian Ye Ее 5 

6 .Russian Yo Ёё 6 

7 .Our Heroes Дельфин Слон Кит 7 

8 .Character Set Support in MySQL 

9 .Character Set as of 4.1+ ∙ Multiple levels ∙ Client ∙ Server ∙ Database ∙ Table ∙ Column ∙ Error messages ∙ Configurable online ∙ Easy conversion 9 

10 .Character Sets and Collations ∙ Character sets define how machine codes are mapped to characters 10 

11 .Character Sets and Collations ∙ Character sets define how machine codes are mapped to characters ∙ But they do not define how to sort 10 

12 .Character Sets and Collations ∙ Character sets define how machine codes are mapped to characters ∙ But they do not define how to sort ∙ Are "е"and "ё"different letters? ∙ Which comes first? ∙ Are "A"and "a"different letters? 10 

13 .Character Sets and Collations ∙ Character sets define how machine codes are mapped to characters ∙ But they do not define how to sort ∙ Are "е"and "ё"different letters? ∙ Which comes first? ∙ Are "A"and "a"different letters? ∙ This is job of collations 10 

14 .Collations as of 4.1+ ∙ MySQL comes with set of pre-defined collations ∙ You can create your own ∙ Take existent file as example ∙ Rename it ∙ Add your own rules ∙ Define number of collation ∙ You are done! 11 

15 .Character Sets Features ∙ Local languages support ∙ European ∙ Cyrillic ∙ Asian ∙ Unicode ∙ UTF8 and UTF8MB4 ∙ UCS2 ∙ UTF16, UTF16LE, UTF32 12 

16 .How Others Support Globalization? Feature Oracle MSSQL PostgreSQL SQLLite MySQL Client Yes Yes Yes Not Yes Server Yes Yes Yes Yes Yes Schema Collation Yes Collation Not Yes Table Collation Yes Collation Not Yes Column Collation Yes Collation Not Yes Error messages Yes Yes Yes Not Yes Collation Yes Yes Yes Yes (limited) Yes Conversion Limited Limited Dump/reload Dump/reload Online 13 

17 .Complicated? Complicated? 14 

18 .Complicated? Not! 15 

19 .Complicated? You just need to know how it works 16 

20 .It Is Simple! 

21 .Only Text Columns can have Character Sets ∙ CHAR ∙ VARCHAR ∙ TEXT and its variants ∙ BINARY ∙ VARBINARY ∙ BLOB and its variants 18 

22 .Character Set can have Multiple Collations ∙ Check Information Schema: mysql> select * from COLLATION_CHARACTER_SET_APPLICABILITY where CHARACTER_SET_NAME=’cp1251’; +---------------------+--------------------+ | COLLATION_NAME | CHARACTER_SET_NAME | +---------------------+--------------------+ | cp1251_bulgarian_ci | cp1251 | | cp1251_ukrainian_ci | cp1251 | | cp1251_bin | cp1251 | | cp1251_general_ci | cp1251 | | cp1251_general_cs | cp1251 | +---------------------+--------------------+ 5 rows in set (0,00 sec) 19 

23 .Character Set can have Multiple Collations ∙ Check Information Schema: ∙ Sort order is different 19 

24 .Character Set can have Multiple Collations ∙ Check Information Schema: ∙ Sort order is different ∙ Russian letters "Е "е "Ё "ё" ∙ Binary collation mysql> select id, bin from coll order by bin; +----+------+ | id | bin | +----+------+ | 4 | Ё | | 2 | ё | | 3 | Е | | 1 | е | +----+------+ 4 rows in set (0,00 sec) 19 

25 .Character Set can have Multiple Collations ∙ Check Information Schema: ∙ Sort order is different ∙ Russian letters "Е "е "Ё "ё" Case Insensitive collation mysql> select id, gen_ci from coll order by gen_ci; +----+--------+ | id | gen_ci | +----+--------+ | 1 | е | | 3 | Е | | 2 | ё | | 4 | Ё | +----+--------+ 4 rows in set (0,00 sec) 19 

26 .Character Set can have Multiple Collations ∙ Check Information Schema: ∙ Sort order is different ∙ Russian letters "Е "е "Ё "ё" Case sensitive collation mysql> select id, gen_cs from coll order by gen_cs; +----+--------+ | id | gen_cs | +----+--------+ | 3 | Е | | 1 | е | | 4 | Ё | | 2 | ё | +----+--------+ 4 rows in set (0,00 sec) 19 

27 .What is Binary Collation? ∙ BINARY fields do not have collations 20 

28 .What is Binary Collation? ∙ BINARY fields do not have collations ∙ Their values sorted in same order as stored mysql> select hex(varbin), convert(varbin using cp1251) as value from coll order by varbin; +-------------+-------+ | hex(varbin) | value | +-------------+-------+ | A8 | Ё | | B8 | ё | | C5 | Е | | E5 | е | +-------------+-------+ 4 rows in set (0,00 sec) 20 

29 .What is Binary Collation? ∙ BINARY fields do not have collations ∙ Their values sorted in same order as stored ∙ Same result provides binary collation mysql> select hex(bin), bin from coll order by bin; +----------+------+ | hex(bin) | bin | +----------+------+ | A8 | Ё | | B8 | ё | | C5 | Е | | E5 | е | +----------+------+ 4 rows in set (0,00 sec) 20