MySQL 查询在 UNION 的每个查询上使用 LIMIT 和 OFFSET 进行搜索

Question

我想这更像是一个概念问题。

* discussion
discussionId
message
timestamp

* comment
commentId
message
timestamp

* tweet
tweetId
message
timestamp

然后在一个页面上我列出第一个让我们说 10 个条目，然后使用 next 加载下 10 个。那里一切正常。

我的问题是:有没有办法限制 UNION 的每个查询，或者我只能对 UNION 的结果执行 LIMIT/OFFSET？我在思考，因为我在想象如果每个表有 1K(我知道这个数字很低)，查询将返回 3K 结果然后只抓取 10。当行数变大时，它不会变慢性能？或者即使将来有更多的表我想做 UNION，那么从每个表中获取 x 并最终获得所需的 10 个不是更好吗？但我无法弄清楚我如何知道哪一个是查询的限制/偏移量的最后一个......所以恐怕我必须选择全部并使用 UNION 的限制。

所以不用

(SELECT 
    message,
    timestamp
FROM 
    discussion
)

UNION

(SELECT 
    message,
    timestamp
FROM 
    comment
)
UNION

(SELECT 
    message,
    timestamp
FROM 
    tweet
)
LIMIT $offset, $limit

有没有可能

(SELECT 
    message,
    timestamp
FROM 
    discussion
    LIMIT $offset, $limit
)

UNION

(SELECT 
    message,
    timestamp
FROM 
    comment
    LIMIT $offset, $limit
)
UNION

(SELECT 
    message,
    timestamp
FROM 
    tweet
    LIMIT $offset, $limit
)
LIMIT $offset, $limit

感谢您的帮助!

Answer 1

SQL Fiddle

MySQL 5.6 架构设置:

CREATE TABLE Message
    (`id` int, `type_id` int, `message` varchar(10), `timestamp` int)
;

INSERT INTO Message
    (`id`, `type_id`, `message`, `timestamp`)
VALUES
    (1, 1, 'message1', 12345678),
    (2, 2, 'message2', 12345679),
    (3, 3, 'message3', 12345680)
;


CREATE TABLE Type
    (`id` int, `type` varchar(10))
;

INSERT INTO Type
    (`id`, `type`)
VALUES
    (1, 'discussion'),
    (2, 'comment'),
    (3, 'tweet')
;


CREATE TABLE DiscussionDetail
    (`id` int, `message_id` int, `from_user` varchar(10), `to_user` varchar(10))
;

INSERT INTO DiscussionDetail
    (`id`, `message_id`, `from_user`, `to_user`)
VALUES
    (1, 1, 'Peter', 'Anna')
;


CREATE TABLE CommentDetail
    (`id` int, `message_id` int, `post_id` varchar(5), `user_id` varchar(5))
;

INSERT INTO CommentDetail
    (`id`, `message_id`, `post_id`, `user_id`)
VALUES
    (1, 2, 'post4', 'user6')
;


CREATE TABLE TweetDetail
    (`id` int, `message_id` int, `twitteracct` varchar(6))
;

INSERT INTO TweetDetail
    (`id`, `message_id`, `twitteracct`)
VALUES
    (1, 3, 'myacct')
;

查询 1:

SELECT Message.*, type,
case when dd.id is not null then from_user 
     when cd.id is not null then post_id  
     when td.id is not null then twitteracct else '' end as detail1,
case when dd.id is not null then to_user  
     when cd.id is not null then user_id else '' end as detail2
FROM Message
INNER JOIN Type ON Type_Id = Type.id
LEFT OUTER JOIN DiscussionDetail dd ON dd.message_id = Message.id AND Type_Id = 1
LEFT OUTER JOIN CommentDetail cd ON cd.message_id = Message.id AND Type_Id = 2
LEFT OUTER JOIN TweetDetail td ON td.message_id = Message.id AND Type_Id = 3
ORDER BY timestamp LIMIT 1,1

Results :

| id | type_id |  message | timestamp |    type | detail1 | detail2 |
|----|---------|----------|-----------|---------|---------|---------|
|  2 |       2 | message2 |  12345679 | comment |   post4 |   user6 |

你也可以为此创建一个 View :

CREATE VIEW AllMessages AS
SELECT Message.*, type,
case when dd.id is not null then from_user 
     when cd.id is not null then post_id  
     when td.id is not null then twitteracct else '' end as detail1,
case when dd.id is not null then to_user  
     when cd.id is not null then user_id else '' end as detail2
FROM Message
INNER JOIN Type ON Type_Id = Type.id
LEFT OUTER JOIN DiscussionDetail dd ON dd.message_id = Message.id AND Type_Id = 1
LEFT OUTER JOIN CommentDetail cd ON cd.message_id = Message.id AND Type_Id = 2
LEFT OUTER JOIN TweetDetail td ON td.message_id = Message.id AND Type_Id = 3

然后:

SELECT *
FROM AllMessages
ORDER BY timestamp LIMIT 1,1

SQL Fiddle