Troubleshooting issues with MySQL Character Sets

请与Percona首席技术服务工程师Sveta Smirnova一起参加,她将于2017年3月9日上午11:00(太平洋标准时间)/下午2:00(东部标准时间)展示“MySQL字符集故障排除”。
许多MySQL新手发现MySQL字符集支持令人费解。但当您了解了它的设计方式后,您会发现它比许多其他竞争性的数据库解决方案更强大。
MySQL允许为每个对象指定一个字符集,并在线更改它。多年来,这有助于创建能够与世界各地的受众一起工作的快速应用程序。但是,它还要求DBA对字符集问题进行故障排除,以深入了解它们的工作方式。不同的排序规则和排序规则会使过程复杂化。
在网络研讨会上,我们将讨论:
-mysql支持哪些字符集和排序规则
-如何设置它们
-如何理解错误消息
-如何解决字符集/排序规则兼容性问题
-有哪些服务器、应用程序、命令行和图形工具选项可用
-首先检查什么以及如何继续故障排除
-各种兼容性问题是什么?
-如何转换在早期版本中创建的数据
-最佳实践是什么

展开查看详情

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