MySQL日期重叠检测函数执行触发2013连接中断错误求助

我尝试编写一个函数，用于检测LeaveStart-LeaveEnd和ProjectStart-ProjectEnd两个日期范围是否存在重叠。执行函数时，MySQL Workbench总是抛出Error Code:2013“查询期间与MySQL服务器失去连接”错误，我推测是无限循环导致的，求指正。

相关代码 #

表结构 #

CREATE TABLE TESTING(
LeaveStart DATE,
LeaveEnd DATE,
ProjectStart DATE,
ProjectEnd DATE,
Det BOOL,
ID INT);

测试数据插入 #

INSERT INTO TESTING VALUES('2018-01-01','2018-01-31','2018-01-10','2018-01-30',FALSE,1);
INSERT INTO TESTING VALUES('2018-01-01','2018-01-31','2018-01-10','2018-01-30',FALSE,2);

初始问题函数 #

DELIMITER //
CREATE FUNCTION SALARY_DEDUCTION (LeaveStart Date, LeaveEnd Date, ProjectStart DATE, ProjectEnd Date)
RETURNS BOOL NO SQL
DETERMINISTIC 
BEGIN
    DECLARE DET BOOL DEFAULT FALSE;
    DECLARE DT DATE DEFAULT LeaveStart;
    WHILE LeaveStart < LeaveEnd DO
    IF DT BETWEEN ProjectStart AND ProjectEnd THEN
    SET DET = TRUE;
    ELSE 
    SET DET = FALSE;
    SET DT = DATE_ADD(DT, INTERVAL 1 DAY);
    END IF;
    END WHILE;
    RETURN DET;
END ; //

DELIMITER ;

函数调用代码 #

UPDATE TESTING AS T
SET 
T.Det = SALARY_DEDUCTION(T.LeaveStart,T.LeaveEnd,T.ProjectStart,T.ProjectEnd)
WHERE 
T.ID=1;

错误原因解析 #

1. Error Code:2013 连接丢失：你的判断完全正确，问题出在无限循环。初始函数的WHILE循环条件是LeaveStart < LeaveEnd，但LeaveStart是函数的输入参数，循环内部从未修改过它。只要初始条件成立，循环就会永久执行，最终导致MySQL连接超时断开。此外，就算循环条件正确，找到重叠日期后仍会遍历所有剩余日期，完全是冗余操作。

2. Error Code:1175 安全更新错误：这是MySQL的SQL_SAFE_UPDATES安全机制触发的。当执行UPDATE或DELETE语句时，如果没有使用主键/唯一索引作为过滤条件（也未指定LIMIT），该机制会阻止操作，防止误操作修改或删除大量数据。执行SET SQL_SAFE_UPDATES=0可临时关闭限制，操作完成后建议执行SET SQL_SAFE_UPDATES=1重新开启。

修改后的优化函数 #

DELIMITER //
CREATE FUNCTION SALARY_DEDUCTION_3(LeaveStart Date, LeaveEnd Date, ProjectStart DATE, ProjectEnd Date)
RETURNS BOOL NO SQL
DETERMINISTIC 
BEGIN
    DECLARE DET BOOL DEFAULT FALSE;
    DECLARE CD DATE DEFAULT LeaveStart;
    myloop: WHILE CD < LeaveEnd DO
    IF CD BETWEEN ProjectStart AND ProjectEnd THEN
    SET DET = TRUE;
    LEAVE myloop; -- 找到重叠日期后立即退出循环，提升执行效率
    END IF;
    SET CD = DATE_ADD(CD, INTERVAL 1 DAY); -- 每次循环更新遍历的日期，确保循环能正常终止
    END WHILE myloop;
    RETURN DET;
END ; //

DELIMITER ;

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