MySQL 8.0多余括号引发1064语法错误问题咨询

问题场景 #

将应用从MySQL 5.7迁移到MySQL 8.0后，店铺搜索API的SQL查询执行报错。

原报错SQL #

SELECT
  SQL_CALC_FOUND_ROWS distinct(shop.id) as id,
  `shop`.`code`,
  `shop`.`name`,
  `shop`.`tel`,
  `shop`.`city_id`,
  `shop`.`address`,
  `shop`.`lat`,
  `shop`.`lon`,
  `history`,
  `distance`
FROM
  (
    (
      select
        shop_master.*,
        (0) history,
        (0) count_updated_at,
        (0) count,
        (0) distance
      FROM
        shop_master
      WHERE
        (
          shop_master.code LIKE "C%"
          OR shop_master.code LIKE "M%"
        )
        AND (shop_master.name LIKE "%myshop%")
      UNION
        DISTINCT (
          select
            shop_master.*,
            (1) history,
            `count_updated_at`,
            `count`,
            (0) distance
          FROM
            shop_master
            JOIN user_shop_count on(
              shop_master.id = user_shop_count.shop_id
              and user_shop_count.user_id = 7878
            )
          WHERE
            shop_master.name LIKE "%myshop%"
            AND shop_master.code NOT LIKE "R%"
        )
      UNION
        DISTINCT (
          select
            shop_master.*,
            (1) history,
            `count_updated_at`,
            `count`,
            (0) distance
          FROM
            shop_category_master
            JOIN shop_category_code ON shop_category_master.code = shop_category_code.category_code
            AND shop_category_master.search_name LIKE "%myshop%"
            JOIN user_shop_count ON shop_category_code.shop_id = user_shop_count.shop_id
            and user_shop_count.user_id = 5026
            JOIN shop_master ON shop_master.id = user_shop_count.shop_id
            AND shop_master.code NOT LIKE "R%"
        )
    ) as shop
  )
ORDER BY
  `count_updated_at` DESC,
  `shop`.`name` asc
LIMIT
  20
;

报错信息 #

ERROR 1064 (42000): 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 ')
ORDER BY
count_updated_at DESC,
shop.name asc
LIMIT
20' at line 66

修正后的SQL #

移除FROM子句中包裹子查询的冗余外层括号后，查询可正常执行：

SELECT
  SQL_CALC_FOUND_ROWS distinct(shop.id) as id,
  `shop`.`code`,
  `shop`.`name`,
  `shop`.`tel`,
  `shop`.`city_id`,
  `shop`.`address`,
  `shop`.`lat`,
  `shop`.`lon`,
  `history`,
  `distance`
FROM
    (
      select
        shop_master.*,
        (0) history,
        (0) count_updated_at,
        (0) count,
        (0) distance
      FROM
        shop_master
      WHERE
        (
          shop_master.code LIKE "C%"
          OR shop_master.code LIKE "M%"
        )
        AND (shop_master.name LIKE "%myshop%")
      UNION
        DISTINCT (
          select
            shop_master.*,
            (1) history,
            `count_updated_at`,
            `count`,
            (0) distance
          FROM
            shop_master
            JOIN user_shop_count on(
              shop_master.id = user_shop_count.shop_id
              and user_shop_count.user_id = 5026
            )
          WHERE
            shop_master.name LIKE "%myshop%"
            AND shop_master.code NOT LIKE "R%"
        )
      UNION
        DISTINCT (
          select
            shop_master.*,
            (1) history,
            `count_updated_at`,
            `count`,
            (0) distance
          FROM
            shop_category_master
            JOIN shop_category_code ON shop_category_master.code = shop_category_code.category_code
            AND shop_category_master.search_name LIKE "%myshop%"
            JOIN user_shop_count ON shop_category_code.shop_id = user_shop_count.shop_id
            and user_shop_count.user_id = 5026
            JOIN shop_master ON shop_master.id = user_shop_count.shop_id
            AND shop_master.code NOT LIKE "R%"
        )
    ) as shop
ORDER BY
  `count_updated_at` DESC,
  `shop`.`name` asc
LIMIT
  20
;

原因解析 #

MySQL 8.0对SQL语法的解析规则做了更严格的规范，尤其是针对子查询的括号嵌套逻辑。在MySQL 5.7中，语法解析器对冗余括号的容忍度较高，允许在FROM子句中对已经定义好别名的子查询再套一层额外括号（也就是原SQL里FROM ((子查询) as shop)这种写法）。但到了8.0版本，官方收紧了语法校验，严格遵循SQL标准的定义：FROM子句后接的子查询只需要用一层括号包裹并指定别名即可，额外的外层括号属于无效语法，因此会触发1064语法错误。

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