【数据库-MySql】Illegal mix of collations for operation ‘concat_ws‘

 1、错误信息 

软件：Navicat for MySQL （Version 9.0.15 - enterprise）

操作：Tools - "Data  Synchronization..."（数据同步），使用Navicat Premium 进行数据同步没有问题

结论：Navicat for MySQL （Version 9.0.15 - enterprise） 这个版本同步数据存在bug，具体分析请详细阅读此文。

[Msg] [Sync] Synchronization Start...
[Msg] [Sync] Synchronizing: localhost_rdis.`atdps_a`.`a1` -> localhost_rdis.`atdps_a`.`prescription_medicine`
[Err] [Sync] Source server SQL execution error:1271 - Illegal mix of collations for operation 'concat_ws'
[Err] [Sync] 1271 - 1271 - Illegal mix of collations for operation 'concat_ws'
[Msg] [Sync] Synchronization Failed: MD5 signature preparation failure
[Msg] [Sync] Finished - Unsuccessful Synchronization
[Msg] [Sync] Source Server: ROLLBACK
[Msg] [Sync] Target Server: ROLLBACK
--------------------------------------------------

2、Mysql 记录日志如下

如何查看Mysql日志记录？

event_time	user_host	thread_id	server	command_type	argument
2020年8月4日 14:34:55	[rdis] @ localhost [127.0.0.1]	110872	0	Connect	rdis@localhost on
2020年8月4日 14:34:55	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Query	SET NAMES utf8
2020年8月4日 14:34:55	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Query	SHOW VARIABLES LIKE 'lower_case_%'
2020年8月4日 14:34:55	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Query	SHOW VARIABLES LIKE 'profiling'
2020年8月4日 14:34:55	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Query	SHOW CHARACTER SET
2020年8月4日 14:34:55	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Query	SHOW COLLATION
2020年8月4日 14:34:55	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Query	SHOW DATABASES
2020年8月4日 14:34:56	[rdis] @ localhost [127.0.0.1]	110873	0	Connect	rdis@localhost on
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Query	SET NAMES utf8
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Query	SHOW VARIABLES LIKE 'lower_case_%'
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Query	SHOW VARIABLES LIKE 'profiling'
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Query	SHOW CHARACTER SET
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Query	SHOW COLLATION
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Query	SHOW DATABASES
2020年8月4日 14:34:56	[rdis] @ localhost [127.0.0.1]	110874	0	Connect	rdis@localhost on
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	SET NAMES utf8
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Init DB	atdps_a
2020年8月4日 14:34:56	[rdis] @ localhost [127.0.0.1]	110875	0	Connect	rdis@localhost on
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Query	SET NAMES utf8
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Init DB	atdps_a
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Query	SET FOREIGN_KEY_CHECKS=0
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	SET AUTOCOMMIT=0
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	BEGIN
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Query	SET AUTOCOMMIT=0
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Query	BEGIN
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	SELECT * FROM `a1` LIMIT 0
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Query	SELECT * FROM `prescription_medicine` LIMIT 0
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	SET net_write_timeout=86400
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	SELECT BINARY CONCAT_WS(',',`SID`) ckey,MD5(CONCAT_WS(',',MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`),- 1),IFNULL(LENGTH(`PATIENT_ID`),- 1))),`SID`,`PATIENT_ID`)) md5a,`SID` FROM    `a1`ORDER BY ckey
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Query	ROLLBACK
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Query	ROLLBACK
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110872	0	Quit
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110874	0	Quit
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110873	0	Quit
2020年8月4日 14:34:56	rdis[rdis] @ localhost [127.0.0.1]	110875	0	Quit

2.1 其报错语句如下

SELECT BINARY CONCAT_WS(',',`SID`) ckey,MD5(CONCAT_WS(',',MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`),- 1),IFNULL(LENGTH(`PATIENT_ID`),- 1))),`SID`,`PATIENT_ID`)) md5a,`SID` FROM	`a1`ORDER BY ckey

2.1.1、美化

SELECT
	BINARY CONCAT_WS(',', `SID`) ckey,
	MD5(
		CONCAT_WS(
			',',
			MD5(
				CONCAT_WS(
					',',
					IFNULL(LENGTH(`SID`) ,- 1),
					IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
				)
			),
			`SID`,
			`PATIENT_ID`
		)
	) md5a,
	`SID`
FROM
	`a1`
ORDER BY
	ckey

2.1.2 问题分析

[Err] 1271 - Illegal mix of collations for operation 'concat_ws'

 2.1.2.1、对于查询进行拆分，插入临时表进行分析

DROP TABLE IF EXISTS t2;CREATE TABLE if  NOT EXISTS t2 SELECT
	BINARY CONCAT_WS(',', `SID`) ckey,
	SID as  SID,
	PATIENT_ID as  PATIENT_ID,
	CONCAT_WS(
					',',
					IFNULL(LENGTH(`SID`) ,- 1),
					IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
				) as l1,
			MD5(
				CONCAT_WS(
					',',
					IFNULL(LENGTH(`SID`) ,- 1),
					IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
				)
			) as l2,

	MD5(
		CONCAT_WS(
			',',
			MD5(
				CONCAT_WS(
					',',
					IFNULL(LENGTH(`SID`) ,- 1),
					IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
				)
			),
			`SID`,
			`PATIENT_ID`
		)
	) md5a
FROM
	`a1`
ORDER BY
	ckey;
SELECT COLUMN_NAME,CHARACTER_SET_NAME,COLLATION_NAME FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA  = 'atdps_a' AND TABLE_NAME = 't2';

 2.1.2.2、查询结果 

 

2.1.2.3、分析

下面语句中 “MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`) ,- 1),IFNULL(LENGTH(`PATIENT_ID`) ,- 1)))” 的字符集是【utf8mb4    utf8mb4_general_ci】而Patient_ID的字符集是【gbk    gbk_chinese_ci】，而CONCAT_WS不允许多个字符集的数据进行字符串拼接。

----------------------------------------------------------------------------------------------    

MD5(
        CONCAT_WS(
            ',',
            MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`) ,- 1),IFNULL(LENGTH(`PATIENT_ID`) ,- 1))),
            `SID`,
            `PATIENT_ID`
        )
    ) md5a

---------------------------------------------------------------------------------------------------

解决方案：使用 CONVERT 函数将不一致的字符集数据转换为一致

---------------------------------------------------------------------------------------------------

    MD5(
        CONCAT_WS(
            ',',
            CONVERT(

             MD5(CONCAT_WS(',',IFNULL(LENGTH(`SID`) ,- 1),IFNULL(LENGTH(`PATIENT_ID`) ,- 1)))

            USING gbk ) COLLATE gbk_chinese_ci,
            `SID`,
            `PATIENT_ID`
        )
    ) md5a

---------------------------------------------------------------------------------------------------

2.1.3、修复问题

SELECT
	BINARY CONCAT_WS(',', `SID`) ckey,
	
	MD5(
		CONCAT_WS(
			',',
			CONVERT(MD5(
				CONCAT_WS(
					',',
					IFNULL(LENGTH(`SID`) ,- 1),
					IFNULL(LENGTH(`PATIENT_ID`) ,- 1)
				)
			) USING gbk ) COLLATE gbk_chinese_ci,
			`SID`,
			`PATIENT_ID`
		)
	) md5a,
	`SID`
FROM
	`a1`
ORDER BY
	ckey;