mysql - 从mysql表中删除N条旧记录

Question

我有一个像这样的 LoginTime 表:

id | user_id | datetime
1  |   1     | 2011-01-19 18:51:01
2  |   1     | 2011-01-19 18:51:02  
3  |   1     | 2011-01-19 18:51:03  
4  |   1     | 2011-01-19 18:51:04  
5  |   1     | 2011-01-19 18:51:05  
6  |   1     | 2011-01-19 18:51:06  
7  |   1     | 2011-01-19 18:51:07  
8  |   1     | 2011-01-19 18:51:08  
9  |   1     | 2011-01-19 18:51:09  
10 |   2     | 2011-01-19 18:51:10  

我只想保留 5 个最新的(按“日期时间”列)记录并删除所有以前的记录，其中 user_id=1

是否可以通过一个 mysql 查询来实现这一目标？

Answer 1

DELETE 
FROM LoginTime 
WHERE user_id = 1 
ORDER BY datetime ASC 
LIMIT 5