mysql COLLATE=utf8mb4_unicode_ci_collate utf8mb4_unicode_ci

记一次mysql COLLATE=utf8mb4_unicode_ci的坑

Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation ‘=’

场景1:

子查询时, 两张表的字符集编码不同,需要在字段后面 加上COLLATE utf8mb4_unicode_ci

报的错误为:

1267 - Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation ‘=’

解决方案:

在子查询的字段后面加上 COLLATE utf8mb4_unicode_ci

场景2:

表连接查询时, 两张表的字符集编码不同,需要在表连接on的后面 加上COLLATE utf8mb4_unicode_ci

SELECT
	sta.user_id AS userId,
	ifnull( a.customerCount, 0 ) AS customerCount,
	ifnull( ( ifnull( enterGroup.enterGroupChatCount, 0 ) / ifnull( a.customerCount, 0 ) ), 0.0000 ) AS customerIncomingGroupPercent,
	ifnull( enterGroup.enterGroupChatCount, 0 ) AS enterGroupChatCount 
FROM
	cpwx_member_customer_statistic sta
	LEFT JOIN (
	SELECT
		count( DISTINCT external_userid ) AS customerCount,
		user_id AS userId 
	FROM
		cpwx_customer_employee_rel 
	WHERE
		deleted = 0 
		AND clique_id = "22313852750823424" 
		AND add_time <= "2020-11-10 23:59:59" 
	GROUP BY
		user_id 
	) a ON sta.user_id = a.userId
	LEFT JOIN (
	SELECT
		count( DISTINCT chatUser.userid ) AS enterGroupChatCount,
		chat.`owner` AS userId 
	FROM
	-- 这里表连接的两张表字符集相同,都是COLLATE = utf8mb4_unicode_ci ,所以不需要加 COLLATE utf8mb4_unicode_ci 
		cpwx_groupchat_user chatUser
		LEFT JOIN cpwx_groupchat chat ON chatUser.chat_id = chat.chat_id 
	WHERE
		chatUser.deleted = 0 
		AND chatUser.type = 2 
		AND chatUser.clique_id = "22313852750823424" 
		AND chatUser.join_time <= "2020-11-10 23:59:59" 
		AND chat.clique_id = "22313852750823424" 
	GROUP BY
		chat.`owner` 
	) enterGroup ON enterGroup.userId = sta.user_id 
-- 这里表连接的两张表字符集不同,需要加上COLLATE utf8mb4_unicode_ci 
COLLATE utf8mb4_unicode_ci 
WHERE
	sta.deleted = 0 
	AND sta.clique_id = "22313852750823424" 
	AND sta.statistic_date >= "2020-11-09 00:00:00" 
	AND sta.statistic_date <= "2020-11-09 23:59:59" 
ORDER BY
	customerIncomingGroupPercent DESC,
	customerCount DESC,
	userId DESC
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

其中 cpwx_groupchat_user 和 cpwx_groupchat 表的字符集为utf8mb4_unicode_ci
cpwx_member_customer_statistic 和 cpwx_customer_employee_rel 字符集为utf8mb4_general_ci

总结:

字符集不同时,区分子查询还是表连接, 加载字段后面还是表连接后面,也是一点点试出来的