MySQL WITH 子句报错: You have an error in your SQL syntax

在MySQL中使用WITH子句时，经常会遇到“You have an error in your SQL syntax”错误。这是因为MySQL不支持WITH子句。

例如，以下代码片段会报错：

with mechanism_tb (C_ID, C_NAME, R_PARENT_ID) as
(
  select C_ID, C_NAME, R_PARENT_ID 
	from mechanism 
	where C_LEVEL = 4

  union all

  select c.C_ID, c.C_NAME, c.R_PARENT_ID
	from mechanism c 
	join mechanism t 
	on c.C_ID = t.R_PARENT_ID
)
select * from mechanism_tb;

错误信息为：

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 'mechanism_tb (C_ID, C_NAME, R_PARENT_ID) as
  (
    select C_ID, C_NAME, R_PARENT_ID 
  	from' at line 1

解决方法：

将WITH子句的内容直接嵌入到主查询中：

select *
from (
  select C_ID, C_NAME, R_PARENT_ID 
  from mechanism 
  where C_LEVEL = 4

  union all

  select c.C_ID, c.C_NAME, c.R_PARENT_ID
  from mechanism c 
  join mechanism t 
  on c.C_ID = t.R_PARENT_ID
) as mechanism_tb;

这样就可以避免使用WITH子句而得到相同的结果。