MySQL查询不适用于NOT IN

时间:2021-11-28 11:28:57

I am writing a query that not work correctly

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

My query:

我的查询:

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

It says

它说

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

#1267 - 操作'='的非法混合排序(utf8_general_ci,IMPLICIT)和(utf8_unicode_ci,IMPLICIT)

3 个解决方案

#1


16  

The problem you are facing is due to incompatible collations between the two tables. One way to come around it is to use COLLATE clause in your query:

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

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

Demo here

在这里演示

#2


3  

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query.

这通常是通过比较两个不兼容的排序字符串或尝试将不同排序规则的数据选择到组合列中引起的。 COLLATE子句允许您指定查询中使用的排序规则。

Or you can ALTER TABLE to match the COLLATE

或者您可以使用ALTER TABLE来匹配COLLATE

#3


3  

problem is in the collation between two tables , so please try COLLATE for this , may be this is resolve by the Help of COLLATE easily .

问题是在两个表之间的整理,所以请尝试COLLATE为此,可能这是由COLLATE的帮助轻松解决。

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

and also check that the data base of that is same

并检查其数据库是否相同

incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query.

不兼容的排序规则或尝试将不同排序规则的数据选择到组合列中。 COLLATE子句允许您指定查询中使用的排序规则。

#1


16  

The problem you are facing is due to incompatible collations between the two tables. One way to come around it is to use COLLATE clause in your query:

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

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

Demo here

在这里演示

#2


3  

This is generally caused by comparing two strings of incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query.

这通常是通过比较两个不兼容的排序字符串或尝试将不同排序规则的数据选择到组合列中引起的。 COLLATE子句允许您指定查询中使用的排序规则。

Or you can ALTER TABLE to match the COLLATE

或者您可以使用ALTER TABLE来匹配COLLATE

#3


3  

problem is in the collation between two tables , so please try COLLATE for this , may be this is resolve by the Help of COLLATE easily .

问题是在两个表之间的整理,所以请尝试COLLATE为此,可能这是由COLLATE的帮助轻松解决。

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

and also check that the data base of that is same

并检查其数据库是否相同

incompatible collation or by attempting to select data of different collation into a combined column. The clause COLLATE allows you to specify the collation used in the query.

不兼容的排序规则或尝试将不同排序规则的数据选择到组合列中。 COLLATE子句允许您指定查询中使用的排序规则。