MySQL8正常执行的CTE UPDATE语句在MariaDB15报错，求兼容方案

原SQL语句（MySQL 8可正常运行） #

WITH
  randomised AS
(
  SELECT
    *,
    (SUM(FLOOR(RAND() * 1.5) + 1) OVER (ORDER BY id DESC)) DIV 4 AS offset
  FROM
    products
)
UPDATE
  products
INNER JOIN
  randomised
    ON randomised.id = products.id
SET
  products.created_at = DATE_SUB(CURDATE(), INTERVAL randomised.offset DAY), products.updated_at = DATE_SUB(CURDATE(), INTERVAL randomised.offset DAY)
;

报错信息 #

#1064 - SQL语法错误；请查阅对应MariaDB版本的手册，了解在'UPDATE
products
INNER JOIN
randomised
ON randomised.id = products...'附近使用的正确语法

修改方案 #

MariaDB 10.x系列版本对UPDATE语句结合CTE（WITH子句）的语法支持与MySQL 8存在差异，将CTE转换为内联子查询即可解决兼容性问题，修改后的SQL如下：

UPDATE
  products
INNER JOIN
  (
    SELECT
      *,
      (SUM(FLOOR(RAND() * 1.5) + 1) OVER (ORDER BY id DESC)) DIV 4 AS offset
    FROM
      products
  ) AS randomised
    ON randomised.id = products.id
SET
  products.created_at = DATE_SUB(CURDATE(), INTERVAL randomised.offset DAY),
  products.updated_at = DATE_SUB(CURDATE(), INTERVAL randomised.offset DAY)
;

说明 #

• 保留原逻辑：子查询完全复用了原CTE中的计算逻辑，保证功能一致
• 兼容双环境：修改后的语句既能在MariaDB 15（实际为MariaDB 10.15）中正常执行，也能在MySQL 8中保持原有功能
• 语法适配：MariaDB对UPDATE语句中直接引用CTE的支持有限，内联子查询是更通用的兼容写法

内容的提问来源于stack exchange，提问作者Neokyuubi