How to Migrate a MSSQL CTE-Based Category Tree Query to MySQL?

Transforming a MSSQL CTE Query to MySQL

Common Table Expressions (CTEs) are a powerful tool for recursive queries in Microsoft SQL Server. However, MySQL does not support CTEs, posing a challenge when converting CTE-based queries to MySQL.

In the example provided, a CTE query is used to build a category tree from the bottom up, starting from a specified category ID. To replicate this functionality in MySQL, a recursive stored procedure must be implemented.

Creating a Recursive Stored Procedure

The following MySQL stored procedure simulates the behavior of the MSSQL CTE:

CREATE PROCEDURE get_category_tree(IN start_category_id INT)
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE id INT;
  DECLARE pid INT;
  DECLARE name VARCHAR(255);

  # Initialize the cursor
  DECLARE cursor_categories CURSOR FOR
    SELECT id, parentid, name
    FROM category
    WHERE id = start_category_id;

  # Open the cursor
  OPEN cursor_categories;

  # Fetch the first row
  FETCH cursor_categories INTO id, pid, name;

  # While there are more rows
  WHILE done = 0 DO
    # Print the current row
    SELECT id, pid, name;

    # If the parent ID is NULL, mark as done
    IF pid IS NULL THEN
      SET done = 1;

    ELSE
      # Move the cursor to the parent row
      SET start_category_id = pid;
      FETCH cursor_categories INTO id, pid, name;
    END IF;
  END WHILE;

  # Close the cursor
  CLOSE cursor_categories;
END PROCEDURE;

Usage

To use the stored procedure, call it with the desired starting category ID as an argument:

CALL get_category_tree(197);

This will print the category tree starting from category 197, traversing up the hierarchy until the root is reached.

The above is the detailed content of How to Migrate a MSSQL CTE-Based Category Tree Query to MySQL?. For more information, please follow other related articles on the PHP Chinese website!