MySQL: how do I limit the number of records stored for a value of a column?

Lets say I have a column of user ID's storing past login successes.

Now, I only want the most r开发者_运维知识库ecent 20 login attempts, so Is there a way to specify the table to not store more than 20 records per user id?

---

There are a few possible ways, one would be to use a Stored Procedure to insert the records. That way, the SP would check if there are more than 20 and drop old ones after inserting the new one. The drawback is that if you manually insert a row, it won't get dropped.

A trigger might work... Create an AFTER INSERT trigger that then deletes the older logins > 20...

---

No, this is not possible.

There are two ways:

• Periodically delete rows from the table, so that 20 rows remain.
• When inserting a row and there are more than 20 rows, update an existing row instead of inserting it.

---

I don't believe you can specify that the table only store a certain number of records, but you can control the behavior of insertions of new records. MySQL gives you some non-standard tools that can help implement this without sprocs or triggers. See this link: http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/

The description in that article is pretty exhaustive, I won't repeat a lot of it here. To summarize, you use MySQL's REPLACE command rather than INSERT, or you use an ON DUPLICATE KEY UPDATE statement as part of an INSERT query. Combine that with a table with two unique keys...

This table has two unique keys: one to serve as a monotonically increasing “row number,” and one to cause the wrap-around effect to work. The only real data is the fruit column. Here’s a query to insert “apples” into the queue:

insert into q(id, modulo, fruit)
 select
  (coalesce(max(id), -1) + 1),
  (coalesce(max(id), -1) + 1) mod 5,
  'apples'
 from q
  on duplicate key update
     id    = values(id),
     fruit = values(fruit)

Here’s what the query does: it finds the maximum value of id in the table, which ought to be efficient since it’s indexed. If there are no rows, the result will be NULL, which COALESCE() converts into -1. Then it adds one to that value, which will become the next largest value in the id sequence. What I’m really doing here is rolling my own AUTO_INCREMENT, with a slight twist: the sequence starts at zero, not one.

Etc. Please see the link for (many) more details. :)

Note that, for your case (since you want to maintain 20 records per user), you'll need to add appropriate filtering logic by UserID. If you're only inserting one row for one user at a time, this should be pretty straightforward...