12.27

1. CTE+关联删除（MySQL 8.0+，复杂条件更清晰）

sql

WITH temp AS (
SELECT id FROM user WHERE last_login_time < '2024-01-01'
)
DELETE u FROM user u JOIN temp t ON u.id = t.id;

• CTE先定义待删数据集，删除逻辑更易读，适合多层筛选场景。

2. 带LOCK IN SHARE MODE的安全删除（避免并发冲突）

sql

BEGIN;
-- 先加共享锁查待删行，防止并发修改
SELECT id FROM user WHERE id = 3003 LOCK IN SHARE MODE;
DELETE FROM user WHERE id = 3003;
COMMIT;

• 共享锁保证删前数据无变更，适合高并发、需精准删单行的场景。

3. FEDERATED表跨库删除（跨实例删关联数据）

sql

-- 先建FEDERATED表映射远程订单表
CREATE TABLE remote_orders (
id INT, user_id INT
) ENGINE=FEDERATED CONNECTION='mysql://user:pass@remote_host:3306/db/orders';

-- 删除本地用户+远程关联订单
BEGIN;
DELETE FROM remote_orders WHERE user_id = 4004;
DELETE FROM user WHERE id = 4004;
COMMIT;

• 适合跨库数据同步清理，需提前配置FEDERATED引擎与远程连接。