MySQL JDBC 在查询中调用 REPLACE(UUID(),'-','') 查出重复的 UUID

MySQL版本是5.7.25,最近升级了JDBC驱动,5.1.45 -> 8.0.13,导致一个SQL报异常,SQL是用一个子查询,拿到数据后插入到另一张表用的。

1
2
3
INSERT INTO table_2 (id, title, modified_date)
(SELECT REPLACE(UUID(),'-','') AS id, table_1.title AS title, NOW() AS modified_date
FROM table_1)

可见table_2的id是在子查询中,用REPLACE(UUID(),'-','')生成的。

这样的语句在MySQL 5.7.25命令行模式下,Workbench下,JDBC 5.1.45下都没有问题,但是JDBC升级到8.0.13后,却报了主键重复的异常。

ERROR SqlExceptionHelper - Duplicate entry 'ae3867585cc611e986e30e045157562c' for key 'PRIMARY'

于是搜索,在Stack Overflow上搜索到了相关问题,注意最佳答案似乎不准确,但后面的一个答案给予了我一些启示。

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

  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

新建一个工程,写一个简单的JDBC demo,从MySQL查询
SELECT REPLACE(UUID(),'-','') from settings

当JDBC版本为5.1.45时

当JDBC升级到8.0.13时,相同的查询语句,返回了不一样的结果

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

推荐的解决办法是:

将隐式转换改为显式转换

例如:

1
2
REPLACE(CONVERT(UUID() USING utf8mb4), '-', '')
REPLACE(UUID(), _utf8'-', _utf8'')

两条语句都可以。

那就这样改呗,又发现Hibernate会对以上statement自动预处理成以下的样子

1
2
REPLACE(CONVERT(UUID() USING[*] utf8mb4),'-','')
REPLACE(UUID(),_UTF8'-'[*],_UTF8'')

这显然会造成语法错误啊!

尴尬,继续搜索了为什么Hibernate会往查询语句里加[*],没有找到答案,最接近的问答在这里:
https://stackoverflow.com/questions/55040454/cant-find-an-error-in-sql-update-statement

在数据库连接字符串中加入&connectionCollation=utf8_general_ci

MySQL Connector/J 8.0 Developer Guide / Connector/J Reference / Using Character Sets and Unicode
https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-reference-charsets.html

因为没有utfmb3可以与连接选项charaterEncoding一起使用的Java样式字符集名称 ,所以utf8mb3用作连接字符集的唯一方法是对连接选项使用utf8mb3 collation(例如utf8_general_ci)的connectionCollation,这会强制设置utf8mb3字符集如上一篇文章中所解释的那样使用。

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

因为新版的JDBC默认连接编码为UTF8MB4
这会让JDBC连接时不再执行SET NAMES utf8mb4

已在上面的demo中测试这是起作用的。

但是:

  1. 对于大项目修改连接字符串的风险比较高;
  2. UTF8MB4可以存储和传输emoji表情,拥有更良好的兼容性,退到UTF8MB3不是好策略。

去掉REPLACE()函数

最后还是整个去掉了REPLACE……

1
2
3
INSERT INTO table_2 (id, title, modified_date)
(SELECT UUID() AS id, table_1.title AS title, NOW() AS modified_date
FROM table_1)

注:因为ID是VARCHAR(45)的,所以能装得下UUID()的返回值。

附:经不完全测试,存在此问题的函数

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

不存在此问题的函数

1
SUBSTRING(UUID(),1,36)

Comments

Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×