MySQL 8.0复杂JOIN更新语句语法错误排查求助

升级MySQL至8.0版本后，执行以下UPDATE查询时触发语法错误：

UPDATE
  MinMaxAvg AS tar
  INNER JOIN (
    SELECT
      SUBSTR(YYYYDD, 1, 8) AS timestamp,
      ROUND(AVG(rainfall), 1) AS rain_total_avg,
      ROUND(MIN(rainfall), 1) AS rain_total_min,
      ROUND(MAX(rainfall), 1) AS rain_total_max
    FROM
      (
        (
          SELECT
            MAX(rain_total) - MIN(rain_total) AS rainfall,
            substr(timestamp, 1, 8) AS YYYYDD
          FROM
            weather
          WHERE
            timestamp > 0
          GROUP BY
            substr(timestamp, 1, 8)
        ) AS T1
      )
    GROUP BY
      SUBSTR(YYYYDD, 1, 8)
  ) AS sor ON tar.timestamp = sor.timestamp
SET
  tar.rain_total_avg = sor.rain_total_avg,
  tar.rain_total_min = sor.rain_total_min,
  tar.rain_total_max = sor.rain_total_max

错误提示：

Error:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') GROUP BY SUBSTR(YYYYDD,1, 8)) AS sor ON tar.timestamp = sor.timestamp SET tar.

问题原因 #

你的SQL语句中存在冗余的嵌套括号：在FROM子句里，你给子查询(SELECT ... AS T1)额外多套了一层括号，变成了((SELECT ... AS T1))。MySQL 8.0对SQL语法的校验更严格，不允许这种不必要的多层嵌套括号，而旧版本MySQL或部分在线SQL检查工具对这类语法兼容度更高，因此未检测出问题。

修正后的SQL #

去掉那层多余的括号即可正常执行：

UPDATE
  MinMaxAvg AS tar
  INNER JOIN (
    SELECT
      SUBSTR(YYYYDD, 1, 8) AS timestamp,
      ROUND(AVG(rainfall), 1) AS rain_total_avg,
      ROUND(MIN(rainfall), 1) AS rain_total_min,
      ROUND(MAX(rainfall), 1) AS rain_total_max
    FROM
      (
        SELECT
          MAX(rain_total) - MIN(rain_total) AS rainfall,
          substr(timestamp, 1, 8) AS YYYYDD
        FROM
          weather
        WHERE
          timestamp > 0
        GROUP BY
          substr(timestamp, 1, 8)
      ) AS T1
    GROUP BY
      SUBSTR(YYYYDD, 1, 8)
  ) AS sor ON tar.timestamp = sor.timestamp
SET
  tar.rain_total_avg = sor.rain_total_avg,
  tar.rain_total_min = sor.rain_total_min,
  tar.rain_total_max = sor.rain_total_max

进一步简化建议 #

外层的GROUP BY SUBSTR(YYYYDD, 1, 8)其实可以去掉，因为子查询T1已经按substr(timestamp, 1, 8)分组，每个YYYYDD对应唯一的rainfall值，外层聚合函数不会改变结果，简化后的SQL如下：

UPDATE
  MinMaxAvg AS tar
  INNER JOIN (
    SELECT
      YYYYDD AS timestamp,
      ROUND(rainfall, 1) AS rain_total_avg,
      ROUND(rainfall, 1) AS rain_total_min,
      ROUND(rainfall, 1) AS rain_total_max
    FROM
      (
        SELECT
          MAX(rain_total) - MIN(rain_total) AS rainfall,
          substr(timestamp, 1, 8) AS YYYYDD
        FROM
          weather
        WHERE
          timestamp > 0
        GROUP BY
          substr(timestamp, 1, 8)
      ) AS T1
  ) AS sor ON tar.timestamp = sor.timestamp
SET
  tar.rain_total_avg = sor.rain_total_avg,
  tar.rain_total_min = sor.rain_total_min,
  tar.rain_total_max = sor.rain_total_max

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