Incorrect string value: '\xE7\xBB\xA0\xEF\xBC\x84...' for column 'name_zh' at row 1

记录解决 Incorrect string value: ‘\xE7\xBB\xA0\xEF\xBC\x84…’ for column ‘name_zh’ at row 1 方法

今天在学习Spring Security + Spring Data Jpa 在启动测试类时报错：

我主要是在做Spring Security 通过Spring Data Jpa 通过bean 自动生成用户表、角色表 并向表内插入数据 时出现错误

-04-22 17:49:39.605 INFO 15576 --- [ main] ingsecurityspringdatajpaApplicationTests : Started SpringsecurityspringdatajpaApplicationTests in 3.964 seconds (JVM running for 5.003)Hibernate: insert into t_user (account_non_expired, account_non_locked, credentials_non_expired, enabled, password, username) values (?, ?, ?, ?, ?, ?)Hibernate: insert into t_role (name, name_zh) values (?, ?)-04-22 17:49:39.807 WARN 15576 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1366, SQLState: HY000-04-22 17:49:39.807 ERROR 15576 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Incorrect string value: '\xE7\xBB\xA0\xEF\xBC\x84...' for column 'name_zh' at row 1org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statementat org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:353)at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:255)at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:528)at ......-04-22 17:49:39.843 INFO 15576 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown initiated...-04-22 17:49:39.850 INFO 15576 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Shutdown completed.Process finished with exit code -1

重新检查了代码，发现代码没有问题，然后打开数据库发现，表已经生成，然后查看错误代码，发现了一条：

-04-22 17:49:39.807 WARN 15576 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 1366, SQLState: HY000-04-22 17:49:39.807 ERROR 15576 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : Incorrect string value: '\xE7\xBB\xA0\xEF\xBC\x84...' for column 'name_zh' at row 1org.springframework.orm.jpa.JpaSystemException: could not execute statement; nested exception is org.hibernate.exception.GenericJDBCException: could not execute statement

那就确定是在添加数据时出现错误，查看了一下生成表的表结构，发现表和库的字符集有问题：

修改库的字符集，并将生成的表删除：

然后重新执行方法，成功了

我解决这个问题其实并没有上面写的这么顺利，各种办法都试了个遍，最终才知道是字符集的问题