MySQL表错误数据识别：现有方案无效的技术求助

从你提供的查询语句和部分结果来看，我们可以从几个核心维度去排查student_room表中的异常数据：

• 日期逻辑冲突校验

  • 首先检查入住与退房日期的合理性：正常场景下check_in_date必须早于check_out_date，可以用这条SQL快速筛选冲突记录：

    SELECT * FROM `student_room` 
    WHERE `user_id` = 17225 
    AND `check_in_date` >= `check_out_date`;
    

  • 验证unallocated_date的逻辑合理性：如果该字段代表取消房间分配的日期，那它应该晚于check_in_date（若用户已入住），或者与未入住的记录匹配。若出现unallocated_date早于入住日期的情况，大概率是数据错误。

• 数据完整性检查

  • 排查日期字段的空值异常：如果业务规则要求这些日期字段必须非空，可通过以下语句筛选空值记录：

    SELECT * FROM `student_room` 
    WHERE `user_id` = 17225 
    AND (`unallocated_date` IS NULL OR `check_in_date` IS NULL OR `check_out_date` IS NULL);
    

  • 检查同一用户的重叠入住记录：同一用户不应存在时间段重叠的入住申请，用自连接查询可快速定位这类冲突：

    SELECT a.*, b.* 
    FROM `student_room` a
    JOIN `student_room` b ON a.user_id = b.user_id 
      AND a.application_id != b.application_id 
      AND a.check_in_date < b.check_out_date 
      AND a.check_out_date > b.check_in_date
    WHERE a.user_id = 17225;
    

• 日期格式合法性验证

  • 如果你的日期字段是VARCHAR类型（而非MySQL原生的DATE/DATETIME），需要校验格式是否合规：

    SELECT * FROM `student_room` 
    WHERE `user_id` = 17225 
    AND (STR_TO_DATE(`check_in_date`, '%Y-%m-%d') IS NULL 
         OR STR_TO_DATE(`check_out_date`, '%Y-%m-%d') IS NULL 
         OR STR_TO_DATE(`unallocated_date`, '%Y-%m-%d') IS NULL);
    

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