mysql - 操作 '=' 的排序规则 (utf8mb4_unicode_ci,EXPLICIT) 和 (utf8_general_ci,COERCIBLE) 的非法混合

Question

好吧，我放弃了。我已经进入这个错误 2 天了，我需要帮助。

免责声明:我将需要帮助来改进这个问题，并会尽力很好地描述手头的问题，到目前为止我为解决该问题所做的工作，并分享我已阅读的博客文章和文档寻找解决方案。

问题(也在上下文中提出):

So the question is, why does the same query behave differently when run from Rails instead of from the mysql command line? Specifically, where is "(utf8_general_ci,COERCIBLE)" coming from?

问题:Autoresponder.find_by(keyword: '😕') 失败并出现以下错误:

ActiveRecord::StatementInvalid: Mysql2::Error: Illegal mix of collations 
(utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) 
for operation '=': 
SELECT  `autoresponders`.* 
FROM `autoresponders`  
WHERE `autoresponders`.`keyword` = '😕' 
LIMIT 1

Autoresponder 是一个具有属性 keyword

的模型

我读到我需要指定我的排序规则。所以我测试了以下代码:

Autoresponder.where('keyword collate utf8mb4_unicode_ci = ?', '😕')

出现以下错误:

Illegal mix of collations 
(utf8mb4_unicode_ci,EXPLICIT) and (utf8_general_ci,COERCIBLE) 
for operation '=': 
SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword collate utf8mb4_unicode_ci = '😕')

所做的只是将排序规则从 IMPLICIT 更改为 EXPLICIT。

我尝试在 Sequel Pro 中运行查询并且它有效(使用和不使用 collat​​e 关键字)。为了清楚起见，这里是查询:

SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword collate utf8mb4_unicode_ci = '😕');

SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword = '😕 ');

并且有效!查询运行没有错误。我还运行了 mysql 并且也能够在那里运行查询。但是当我将查询粘贴到 mysql 命令行时，我注意到了一些事情。它自动使用字符的 Unicode 名称而不是实际字符。这是在 mysql 命令行中观察到的查询:

SELECT `autoresponders`.* 
FROM `autoresponders`  
WHERE (keyword collate utf8mb4_unicode_ci ='\U+1F615');

此查询有效。

那么问题来了，为什么同样的查询在 Rails 中失败但在 Sequel Pro 中有效？具体来说，“(utf8_general_ci,COERCIBLE)”是从哪里来的，我该如何解决这个问题？

我认为它可能来自 ActiveRecord，但在 Rails 控制台中运行 ActiveRecord::Base.connection.collat​​ion 返回 utf8mb4_unicode_ci

这是我的数据库字符编码和排序规则变量(以及检索它们的查询)。

SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
character_set_client        utf8mb4
character_set_connection    utf8mb4
character_set_database      utf8mb4
character_set_filesystem    binary
character_set_results       utf8mb4
character_set_server        latin1
character_set_system        utf8
collation_connection        utf8mb4_unicode_ci
collation_database          utf8mb4_unicode_ci
collation_server            latin1_swedish_ci

这是 Autoresponders 表的创建语法:

CREATE TABLE `autoresponders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `keyword` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `body` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `provisioned_number_id` int(11) DEFAULT NULL,
  `outgoing_provisioned_number_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

上下文:Rails 4.0.13，Mysql 5.6.22-1+deb.sury.org~precise+1-log

以下是我目前阅读的一些博客文章和 SO 文章: https://mathiasbynens.be/notes/mysql-utf8mb4

http://airbladesoftware.com/notes/fixing-mysql-illegal-mix-of-collations/

Is "SET CHARACTER SET utf8" necessary?

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

Not case sensitive search with active record

https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_collation_server

所有这些促使我创建了这个模因:

此致

精疲力尽的开发人员。

谢谢。

Answer 1

我遇到了类似的问题，最后解决了。一开始，我的MySQL conf是:

character-set-server = utf8
collation-server     = utf8_general_ci

有一天，我发现emoji只能用utf8mb4才能正确保存，所以我把指定列的字符集和排序规则改成这样:

  `nickname` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,

到目前为止，一切正常，数据可以通过 java web 应用程序正确保存和显示。

但是当我像这样查询数据时

SELECT * FROM table_name WHERE nickname LIKE '%😈%';

出现错误。

最后我将 mysql conf 更改为

character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci

一切顺利。

还要确保 SET NAMES utf8; 的任何调用都被删除或替换为 SET NAMES utf8mb4

Here is a screenshot of mysql client, notice the nickname attribute