MySQL怎么存文本不乱码

导读

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

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

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

yejr@imysql.com> alter table t1 convert to character set utf8mb4;

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

yejr@imysql.com> alter table t1 convert to
  character set utf8mb4 collate utf8mb4_bin;

3、只修改某列的字符集

yejr@imysql.com> alter table t1 modify c1 varchar(20)
  character set utf8mb4 not null default ‘’;

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

yejr@imysql.com> 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表情符。

参考及延伸阅读

FAQ系列 – 调用存储过程时报错 Illegal mix of collations

导读

执行存储过程,提示 ERROR 1267 错误,咋整?

其实这是一篇旧文,只不过最近好几位朋友都遇到了,于是翻出来重发。

问题

调用存储过程时,发生报错,信息如下:

ERROR 1267 (HY000): Illegal mix of collations (gb2312_chinese_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

很明显嘛,这是字符集方面的问题。

解决

先检查数据表,确认字符集是 gb2312 没错。

再检查连接字符集(character_set_client、character_set_connection),服务器端字符集(character_set_server)也全都是 gb2312。

怀疑是字段的字符集有问题,修改成 gb2312,还是报错。

后来创建一个临时变量,设定其字符集为 gb2312,再调用存储过程,仍然不行

set @tmp_str1 = ‘abcde’ collate gb2312_chinese_ci;

有点糊涂之际,突然想起来数据库(schema)的字符集可能不是 gb2312。

检查了下,果然,schema的字符集是 latin1 的,修改成 gb2312 再执行存储过程就正常了。

ALTER DATABASE `yejr` CHARACTER SET gb2312;

建议

遇到字符集问题时,应从服务器端(character_set_server),到数据库(character_set_database),到数据表(table),然后客户端(character_set_client)、连接层(character_set_connection)、结果层(character_set_results)的字符集都检查一遍,确认是否都一致。

默认地,创建新 schema 时,它的字符集是从 character_set_server 直接继承的。
创建新 table 时从 schema 的字符集直接继承的,table 中的 fields 则从 table 继承,各层逐级继承。

但创建 procedure / function 时,则是从 character_set_database(而非 character_set_server) 直接继承的。个别情况下,我们创建 db 时,可能没注意到这点,就可能遇到本例的问题了。

因此,最好是在所有的环节,从头到尾,全都用同一套字符集,不要混合着用,保不准哪天就玩飞了~

UTF8字符集的表可以直接转UTF8MB4吗

导读

我的小密圈里有读者提到一个线上库是utf8字符集,想问怎样将其转成utf8mb4

问题

我的小密圈“「老叶茶馆」铁粉圈”上有读者提问:金** 提问:叶老师,有一个MySQL库是utf8的,不大,4g多,怎样转成utf8mb4.可以有一定的停机时间。

解读

utf8是utf8mb4的子集,一般情况下,应该是可以直接修改表字符集的。
修改字符集的几种方法

方法一

  1. 修改表默认字符集
mysql> alter table j1 default character set utf8mb4;
  1. 随后再修改所有字符型列的字符集
mysql> alter table j1 modify name varchar(20) character set utf8mb4 not null default '';

方法二

也是执行ALTER TABLE来修改,但有更简单的解法

mysql> alter table j1 convert to character set utf8mb4;

备注

上面两种方法,其实是有区别的。

  • 采用方法一,如果遇到某个列字符集转换完后字节数超限了,会提示错误。
  • 而采用方法二,如果遇到某个列字符集转换完后字节数超限了,则会将这个列数据类型转换成可以容纳更大长度的类型,比如从 TEXT 转成 LONGTEXT 等。

方法三

如果不放心,可以用mysqldump逻辑备份方式,用utf8mb4字符集把数据备份出来,新建表,恢复回去,应该也可以的。

结论

想从小字节数(2字节/3字节)字符集(gb2312、utf8)转换到大字节数(4字节)字符集(utf8mb4),是可以直接转换的。
相反,想从大字节数字符集转成小的,则会有风险,例如字符串被截断等。

案例测试

yejr@imysql.com [test]>set names gb2312;

yejr@imysql.com [test]>show create table t1;
| t1    | CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` char(2) NOT NULL DEFAULT ''
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

yejr@imysql.com [test]>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|  232 | 你我 |
|   47 | 你我 |
|    3 | 你我 |
|    5 | 你我 |
|    8 | 你我 |
|   -1 | 你我 |
+------+--------+

//直接修改字符集
yejr@imysql.com [test]>alter table t1 convert to character set utf8mb4;

//把客户端工具的终端字符集从gb2312改成utf8
//同时修改mysql客户端字符集,重新读取数据
yejr@imysql.com [test]>set names utf8mb4;

yejr@imysql.com [test]>select * from t1;
+------+--------+
| id   | name   |
+------+--------+
|  232 | 你我 |
|   47 | 你我 |
|    3 | 你我 |
|    5 | 你我 |
|    8 | 你我 |
|   -1 | 你我 |
+------+--------+

可以看到,是可以直接转换的。

当然了,生产环境中,如果也想这么做,最好还是先在测试环境进行更严格的测试演练,确保无误后再实施。

参考

全文完。


扫码加入叶老师的「MySQL核心优化课」,开启MySQL的修行之旅吧。

MySQL DATE_FORMATE函数内置字符集的坑

今天帮同事处理一个SQL(简化过后的)执行报错:

mysql> select date_format('2013-11-19','Y-m-d') > timediff('2013-11-19', '2013-11-20');                                         

ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '>'

乍一看挺莫名其妙的,查了下手册,发现有这么一段:

The language used for day and month names and abbreviations is controlled by the value of the lc_time_names system variable (Section 9.7, “MySQL Server Locale Support”).

The DATE_FORMAT() returns a string with a character set and collation given by character_set_connection and collation_connection so that it can return month and weekday names containing non-ASCII characters.

也就是说,DATE_FORMATE() 函数返回的结果是带有字符集/校验集属性的,而 TIMEDIFF() 函数则没有字符集/校验集属性,我们来验证一下:

mysql> set names utf8;
mysql> select charset(date_format('2013-11-19','Y-m-d')), charset(timediff('2013-11-19', '2013-11-20'));
+--------------------------------------------+-----------------------------------------------+
| charset(date_format('2013-11-19','Y-m-d')) | charset(timediff('2013-11-19', '2013-11-20')) |
+--------------------------------------------+-----------------------------------------------+
| utf8                                       | binary                                        |
+--------------------------------------------+-----------------------------------------------+

mysql> set names gb2312;
mysql> select charset(date_format('2013-11-19','Y-m-d')), charset(timediff('2013-11-19', '2013-11-20'));
+--------------------------------------------+-----------------------------------------------+
| charset(date_format('2013-11-19','Y-m-d')) | charset(timediff('2013-11-19', '2013-11-20')) |
+--------------------------------------------+-----------------------------------------------+
| gb2312                                     | binary                                        |
+--------------------------------------------+-----------------------------------------------+

可以看到,随着通过 SET NAMES 修改 character_set_connection、collation_connection  值,DATE_FORMAT() 函数返回结果的字符集也跟着不一样。在这种情况下,想要正常工作,就需要将结果进行一次字符集转换,例如:

mysql> select date_format('2013-11-19','Y-m-d') > convert(timediff('2013-11-19', '2013-11-20') using utf8);
+----------------------------------------------------------------------------------------------+
| date_format('2013-11-19','Y-m-d') > convert(timediff('2013-11-19', '2013-11-20') using utf8) |
+----------------------------------------------------------------------------------------------+
|                                                                                            1 |
+----------------------------------------------------------------------------------------------+

就可以了 :)

P.S,MySQL的版本:5.5.20-55-log Percona Server (GPL), Release rel24.1, Revision 217

MySQL字符集的一个坑

今天帮同事处理一个棘手的事情,问题是这样的:

无论在客户机用哪个版本的mysql客户端连接服务器,发现只要服务器端设置了

character-set-server = utf8

之后,

character_set_client、 character_set_connection、character_set_results

就始终都是和服务器端保持一致了,即便在mysql客户端加上选项

--default-character-set=utf8

也不行,除非连接进去后,再手工执行命令

set names latin1

,才会将client、connection、results的字符集改过来。

经过仔细对比,最终发现让我踩坑的地方是,服务器端设置了另一个选项:

skip-character-set-client-handshake

文档上关于这个选项的解释是这样的:

--character-set-client-handshake

Don't ignore character set information sent by the client. To ignore client information and use the default server character set, use --skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0

这么看来,其实也是有好处的。比如启用 skip-character-set-client-handshake 选项后,就可以避免客户端程序误操作,使用其他字符集连接进来并写入数据,从而引发乱码问题。