存储过程本身不保证安全删重，真正决定安全性的，是执行逻辑是否可预判、是否加锁可控、是否支持回滚验证；多数人直接封装delete语句反而放大锁表或误删风险。
存储过程本身不保证安全删重，真正决定安全性的，是执行逻辑是否可预判、是否加锁可控、是否支持回滚验证——多数人直接封装 DELETE 语句反而放大风险。
为什么用存储过程删重容易锁表或误删
很多人把“查重+删重”写进存储过程就以为万事大吉，结果在生产环境跑一次就卡住整个表。核心问题不是语法错，而是没考虑数据库的执行行为：
-
DELETE FROM t WHERE id IN (SELECT ...)在 MySQL 中会触发全表扫描+隐式锁升级，尤其当子查询未走索引时，可能锁住上万行 - SQL Server 的参数嗅探会让第一次编译的执行计划被复用，若首次传入的是小数据集，后续大表执行就可能走嵌套循环而非哈希匹配，内存爆满
- PostgreSQL 的
WITH ... DELETE虽原子，但若PARTITION BY字段无索引，ROW_NUMBER()排序阶段就会吃光 shared_buffers - 没加事务包装或没设
SET XACT_ABORT ON（SQL Server）/BEGIN TRANSACTION（PG），出错时部分删掉就停了，无法回滚
MySQL 8.0+ 存储过程中绕过 Error 1093 的硬限制
MySQL 禁止在 DELETE 中直接引用同一张表的子查询，这不是 bug，是引擎设计限制。强行拼 EXEC(@sql) 不仅失去语法检查，还让 SQL 注入风险翻倍。
安全做法是用临时表中转，且必须显式命名、带 DROP TEMPORARY TABLE IF EXISTS：
DELIMITER $$
CREATE PROCEDURE clean_users_by_email()
BEGIN
DECLARE deleted_count INT DEFAULT 0;
<p>DROP TEMPORARY TABLE IF EXISTS _dup_ids;
CREATE TEMPORARY TABLE _dup_ids (
id INT PRIMARY KEY
);</p><p>INSERT INTO _dup_ids
SELECT id FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
FROM users
WHERE email IS NOT NULL
) t WHERE rn > 1;</p><p>DELETE u FROM users u
INNER JOIN _dup_ids d ON u.id = d.id;</p><p>GET DIAGNOSTICS deleted_count = ROW_COUNT;
SELECT CONCAT('Deleted ', deleted_count, ' duplicate rows') AS result;</p><p>DROP TEMPORARY TABLE _dup_ids;
END$$
DELIMITER ;
</p>
- 临时表名加下划线前缀，避免和业务表名冲突
- 必须用
INNER JOIN写法，不能用WHERE id IN (SELECT ...)，否则仍报 1093 -
GET DIAGNOSTICS比ROW_COUNT()更可靠，它返回的是刚执行的DELETE影响行数，不受前面INSERT干扰
SQL Server 存储过程中防参数嗅探的关键设置
如果你传入 @key_columns NVARCHAR(200) 动态拼字段名，等于主动放弃执行计划缓存和安全边界。正确姿势是：固定去重字段 + 强制重编译 + 显式提示索引。
- 只允许按已建索引的列去重，比如
email列有INDEX idx_email ON users(email) - 存储过程开头加
WITH RECOMPILE，避免复用旧计划 - 在 CTE 中用
OPTION (RECOMPILE)，让优化器每次根据实际数据量生成计划 - 删除前加
IF NOT EXISTS (SELECT 1 FROM sys.dm_db_index_usage_stats ...)校验索引是否存在，防止误跑
示例关键片段：
CREATE OR ALTER PROCEDURE clean_users_by_email
WITH RECOMPILE
AS
BEGIN
SET NOCOUNT ON;
<p>-- 验证索引存在
IF NOT EXISTS (
SELECT 1 FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('users') AND c.name = 'email'
)
BEGIN
RAISERROR('Missing index on users.email', 16, 1);
RETURN;
END</p><p>WITH duplicates AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
FROM users WITH (NOLOCK)
WHERE email IS NOT NULL
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1)
OPTION (RECOMPILE);
END
</p>
真正该优先做的三件事，比写存储过程重要得多
别急着封装逻辑。先确认这三点是否到位，否则任何存储过程都是空中楼阁：
- 目标字段（如
email）是否已有NOT NULL约束？没有的话，NULL = NULL在GROUP BY或PARTITION BY中行为不一致，PG 和 SQL Server 处理方式就不同 - 是否在非高峰时段执行？哪怕加了
TOP 1000分批删，第一次运行也建议用SELECT COUNT(*)预估影响行数，而不是直接DELETE - 是否留有恢复手段？例如提前
SELECT * INTO users_backup_20260804 FROM users，而不是依赖备份窗口
最常被忽略的点：ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) 中的 y 字段如果没有索引，排序成本会随数据量平方增长——这个代价不会因放进存储过程而降低，只会更难定位。
