How Can I Replicate a Recursive MSSQL CTE Query for Building a Category Tree in MySQL?

Transforming a MSSQL CTE Query to MySQL

In a MySQL schema with a 'category' table containing columns for id, parent id, and name, you wish to replicate a MSSQL CTE query that builds a category tree from the bottom up for a specified category ID. This query is as follows:

with CTE (id, pid, name)
as
(
    select id, parentid as pid,name
    from category
    where id = 197
      union all
        select CTE.pid as id , category.parentid as pid, category.name
        from CTE
          inner join category
            on category.id = CTE.pid
 )
 select * from CTE 

Unfortunately, MySQL lacks support for CTEs. As a result, using a subquery in place of them may not be an option because the CTE in question is a recursive one, referencing itself within the query. MySQL does not support recursive CTEs.

To achieve similar functionality, a stored procedure must be implemented. A previous answer on how to "Generate Depth based tree from Hierarchical Data in MySQL (no CTEs)" can serve as a starting point for crafting such a procedure.

The above is the detailed content of How Can I Replicate a Recursive MSSQL CTE Query for Building a Category Tree in MySQL?. For more information, please follow other related articles on the PHP Chinese website!