【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(),'-')