mysql 最短路径

-- 创建示例表
CREATE TABLE graph (
    node_id INT,
    neighbor_id INT,
    weight INT
);

-- 插入示例数据
INSERT INTO graph (node_id, neighbor_id, weight) VALUES
(1, 2, 10),
(1, 3, 15),
(2, 3, 12),
(2, 4, 15),
(3, 4, 10);

-- 计算从节点 1 到其他节点的最短路径
WITH RECURSIVE cte (node_id, distance, path) AS (
    SELECT node_id, 0, CAST(node_id AS VARCHAR)
    FROM graph
    WHERE node_id = 1
    UNION ALL
    SELECT g.neighbor_id, cte.distance + g.weight, CONCAT(cte.path, ' -> ', g.neighbor_id)
    FROM graph g
    JOIN cte ON g.node_id = cte.node_id
    WHERE g.neighbor_id NOT IN (cte.node_id, SUBSTRING_INDEX(cte.path, ' -> ', -1))
)
SELECT node_id, distance
FROM cte
ORDER BY distance;