向MySQL插入UUID时报'Incorrect string value'错误求助

我定义了如下Java数据类：

@Data
@JsonIgnoreProperties(ignoreUnknown = true)
@Entity
@Table(name = "task")
public class Task {

    @Id
    @Column(name = "id", nullable = false, updatable = false)
    private UUID id;  // = UUID.randomUUID()

    @Column(name = "account_id", nullable = false)
    private Long accountId;

    ...
}

尝试将该对象保存至MySQL 5.7数据库时，出现以下错误：

insert 
into
    `task` (
        `account_id`, `created_at`, `task_status`, `task_type`, `id`
    ) 
values
    (?, ?, ?, ?, ?)
2022-08-15 08:57:57.943 TRACE 588 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [BIGINT] - [1]
2022-08-15 08:57:57.943 TRACE 588 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [TIMESTAMP] - [2022-08-15T08:57:57.943419Z]
2022-08-15 08:57:57.943 TRACE 588 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [VARCHAR] - [RUNNING]
2022-08-15 08:57:57.943 TRACE 588 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [4] as [VARCHAR] - [USER_IMPORT]
2022-08-15 08:57:57.944 TRACE 588 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [5] as [BINARY] - [c1808d19-f448-4d4f-9908-9e767d56b04f]
2022-08-15 08:57:57.945 ERROR 588 --- [           main] o.h.e.jdbc.batch.internal.BatchingBatch  : HHH000315: Exception executing batch [java.sql.BatchUpdateException: Incorrect string value: '\xC1\x80\x8D\x19\xF4H...' for column 'id' at row 1], SQL: insert into `task` (`account_id`, `created_at`, `task_status`, `task_type`, `id`) values (?, ?, ?, ?, ?)
2022-08-15 08:57:57.945  WARN 588 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1366, SQLState: HY000
2022-08-15 08:57:57.945 ERROR 588 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Incorrect string value: '\xC1\x80\x8D\x19\xF4H...' for column 'id' at row 1
Tests run: 1, Failures: 0, Errors: 1, Skipped: 0, Time elapsed: 0.04 sec <<< FAILURE! - in com.validity.entitlementui.service.TaskServiceIntegrationTest
saveTaskTest(com.validity.entitlementui.service.TaskServiceIntegrationTest)  Time elapsed: 0.036 sec  <<< ERROR!
org.springframework.orm.jpa.JpaSystemException: could not execute batch; nested exception is org.hibernate.exception.GenericJDBCException: could not execute batch
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)

数据库中id字段定义为VARCHAR(36)，字符集设置为utf8mb4，请问问题原因及解决办法？

从日志关键信息可以看出：Hibernate将UUID类型的id参数以[BINARY]类型绑定，但数据库里id是VARCHAR(36)字符串类型。二进制数据被当作字符串插入时，会生成MySQL无法识别的非法字符序列（如日志中的\xC1\x80\x8D\x19\xF4H...），触发1366错误。

Hibernate默认对UUID类型的映射逻辑会根据数据库方言选择存储方式，在MySQL 5.7环境下，可能默认选择了二进制存储，和你的字符串类型字段不匹配，导致插入失败。

以下方案任选其一即可：

方案1：指定UUID的映射类型为字符串 #

在UUID字段上添加注解，强制Hibernate将其映射为字符串类型：

@Id
@Column(name = "id", nullable = false, updatable = false)
@Type(type = "org.hibernate.type.UUIDCharType") // 也可简写为"uuid-char"
private UUID id;

如果是Hibernate 5.2+版本，也可以用@JdbcTypeCode指定SQL类型：

@Id
@Column(name = "id", nullable = false, updatable = false)
@JdbcTypeCode(SqlTypes.CHAR)
private UUID id;

方案2：将实体类的id字段改为String类型 #

直接用String存储UUID的字符串形式，绕过类型映射问题：

@Id
@Column(name = "id", nullable = false, updatable = false)
private String id = UUID.randomUUID().toString();

方案3：全局配置Hibernate的UUID映射 #

在Spring Boot配置文件中添加全局设置，让Hibernate默认将UUID映射为字符串：

# application.properties
spring.jpa.properties.hibernate.type.preferred_uuid_jdbc_type=CHAR

内容的提问来源于stack exchange，提问作者Matt