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)
);

SELECT 
    e1.id AS employee_id,
    e1.name AS employee_name,
    e2.id AS manager_id,
    e2.name AS manager_name
FROM 
    employees e1
LEFT JOIN 
    employees e2 ON e1.manager_id = e2.id;

DELIMITER //

CREATE PROCEDURE check_circular_reference(IN employee_id INT, IN manager_id INT)
BEGIN
    DECLARE current_manager_id INT;
    DECLARE done INT DEFAULT FALSE;

    CREATE TEMPORARY TABLE temp_manager_ids (id INT);

    INSERT INTO temp_manager_ids VALUES (manager_id);

    WHILE NOT done DO
        SELECT manager_id INTO current_manager_id 
        FROM employees 
        WHERE id = (SELECT id FROM temp_manager_ids ORDER BY id DESC LIMIT 1);

        IF current_manager_id = employee_id THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Circular reference detected';
            LEAVE WHILE;
        END IF;

        INSERT INTO temp_manager_ids VALUES (current_manager_id);
    END WHILE;

    DROP TEMPORARY TABLE temp_manager_ids;
END //

DELIMITER ;

CALL check_circular_reference(1, 2);