12.21

1. 按正则匹配删除（模糊匹配特定格式数据）

sql
-- 删除手机号不是11位纯数字的用户记录
DELETE FROM user WHERE phone NOT REGEXP '^1[0-9]{10}$';

-- 删除用户名包含特殊字符（非字母/数字/下划线）的记录
DELETE FROM user WHERE username REGEXP '[^a-zA-Z0-9_]';

2. 带DISTINCT去重删除（保留唯一数据，删除重复项）

sql
-- 保留score表中同一user_id+subject的最新1条记录，删除其余重复项
DELETE s1 FROM score s1
JOIN score s2
ON s1.user_id = s2.user_id AND s1.subject = s2.subject AND s1.create_time < s2.create_time;

3. 结合函数条件删除（按字段计算结果筛选）

sql
-- 删除年龄>60的用户（假设存的是出生日期，用TIMESTAMPDIFF计算年龄）
DELETE FROM user
WHERE TIMESTAMPDIFF(YEAR, birthday, CURDATE()) > 60;

-- 删除订单金额为0或NULL的无效订单
DELETE FROM orders
WHERE IFNULL(order_amount, 0) = 0;