Mysql字符集的设置

字符集概念

  • 字符 (Character): 语言中最小的表达符号,例如 a、b、c等;
  • 字符编码(Encoding): 对每一个字符赋予一个数值,用数值来代表对应的字符,这个数值就是字符的编码,如 a 赋值 97 、b 赋值 98、c 赋值 99 ,则97、98、99分别为a、b、c的字符编码;
  • 字符集(Character Set): 给一些列字符赋予对应的编码后,所有这些编码对组成的集合就是字符集,如ANSII、GBK、utf-8…
  • 字符序(Collation): 在同一字符集质检的比较规则,一个collation对应一个字符集,一个字符集对应多个collation,通常会有一个默认的default collation。mysql中字符序以字符集开头,_ci(表示不区分大小写)、_cs(大小写敏感)、_bin(编码值比较) 如 : utf8_general_ci下 a 与 A 相等;

Mysql中的字符集

Mysql中字符集的查看及含义

  1. show variables like character%”; //查看mysql系统字符集
  2. +————————–+—————————-+
  3. | Variable_name | Value |
  4. +————————–+—————————-+
  5. | character_set_client | utf8 |
  6. | character_set_connection | utf8 |
  7. | character_set_database | latin1 |
  8. | character_set_filesystem | binary |
  9. | character_set_results | utf8 |
  10. | character_set_server | latin1 |
  11. | character_set_system | utf8 |
  12. | character_sets_dir | /usr/share/mysql/charsets/ |
  13. +————————–+—————————-+
  • character_set_client 客户端来源数据使用的字符集
  • character_set_connection 连接层字符集
  • character_set_database 当前数据库的默认字符集
  • character_set_results 查询结果字符集
  • character_set_server 内部操作字符集
  • character_set_system 系统元数据(字段名等)字符集

Mysql中字符集转换过程

  • MysqlServer收到请求数据从character_set_client转为character_set_connection;
  • 进行内部操作时从character_set_connection转为内部操作字符集,内部操作字符集确定规则为
  • 使用每个字段上的character set 值
  • 若上述值不存在,使用数据表的default character set 设定值
  • 若上述值不存在,使用数据库的default character set 设定值
  • 若上述值不存在,使用 character_set_server设定值
  • 将操作结果从上述字符集转为character_set_results设定字符集

Mysql字符集的设定

  • 连接时设定,作用域仅限本连接。使用 set names utf8;
  • 通过修改my.cnf设定
  1. [mysqld]
  2. character_set_server=latin1
  3. init_connect=’SET NAMES latin1
  4. [client]
  5. default_character_set=latin1 //只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用

Mysql使用时常碰到的问题

  • 像默认字符集为utf8的数据表插入数据,插入时未指定字符集、查询是指定utf8字符集
  • 插入时根据mysql默认设置 character_set_client=latin1 > character_set_connection=latin1 > utf8。
  • 查询时设置了utf8则 utf8 > character_set_results utf8
  • 像默认字符集为Latin1的数据表插入数据

连接字符设置为utf8,此时若原始数据中含有\u0000~\u00ff范围以外的Unicode字 符,会因为无法在latin1字符集中表示而被转换为“?”(0x3F)符号,则查询出的字符为?

Mysql常用的检查字符集的命令

  1. show character set; //显示所有字符集
  2. +———-+———————————+———————+——–+
  3. | Charset | Description | Default collation | Maxlen |
  4. +———-+———————————+———————+——–+
  5. | big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
  6. | dec8 | DEC West European | dec8_swedish_ci | 1 |
  7. | cp850 | DOS West European | cp850_general_ci | 1 |
  8. | hp8 | HP West European | hp8_english_ci | 1 |
  9. | koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
  10. | latin1 | cp1252 West European | latin1_swedish_ci | 1 |
  11. | latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
  12. show collation; //显示字符序
  13. +————————–+———-+—–+———+———-+———+
  14. | Collation | Charset | Id | Default | Compiled | Sortlen |
  15. +————————–+———-+—–+———+———-+———+
  16. | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
  17. | big5_bin | big5 | 84 | | Yes | 1 |
  18. | dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
  19. | dec8_bin | dec8 | 69 | | Yes | 1 |
  20. | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 |
  21. show variables like character%”; //显示当前字符集设置
  22. +————————–+—————————-+
  23. | Variable_name | Value |
  24. +————————–+—————————-+
  25. | character_set_client | utf8 |
  26. | character_set_connection | utf8 |
  27. | character_set_database | latin1 |
  28. | character_set_filesystem | binary |
  29. | character_set_results | utf8 |
  30. | character_set_server | latin1 |
  31. | character_set_system | utf8 |
  32. | character_sets_dir | /usr/share/mysql/charsets/ |
  33. +————————–+—————————-+
  34. show variables like collation%”; //显示字符序设置
  35. +———————-+——————-+
  36. | Variable_name | Value |
  37. +———————-+——————-+
  38. | collation_connection | utf8_general_ci |
  39. | collation_database | latin1_swedish_ci |
  40. | collation_server | latin1_swedish_ci |
  41. +———————-+——————-+

Mysql中字符集使用时注意事项

– 建立数据库和数据表是尽量指定字符集而不要依赖mysql默认设置,否则MySql升级是会很麻烦
– 数据库和连接字符集都使用latin1时虽然大部分情况下都可以解决乱码问题,但缺点是无法以字符为单位来进行SQL操作,一般情况下将数据库和连接字符集都置为utf8是较好的选择
– 对于mysql PHP API,一般页面级的PHP程序总运行时间较短,在连接到数据库以后显式用SET NAMES语句设置一次连接字符集即可;但当使用长连接时,请注意保持连接通畅并在断开重连后用SET NAMES语句显式重置连接字符集
– my.cnf中的default_character_set设置只影响mysql命令连接服务器时的连接字符集,不会对使用libmysqlclient库的应用程序产生任何作用
– 对字段进行的SQL函数操作通常都是以内部操作字符集进行的,不受连接字符集设置的影响

深入理解SET NAMES和mysql(i)_set_charset的区别