带外键关联的MySQL表按时间字段生成删除脚本需求

Got it, let's work through this problem properly—since we can't rely on DELETE CASCADE or disable foreign keys in production, we need to be methodical about the order of deletions and make sure every step plays nice with your foreign key constraints.

First, you need to nail down the correct deletion sequence: child tables (those with foreign keys pointing to other tables) must be cleaned up first, followed by parent tables (the ones being referenced). If you skip this order, you'll hit foreign key constraint errors immediately.

To get a clear view of your table relationships, run this query against your database (replace your_database_name with your actual DB name):

SELECT 
  TABLE_NAME AS child_table,
  REFERENCED_TABLE_NAME AS parent_table
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
  REFERENCED_TABLE_SCHEMA = 'your_database_name'
  AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY REFERENCED_TABLE_NAME DESC;

This will list every child table and its associated parent. Use this to build your deletion order—for example: if order_items references orders, and orders references users, your sequence is order_items → orders → users.

For each table, your DELETE query needs to filter by your time field (let's assume it's named created_at for this example). Adjust the interval (e.g., INTERVAL 30 DAY) to match your retention policy.

For Child Tables #

Straightforward deletion with the time filter:

DELETE FROM order_items 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);

For Parent Tables #

To avoid accidental deletions of parent records that still have linked child data, add a check to ensure no child records exist for the parent:

DELETE FROM users 
WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
AND NOT EXISTS (
  SELECT 1 FROM orders WHERE orders.user_id = users.id
);

This ensures you only delete parent records that have already had their associated child data removed.

In production, deleting thousands of rows in one go can lock tables and impact performance. Instead, use batch deletions to limit the number of rows removed per operation:

-- Batch delete order_items (500 rows at a time)
WHILE EXISTS (
  SELECT 1 FROM order_items WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
) DO
  DELETE FROM order_items 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY)
  LIMIT 500;
  -- Add a small sleep to reduce database load
  SELECT SLEEP(0.1);
END WHILE;

Adjust the LIMIT and SLEEP values based on your database's capacity—smaller batches mean less lock contention but take longer to complete.

Let's put it all together with a real-world example. Suppose you have three tables: users (parent), orders (child of users), order_items (child of orders), all with a created_at time field. Here's a complete script to delete data older than 90 days:

-- Step 1: Delete from deepest child table first
WHILE EXISTS (
  SELECT 1 FROM order_items WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
) DO
  DELETE FROM order_items 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
  LIMIT 500;
  SELECT SLEEP(0.1);
END WHILE;

-- Step 2: Delete from intermediate child table
WHILE EXISTS (
  SELECT 1 FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
) DO
  DELETE FROM orders 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
  LIMIT 500;
  SELECT SLEEP(0.1);
END WHILE;

-- Step 3: Delete from parent table (only records with no remaining children)
WHILE EXISTS (
  SELECT 1 FROM users 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
  AND NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
) DO
  DELETE FROM users 
  WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
  AND NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id)
  LIMIT 500;
  SELECT SLEEP(0.1);
END WHILE;

• Test First: Always run a SELECT instead of DELETE to verify the data you're targeting. For example:

  SELECT COUNT(*) FROM orders WHERE created_at < DATE_SUB(NOW(), INTERVAL 30 DAY);
  

• Index Your Time Field: Make sure created_at (or your time column) has an index—without it, every DELETE will do a full table scan, which is slow and resource-heavy.
• Monitor Load: Keep an eye on your database's CPU, memory, and lock metrics while running these scripts. Adjust batch sizes or add longer sleeps if you see performance degradation.
• Backup First: Take a backup of the tables you're modifying before running any deletion scripts in production—you never know when you'll need to roll back.

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