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

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

2

3INSERT 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上搜索到了相关问题，注意最佳答案似乎不准确，但后面的一个答案给予了我一些启示。uuid()output is utf8, no matter what your charset is ;

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;

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

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

REPLACE(UUID(), _utf8'-', _utf8'')

两条语句都可以。

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

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

REPLACE(UUID(),_UTF8'-'[*],_UTF8'')

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

在数据库连接字符串中加入&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

4String jdbcUrl = "jdbc:mysql://localhost:3306/test" +

"?useUnicode=true" +

"&characterEncoding=UTF-8" +

"&connectionCollation=utf8_general_ci";

因为新版的JDBC默认连接编码为UTF8MB4

这会让JDBC连接时不再执行SET NAMES utf8mb4

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

但是：对于大项目修改连接字符串的风险比较高；

UTF8MB4可以存储和传输emoji表情，拥有更良好的兼容性，退到UTF8MB3不是好策略。

去掉REPLACE()函数

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

2

3INSERT 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

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

CONCAT(UUID(),'-')

不存在此问题的函数1SUBSTRING(UUID(),1,36)