java - mysql如何限制每天insert的次数？

Question

需求是这样的：
用户每天最多领取礼包3次，每次insert一条记录，但是在并发的情况下会超出3次，如果解决？
有个已知的方法如下，但是并发下会产生死锁

INSERT INTO t_test (gift_id, user_id, `date`) SELECT v_gift_id, v_user_id, v_date FROM dual WHERE not exists (select * from t_test where `user_id` = v_user_id and `date` = v_date GROUP BY `user_id`, `date` HAVING count(*) > 2);

Answer 1

As @junze said, it is usually made with redis.

For example, store the number of times each user insert in redis中存储每个用户insert的次数。假如key格式是user:count. If the key format is user:count

Check firstcount的值,小于3，执行插入，判断insert的结果，insert成功的话，对key user:count执行incr, incr user:count,写入失败，不执行incr every time.

This can avoid high concurrency exceeding 3.

At the same time, you can set this key设置过期时间 expire user:count 60*60*24 to 1 day. After one day, the key will be deleted and re-initialized the next day.

Answer 2

Use the string type of redis for counting. Redis supports high concurrency better

Answer 3

MySQL cannot help you do this. You can put the user's gift pack count in the cache, add some constants as the cache key based on the user's ID, store the count in it, and retrieve the count from the cache every time you insert. , judge whether it is less than your number of times, if it is less than your number, continue, if not, do not continue. If you continue, the number will be increased by one after insert and placed in the cache.

Answer 4

It is recommended to do it at the logic layer

Answer 5

Mysql doesn’t seem to have this mechanism. I feel that this control is also a logical control

Answer 6

If you must use mysql to do it, use transactions to ensure it.