MySQL中如何获取源与目标节点间的最短路径？WITH RECURSIVE报错求方案

别担心，除了WITH RECURSIVE，咱们还有几种实用方法可以在MySQL里找到源节点到目标节点的最短路径，还能直接列出构成路径的所有节点。下面我会结合常见的表结构给你详细说明，你可以根据自己的实际表结构调整字段名~

先明确假设的表结构 #

因为你没贴出具体表结构，我先假设最常见的边表结构（如果你的表是节点表或者字段名不同，直接替换对应字段即可）：

无权重的边表（只记录节点间的连接关系） #

CREATE TABLE edges (
    source_node INT,  -- 源节点
    target_node INT,  -- 目标节点
    PRIMARY KEY (source_node, target_node)
);

有权重的边表（比如记录距离、成本等） #

CREATE TABLE edges (
    source_node INT,
    target_node INT,
    weight DECIMAL(10,2),  -- 边的权重（比如距离、费用）
    PRIMARY KEY (source_node, target_node)
);

方法一：无权重最短路径（最少跳数）——用BFS+临时表迭代 #

BFS天生适合找无权重的最短路径，咱们可以用临时表记录探索过程，逐步迭代直到找到目标节点：

-- 先设置你的源节点和目标节点
SET @source = 1;
SET @target = 5;

-- 创建临时表存储路径信息：当前节点、路径长度、路径节点列表
DROP TEMPORARY TABLE IF EXISTS path_search;
CREATE TEMPORARY TABLE path_search (
    current_node INT PRIMARY KEY,
    path_length INT NOT NULL,
    path_nodes VARCHAR(1000) NOT NULL  -- 用逗号分隔节点，比如"1,3,5"
);

-- 初始化：把源节点加入临时表
INSERT INTO path_search (current_node, path_length, path_nodes)
VALUES (@source, 0, CAST(@source AS CHAR));

-- 迭代搜索路径
WHILE (SELECT COUNT(*) FROM path_search WHERE current_node = @target) = 0 DO
    -- 找到当前所有路径的下一跳节点，且未被访问过
    INSERT IGNORE INTO path_search (current_node, path_length, path_nodes)
    SELECT 
        e.target_node,
        ps.path_length + 1,
        CONCAT(ps.path_nodes, ',', e.target_node)
    FROM path_search ps
    JOIN edges e ON ps.current_node = e.source_node
    WHERE NOT EXISTS (
        SELECT 1 FROM path_search ps2 WHERE ps2.current_node = e.target_node
    );
    
    -- 如果没有新节点加入，说明目标节点不可达，直接退出循环
    IF ROW_COUNT() = 0 THEN
        LEAVE;
    END IF;
END WHILE;

-- 查询最短路径（取路径长度最小的那条）
SELECT path_nodes AS 最短路径节点列表
FROM path_search
WHERE current_node = @target
ORDER BY path_length ASC
LIMIT 1;

方法二：有权重最短路径——用Dijkstra算法+临时表 #

如果你的边有权重（比如距离、成本），需要找总权重最小的路径，咱们可以用Dijkstra算法结合临时表实现：

-- 设置源节点和目标节点
SET @source = 1;
SET @target = 5;

-- 创建临时表存储Dijkstra算法的中间结果：节点、到源节点的最短距离、路径节点列表
DROP TEMPORARY TABLE IF EXISTS dijkstra;
CREATE TEMPORARY TABLE dijkstra (
    node INT PRIMARY KEY,
    distance DECIMAL(10,2) NOT NULL,
    path VARCHAR(1000) NOT NULL
);

-- 初始化：源节点到自身的距离为0
INSERT INTO dijkstra (node, distance, path)
VALUES (@source, 0, CAST(@source AS CHAR));

-- 迭代更新最短路径
WHILE (SELECT COUNT(*) FROM dijkstra WHERE node = @target) = 0 OR (SELECT MIN(distance) FROM dijkstra WHERE node = @target) > (SELECT MIN(distance) FROM dijkstra WHERE node NOT IN (SELECT target_node FROM edges JOIN dijkstra ON edges.source_node = dijkstra.node)) DO
    -- 找到当前未处理的节点中距离源节点最近的
    SELECT node, distance, path INTO @current_node, @current_dist, @current_path
    FROM dijkstra
    WHERE node NOT IN (SELECT DISTINCT source_node FROM edges WHERE target_node IN (SELECT node FROM dijkstra))
    ORDER BY distance ASC
    LIMIT 1;
    
    -- 如果没有可处理的节点，说明目标不可达，退出循环
    IF @current_node IS NULL THEN
        LEAVE;
    END IF;
    
    -- 更新相邻节点的最短距离和路径
    INSERT INTO dijkstra (node, distance, path)
    SELECT 
        e.target_node,
        @current_dist + e.weight,
        CONCAT(@current_path, ',', e.target_node)
    FROM edges e
    WHERE e.source_node = @current_node
    ON DUPLICATE KEY UPDATE
        distance = LEAST(distance, @current_dist + e.weight),
        path = IF(distance > @current_dist + e.weight, CONCAT(@current_path, ',', e.target_node), path);
END WHILE;

-- 查询最短路径及总权重
SELECT 
    path AS 最短路径节点列表,
    distance AS 总权重
FROM dijkstra
WHERE node = @target
ORDER BY distance ASC
LIMIT 1;

关于你遇到的WITH RECURSIVE错误 #

顺便提一下，WITH RECURSIVE在MySQL 8.0及以上才支持，如果你的MySQL版本低于8.0，肯定会报错。如果是版本没问题，那大概率是递归逻辑没处理好（比如没有终止条件、出现节点循环导致无限递归），不过上面的临时表方法兼容性更好，在MySQL 5.5及以上都能运行。

内容的提问来源于stack exchange，提问作者jabey