115

MySQL怎么存文本不乱码?

 6 years ago
source link: http://mp.weixin.qq.com/s/zGoP9OutpkzWrdOHsRk7Uw
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

导读

MySQL里怎么存储那些看起来会乱码的字符?

我在“UTF8字符集的表怎么直接转UTF8MB4”一文中介绍了如何把表字符集由UTF8直接转换成UTF8MB4的几种方法。

1、只修改字符集(使用默认校验集)

[email protected]> alter table t1 convert to character set utf8mb4

2、同时修改表字符集和校验集

[email protected]> alter table t1 convert to
  character set utf8mb4 collate utf8mb4_bin;

3、只修改某列的字符集

[email protected]> alter table t1 modify c1 varchar(20)
  character set utf8mb4 not null default ‘’

4、同时修改某列的字符集和校验集

[email protected]> alter table t1 modify c1 varchar(20)
  character set utf8mb4
  collate utf8mb4_unicode_ci
  not null default ‘’

好了,有个字符集为UTF8MB4的表中想存储各类不同字符集的文本,有哪些注意事项亿避免乱码?

如果是通过WEB接口存储数据,则建议在browser端、server端全都采用UTF8字符集,MySQL Server端采用UTF8/UTF8MB4均可(针对大多数文本,其实UTF8字符集就足够存储的了)。

其中,MySQL端的字符集设置比较让人头大,涉及到的字符集有好几个:

  • character_set_server,server端默认字符集;

  • character_set_database,database默认字符集,若未设定,则和 character_set_server 的设定一样;database中的 数据表/stored procedure/stored function 也可以自行设定字符集,若未指定,则和 character_set_database 的设置一样;数据表中的字符类型列,也可以单独设定字符集,若未设定,则和该表指定的字符集一样;

  • character_set_client,客户端显示读取结果的字符集;

  • character_set_connection,客户端从server端读取数据时传输字符集;

  • character_set_results,server端将数据发送给客户端时的字符集;

可见,涉及到字符集的因素实在太多,因此我们强烈建议各个环节全部采用同一种字符集,避免出现意外状况。

MySQL采用UTF8MB4字符集时,存储文本实际消耗字节数是由文本内容的字节数决定的,并非总是需要4字节,列举几种情况:

  • 输入字符集任意,且存储ASCII字符时,每个字符需要1byte;

  • 输入字符集是GB2312,且存储的字符是汉字时,每个字符需要2bytes;

  • 输入字符集是UTF8/UTF8MB4,且存储的字符是低编码汉字时,每个字符需要3bytes;

  • 输入字符集是UTF8/UTF8MB4,且存储的字符是高编码汉字时,每个字符需要4bytes;

  • 输入字符集是binary,且存储的字符是高编码汉字时,每个字符需要4bytes;

  1. 从前端到后端(浏览器=>WEB Server=>MySQL连接层=>Server层=>DB层>TABLE层),尽可能使用同一种字符集;

  2. 尽可能采用大字符集,也就是优先级:UTF8Mb4 > UTF8 > GBK > LATIN1;

  3. 采用逻辑备份数据时,切记要不定期进行恢复测试,我以前在这方面栽过一次,教训惨痛。

附1,关于编码简介

  • ASCII码,占7bit,由128个字符组成,包括大小写字母、数字0-9、标点符号、非打印字符(换行符、制表符等4个)以及控制字符(退格、响铃等)组成;

  • latin1,占1byte,在ASCII基础上,增加128 ~ 255区间的字符;

  • GB2312等CJK字符集,可变长字符集,最多占2bytes,用于存储常见的CJK字符;

  • UTF8,可变长字符集,最多占3bytes,可以囊括ASCII、CJK及其他绝大多数常用语言文字;这中间其实还有个UNICODE字符集,它也是2bytes的,也能囊括ASCII字符,但即便是ASCII字符也需要消耗2bytes,存在一定浪费,而用UTF8存储ASCII字符时,实际只需要1byte,更为节省存储空间;

  • UTF8MB4,可变长字符集,最多占4bytes,可以包含上面其他几种字符集;同样地,以UTF8MB4存储ASCII字符时,实际上也是只占用1bytes,存储一般的汉字占用3bytes,而存储个别汉字则需要4bytes,存储emoji也至少需要4bytes;

附2,字符集兼容在线测试

为了方便大家,我写了个简单的PHP接口供测试,可以提交一些不常见的汉字,或者emoji表情符,看看是否都能正常显示。
开发这个接口时,发现钉钉中的个别表情符是由2个4字节编码组成的,也就是说一个emoji表情符,其实是需要8个字节的。
这个接口最后输出的格式是:

字符串 : 字节数

比如 "a齒a : 5" ,表示 "a齒a" 这个字符串共消耗 5个字节,因为 "齒" 其实只需要3个字节来存储,虽然看起来挺大一坨的。

相应的代码如下:

select vchar, length(vchar) as vcharlen

测试接口链接: utf8mb4字符集兼容性测试接口

如果想要写入4字节的汉字,可以从龙泉寺提供的字库拷贝过来,或者插入emoji表情符。

参考及延伸阅读

最近又进入原创低谷期,主要没人买茶不开心没动力写文章,嘿嘿

好铁观音尽在

「老叶茶馆」

http://yejinrong.com

Image

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK