mysql - 想要在添加新记录时删除最旧的记录，留下不超过 5 条记录

Question

我有下表:

id      user_id     message     timestamp
----    -------     --------    ----------
1        abc        message1    2015-07-27 19:10:40
2        def        message1    2015-07-27 19:20:41
3        xyz        message1    2015-07-27 19:30:41
4        abc        message2    2015-07-28 19:11:40
5        abc        message3    2015-07-28 19:12:40
6        abc        message4    2015-07-28 19:13:40
7        abc        message5    2015-07-28 19:14:40

当用户“abc”添加第六条消息时，我希望删除第一条(最旧的)消息。

我正在尝试以下两个 SQL 查询，但它总共只留下 5 条消息，不考虑 user_id

INSERT INTO sample(user_id, message, created)
VALUES('abc', 'message6', now());

DELETE FROM sample 
WHERE user_id = 'abc' 
  AND id NOT IN (SELECT id 
                 FROM 
                     (SELECT id 
                      FROM sample 
                      ORDER BY id DESC 
                      LIMIT 5) x 
                ); 

提前致谢

Answer 1

您在第二个查询中缺少用户的选择。

此外，正如 Jorge Campos 所强调的那样，您应该按日期排序，以便现有消息的编辑/更新被视为最新消息而不是旧消息。

尝试

INSERT INTO sample(user_id,message,created)
VALUES('abc','message6',now());

DELETE FROM sample WHERE user_id = 'abc' AND id NOT IN ( 
  SELECT id 
  FROM ( 
    SELECT id 
    FROM sample WHERE user_id = 'abc'
    ORDER BY created DESC 
    LIMIT 5
    ) x 
  );