java 带有 JPA 的 MySQL：排序规则 (utf8mb4_general_ci,IMPLICIT) 和 (utf8_general_ci,COERCIBLE) 的非法混合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26279029/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL with JPA: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
提问by GameDroids
I need to be able to store characters like \xF0\x9F\x94\xA5in my database, which, according to this postneed UTF8mb4encoding.
我需要能够\xF0\x9F\x94\xA5在我的数据库中存储字符，根据这篇文章需要UTF8mb4编码。
So I set up my database with
所以我设置了我的数据库
CREATE DATABASE `myDB` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
and verified in the MySQL shell if it was effective:
并在 MySQL shell 中验证它是否有效：
SHOW FULL COLUMNS FROM myTable;
+---------+------------------+--------------------+----
| Field | Type | Collation | ...
+---------+------------------+--------------------+-----
| id | int(10) unsigned | NULL | ...
| myColumn| text | utf8mb4_general_ci | ...
+---------+------------------+--------------------+-----
So far so good.
到现在为止还挺好。
After running my program, I got this Exception:
运行我的程序后，我得到了这个异常：
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'like'
Error Code: 1267
For the record: I am using the Java Persistency API (JPA) in my webapplication with GlassFish 3.1. The Exceptiongets thrown when executing a named query:
作为记录：我在带有 GlassFish 3.1 的 Web 应用程序中使用 Java Persistency API (JPA)。在Exception执行命名查询时被抛出：
@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c FROM myTable c WHERE c.myColumn LIKE :myColumn")
However, it appears that the error only occurs when the queried String actually contains those weired emoji characters (\xF0\x9F\x94\xA5)
但是，似乎只有当查询的 String 实际上包含那些 weired emoji 字符 ( \xF0\x9F\x94\xA5)时才会发生错误
Call: SELECT id, myColumn FROM myDB.myTable WHERE myColumn LIKE ?
bind => [Something something Lorem Ipsum ]
So I thought, that somewhere might still be a utf8_general_ciset up and I tried putting the COLLATIONdirectly into the query (as suggested in this post here)
所以我想，某个地方可能仍然是一个utf8_general_ci设置，我尝试将COLLATION直接放入查询中（如本文中建议的那样）
@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c COLLATE utf8mb4_general_ci FROM myTable c WHERE c.myColumn LIKE :myColumn")
But still nothing.
但还是什么都没有。
Then I tried putting the collation directly in the connection (in GlassFish I am using a connection_pool) as I read here
然后我尝试将排序规则直接放在连接中（在 GlassFish 中我使用的是connection_pool），因为我在这里阅读
characterEncoding, UTF8mb4
But GlassFish said only Connection could not be allocated because: Unsupported character encoding 'UTF8mb4'
但 GlassFish 只说 Connection could not be allocated because: Unsupported character encoding 'UTF8mb4'
The last thing I did was checking the Database System (I am using MariaDB)
我做的最后一件事是检查数据库系统（我使用的是 MariaDB）
show variables WHERE variable_name like "col%";
+----------------------+------------------+
| Variable_name | Value |
+----------------------+------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf32_general_ci |
| collation_server | utf8_general_ci |
+----------------------+------------------+
And now I am completely lost...
现在我完全迷失了......
What can I do to use utf8mb4or utf-32or anything else for that matter that is more advanced that simple UTF-8 ?
我可以做些什么来使用utf8mb4或utf-32或其他任何比简单的 UTF-8 更高级的东西？
回答by guido
You do not need any change in the Java side, as utf8mb4is just UTF-8in Java.
您不需要在 Java 方面进行任何更改，utf8mb4就像UTF-8在 Java 中一样。
Instead, as you can see here:
相反，正如您在此处看到的：
show variables WHERE variable_name like "col%";
+----------------------+------------------+
| Variable_name | Value |
+----------------------+------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf32_general_ci |
| collation_server | utf8_general_ci |
+----------------------+------------------+
your connection setting is still utf8_general_ci; to set it at the connection level, one option is to execute the (mysql specific) query:
您的连接设置仍然是utf8_general_ci；要将其设置在连接级别，一种选择是执行（特定于 mysql 的）查询：
SET NAMES='utf8mb4'
before any attempt to use the utf8mb4 collation; or, generally for the mysql server, in /etc/my.cnf:
在尝试使用 utf8mb4 排序规则之前；或者，通常对于 mysql 服务器，在/etc/my.cnf：
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
Another option without changing the connection string, is using a jdbc driver version >= 5.1.13: http://www.opensubscriber.com/message/[email protected]/14151747.html
不更改连接字符串的另一个选项是使用 jdbc 驱动程序版本 >= 5.1.13：http: //www.opensubscriber.com/message/[email protected]/14151747.html
