如何使用 Spring Boot 在注释或 application.properties 文件中将表属性的排序规则设置为 utf8_bin [英] How to set collation for table attribute as utf8_bin in either annotation or application.properties file using Spring Boot

问题描述

如何使用 Spring Boot 在注解或 application.properties 文件中将表属性的排序规则设置为 utf8_bin?

How do I set the collation for table attribute as utf8_bin in either annotation or the application.properties file using Spring Boot?

我尝试了很多方法，但都没有奏效.你能帮忙吗?

I have tried many ways but they did not work. Can you help?

我尝试了以下方法.

首先:像这样使用@Column注解:

First: Using @Column annotation like this:

@Column(name = "user_id",columnDefinition="VARCHAR(255) COLLATE utf8_bin")

第二:

 @Column(columnDefinition="VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin")

第三:使用 application.properties 文件

Third: Using application.properties file

spring.jpa.properties.hibernate.connection.characterEncoding=utf-8
spring.jpa.properties.hibernate.connection.CharSet=utf-8
spring.jpa.properties.hibernate.connection.useUnicode=true
spring.jpa.properties.hibernate.connection.collationConnection=utf8_bin

第四:

spring.datasource.url =jdbc:mysql://localhost:3306/iot_schema?createDatabaseIfNotExist=true&useUnicode=true&connectionCollation=utf8_bin&characterSetResults=utf8

推荐答案

这是一个受类似问题答案启发的解决方案:Set Table character- 使用 Hibernate 方言设置/整理?

Here's a solution inspired by answer to similar question: Set Table character-set/collation using Hibernate Dialect?

扩展首选的 MySQL 方言并覆盖其 getTableTypeString() 方法，如下所示:

Extend the preferred MySQL dialect and override its getTableTypeString() method like this:

public class MySQLCustomDialect extends MySQL8Dialect {
    @Override
    public String getTableTypeString() {
        return " ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin";
    }
}

设置要在 application.properties 中使用的类:

Set that class to be used in application.properties:

spring.jpa.properties.hibernate.dialect=my.package.MySQLCustomDialect

这是生成的 SQL 查询:

Here's the generated SQL query:

    create table test_table (
      ...
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

这篇关于如何使用 Spring Boot 在注释或 application.properties 文件中将表属性的排序规则设置为 utf8_bin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！