MySQL创建存储过程遇Error Code:1525及语法错误求助

初始错误原因（Error Code: 1525） #

你的存储过程中，对DATE类型参数P_event_start_date和P_event_end_date做了<> ''的比较，这是错误的：

• MySQL中DATE类型与空字符串''进行比较时，会强制将空字符串转换为DATE类型，触发Incorrect DATE value: ''错误，无论你传入的是NULL还是合法日期，这个比较都会执行并报错。

更新后错误原因（Error Code: 1064） #

你移除了DATE参数与''的比较，但出现语法错误，核心问题是变量混淆：

• 你声明了局部变量mainQuery，但初始化的是会话变量@mainQuery，后续拼接时又使用了未赋值的局部变量mainQuery（默认值为NULL），导致最终拼接后的SQL语句为NULL，执行PREPARE stmt FROM @mainQuery时就会报语法错误。

修复后的存储过程 #

下面是修正后的版本，同时解决了SQL注入风险（使用参数绑定替代字符串拼接）：

CREATE DEFINER=`root`@`%` PROCEDURE `SearchEvents`(
    IN P_team_id INT, 
    IN P_event_name VARCHAR(255), 
    IN P_event_start_date DATE, 
    IN P_event_end_date DATE
)
BEGIN
    -- 使用会话变量存储动态SQL，避免局部变量混淆
    SET @mainQuery = 'SELECT * FROM events WHERE 1=1';
    SET @team_id = P_team_id;
    SET @event_name = P_event_name;
    SET @start_date = P_event_start_date;
    SET @end_date = P_event_end_date;

    -- 拼接条件时使用参数占位符，避免SQL注入
    IF P_team_id IS NOT NULL THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND team_id = ?');
    END IF;

    IF P_event_name IS NOT NULL AND P_event_name <> '' THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND (event_name_en LIKE CONCAT(''%'', ?, ''%'') OR event_name_ar LIKE CONCAT(''%'', ?, ''%''))');
    END IF;

    IF P_event_start_date IS NOT NULL THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND event_start_date >= ?');
    END IF;

    IF P_event_end_date IS NOT NULL THEN
        SET @mainQuery = CONCAT(@mainQuery, ' AND event_end_date <= ?');
    END IF;

    -- 按顺序绑定参数，匹配动态SQL中的占位符数量
    PREPARE stmt FROM @mainQuery;
    CASE
        WHEN P_team_id IS NOT NULL AND P_event_name IS NOT NULL AND P_event_name <> '' AND P_event_start_date IS NOT NULL AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @team_id, @event_name, @event_name, @start_date, @end_date;
        WHEN P_team_id IS NOT NULL AND P_event_name IS NOT NULL AND P_event_name <> '' AND P_event_start_date IS NOT NULL THEN
            EXECUTE stmt USING @team_id, @event_name, @event_name, @start_date;
        WHEN P_team_id IS NOT NULL AND P_event_name IS NOT NULL AND P_event_name <> '' AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @team_id, @event_name, @event_name, @end_date;
        WHEN P_team_id IS NOT NULL AND P_event_name IS NOT NULL AND P_event_name <> '' THEN
            EXECUTE stmt USING @team_id, @event_name, @event_name;
        WHEN P_team_id IS NOT NULL AND P_event_start_date IS NOT NULL AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @team_id, @start_date, @end_date;
        WHEN P_team_id IS NOT NULL AND P_event_start_date IS NOT NULL THEN
            EXECUTE stmt USING @team_id, @start_date;
        WHEN P_team_id IS NOT NULL AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @team_id, @end_date;
        WHEN P_team_id IS NOT NULL THEN
            EXECUTE stmt USING @team_id;
        WHEN P_event_name IS NOT NULL AND P_event_name <> '' AND P_event_start_date IS NOT NULL AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @event_name, @event_name, @start_date, @end_date;
        WHEN P_event_name IS NOT NULL AND P_event_name <> '' AND P_event_start_date IS NOT NULL THEN
            EXECUTE stmt USING @event_name, @event_name, @start_date;
        WHEN P_event_name IS NOT NULL AND P_event_name <> '' AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @event_name, @event_name, @end_date;
        WHEN P_event_name IS NOT NULL AND P_event_name <> '' THEN
            EXECUTE stmt USING @event_name, @event_name;
        WHEN P_event_start_date IS NOT NULL AND P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @start_date, @end_date;
        WHEN P_event_start_date IS NOT NULL THEN
            EXECUTE stmt USING @start_date;
        WHEN P_event_end_date IS NOT NULL THEN
            EXECUTE stmt USING @end_date;
        ELSE
            EXECUTE stmt;
    END CASE;
    DEALLOCATE PREPARE stmt;

    -- 清理会话变量
    SET @mainQuery = NULL;
    SET @team_id = NULL;
    SET @event_name = NULL;
    SET @start_date = NULL;
    SET @end_date = NULL;
END

关键修复点 #

• 移除DATE类型参数与空字符串的比较，仅判断IS NOT NULL
• 统一使用会话变量@mainQuery，避免局部变量和会话变量混淆
• 使用参数占位符?替代直接字符串拼接，彻底避免SQL注入风险
• 通过CASE分支按条件绑定对应参数，确保动态SQL执行时参数数量与占位符匹配

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