故障现象:
主从同步报错,Error 'Illegal mix of collations (utf8mb4_unicode_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8mb4_unicode_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'hhrchina'. Query: 'INSERT INTO t_register_address (addressType, addressName) VALUES ('sh_jincheng', concat('上海市金山区海丰路65号', NAME_CONST('i',4701),'室'))'
从库手工执行该SQL,同样报错;
去主库查找当前pos的binlog,确实是这条SQL,在主库上执行,同样报错
原因分析:
版本为官方mysql5.6.20,分别测试了以下几种场景
mysql> SELECT NAME_CONST('TESasdT', 1);
+---------+
| TESasdT |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT NAME_CONST('TESa阿斯顿sdT', 1);
+------------------+
| TESa阿斯顿sdT |
+------------------+