php - MySQL 查询不适用于 NOT IN

Question

我正在编写一个无法正常工作的查询

我的查询:

SELECT * 
FROM admin_marker 
WHERE admin_marker.city NOT IN (SELECT target FROM messsage)

它说

#1267 - Illegal mix of collations
(utf8_general_ci,IMPLICIT) and
(utf8_unicode_ci,IMPLICIT) for operation '='

Answer 1

您遇到的问题是由于两个表之间的排序规则不兼容。解决它的一种方法是在查询中使用 COLLATE 子句:

SELECT * 
FROM admin_marker 
WHERE admin_marker.city NOT IN (SELECT target COLLATE utf8_general_ci 
                                FROM messsage)

Demo here