任务管理数据库优化及Slot任务今日未完成查询问题

一、数据库架构优化方案 #

1. 清理冗余字段与逻辑冲突 #

• task表中的task_status和completed_on字段仅对截止日期式任务有效，Slot式任务的完成状态完全依赖task_status表，建议通过表约束明确规则：当task_type=1（Slot任务）时，task_status必须为0，completed_on必须为NULL，避免逻辑矛盾。
• task_slot表的slot字段存储字符串不利于时间维度的查询与排序，建议拆分为结构化时间字段：

  ALTER TABLE task_slot 
  ADD COLUMN start_time TIME NOT NULL,
  ADD COLUMN end_time TIME NOT NULL;
  -- 示例更新：将'10 AM to 12 PM'转换为start_time='10:00:00'，end_time='12:00:00'
  -- 可保留slot字段作为前端显示文本，或通过CONCAT动态生成
  

2. 索引优化 #

• 针对task_status表的常见查询场景（按任务ID+完成日期检索），创建联合索引：

  CREATE INDEX idx_task_completed ON task_status(task_id, task_completed_on);
  

• 针对task表的查询条件（任务类型+分配对象），创建联合索引提升筛选效率：

  CREATE INDEX idx_task_type_given_to ON task(task_type, task_given_to);
  

3. 约束与数据一致性优化 #

• 修复task表task_slot_id的外键约束，设置ON UPDATE CASCADE ON DELETE RESTRICT，避免出现测试数据中task_slot_id=1但无对应slot记录的无效关联。
• task_status表的task_completed_on字段设置为NOT NULL，因为记录完成状态时必须存在完成时间。

二、SQL查询修复（获取指定用户今日未完成的Slot任务） #

原查询出现重复行，是因为LEFT JOIN了包含多条完成记录的task_status表，导致单任务生成多行结果。以下是优化后的SQL，确保每个任务仅返回一行：

SELECT 
    t.task_id,
    ts.slot,
    t.task_given_by,
    t.task_slot_id,
    t.task_title,
    t.task_detail,
    0 AS task_status,
    t.created,
    NULL AS task_completed_on,
    t.task_type,
    t.deadline,
    t.task_given_to,
    CONCAT(u1.first_name, ' ', u1.last_name) AS task_given_by_name,
    CONCAT(u2.first_name, ' ', u2.last_name) AS task_given_to_name
FROM task t
LEFT JOIN task_slot ts ON ts.task_slot_id = t.task_slot_id
LEFT JOIN users u1 ON u1.user_id = t.task_given_by
LEFT JOIN users u2 ON u2.user_id = t.task_given_to
WHERE 
    t.task_given_to = 83 
    AND t.task_type = 1
    AND NOT EXISTS (
        SELECT 1 
        FROM task_status tst 
        WHERE tst.task_id = t.task_id 
        AND DATE(tst.task_completed_on) = '2023-08-19'
    );

核心说明 #

• 使用NOT EXISTS子查询直接过滤掉今日已完成的Slot任务，无需关联task_status表，彻底避免重复行。
• 若需动态获取当前日期，将'2023-08-19'替换为CURDATE()即可。

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