MySQL基于两表条件删除表行结果异常 误删非目标数据

• 核心诱因是字段类型不匹配：你的date字段大概率不是DATE/DATETIME等标准日期类型，而是字符串类型。字符串比较遵循字典序规则，和实际日期的先后排序逻辑完全不一致，只要日期存储格式不是严格的YYYY-MM-DD/YYYY-MM-DD HH:MM:SS格式，就会出现比较结果错乱，误删非目标区间的数据。如果是标准日期类型，2020年12月、2021年12月的日期值必然小于2022年1月的最小日期，根本不可能落在2022年1月-2022年7月的区间内。
• 写法存在逻辑隐患：你当前通过取T2最大、最小日期做BETWEEN匹配的逻辑，仅适用于T2日期是连续无缺口的场景。如果T2的日期存在断点，会误删T1中落在min和max之间、但T2中实际不存在的日期记录。
• 额外风险点：你写查询条件时没有给date字段加表别名，多表查询场景下很容易出现字段归属歧义，触发数据库的隐式解析逻辑，导致匹配条件完全偏离预期。

强制操作规范：执行任何DELETE语句前，先把DELETE替换为SELECT * 执行，校验返回的结果集完全符合删除预期后，再执行删除操作，避免数据丢失。

1. 先校验字段类型，执行以下SQL确认date字段的实际类型：

SHOW COLUMNS FROM T1 LIKE 'date';
SHOW COLUMNS FROM T2 LIKE 'date';

如果返回的字段类型是VARCHAR/CHAR等字符串类型，必须在查询时做显式日期转换，禁止直接用字符串做日期大小比较。
2. 根据实际需求选择对应写法：

• 场景1：需要删除T1中所有落在T2日期覆盖区间（即T2最早日期到最晚日期之间）的记录
  以下示例假设日期字符串格式为YYYY-MM-DD，如果是其他格式（比如YYYY/MM/DD/MM-DD-YYYY等），对应修改STR_TO_DATE函数的格式参数即可。

-- 第一步：校验待删除数据
SELECT * FROM T1
WHERE STR_TO_DATE(`date`, '%Y-%m-%d') BETWEEN 
(SELECT MIN(STR_TO_DATE(`date`, '%Y-%m-%d')) FROM T2)
AND 
(SELECT MAX(STR_TO_DATE(`date`, '%Y-%m-%d')) FROM T2);

-- 第二步：校验通过后执行删除
DELETE FROM T1
WHERE STR_TO_DATE(`date`, '%Y-%m-%d') BETWEEN 
(SELECT MIN(STR_TO_DATE(`date`, '%Y-%m-%d')) FROM T2)
AND 
(SELECT MAX(STR_TO_DATE(`date`, '%Y-%m-%d')) FROM T2);

• 场景2：需要删除T1中所有在T2表有对应日期记录的数据（即T2日期不连续时，只删除两边都存在的日期）
  不要用BETWEEN取最值的写法，改用EXISTS做精准匹配：

-- 第一步：校验待删除数据
SELECT t1.* FROM T1 t1
WHERE EXISTS (
    SELECT 1 FROM T2 t2
    WHERE STR_TO_DATE(t1.`date`, '%Y-%m-%d') = STR_TO_DATE(t2.`date`, '%Y-%m-%d')
);

-- 第二步：校验通过后执行删除
DELETE t1 FROM T1 t1
WHERE EXISTS (
    SELECT 1 FROM T2 t2
    WHERE STR_TO_DATE(t1.`date`, '%Y-%m-%d') = STR_TO_DATE(t2.`date`, '%Y-%m-%d')
);

• 数据校验完成后，建议把两张表的date字段修改为标准DATE类型，从根源上避免字符串比较的排序错乱问题。
• 两张表都没有主键的话，建议新增自增主键，同时给date字段加索引，提升查询、删除的执行效率。
• 批量删除前务必备份T1表数据，避免误删后无法恢复。

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