如何为MySQL中存储的password记录设置30分钟过期超时？

Hey there! Let's figure out how to add that timeout mechanism for the passwords you're storing in MySQL via Python. You're already saving the password and its expiration timestamp codetimeout—that's a solid foundation. Here are two practical approaches to make those passwords expire as intended:

Option 1: Validate Expiration in Your Python Application (Query-Time Filtering) #

This method lets you ignore expired passwords when retrieving them, without modifying the database directly. Every time you fetch a password, add a condition to check if the expiration time hasn't passed yet.

Example Python Query #

# Fetch only non-expired passwords (adjust the WHERE clause to match your use case)
cur.execute("""
    SELECT password 
    FROM generatedcode 
    WHERE codetimeout > NOW() 
    -- Add any other filters you need (e.g., user ID, code type)
""")

Key Notes: #

• Use MySQL's NOW() function to get the database server's current time—this avoids timezone mismatches between your Python app and MySQL. If you're using UTC time everywhere, swap NOW() with UTC_TIMESTAMP() for consistency.
• When generating the timestamp value in Python, make sure it's set to the correct expiration time. For example, to create a password that expires in 24 hours:

  from datetime import datetime, timedelta
  
  # Generate UTC expiration time (matches MySQL's UTC_TIMESTAMP())
  expiration_time = datetime.utcnow() + timedelta(hours=24)
  timestamp = expiration_time.strftime('%Y-%m-%d %H:%M:%S')
  

Option 2: Automatically Clean Expired Passwords with MySQL Events #

If you want to keep your database table clean by deleting expired passwords automatically, use MySQL's built-in event scheduler. This offloads the work from your Python app to the database.

Step 1: Enable the Event Scheduler #

First, check if the scheduler is active:

SHOW VARIABLES LIKE 'event_scheduler';

If the result is OFF, turn it on temporarily (for the current session):

SET GLOBAL event_scheduler = ON;

To make this permanent, add event_scheduler = ON to your MySQL config file (my.cnf or my.ini) and restart the server.

Step 2: Create the Cleanup Event #

This example creates an event that runs every hour to delete expired passwords:

CREATE EVENT clean_expired_passwords
ON SCHEDULE EVERY 1 HOUR
STARTS CURRENT_TIMESTAMP
DO
  DELETE FROM generatedcode WHERE codetimeout <= NOW();

Customization Tips: #

• Adjust the frequency: Change EVERY 1 HOUR to EVERY 10 MINUTE for more frequent cleanup, or EVERY 1 DAY for daily runs.
• Set a specific start time: Replace CURRENT_TIMESTAMP with a timestamp like '2024-01-01 02:00:00' to start the event at a particular time.
• Ensure your database user has the EVENT permission to create and manage events.

Which Option Should You Choose? #

• Go with Option 1 if you need to retain expired passwords for auditing purposes, or if you prefer to handle expiration logic in your app.
• Go with Option 2 if you want to minimize clutter in your database and reduce the amount of logic in your Python code.

内容的提问来源于stack exchange，提问作者user9580162