我需要能够存储像这样的字符\xF0\x9F\x94\xA5
在我的数据库中,根据这个帖子 https://stackoverflow.com/a/13754976/896249 need UTF8mb4
编码。
所以我设置了我的数据库
CREATE DATABASE `myDB` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci
并在MySQL shell中验证是否有效:
SHOW FULL COLUMNS FROM myTable;
+---------+------------------+--------------------+----
| Field | Type | Collation | ...
+---------+------------------+--------------------+-----
| id | int(10) unsigned | NULL | ...
| myColumn| text | utf8mb4_general_ci | ...
+---------+------------------+--------------------+-----
到目前为止,一切都很好。
运行我的程序后,我得到了这个例外:
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
作为记录:我在 Web 应用程序中使用 GlassFish 3.1 的 Java Persistence API (JPA)。这Exception
执行命名查询时抛出:
@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c FROM myTable c WHERE c.myColumn LIKE :myColumn")
然而,似乎只有当查询的字符串实际上包含那些奇怪的表情符号字符时才会发生错误(\xF0\x9F\x94\xA5
)
Call: SELECT id, myColumn FROM myDB.myTable WHERE myColumn LIKE ?
bind => [Something something Lorem Ipsum ????????]
所以我想,某个地方可能仍然是一个utf8_general_ci
设置并尝试将COLLATION
直接进入查询(按照建议在这篇文章中 https://stackoverflow.com/a/183624/896249)
@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c COLLATE utf8mb4_general_ci FROM myTable c WHERE c.myColumn LIKE :myColumn")
但还是什么都没有。
然后我尝试将排序规则直接放入连接中(在 GlassFish 中我使用的是连接池) as I 在这里读 https://stackoverflow.com/a/20546571/896249
characterEncoding, UTF8mb4
但 GlassFish 只说Connection could not be allocated because: Unsupported character encoding 'UTF8mb4'
我做的最后一件事是检查数据库系统(我正在使用 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 |
+----------------------+------------------+
现在我完全迷失了......
我可以做什么来使用utf8mb4
or utf-32
或者其他比简单的 UTF-8 更高级的东西?