php - MYSQL如何控制每条记录的最大行数？

Question

表结构为:

user
-----
id

video
------
id

user_views
----------
id
user_id
video_id
create_date

而且user_views可以重复，为了防止数据变得非常庞大，我想限制user_views记录:

每个用户最多可记录20条user_views，超过则替换最旧的记录，先进先出

问题是，我该如何构造插入查询？

现在我的方法是使用 PHP

1. 计算特定用户的行数。
2. 如果 > 20 ，更新最后一条记录(按 create_date 排序)
3. else ,插入新记录

它有效，但我想要一种性能更高的方法，即在 MYSQL 中进行一次插入查询。

感谢您的帮助。

Answer 1

这是一个简单的解决方案:创建虚拟记录以便始终每个用户有 20 次浏览。

insert into user_views (user_id, video_id, create_date)
select id, null, '1920-01-01' from user u
where (select count(*) from user_views uv where uv.user_id = u.id) < 20
union all
select id, null, '1919-01-01' from user u
where (select count(*) from user_views uv where uv.user_id = u.id) < 19
union all
select id, null, '1918-01-01' from user u
where (select count(*) from user_views uv where uv.user_id = u.id) < 18
union all
select id, null, '1917-01-01' from user u
where (select count(*) from user_views uv where uv.user_id = u.id) < 17
union all
...

选择数据以显示它们时，排除虚拟记录:

select *
from user_views
where user_id = 123
and video_id is not null; -- dummy entries have video_id null

“插入”新数据时，使用更新:

update user_views
set video_id = 456, create_date = current_date()
where id = 
(
  select id 
  from
  (
    select id 
    from user_views
    where user_id = 123
    order by create_date
    limit 1
  ) oldest
);

(MySQL中需要子查询中的子查询，因为在访问正在更新的同一个表时有限制。)

SQL fiddle 在这里:http://sqlfiddle.com/#!9/11f2c4/1。