mysql 递归表的创建

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- 示例：使用LIMIT限制递归深度
WITH RECURSIVE employee_hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy LIMIT 10;

-- 示例：使用事务处理插入数据
START TRANSACTION;
INSERT INTO employees (name, manager_id) VALUES ('Alice', NULL);
INSERT INTO employees (name, manager_id) VALUES ('Bob', LAST_INSERT_ID());
COMMIT;