java MySQL Workbench 和 JDBC 中的 utf8mb4
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44591895/
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
utf8mb4 in MySQL Workbench and JDBC
提问by motagirl2
I've been working with a UTF-8 encoded MySQL DB that now needs to be able to store 4-byte emojis, so I decided to change from utf8 encoding to utf8mb4:
我一直在使用 UTF-8 编码的 MySQL 数据库，它现在需要能够存储 4 字节的表情符号，所以我决定从 utf8 编码更改为 utf8mb4：
ALTER DATABASE bstdb CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE HISTORY CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE HISTORY CHANGE SOURCE_CONTEXT SOURCE_CONTEXT VARCHAR(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
And changed mysql.conf.d "character-set-server = utf8" to "character-set-server = utf8mb4"
并将 mysql.conf.d "character-set-server = utf8" 更改为 "character-set-server = utf8mb4"
After these steps, I am able to store emojis (as ),but only when the SQL query is executed in the MySQL console: When I try to launch the query from MySQL Workbench or from a Wildfly webapp, I am getting this error:
完成这些步骤后，我可以存储表情符号（as ），但仅当在 MySQL 控制台中执行 SQL 查询时：当我尝试从 MySQL Workbench 或 Wildfly webapp 启动查询时，我收到此错误：
Error Code: 1366. Incorrect string value: '\xF0\x9F\x92\xA2' for column 'SOURCE_CONTEXT' at row 1
错误代码：1366。不正确的字符串值：第 1 行“SOURCE_CONTEXT”列的“\xF0\x9F\x92\xA2”
I assume I need to change the way the clients are connecting to the DB, but I have no clue on how. I've read something on using "useUnicode=yes" in JDBC, but does not work.
我假设我需要更改客户端连接到数据库的方式，但我不知道如何更改。我读过一些关于在 JDBC 中使用“useUnicode=yes”的内容，但没有用。
${bdpath:3306/bstdb?useUnicode=yes}
${bdpath:3306/bstdb?useUnicode=yes}
Edit: As suggested in comments, I tried with:
编辑：正如评论中所建议的，我尝试了：
${bdpath:3306/bstdb?characterEncoding=UTF-8}
${bdpath:3306/bstdb?characterEncoding=UTF-8}
but no luck, I am getting the same "Incorrect string value: '\xF0\x9F\x92\xA2'" error.
但不幸的是，我收到了相同的“字符串值不正确：'\xF0\x9F\x92\xA2'”错误。
Also tried
也试过
${bdpath:3306/bstdb?useUnicode=true&characterEncoding=utf8mb4&}
${bdpath:3306/bstdb?useUnicode=true&characterEncoding=utf8mb4&}
but it refuses to stablish a connection.
但它拒绝建立连接。
Any idea on how to configure MySQL workbench and/or JDBC/Wildfly?
关于如何配置 MySQL 工作台和/或 JDBC/Wildfly 的任何想法？
MySQL version is 5.7.18
MySQL 版本是 5.7.18
MySQL WorkBench version is 6.0.8
MySQL WorkBench 版本为 6.0.8
JDBC driver version is 5.1.34
JDBC 驱动版本为 5.1.34
Thanks!
谢谢！
采纳答案by motagirl2
Finally, it works. It was an issue with stored procedures, that was still utf8 instead of utf8mb4 after the migration. It was a 2-steps solution.
最后，它起作用了。这是存储过程的问题，迁移后仍然是 utf8 而不是 utf8mb4。这是一个 2 步解决方案。
- As suggested by @mike-adamenko set my.cnf to have the following
- 正如@mike-adamenko 所建议的那样，将 my.cnf 设置为以下内容
[client] default-character-set = utf8mb4
[mysql] default-character-set = utf8mb4
[mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci
[客户端] 默认字符集 = utf8mb4
[mysql] 默认字符集 = utf8mb4
[mysqld] 字符集客户端握手 = FALSE 字符集服务器 = utf8mb4 校对服务器 = utf8mb4_unicode_ci
Execute in mysql:
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
Drop procedures involved, and create them again. They will be in utf8mb4. Can be checked with
在mysql中执行：
设置名称 utf8mb4 整理 utf8mb4_unicode_ci;
删除所涉及的程序，并重新创建它们。它们将在 utf8mb4 中。可以检查
SHOW PROCEDURE STATUS where name LIKE 'procedure_name';
SHOW PROCEDURE STATUS where name LIKE 'procedure_name';
回答by Mike Adamenko
Use characterEncoding=utf8for jdbc url
使用characterEncoding=utf8的JDBC URL
jdbc:mysql://x.x.x.x:3306/db?useUnicode=true&characterEncoding=utf8
Also check that you have configured MySQL to work with utf8mb4
还要检查您是否已配置 MySQL 以使用 utf8mb4
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
See here
看这里
回答by Alaa Nassef
回答by N00b Pr0grammer
You can follow the documentation available for MySQL to resolve your problem. Here's the MySQL documentation, that you could refer to.
您可以按照适用于 MySQL 的文档来解决您的问题。这是MySQL 文档，您可以参考。
Basically, your ALTER TABLEscripts can be changed as per the documentation mentioned above and then you could use the following parameter in your connection string for the changes to take effect.
基本上，您的ALTER TABLE脚本可以根据上面提到的文档进行更改，然后您可以在连接字符串中使用以下参数以使更改生效。
jdbc:mysql://localhost/yourdatabasename?useUnicode=true&characterEncoding=UTF-8
Please don't forget to restart your MySQL services after making the character set and the encoding changes.
请不要忘记在更改字符集和编码后重新启动 MySQL 服务。
