在mysql中连接任意行的字符串（分层查询）

Question

我有一个带有专辑的 mysql 表。 每个专辑可以是顶级专辑，也可以是另一个专辑的子专辑。 每个相册都有一个文件夹名称，它是其图片所在的文件夹的名称。每个相册还有一个名为“parent”的字段，它是父相册的 ID。 所以，如果我有一个像这样的图像路径：

root/album1/album2/image1.jpg

那么数据库中的专辑表将如下所示：

id parent foldername
1  NULL   root
2  1      album1
3  2      album2

那么问题是，如何仅使用 mysql 从该表中获取之前打印的路径？

Answer 1

我不确定在数据库中存储一棵树是一个好主意...

为了让您的问题简单，也许只需将专辑的完整路径存储在表的一列中...

id parent path           foldername
1  NULL   /              root
2  1      /root/         album1
3  2      /root/album1/  album2

Answer 2

完全未经测试，是我凭空想象出来的……

DECLARE @FOLDER VARCHAR(200)
DECLARE @TOPID AS INT
DECLARE @MYID As int

CREATE TABLE #tmp
(
    [id] INT,
    [path] VARCHAR(50)
)

DECLARE tempCursor CURSOR FOR SELECT id FROM albums ORDER BY id
OPEN tempCursor

FETCH NEXT FROM tempCursor INTO @TOPID

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @FOLDER = ''
    SET @MYID = @TOPID

    WHILE @MYID is not null
    BEGIN
        SELECT @MYFOLDER = foldername FROM albums WHERE id = @MYID
        SET @FOLDER = @MYFOLDER + '/' + @FOLDER
        SELECT @MYID = parent FROM albums WHERE id = @MYID
    END

    INSERT INTO #tmp
    SELECT @TOPID, @FOLDER

    FETCH NEXT FROM tempCursor INTO @TOPID
END
CLOSE tempCursor
DEALLOCATE tempCursor

SELECT * FROM #tmp
DROP TABLE #tmp

这至少应该让您知道如何获取路径名。 您从未指定文件名的存储位置。

顺便说一句，这会很慢。 我讨厌使用光标...

Answer 3

DB2、SQL Server、PostgreSQL 和 Oracle 都支持通用表表达式 (CTE)，可用于完成此任务。 Oracle 还有“CONNECT BY”关键字。

我知道这并不能解决你的问题，但也许它会帮助其他人寻找 MySQL 以外的解决方案。

在您的情况下，出于性能考虑，我建议将完整路径存储在列中 - 插入/更新时对非规范化数据的管理可能会在读取性能方面获得很多倍的回报。