非法的排序规则混合MySQL错误

Question

在处理大量数据时出现此奇怪的错误...Error Number: 1267Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='SELECT COUNT(*) as num from keywords WHERE campaignId='12' AND LCASE(keyword)='hello again 昔 ã‹ã‚‰ ã‚ã‚‹ å ´æ‰€'我该怎么解决？我可以以某种方式转义字符串，以免发生此错误，还是我需要以某种方式更改表编码，如果是，应该将其更改为什么？

Answer 1

SET collation_connection = 'utf8_general_ci';

然后为您的数据库

ALTER DATABASE your_database_name CHARACTER SET utf8 COLLATE utf8_general_ci;

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL有时会毫无理智地潜入瑞典。

Answer 2

您应该将表编码和连接编码都设置为UTF-8：

ALTER TABLE keywords CHARACTER SET UTF8; -- run once

和

SET NAMES 'UTF8';

SET CHARACTER SET 'UTF8';