设置MySQL表中每个userid的最大条目数

Question

Say I have the following table

//table user_update
update_id   |   userid   |   update_message   |   timestamp

Is there a way to set a maximum number of entries per userid? I want to make it so after a user types say 5 updates, any more updates entered after that deletes the oldest update. I know how to do this through PHP, just curious if there's any way to do this through MySQL only.

Answer 1

The only way I can think of doing this database-side would be to use a TRIGGER on the table. Maybe something like this:

CREATE TRIGGER check_for_too_many_rows
AFTER INSERT ON User_Update
FOR EACH ROW BEGIN
   DO SOME LOGIC TO CHECK THE COUNT AND DELETE IF MORE THAN 5...
END;

Here is some additional information:

http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html

Good luck.