MySQL存储过程首次运行正常，二次带不同参数运行失败

Hey there, let's break down this problem and get your scheduling procedure working reliably every time. That SQL Error [1054] you're seeing happens because MySQL can't parse a column name that includes spaces (like TM 3) when it's not properly escaped. Here's why it's happening and how to fix it:

Why This Happens #

Your error message mentions Unknown column 'd0294220.duties.TM 3' — notice how MySQL is interpreting TM 3 as two separate parts (d0294220.duties.TM plus a stray 3) instead of a single column name. This almost always means:

• Your stored procedure uses dynamic SQL to build queries, and it's not wrapping column names with spaces/special characters in backticks (`).
• Session variables (like @sql or column lists) from the first call aren't being reset, so the second call ends up with corrupted SQL syntax.

Step-by-Step Fixes #

1. Escape Column Names with Backticks #

Any column name containing spaces, special characters, or reserved words needs to be wrapped in backticks so MySQL recognizes it as a single identifier. If you're building a dynamic column list, make sure each column is escaped.

For example, if you're fetching column names from your view to build the query, modify that part to include backticks:

-- Generate a properly escaped column list
SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`')) INTO @escaped_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'd0294220' AND TABLE_NAME = 'duties';

2. Reset Session Variables Before Each Call #

MySQL session variables (those starting with @) persist between procedure calls. If you don't reset them, the second call might reuse leftover values from the first, leading to broken SQL. Add reset lines at the start of your procedure:

-- Reset variables to avoid carryover from previous calls
SET @sql = NULL;
SET @escaped_columns = NULL;

3. Full Corrected Procedure Example #

Here's how your procedure might look with these fixes applied (adjust the logic to match your actual view and parameters):

DELIMITER //
CREATE PROCEDURE fetch_schedule(IN target_member VARCHAR(50))
BEGIN
    -- Reset session variables first
    SET @sql = NULL;
    SET @escaped_columns = NULL;

    -- Get properly escaped column names from your duties view
    SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`')) INTO @escaped_columns
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'duties';

    -- Build dynamic SQL based on input parameter
    IF target_member IS NULL OR target_member = 'all' THEN
        SET @sql = CONCAT('SELECT ', @escaped_columns, ' FROM duties;');
    ELSE
        SET @sql = CONCAT('SELECT ', @escaped_columns, ' FROM duties WHERE member_name = ''', target_member, ''';');
    END IF;

    -- Execute the dynamic query
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

Quick Checks to Verify #

• Double-check your view duties — if it uses pivoting or dynamic column names (like TM 3), those columns must always be referenced with backticks.
• Test the procedure with the problematic parameter first after applying fixes to confirm the error is gone.

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