How to use MySQL's unique index to limit users to only insert one piece of data within a specific time period?

MySQL database: Restrict data insertion within a specific time period

In application development, it is often necessary to restrict the user to perform only one specific operation within a given time range, for example, allowing only one database record to be inserted per hour. This article discusses how to use the MySQL database mechanism to implement this function.

Challenge: Concurrent requests and data integrity

The goal is to ensure that only one data is allowed to be inserted within the same hour, even in the face of multiple concurrent requests. Relying solely on MySQL unique indexes cannot directly solve this problem, because unique indexes are usually aimed at a single column or a combination of columns and cannot directly associate a time period.

Solution: Combined with Redis or database lock

Solution 1: Efficient Redis distributed lock (high concurrency scenario)

For high concurrency scenarios, Redis distributed locks provide efficient solutions:

1. Get Redis lock: Before inserting the database, try to acquire the Redis distributed lock. The key of the lock may be the identification of the current hour (eg, hourly_insert_lock:2024-10-27-10 ).
2. Check the time period: After the lock is successfully acquired, query the timestamp of the last record within the current hour in the database. Insert is refused if the timestamp is within the same hour.
3. Insert data: If the timestamp is not within the same hour, perform a database insertion operation.
4. Release Redis lock: After insertion is successful, release the Redis lock.

This solution utilizes the high-performance features of Redis to effectively avoid concurrent conflicts and ensure data integrity.

Solution 2: Database lock (low concurrency scenario)

For low concurrency scenarios, you can use database locks:

1. Get database locks: Use database transaction and row-level locks (for example, SELECT ... FOR UPDATE ) to lock rows in related tables.
2. Check the time period: Similar to Scheme 1, check the timestamp of the last record within the current hour in the database.
3. Insert data: If the timestamp is not within the same hour, perform a database insertion operation.
4. Submit transaction: After the insertion is successful, submit the transaction and release the database lock.

This solution is relatively simple, but database locks under high concurrency will affect performance.

Through the above two methods, users can effectively limit only one data to be inserted within a specific time period to maintain the data integrity of the database. Which option to choose depends on the amount of concurrency and performance requirements of the application.

The above is the detailed content of How to use MySQL's unique index to limit users to only insert one piece of data within a specific time period?. For more information, please follow other related articles on the PHP Chinese website!