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

Question

Suppose I have a user ID column that stores past access successes.

Now I only need the last 20 login attempts, so is there a way to indicate that the table does not store more than 20 entries per user ID?

Answer 1

There are several possible ways: use a stored procedure to insert records. Thus, SP will check if there are more than 20 and discard old ones after inserting a new one. The downside is that if you manually insert a row, it will not be deleted.

A trigger ... AFTER INSERT, > 20...

Answer 2

, .

:

• , 20 .
• 20 .

Answer 3

, , , . MySQL , sprocs . . : http://www.xaprb.com/blog/2007/01/11/how-to-implement-a-queue-in-sql/

, . , MySQL REPLACE INSERT ON DUPLICATE KEY UPDATE INSERT. ...

: , " ", , . - . Heres "" :

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)

: id , . , NULL, COALESCE() -1. , . , , AUTO_INCREMENT : , .

Etc. . ().:)

, ( 20 ), UserID. , ...