引起:java.sql.SQLException:不支持的字符编码'utf8mb4' [英] Caused by: java.sql.SQLException: Unsupported character encoding 'utf8mb4'

问题描述

我正在编写一个将大量使用 utf-8 编码的应用程序.对于服务器，我使用的是 Java servlet，而对于 UI，我使用的是带有 jsp 和 javascript 的 Java Servlet.
所以，在忽略了一段时间的胡言乱语之后，我决定解决utf-8编码问题.在寻找解决方案的同时，我做了一个过滤器类
我在这里遵循@cosoant 的回答:https://stackoverflow.com/questions/138948/how-to-get-utf-8-working-in-java-webapps 直到MySQL 数据库和表"(不包括)，因为我还在我的 <property name="hibernate.hbm2ddl.auto" value="create"/> 中开发我的 persistence.xml.
然后我明白了mysql的utf8并不完全是世界其他地方的utf 8，所以我将persistence.xml中的characterEncoding改为:<property name="hibernate.connection.characterEncoding" value="utf8mb4"/>另外，按照这个:https://mathiasbynens.be/notes/mysql-utf8mb4 链接我升级了 MySqlServer，并添加:

I am writing an app that is going to heavily use utf-8 encoding. For the server I'm using java servlets, and for the UI I'm using Java Servlets with jsp and javascript.
So, after ignoring the gibberish for some time, I decided to solve the utf-8 encoding issue. While looking for a solution, I made a filter class
I followed @cosoant's answer here: https://stackoverflow.com/questions/138948/how-to-get-utf-8-working-in-java-webapps up until "MySQL database and tables" (excluding), because I am still developing with <property name="hibernate.hbm2ddl.auto" value="create" /> in my persistence.xml.
Then I understood that mysql's utf8 is not exactly the rest of the world's utf 8, so I changed characterEncoding in persistence.xml to: <property name="hibernate.connection.characterEncoding" value="utf8mb4" /> Also, following this : https://mathiasbynens.be/notes/mysql-utf8mb4 link I upgraded MySqlServer, and added:

[客户]默认字符集 = utf8mb4

[client] default-character-set = utf8mb4

[mysql] 默认字符集 = utf8mb4

[mysql] default-character-set = utf8mb4

[mysqld] 字符集客户端握手 = FALSE 字符集服务器 =utf8mb4 校对服务器 = utf8mb4_unicode_ci

[mysqld] character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

到mysql.cnf.我通过在终端中键入 sudo service mysql restart 重新启动了 mysql，并确保当我进入 mysql 命令行 UI 并键入:SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' ORVariable_name LIKE 'collat​​ion%'; 这个:

to mysql.cnf. I restarted mysql by typing sudo service mysql restart in terminal and made sure that when I go into mysql command line UI and type: SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; this:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

就是我得到的.
我还用 mysql-connector-java-5.1.44-bin.jar 切换了 com.mysql.jdbc_5.1.5.jar .

is what I get.
I also switched the com.mysql.jdbc_5.1.5.jar with mysql-connector-java-5.1.44-bin.jar .

而且，每次尝试访问数据库时都会出现上述错误.
这是我的堆栈跟踪:

And still, I get the above error each time I try to access the DB.
this is my stack trace:

Exception in thread "main" java.lang.ExceptionInInitializerError
    at tests.Test.main(Test.java:38)
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:271)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:233)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:242)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:858)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:885)
    at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:58)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:39)
    at utils.HibernateUtil.<clinit>(HibernateUtil.java:9)
    ... 1 more
Caused by: org.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:105)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:123)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:41)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:58)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections.addConnections(PooledConnections.java:123)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections.<init>(PooledConnections.java:42)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections.<init>(PooledConnections.java:20)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections$Builder.build(PooledConnections.java:161)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.buildPool(DriverManagerConnectionProviderImpl.java:109)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:72)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:242)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.buildJdbcConnectionAccess(JdbcEnvironmentInitiator.java:145)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:66)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:259)
    ... 15 more
Caused by: java.sql.SQLException: Unsupported character encoding 'utf8mb4'.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2549)
    at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2519)
    at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3140)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:763)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.D    Exception in thread "main" java.lang.ExceptionInInitializerError
    at tests.Test.main(Test.java:38)
Caused by: org.hibernate.service.spi.ServiceException: Unable to create requested service [org.hibernate.engine.jdbc.env.spi.JdbcEnvironment]
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:271)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:233)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.engine.jdbc.internal.JdbcServicesImpl.configure(JdbcServicesImpl.java:51)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:242)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.handleTypes(MetadataBuildingProcess.java:352)
    at org.hibernate.boot.model.process.spi.MetadataBuildingProcess.complete(MetadataBuildingProcess.java:111)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.metadata(EntityManagerFactoryBuilderImpl.java:858)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:885)
    at org.hibernate.jpa.HibernatePersistenceProvider.createEntityManagerFactory(HibernatePersistenceProvider.java:58)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:55)
    at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:39)
    at utils.HibernateUtil.<clinit>(HibernateUtil.java:9)
    ... 1 more
Caused by: org.hibernate.exception.JDBCConnectionException: Error calling Driver#connect
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator$1$1.convert(BasicConnectionCreator.java:105)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.convertSqlException(BasicConnectionCreator.java:123)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:41)
    at org.hibernate.engine.jdbc.connections.internal.BasicConnectionCreator.createConnection(BasicConnectionCreator.java:58)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections.addConnections(PooledConnections.java:123)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections.<init>(PooledConnections.java:42)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections.<init>(PooledConnections.java:20)
    at org.hibernate.engine.jdbc.connections.internal.PooledConnections$Builder.build(PooledConnections.java:161)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.buildPool(DriverManagerConnectionProviderImpl.java:109)
    at org.hibernate.engine.jdbc.connections.internal.DriverManagerConnectionProviderImpl.configure(DriverManagerConnectionProviderImpl.java:72)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.configureService(StandardServiceRegistryImpl.java:94)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:242)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService(AbstractServiceRegistryImpl.java:210)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.buildJdbcConnectionAccess(JdbcEnvironmentInitiator.java:145)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:66)
    at org.hibernate.engine.jdbc.env.internal.JdbcEnvironmentInitiator.initiateService(JdbcEnvironmentInitiator.java:35)
    at org.hibernate.boot.registry.internal.StandardServiceRegistryImpl.initiateService(StandardServiceRegistryImpl.java:88)
    at org.hibernate.service.internal.AbstractServiceRegistryImpl.createService(AbstractServiceRegistryImpl.java:259)
    ... 15 more
Caused by: java.sql.SQLException: Unsupported character encoding 'utf8mb4'.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionPropertiesImpl.postInitialization(ConnectionPropertiesImpl.java:2549)
    at com.mysql.jdbc.ConnectionPropertiesImpl.initializeProperties(ConnectionPropertiesImpl.java:2519)
    at com.mysql.jdbc.ConnectionImpl.initializeDriverProperties(ConnectionImpl.java:3140)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:763)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:38)
    ... 30 moreelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:389)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
    at org.hibernate.engine.jdbc.connections.internal.DriverConnectionCreator.makeConnection(DriverConnectionCreator.java:38)
    ... 30 more

我想我已经陷入困境.
我很感激任何帮助.谢谢！

I think I got to the point where I'm stuck.
I'd appreciate any help. Thanks!

推荐答案

在 hibernate xml 配置文件中试试这个:

Try this in the hibernate xml configuration file:

<property name="hibernate.connection.CharSet">utf8mb4</property> 
<property name="hibernate.connection.characterEncoding">utf8</property>
<property name="hibernate.connection.useUnicode">true</property>

同时对连接 url 进行如下更改:

Also make changes to the connection url as follows:

db.url=jdbc:mysql://localhost:3306/db_name?useUnicode=true&character_set_server=utf8mb4

以上改动足以让我从 utf8 升级到 utf8mb4 字符集方案.

The above changes were enough for me to upgrade from utf8 to utf8mb4 charset scheme.

作为旁注，我想澄清一下，UTF8 是字符编码，而 UTF8MB4 是 MySQL 支持的字符集.UTF8MB4 是 UTF8 的超集.有关详细信息，请参阅此链接:https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

As a side note I would like to make one clarification that UTF8 is the character encoding while UTF8MB4 is a character set that MySQL supports. UTF8MB4 is a superset to UTF8. Refer this link for more information: https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

这篇关于引起:java.sql.SQLException:不支持的字符编码'utf8mb4'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！