含UNION的MySQL查询本地Xampp正常但生产环境报错如何解决

• 语法兼容性问题：MySQL 5.7、MariaDB 10.3及更早版本的SQL解析器不支持IN子句中用额外括号包裹UNION的多个子查询块的写法。你当前查询中created_by IN后面紧跟的((SELECT ... UNION SELECT ...) UNION (SELECT ...))嵌套括号写法，只有MariaDB 10.4及以上版本做了解析兼容，低版本会直接报1064语法错误。
• 冗余逻辑问题：你在WHERE语句末尾已经全局限定了server_type = 'PUBLIC'，前面OR分支中写的server_type = 'PRIVATE'条件不会生效，属于无效逻辑。

移除IN子句中包裹UNION块的多余嵌套括号即可兼容所有版本，修改后的查询语句如下：

SELECT
    COUNT(*) AS AGGREGATE
FROM
    `ps_servers`
WHERE
    (
        (`created_by` = 4)
        OR (
            created_by IN(
                SELECT id FROM users WHERE parent_id = 4
                UNION
                SELECT U.id FROM users U LEFT JOIN users T ON U.parent_id = T.id
            )
            AND server_type IN ('PUBLIC', 'PRIVATE')
        )
        OR (
            created_by IN(
                SELECT parent_id FROM users WHERE id = 4
                UNION
                SELECT T.parent_id FROM users U LEFT JOIN users T ON U.parent_id = T.id WHERE U.id = 4
                UNION
                SELECT A.parent_id FROM users U LEFT JOIN users T ON U.parent_id = T.id LEFT JOIN users A ON T.parent_id = A.id WHERE U.id = 4
            )
            AND server_type = 'PUBLIC'
        )
    )
    AND `server_type` = 'PUBLIC'
    AND `status` <> 'DELETED'

如果你的业务确实需要包含server_type = 'PRIVATE'的记录，删掉末尾全局的AND server_type = 'PUBLIC'条件即可。

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