mysql - 为什么 MySQL COUNT(*) 查询返回空集？

Question

我有 3 个表:

• user(用户表)

• post(包含 author 字段的帖子表，它是用户 id 的 FOREIGN KEY)

• friend(表示用户与用户之间关系的表，具有 FOREIGN KEY 字段:user_id 和 friend

我有一个查询，我可以在其中设置过滤结果的值，如下所示(来自 Java PreparedStatement):

SELECT post.* FROM post, friend WHERE friend.user_id = ? AND post.author = friend.friend AND post.body LIKE ? AND post.date BETWEEN ? AND ? LIMIT ?,?

像这样在查询中插入值:

SELECT post.* FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%word%' AND post.date BETWEEN '2016-02-10 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;

我得到一个包含 5 个帖子的列表。但是现在，我想获得结果的数量。我试过:

SELECT COUNT(*) FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%word%' AND post.date BETWEEN '2016-02-10 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;

但是我得到了 Empty set。为什么我得到这个而不是 5？

编辑:

如果有帮助，我会发布数据库代码和一些数据插入。

DROP DATABASE IF EXISTS dbname;
CREATE DATABASE dbname;
USE dbname;

CREATE TABLE user (
    id          INT             NOT NULL    AUTO_INCREMENT,
    username    VARCHAR(20)     NOT NULL    UNIQUE,
    name        VARCHAR(70)     NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE post (
    id         INT             NOT NULL    AUTO_INCREMENT,
    date       DATETIME        NOT NULL,
    title      VARCHAR(100)    NOT NULL,
    body       VARCHAR(1000)   NOT NULL,
    author     INT             NOT NULL,
    PRIMARY KEY(id)
) ENGINE=InnoDB;

CREATE TABLE friend (
    user_id     INT             NOT NULL,
    friend      INT             NOT NULL,
    PRIMARY KEY(user_id, friend)
) ENGINE=InnoDB;

ALTER TABLE post
    ADD FOREIGN KEY fk_autor_post(author) REFERENCES user(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

ALTER TABLE friend
    ADD FOREIGN KEY fk_user_id(user_id) REFERENCES user(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

ALTER TABLE friend
    ADD FOREIGN KEY fk_friend_id(friend) REFERENCES user(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;


INSERT INTO user VALUES(null, "uname1", "User 1");
INSERT INTO user VALUES(null, "uname2", "User 2");
INSERT INTO user VALUES(null, "uname3", "User 3");
INSERT INTO user VALUES(null, "uname4", "User 4");
INSERT INTO user VALUES(null, "uname5", "User 5");

INSERT INTO friend VALUES(1,2);
INSERT INTO friend VALUES(1,3);
INSERT INTO friend VALUES(1,4);
INSERT INTO friend VALUES(1,5);
INSERT INTO friend VALUES(2,1);
INSERT INTO friend VALUES(2,5);
INSERT INTO friend VALUES(4,1);
INSERT INTO friend VALUES(2,4);
INSERT INTO friend VALUES(2,3);

INSERT INTO post values (null, '2016-01-01 10:00:00', "Title post 1", "Body 1", 1);
INSERT INTO post values (null, '2016-01-01 10:00:00', "Title post 2", "Body 2", 1);
INSERT INTO post values (null, '2016-01-01 10:20:40', "Title post 3", "Body 3", 1);
INSERT INTO post values (null, '2016-01-01 10:20:00', "Title post 4", "Body 4", 4);
INSERT INTO post values (null, '2016-02-05 10:00:00', "Title post 5", "Body 5", 5);
INSERT INTO post values (null, '2016-01-05 10:00:00', "Title post 6", "Body 6", 2);
INSERT INTO post values (null, '2016-02-01 22:00:00', "Title post 7", "Body 7", 2);
INSERT INTO post values (null, '2016-02-01 10:00:00', "Title post 8", "Body 8", 3);
INSERT INTO post values (null, '2016-01-02 10:00:00', "Title post 9", "Body 9", 5);
INSERT INTO post values (null, '2016-01-01 10:40:00', "Title post 10", "Body 10", 1);

查询:

SELECT post.* FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%%' AND post.date BETWEEN '2016-01-01 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;

返回:

+----+---------------------+--------------+--------+--------+
| id | date                | title        | body   | author |
+----+---------------------+--------------+--------+--------+
|  2 | 2016-01-01 10:00:00 | Title post 2 | Body 2 |      1 |
|  3 | 2016-01-01 10:20:40 | Title post 3 | Body 3 |      1 |
|  4 | 2016-01-01 10:20:00 | Title post 4 | Body 4 |      4 |
|  5 | 2016-02-05 10:00:00 | Title post 5 | Body 5 |      5 |
|  8 | 2016-02-01 10:00:00 | Title post 8 | Body 8 |      3 |
+----+---------------------+--------------+--------+--------+
5 rows in set (0.00 sec)

查询:

SELECT COUNT(*) FROM post, friend WHERE friend.user_id = 2 AND post.author = friend.friend AND post.body LIKE '%%' AND post.date BETWEEN '2016-01-01 00:00:00' AND '2016-12-01 00:00:00' LIMIT 1,5;

返回:

Empty set (0.00 sec)

同样使用 COUNT(post.id), COUNT(post.body), SUM(1), SUM (post.id)。使用 COUNT(post.*) 时出错:

Answer 1

我在自己的数据库上尝试了类似的查询，得到了与您相同的结果。我将限制从 1.5 更改为 0.5 并得到了预期的结果。我不知道这是否适合您。