【MySQL】MySql UUID duplication BUG

问题背景

最近调试Kettle脚本，一个环境脚本正常执行，另外一个报错主键冲突。脚本如下。

INSERT INTO tj_dy_info (id, name,age)
(SELECT REPLACE(UUID(),'-','') AS id, xm as name, age FROM dy_info)

错误原因

检查正常执行的MySQL版本8.0.25，出错的MySQL版本5.7.32。

在Stack Overflow上搜索到了相关问题

https://stackoverflow.com/questions/9750536/mysql-uuid-duplication-bug

The unexpect result came out because there are inexplicit conversion happend when your charset client and charset result is not utf8;

1 uuid()output is utf8, no matter what your charset is ;

2 when your charset client and charset result is some of high priority than utf8 such as utf8mb4 , then the inexplicit conversion happen,other lower priority charset like latin1 that work fine;

3 inexplicit conversion will turn uuid() into constant string before sql execute,so after sql execute finish , the same uuid() return

隐式转换会将UUID()的返回值从UTF8转换成优先级更高的UTF8MB4，转换过程导致结果成为了常量。

解决方法

推荐解决方法如下：

-- 推荐使用
select replace( convert(uuid() using utf8mb4), '-','') from dy_info;
select replace( uuid(),_utf8'-',_utf8'') from dy_info;

设置JDBC连接

Navicat->【MySQL连接】->【编辑连接】->【高级】->【编码】设置为UTF-8测试问题解决。

修改JDBC连接。【未测试】不建议使用，可能导致数据乱码

String jdbcUrl = "jdbc:mysql://localhost:3306/test" +
        "?useUnicode=true" +
        "&characterEncoding=UTF-8" +
        "&connectionCollation=utf8_general_ci";

附：经不完全测试，存在此问题的函数

REPLACE(UUID(),'-','')
CONCAT(UUID(),'-')

参考博客：https://www.imaegoo.com/2019/jdbc-uuid-duplication/