在 MySQL 中拆分路径枚举模型 PathString

发布于 2024-10-10 06:16:04 字数 2026 浏览 3 评论 0原文

我正在尝试按照 Joe Celko 的 book (第 38 页)实现路径枚举模型。我的表(以及仅包含顺序整数的支持表)的相关属性如下所示:

Contribution
------------
ContributionID
PathString

_IntegerSeries
--------------
IntegerID

_IntegerSeries 包含整数 1 到 n,其中 n 比我需要的要大。 Contribution 包含三个记录:

1  1
2  12
3  123

... 我使用 Joe 查询的修改版本:

SELECT SUBSTRING( c1.PathString
     FROM (s1.IntegerID * CHAR_LENGTH(c1.ContributionID))
     FOR CHAR_LENGTH(c1.ContributionID)) AS ContID
FROM
 Contribution c1, _IntegerSeries s1
WHERE
 c1.ContributionID = 3
 AND s1.IntegerID <= CHAR_LENGTH(c1.PathString)/CHAR_LENGTH(c1.ContributionID);

... 成功返回包含层次结构中 ContributionID 3 的所有上级的结果集。现在,在这个示例中,PathString 列保存纯整数值,显然一旦我们达到 ContributionID 10,我们就会遇到麻烦。因此,我们修改 PathString 列以包含分隔符:

1   1.
2   1.2.
3   1.2.3.

现在...本书没有给出获取上级的示例当 PathString 使用分隔符时......所以我稍后必须弄清楚这一点。但它确实给出了如何拆分 PathString 的示例(我猜这将帮助我进行高级搜索)。执行此操作的示例代码的 MySQL 版本是:

SELECT SUBSTRING( '.' || c1.PathString || '.'
     FROM s1.IntegerID + 1
     FOR LOCATE('.', '.' || c1.PathString || '.', s1.IntegerID + 1) - s1.IntegerID - 1) AS Node
FROM _IntegerSeries s1, Contribution c1
WHERE
 SUBSTRING('.' || c1.PathString || '.' FROM s1.IntegerID FOR 1) = '.'
 AND IntegerID < CHAR_LENGTH('.' || c1.PathString || '.');

...但是此代码返回一个空结果集。我做错了什么,但我不确定是什么。我想我应该在用电子邮件打扰 Joe 之前将其发布到 stackoverflow 社区。有人有什么想法吗?


更新


Quassnoi的查询...测试后稍作修改,但与原来的功能完全相同。很不错。比我用的干净多了。非常感谢。

SET @contributionID = 3;

SELECT  ca.*
FROM
    Contribution c INNER JOIN _IntegerSeries s
        ON s.IntegerID < @contributionID AND SUBSTRING_INDEX(c.PathString, '.', s.IntegerID) <> SUBSTRING_INDEX(c.PathString, '.', s.IntegerID + 1)
    INNER JOIN Contribution ca
        ON ca.PathString = CONCAT(SUBSTRING_INDEX(c.PathString, '.', s.IntegerID), '.')
WHERE c.ContributionID = @contributionID;

I'm trying to implement a Path Enumeration model as per Joe Celko's book (page 38). The relevant attributes of my table (and the support table that just contains sequential integers) look like this:

Contribution
------------
ContributionID
PathString

_IntegerSeries
--------------
IntegerID

_IntegerSeries contains integers 1 to n where n is bigger than I'll ever need. Contribution contains three records:

1  1
2  12
3  123

... and I use a modified version of Joe's query:

SELECT SUBSTRING( c1.PathString
     FROM (s1.IntegerID * CHAR_LENGTH(c1.ContributionID))
     FOR CHAR_LENGTH(c1.ContributionID)) AS ContID
FROM
 Contribution c1, _IntegerSeries s1
WHERE
 c1.ContributionID = 3
 AND s1.IntegerID <= CHAR_LENGTH(c1.PathString)/CHAR_LENGTH(c1.ContributionID);

... to successfully return a result set containing all of ContributionID 3's superiors in the hierarchy. Now, in this example, the PathString column holds plain integer values and obviously we run into trouble once we hit ContributionID 10. So we modify the PathString column to include separators:

1   1.
2   1.2.
3   1.2.3.

Now... the book doesn't give an example of getting superiors when the PathString uses delimiters... so I'll have to figure that out later. But it does give an example for how to split up a PathString (which I'm guessing is going to help me do superior searches). The MySQL version of the example code to do this is:

SELECT SUBSTRING( '.' || c1.PathString || '.'
     FROM s1.IntegerID + 1
     FOR LOCATE('.', '.' || c1.PathString || '.', s1.IntegerID + 1) - s1.IntegerID - 1) AS Node
FROM _IntegerSeries s1, Contribution c1
WHERE
 SUBSTRING('.' || c1.PathString || '.' FROM s1.IntegerID FOR 1) = '.'
 AND IntegerID < CHAR_LENGTH('.' || c1.PathString || '.');

... but this code returns an empty result set. I'm doing something wrong, but I'm not sure what. Figured I'd put this out to the stackoverflow community prior to bothering Joe with an email. Anyone have any thoughts?


UPDATE


Quassnoi's query... slightly modified a bit after testing, but exactly the same as his original functionally. Very nice. Much cleaner than what I was using. Big thanks.

SET @contributionID = 3;

SELECT  ca.*
FROM
    Contribution c INNER JOIN _IntegerSeries s
        ON s.IntegerID < @contributionID AND SUBSTRING_INDEX(c.PathString, '.', s.IntegerID) <> SUBSTRING_INDEX(c.PathString, '.', s.IntegerID + 1)
    INNER JOIN Contribution ca
        ON ca.PathString = CONCAT(SUBSTRING_INDEX(c.PathString, '.', s.IntegerID), '.')
WHERE c.ContributionID = @contributionID;

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

遗失的美好 2024-10-17 06:16:04

这是因为MySQL中的||是布尔OR,而不是字符串连接。

要查找给定 Contribution 的所有祖先,请使用:

SELECT  ca.*
FROM    Contribution с
JOIN    IntegerSeries s
ON      IntegerID < CHAR_LENGTH(c.path)
        AND SUBSTRING_INDEX(c.path, '.', IntegerID) <> SUBSTRING_INDEX(c.path, '.', IntegerID + 1)
JOIN    Contribution ca
ON      ca.path = CONCAT(SUBSTRING_INDEX(c.path, '.', IntegerID), '.')
WHERE   c.ContributionID = 3

This is because || in MySQL is boolean OR, not string concatenation.

To find all ancestors of a given Contribution, use:

SELECT  ca.*
FROM    Contribution с
JOIN    IntegerSeries s
ON      IntegerID < CHAR_LENGTH(c.path)
        AND SUBSTRING_INDEX(c.path, '.', IntegerID) <> SUBSTRING_INDEX(c.path, '.', IntegerID + 1)
JOIN    Contribution ca
ON      ca.path = CONCAT(SUBSTRING_INDEX(c.path, '.', IntegerID), '.')
WHERE   c.ContributionID = 3
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文