时间戳

首页 > 解决方案 > 每行的 MySQL LEFT JOIN LIMIT

mysql - 每行的 MySQL LEFT JOIN LIMIT

问题描述

我想获得一个帖子列表，包括每个特定帖子的最后 2 条评论（如果有的话）。我一直在使用下面的查询，它返回每个帖子的所有评论，但是它有一个限制，因为默认情况下 GROUP_CONCAT() 长度限制为 1024 个字符，所以如果帖子上有很多评论， 'comments' 值将被截断，不再是有效的 JSON 输出。

SELECT post.*,
    CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT(
        'id', postComment.id,
        'date', postComment.date,
        'content', postComment.content,
        'user', CONCAT(postComment.fName, postComment.lName)
    ) ORDER BY postComment.id DESC),']') AS comments
FROM posts AS post
    LEFT JOIN (
        SELECT comment.*, commentUser.fName, commentUser.lName
        FROM comments AS comment
            LEFT JOIN users AS commentUser ON comment.uID = commentUser.id
        ORDER BY comment.id DESC
    ) AS postComment ON postComment.postID = post.id
WHERE post.uID = 37
GROUP BY post.id
ORDER BY post.id DESC
LIMIT 0,5;

由于这个限制，我想通过添加LIMIT 0,2到 LEFT JOIN SELECT 子句中只返回每个帖子的最后 2 条评论，如下所示：

SELECT post.*,
    CONCAT('[', GROUP_CONCAT(DISTINCT JSON_OBJECT(
        'id', postComment.id,
        'date', postComment.date,
        'content', postComment.content,
        'user', CONCAT(postComment.fName, postComment.lName)
    ) ORDER BY postComment.id DESC),']') AS comments
FROM posts AS post
    LEFT JOIN (
        SELECT comment.*, commentUser.fName, commentUser.lName
        FROM comments AS comment
            LEFT JOIN users AS commentUser ON comment.uID = commentUser.id
        ORDER BY comment.id DESC
        LIMIT 0, 2
    ) AS postComment ON postComment.postID = post.id
WHERE post.uID = 37
GROUP BY post.id
ORDER BY post.id DESC
LIMIT 0,5;

但现在它只返回第一个帖子的前两条评论......

谁能告诉我如何才能正确地完成这项工作，因此查询结果会返回每个特定帖子的前两条评论以及为什么会发生这种情况？

干杯!

标签： mysqlsqlarraysjsonstring

解决方案

作为初学者：如果您的实际问题是对返回的字符数的限制GROUP_CONCAT()，您可以通过修改会话变量来增加它group_concat_max_len，如下所示：

SET SESSION group_concat_max_len = 1000000;

然后您可以运行您的查询（在同一个会话中）。

至于您的问题本身：我建议将其LEFT JOIN转换为相关子查询；基本上每个帖子都会重新执行子查询，因此您可以使用ORDER BYand LIMIT：

SELECT p.*, 
    CONCAT(
        '[', 
        (
            SELECT GROUP_CONCAT(
                JSON_OBJECT(
                    'id', c.id,
                    'date', c.date,
                    'content', c.content,
                    'user', CONCAT(u.fName, u.lName)
                )
                ORDER BY c.id DESC
            )
            FROM comments AS c
            LEFT JOIN users AS u ON c.uID = u.id
            WHERE c.postID = p.id
            ORDER BY c.date DESC
            LIMIT 2
        ), 
        ']'
    ) AS comments
    FROM posts AS p
WHERE p.uID = 37
GROUP BY p.id
ORDER BY p.id DESC
LIMIT 0,5;

最后的想法：升级到 MySQL 5.7.22 或更高版本！然后你就可以使用JSON_ARRAYAGG()了，你再也不用担心这一切了。

推荐阅读