mysql：使用别名为concat定义数据类型

r9f1avp5 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(319)

这个问题是关于在调用时定义容量/最大长度的可能性 CONCAT 并将其存储为别名。
我有一个相当复杂的mysql查询，它使用公共表表达式（commontableexpressions，cte）对注解进行建模。它创建了一个新变量 path 由注解的分数（投票数）和id组成，id用逗号分隔，并使用 CONCAT . 这允许按分数对线程内的注解进行排序。
一 path 看起来像是。 000010,000005,000014,000008 ，这意味着id为的注解 8 得分为 14 它的父级本身没有父级，它的id为 5 还有很多 10 . 所有的评论 path 这种格式允许按我想要的方式对它们进行排序。
底线是最初 path 只包含一个带有id的score，在递归调用中，当访问子对象时，我们将继续连接到越来越长的路径中。
然而，似乎最初的呼吁 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

（灵感来源：按线程路径和总投票数排序注解）
最初我创建 path 与 CONCAT_WS(",", LPAD(r.votes,6,0), LPAD(r.id,6,0), LPAD(0,243,0)) as path ，它创建包含最上面的注解（没有父注解）的分数和id的路径，并在右侧填充243个零。例如。 000010,000005,0...0 对于id为的最上面的注解 5 .
然后递归地（但仅在第一次递归调用时有效，因为此后模式永远不匹配），我们使用正则表达式删除所有尾随的零，包括最后一个逗号，并添加此注解的分数和id： CONCAT_WS(",", REGEXP_REPLACE(fle.path, ",[0]+$", ""), LPAD(r.votes,6,0), LPAD(r.id,6,0)) as path .
因此，在最初的定义中添加一些东西是很好的 path 而不是这项工作。但我不知道还有什么方法可能更好？
任何帮助和想法都将不胜感激！
//编辑：在gmb的帮助下，这个问题得到了解决（并得到了简化），还有一个小小的补充，请看我在接受答案下的评论。

sql mysql count recursive-query String

来源：https://stackoverflow.com/questions/62384927/mysql-define-data-type-for-concat-with-alias

2条答案

按热度按时间

wi3ka0sx1#

把路径附加到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 两个查询之间不需要括号

赞(0）回复(0）举报 2021-07-26

55ooxyrt2#

下面是我为mariadb编写的代码，由于json数组的初始创建限制了它的容量，因此无法附加到它。
我用的是 CONCAT_WS 以及 CAST 最初创建 path 到 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

赞(0）回复(0）举报 2021-07-26

我来回答

mysql：使用别名为concat定义数据类型

2条答案

相关问题

热门标签

最新问答