Mysql8无法向上插入生成的uuid

Question

我有一个包含2列的mysql8表：ID(Int)和UUID(Varchar36)：

CREATE TABLE `upsert_test` (
  `ID` int unsigned DEFAULT NULL COMMENT 'id;',
  `UUID` varchar(36) DEFAULT NULL
) ENGINE=InnoDB;

我需要在没有唯一索引的情况下模拟upsert的行为，最后我得到了下面的sql命令，它在MySQL5.7上运行得很好：

INSERT INTO upsert_test
SELECT 1 AS ID, UUID() AS "UUID"
FROM upsert_test
WHERE ID=1
HAVING COUNT(*)=0;

但是，当升级到mysql8.0时，只会插入一个空的UUID，如下所示：

mysql> SELECT * FROM upsert_test;
+------+------+
| ID   | UUID |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

如果我执行UUID子命令，我会得到一个有效的select (这个查询实际上是在插入任何数据之前执行的)：

mysql> SELECT 1 AS ID, UUID() AS "UUID"
    -> FROM upsert_test
    -> WHERE ID=1
    -> HAVING COUNT(*)=0;
+----+--------------------------------------+
| ID | UUID                                 |
+----+--------------------------------------+
|  1 | a1fbea94-f6c4-11eb-903c-0242ac110002 |
+----+--------------------------------------+
1 row in set (0.00 sec)

它就是不能被插入。如果我忽略HAVING部分：

mysql> INSERT INTO upsert_test
    -> SELECT 2 AS ID, UUID() AS "UUID";
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM upsert_test;
+------+--------------------------------------+
| ID   | UUID                                 |
+------+--------------------------------------+
|    1 | NULL                                 |
|    2 | 033e03f7-f6c5-11eb-903c-0242ac110002 |
+------+--------------------------------------+
2 rows in set (0.00 sec)

我们可以看到插入了一个有效的UUID。

我整个下午都在为这个问题而苦苦挣扎，就是想不出问题是什么。

Answer 1

有人在stackexchange上回答了这个问题，所以我只是复制并粘贴在这里：

您需要一个subselect才能包含包含这两个值的有效行

    CREATE TABLE `upsert_test` (
      `ID` int unsigned DEFAULT NULL COMMENT 'id;',
      `UUID` varchar(36) DEFAULT NULL
    ) 

    INSERT INTO upsert_test
    SELECT *  FROM
    (SELECT 1 AS ID, UUID() AS "UUID"
    FROM upsert_test
    WHERE ID=1
    HAVING COUNT(*)=0) t1;

    SELECT * FROM upsert_test

    ID | UUID                                
    -: | :-----------------------------------
     1 | 63b0fa5b-f6d1-11eb-871d-00163e64f9cc