mysql主从字符集_主从同步混合字符集问题

今天搭建一个主从，启动同步报如下信息:

180118 17:20:37 [ERROR] Slave SQL: Error 'Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat'' on query. Default database: 'rdcs_jgs'. Query: 'insert into jgs_bj_log(pnnode,realtime,value,dd_bj,d_bj,gg_bj,g_bj,flag,bjms,ds)

values( NAME_CONST('v_pnnode',_utf8'1HD10CL002' COLLATE 'utf8_general_ci'),NOW(), NAME_CONST('v_value',9.52461), NAME_CONST('v_dd_bj',NULL), NAME_CONST('v_d_bj',8.000), NAME_CONST('v_gg_bj',9.900), NAME_CONST('v_g_bj',9.500),1,CONCAT('超过高报限值',  NAME_CONST('v_g_bj',9.500) ,'报警'), NAME_CONST('v_ds',_utf8'#1机组吸收塔液位2' COLLATE 'utf8_general_ci'))', Error_code: 1270

180118 17:20:37 [Warning] Slave: Illegal mix of collations (utf8_general_ci,COERCIBLE), (latin1_swedish_ci,IMPLICIT), (utf8_general_ci,COERCIBLE) for operation 'concat' Error_code: 1270

concat函数不支持两种不同的字符集进行连接。为什么会有两种不同的字符集呢？

其中"超过高报限值"是utf8字符集，NAME_CONST('v_d_bj',8.000)函数得到的数值型常量会被自动转换成latin1字符集。

针对这个错误，可以先绕过该错误，在my.cnf加上slave_skip_errors = 1270，跳过该错误，同时跟开发人员沟通下，修改下语句，数字转换成字符类型再拼接.

测试如下:

mysql> select CONCAT(NAME_CONST('aa',999.99),'测试');

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

mysql> select CONCAT(cast(NAME_CONST('aa',999.99) as char),'测试');

+--------------------------------------------------------+

| CONCAT(cast(NAME_CONST('aa',999.99) as char),'测试')   |

+--------------------------------------------------------+

| 999.99测试                                             |

+--------------------------------------------------------+

1 row in set (0.00 sec)

-- The End --