我需要能够像\xF0\x9F\x94\xA5在我的数据库中那样存储字符,根据这篇文章,这些字符需要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应用程序中将Java Persistency API(JPA)与GlassFish
3.1一起使用。在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直接进入查询(如建议在这个职位在这里)
@NamedQuery(name = "myTable.findByMyColumn", query = "SELECT c COLLATE utf8mb4_general_ci FROM myTable c WHERE c.myColumn LIKE :myColumn")
但是仍然没有。
然后我尝试将排序规则直接放在连接中(在GlassFish中,我正在使用 connection_pool
),如我在此处阅读的
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或utf-32或任何其他为此事是更先进的那么简单UTF-8?