mysql 查找根节点

CREATE TABLE tree_nodes (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    parent_id INT
);

SELECT t1.id, t1.name
FROM tree_nodes t1
LEFT JOIN tree_nodes t2 ON t1.parent_id = t2.id
WHERE t2.id IS NULL;

WITH RECURSIVE cte AS (
    SELECT id, name, parent_id
    FROM tree_nodes
    WHERE parent_id IS NULL
    UNION ALL
    SELECT tn.id, tn.name, tn.parent_id
    FROM tree_nodes tn
    INNER JOIN cte ON tn.parent_id = cte.id
)
SELECT id, name
FROM cte
WHERE parent_id IS NULL;

ALTER TABLE tree_nodes ADD COLUMN path VARCHAR(255);

INSERT INTO tree_nodes (id, name, parent_id, path) VALUES
(1, 'Root', NULL, '1'),
(2, 'Child1', 1, '1.2'),
(3, 'Child2', 1, '1.3');

SELECT id, name
FROM tree_nodes
WHERE path LIKE '1%';