MySQL:使用别名定义CONCAT的数据类型

Question

这个问题是关于在调用CONCAT并将其存储为别名时定义容量/最大长度的可能性。

我有一个相当复杂的MySQL查询，使用通用表表达式(CTE(来建模注释。它创建一个新变量path由评论的分数(#票数(和id组成，用逗号分隔，并使用CONCAT与其父路径连接。这允许按分数对线程中的评论进行排序。

path看起来像例如000010,000005,000014,000008，这意味着 id8的注释的分数为14，其父级本身没有父级，其 id5和分数为10。由于所有评论都path这种格式，因此可以按照我想要的方式对它们进行排序。

最重要的是，最初path仅由带有 id 的单个分数组成，在递归调用中，我们将在访问孩子时继续连接成越来越长的路径。

但是，似乎对CONCAT的初始调用会立即将所有后续连接的大小限制为 15 个，以达到最长的初始连接，因此它们只是在 15 个字符后被剪切。使初始串联长度超过 15，会将后续串联限制为最长的初始连接(因此实际上不会连接任何内容(。

目前，我已经通过最初在右侧填充大量零并在递归调用中删除它们来解决此问题。但是，这使用正则表达式，即使它相当简单，恐怕对性能不利。

有没有办法通过初始调用CONCAT定义创建的别名变量的容量/最大长度应该是多少？

这是进行的查询：

WITH RECURSIVE first_comments (id, content, parent_id, user_id, created, votes, path) AS (
(
SELECT r.id, r.content, r.parent_id, r.user_id, r.created, r.votes, CONCAT_WS(",", LPAD(r.votes,6,0), LPAD(r.id,6,0), LPAD(0,243,0)) as path
FROM (
SELECT c.id, c.content, c.parent_id, c.user_id, c.created, COUNT(DISTINCT v.id) AS votes
FROM comments AS c
LEFT JOIN comment_votes AS v ON c.id = v.comment_id
WHERE c.post_id = ? AND c.parent_id IS NULL
GROUP BY c.id
) as r
)
UNION ALL
(
SELECT r.id, r.content, r.parent_id, r.user_id, r.created, r.votes, CONCAT_WS(",", REGEXP_REPLACE(fle.path, ",[0]+$", ""), LPAD(r.votes,6,0), LPAD(r.id,6,0)) as path
FROM first_comments AS fle
JOIN (
SELECT c.id, c.content, c.parent_id, c.user_id, c.created, COUNT(DISTINCT v.id) AS votes
FROM comments AS c
LEFT JOIN comment_votes AS v ON c.id = v.comment_id
WHERE c.post_id = ?
GROUP BY c.id
) AS r ON fle.id = r.parent_id
)
)
SELECT id, content, parent_id, user_id, path, created, votes FROM first_comments
ORDER BY pat

(灵感来源：按线程路径和总票数对评论进行排序(

最初，我使用CONCAT_WS(",", LPAD(r.votes,6,0), LPAD(r.id,6,0), LPAD(0,243,0)) as path创建path，它创建包含最顶层评论的分数和 id 的路径(没有父级(，并在右侧填充 243 个零。所以例如000010,000005,0...0为 ID5的最上面的评论。

然后递归(但实际上只对第一个递归调用，因为此后模式永远不会匹配(，我们使用正则表达式删除所有尾随零，包括最后一个逗号，并添加此注释的分数和 id：CONCAT_WS(",", REGEXP_REPLACE(fle.path, ",[0]+$", ""), LPAD(r.votes,6,0), LPAD(r.id,6,0)) as path。

因此，最好只是在path的初始定义中添加一些东西，而不是这个解决方法。但我不知道还有什么其他方法可能更好？

任何帮助和想法不胜感激！

编辑：在GMB的帮助下，问题得到了解决(和简化(，并进行了少量补充，请参阅我在接受答案下的评论。

Answer 1

将路径附加到 JSON 数组而不是字符串中怎么样？这无缝地克服了您遇到的问题，您仍然可以order by。

所以：

WITH RECURSIVE first_comments (id, content, parent_id, user_id, created, votes, js_path) AS (
SELECT 
c.id, 
c.content, 
c.parent_id, 
c.user_id, 
c.created, 
COUNT(DISTINCT v.id) AS votes,
JSON_ARRAY(LPAD(COUNT(DISTINCT v.id), 6, 0), LPAD(c.id, 6, 0)) as js_path
FROM comments AS c
LEFT JOIN comment_votes AS v ON c.id = v.comment_id
WHERE c.post_id = ? AND c.parent_id IS NULL
GROUP BY c.id
UNION ALL
SELECT 
r.id, 
r.content, 
r.parent_id, 
r.user_id, 
r.created, 
r.votes, 
JSON_ARRAY_APPEND(
fle.js_path, 
'$', LPAD(r.votes, 6, 0), 
'$', LPAD(r.id, 6, 0)
) as js_path
FROM first_comments AS fle
JOIN (
SELECT 
c.id, 
c.content, 
c.parent_id, 
c.user_id, 
c.created, 
COUNT(DISTINCT v.id) AS votes
FROM comments AS c
LEFT JOIN comment_votes AS v ON c.id = v.comment_id
WHERE c.post_id = ?
GROUP BY c.id
) AS r ON fle.id = r.parent_id
)
SELECT id, content, parent_id, user_id, js_path, created, votes 
FROM first_comments
ORDER BY js_path

请注意，我简化了查询，如下所示：

• 递归查询的锚点中不需要子查询

• union all不需要两个查询两边的括号

Answer 2

为了完成，以下是我想出的用于MariaDB的代码，为此，JSON 数组的初始创建限制了其容量，因此无法附加到它。

相反，我使用CONCAT_WS，并将path的初始创建CAST到一个VARCHAR(255)，它应该足够大以包含最长的路径。

我还必须稍微更改路径中分数的计算，以便它们出现的顺序符合预期。

WITH RECURSIVE first_comments (id, content, parent_id, user_id, created, level, votes, path) AS (
SELECT
c.id,
c.content,
c.parent_id,
c.user_id,
c.created,
0 as level,
COUNT(DISTINCT v.id) AS votes,
CAST(CONCAT_WS(",", LPAD(999999-COUNT(DISTINCT v.id), 6, 0), LPAD(c.id, 6, 0)) AS VARCHAR(255)) as path
FROM comments AS c
LEFT JOIN comment_votes AS v ON c.id = v.comment_id
WHERE c.post_id = ? AND c.parent_id IS NULL
GROUP BY c.id
UNION ALL
SELECT
r.id,
r.content,
r.parent_id,
r.user_id,
r.created,
fle.level+1 as level,
r.votes,
CONCAT_WS(
",",
fle.js_path,
LPAD(999999-r.votes, 6, 0),
LPAD(r.id, 6, 0)
) as path
FROM first_comments AS fle
JOIN (
SELECT
c.id,
c.content,
c.parent_id,
c.user_id,
c.created,
COUNT(DISTINCT v.id) AS votes
FROM comments AS c
LEFT JOIN comment_votes AS v ON c.id = v.comment_id
WHERE c.post_id = ?
GROUP BY c.id
) AS r ON fle.id = r.parent_id
)
SELECT id, content, parent_id, user_id, created, level, votes, path
FROM first_comments
ORDER BY path ASC