Java 如何在 Spring 管理的 MySQL JDBC 连接上设置 useUnicode=true 和 characterEncoding=utf8 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19052676/
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
How to set useUnicode=true and characterEncoding=utf8 properties on Spring-managed MySQL JDBC connection
提问by Haxton Fale
I'm currently building a Spring MVC webapp and the database is the vital backend part. For whatever reason, however, Spring is refusing to process the data as UTF-8. Since the views, resources and browsers are set to Unicode, and so are the tables in the database (and also reading quite a few similar questions asked), I have established that the problem lies in the database connection.
我目前正在构建一个 Spring MVC webapp，数据库是至关重要的后端部分。但是，无论出于何种原因，Spring 都拒绝将数据处理为 UTF-8。由于视图、资源和浏览器设置为 Unicode，数据库中的表也是如此（并且还阅读了很多类似的问题），我已经确定问题出在数据库连接上。
The JDBC Driver should be provided two items in connectionProperties: useUnicode (set to yesand characterEncoding (set to utf8). It turns out, however, it's impossible.
JDBC Driver 应该在 connectionProperties 中提供两个项目：useUnicode（设置为yes和 characterEncoding（设置为utf8）。但事实证明，这是不可能的。
JDBC is a bean, and as such is configured via an XML file, like so:
JDBC 是一个 bean，因此通过 XML 文件进行配置，如下所示：
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/<database>" />
<property name="username" value="<not telling>" />
<property name="password" value="<not telling>" />
This setup converts all non-alphanumeric characters pulled from the database (such as arrows or Greek letters) into question marks. Which is, obviously, unacceptable.
此设置将从数据库中提取的所有非字母数字字符（例如箭头或希腊字母）转换为问号。这显然是不可接受的。
I tried multiple solutions: specified the JDBC URL as jdbc:mysql://localhost:3306/<database>?useUnicode=yes&characterEncoding=utf8, played with my.inifile (and MySQL Workbench) to force everything in the database to default to utf8charset, and something that caused the largest headache: adding <property name="connectionProperties" value="useUnicode=yes;characterEncoding=utf8" />. Turns out, it's literally impossible to set two connectionProperties within a single bean, because... there is no separating character mentioned anywhere (the bean will attempt to read it as trying to set yes;characterEncoding=utf8as the value of useUnicode). So my question is: how does I utf8?
我尝试了多种解决方案：将 JDBC URL 指定为jdbc:mysql://localhost:3306/<database>?useUnicode=yes&characterEncoding=utf8，使用my.ini文件（和 MySQL Workbench）强制数据库中的所有内容默认为utf8字符集，以及引起最大头痛的事情：添加<property name="connectionProperties" value="useUnicode=yes;characterEncoding=utf8" />. 事实证明，在单个 bean 中设置两个 connectionProperties 实际上是不可能的，因为……在任何地方都没有提到分隔字符（bean 将尝试将其读取为试图设置yes;characterEncoding=utf8为 的值useUnicode）。所以我的问题是：我如何使用 utf8？
采纳答案by hmjd
The problem may be caused by specifying utf8and not UTF-8. From Using Character Sets and Unicode:
该问题可能是由指定utf8而不是UTF-8 引起的。从使用字符集和 Unicode：
When specifying character encodings on the client side, use Java-style names. The following table lists MySQL character set names and the corresponding Java-style names...
在客户端指定字符编码时，请使用 Java 样式的名称。下表列出了 MySQL 字符集名称和相应的 Java 样式名称...
and utf8maps to UTF-8. Try the following JDBC URL:
和utf8映射到UTF-8。尝试以下 JDBC URL：
jdbc:mysql://localhost:3306/?useUnicode=yes&characterEncoding=UTF-8
jdbc:mysql://localhost:3306/?useUnicode=yes&characterEncoding=UTF-8
回答by Nikos Paraskevopoulos
Did you try:
你试过了吗：
<property name="connectionProperties">
<props>
<prop key="useUnicode">yes</prop>
<prop key="characterEncoding">utf8</prop>
</props>
</property>
And also: Did you try a simple Java client (console application) that connects to the DB? Does the UTF-8 work in this case?
还有：您是否尝试过连接到数据库的简单 Java 客户端（控制台应用程序）？UTF-8 在这种情况下有效吗？
回答by Conor O'Neill
Note that if you are using Spring Boot, you can do this using properties within application.propertiesinstead of having to add extra parameters to your connection strings:
请注意，如果您使用的是 Spring Boot，则可以使用其中的属性来执行此操作，application.properties而不必向连接字符串添加额外的参数：
Put this into application.properties:
将其放入 application.properties 中：
spring.datasource.connectionProperties=useUnicode=true;characterEncoding=utf-8;
回答by Kamil Kacperski
I had the same issue while using Spring-boot + embedded H2 + Hibernate, but the problem was at the moment of reading SQL script. (data.sql) The encoding in the database was broken every time when I it read any foreign character.
我在使用 Spring-boot + 嵌入式 H2 + Hibernate 时遇到了同样的问题，但问题出在读取 SQL 脚本的时候。(data.sql) 每次我读取任何外来字符时，数据库中的编码都会被破坏。
Adding the following line to my application.properties solved the problem:
将以下行添加到我的 application.properties 解决了问题：
spring.datasource.sqlScriptEncoding=UTF-8
spring.datasource.sqlScriptEncoding=UTF-8
If it is possible go to the console and add some data manually. If the foreign characters appear in the right way. Then this solution may work fine for you.
如果可能，请转到控制台并手动添加一些数据。如果外来字符以正确的方式出现。那么这个解决方案可能适合你。
I have found that solution here:
我在这里找到了解决方案：
http://ufasoli.blogspot.com/2014/07/spring-boot-jpa-broken-encoding-on.html
http://ufasoli.blogspot.com/2014/07/spring-boot-jpa-broken-encoding-on.html
回答by maruf571
I wasted time to generate utf-8 tables. Non of above worked for me. At last I have change my mysql config file & it works like a charm. If you are in linux(I am in ubuntu, I am sure there is a file for windows) open the file /etc/mysql/my.cnf and add the following code.
我浪费时间生成 utf-8 表。以上非对我有用。最后我更改了我的 mysql 配置文件，它就像一个魅力。如果你在linux（我在ubuntu，我肯定有windows的文件）打开文件/etc/mysql/my.cnf并添加以下代码。
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
Don't forget to restart mysql service.
不要忘记重启mysql服务。
