Error number: 3576; Symbol: ER_CTE_RECURSIVE_FORBIDDEN_JOIN_ORDER; SQLSTATE: HY000
Message: In recursive query block of Recursive Common Table Expression ‘%s’, the recursive table must neither be in the right argument of a LEFT JOIN, nor be forced to be non-first with join order hints
错误说明
ER_CTE_RECURSIVE_FORBIDDEN_JOIN_ORDER是MySQL服务器上的一个错误消息,代号为3576,消息显示:
“使用被CTE定义的递归定义的表的连接顺序是被禁止的”。
这个消息表明,MySQL 无法允许在递归查询表(CTE)中对表的连接次序发生变化。这实际上是由于允许连接次序变化会使查询变得更复杂且不可预测,或可能出现问题,所以MySQL禁止了连接顺序的变化。
常见案例
一个典型的场景,可能会引发ER_CTE_RECURSIVE_FORBIDDEN_JOIN_ORDER错误,是使用四个表来连接一个CTE表的情况,如:
WITH cte_table
AS (
SELECT c1, c2,c3 FROM t1
WHERE c2 = “value”
)
SELECT c1, c2, c3 FROM cte_table c1
LEFT JOIN t2 c2 ON c2.c2 = c1.c2
LEFT JOIN t3 c3 ON c3.c3 = c1.c3
LEFT JOIN t4 c4 ON c4.c4 = c1.c2
;
这段语句求结果时,尝试将后面三个表的连接从“Left Join t2 c2……”开始变更为从“Left Join t3 c3……”开始,连接顺序发生变化,就会导致MySQL无法查询通过,而抛出ER_CTE_RECURSIVE_FORBIDDEN_JOIN_ORDER错误。
解决方法
要解决ER_CTE_RECURSIVE_FORBIDDEN_JOIN_ORDER错误,显而易见,最重要的是确保查询语句中,CTE定义的表及其他后续连接的表按照定义的连接顺序来连接,而不能出现连接顺序的变化。
假设想要将以上查询语句的连接顺序变更一下,有几种方法可以解决:
方法一:将已有的查询语句,使用Subquery或View来重写
Subquery 方法:
SELECT * FROM
(SELECT c1,c2,c3 FROM cte_table c1
LEFT JOIN t2 c2 ON c2.c2 = c1.c2
LEFT JOIN t3 c3 ON c3.c3 = c1.c3
LEFT JOIN t4 c4 ON c4.c4 = c1.c2)
AS Subquery
LEFT JOIN t3 c3 ON Subquery.c3 = c3.c3;
View方法:
CREATE VIEW cte_table_view AS
SELECT c1,c2,c3 FROM cte_table c1
LEFT JOIN t2 c2 ON c2.c2 = c1.c2
LEFT JOIN t3 c3 ON c3.c3 = c1.c3
LEFT JOIN t4 c4 ON c4.c4 = c1.c2;
SELECT * FROM cte_table_view
LEFT JOIN t3 c3 ON cte_table_view.c3 = c3.c3;
方法二:使用自连接的方法来替换原来的连接方式:
SELECT c1.c1, c1.c2, c1.c3, c3.c3
FROM cte_table c1
LEFT JOIN t2 c2 on c2.c2 = c1.c2
LEFT JOIN cte_table c3 on c3.c3 = c1.c3
LEFT JOIN t4 c4 ON c4.c4 = c1.c2;
总之,所有必要的调整和重写方法都可以有效的避免 ER_CTE_RECURSIVE_FORBIDDEN_JOIN_ORDER 错误的发生。