Spring Data Jpa Mysql使用utf8mb4編碼1 問題:資料庫字元集和排序規則不一緻2 解決方法

• 1 问题:数据库字符集和排序规则不一致
• 2 解决方法
  • 2.1 将本地数据库改成utf8mb4，utf8mb4_unicode_ci
    • 2.1.1 找到my.cnf，一般在/etc/mysql/my.cnf，可以用locate my.cnf查找。修改下面三部分
    • 2.1.2 重启数据库，检查变量
    • 2.1.3 将已经建好的数据库、表和列转换成utf8mb4,utf8mb4_unicode_ci
  • 2.2 在Spring Boot中配置，不修改本地数据库
    • 2.2.1 在JPA建表时设置表的编码和排序规则
    • 2.2.2 设置连接初始化SQL

1 问题:数据库字符集和排序规则不一致

最近需要向一个已有的数据库进行扩充（已有数据库是由PHP建的，后来由Java进行扩展），但是出现了新表和旧表无法建立外键的问题，后来发现是因为编码问题，服务器数据库和我本地数据库的字符集和排序规则不对应，服务器数据库使用的是utf8mb4，utf8mb4_unicode_ci而我本地使用的是utf8，utf8_general_ci。

2 解决方法

2.1 将本地数据库改成utf8mb4，utf8mb4_unicode_ci

该方法参考: 更改MySQL数据库的编码为utf8mb4

2.1.1 找到my.cnf，一般在/etc/mysql/my.cnf，可以用locate 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 
init_connect='SET NAMES utf8mb4'
           

2.1.2 重启数据库，检查变量

必须保证

系统变量	描述
character_set_client	(客户端来源数据使用的字符集)
character_set_connection	(连接层字符集)
character_set_database	(当前选中数据库的默认字符集)
character_set_results	(查询结果字符集)
character_set_server	(é»˜è®¤çš„å† éƒ¨æ“ä½œå­—ç¬¦é›†)

这几个变量必须是utf8mb4。

2.1.3 将已经建好的数据库、表和列转换成utf8mb4,utf8mb4_unicode_ci

更改数据库编码：ALTER DATABASE

database_name

CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

更改表编码：ALTER TABLE

table_name

CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

更改列编码：ALTER TABLE

table_name

CHANGE

column_name

column_name

VARCHAR( 36 ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL

Mysql官方文档

Database Character Set and Collation

Table Character Set and Collation

Column Character Set and Collation

如果我不想修改本地数据库呢？那就用下面这种方法。（我用的是第二种）

2.2 在Spring Boot中配置，不修改本地数据库

2.2.1 在JPA建表时设置表的编码和排序规则

1. 重写MySQL5InnoDBDialect#getTableTypeString()

public class MySQL5InnoDBDialectUtf8mb4 extends MySQL5InnoDBDialect {
    @Override
    public String getTableTypeString() {
        return "ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci";
    }
}
           

1. 配置hibernate.dialect

spring:
  jpa:
    properties:
      hibernate:
        dialect: com.xxx.MySQL5InnoDBDialectUtf8mb4
           

2.2.2 设置连接初始化SQL

配置Druid连接池,如果为其他连接池,设置对应的connectionInitSqls即可

DruidConfig.java

@Configuration
public class DruidConfig {
    @Value("${spring.datasource.druid.connection-init-sqls")
    private List<String> connectionInitSqls;

    @Bean
    public DruidDataSource dataSource() {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setConnectionInitSqls(connectionInitSqls);
        return dataSource;
    }
}
           

application.yml

spring:
  datasource:
    druid:
      connection-init-sqls: ["SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci"]
           

最后按上述设置Spring Data Jpa 生成的表、字段就会是utf8mb4，utf8mb4_unicode_ci

PS: mysql.url还是需要设置characterEncoding=utf8不然会出现中文乱码