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

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

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，转换过程导致结果成为了常量。

推荐的解决办法是：

将隐式转换改为显式转换

例如：

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

两条语句都可以。

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

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字符集如上一篇文章中所解释的那样使用。

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……

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()的返回值。

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

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

不存在此问题的函数

SUBSTRING(UUID(),1,36)