MySQL phpMyAdmin交换三列报错求助：Unknown column 'temp'

Hey there! Let's break down why you're seeing that "Unknown column 'temp' in 'field list'" error and fix your stored procedure to swap those three columns in the idsaccess table.

The Root of the Problem #

When you tried using a declared local variable (like DECLARE temp ...) inside your UPDATE statement, MySQL prioritizes looking for a column named temp in your idsaccess table instead of recognizing it as a variable. Even switching to a session variable @temp might have failed if your syntax wasn't aligned with how MySQL parses variables in UPDATE clauses.

The Fix: Swap Columns with Session Variables #

For swapping values across all rows in three columns (let's assume you want to cycle values: referer ← size_var, size_var ← agent, agent ← original referer), here's a working stored procedure using session variables (marked with @ to avoid column name confusion):

DELIMITER //
CREATE PROCEDURE px()
BEGIN
    -- Cycle values between referer, size_var, and agent for all rows
    UPDATE idsaccess
    SET
        @temp = referer,       -- Store original referer value in session variable
        referer = size_var,    -- Set referer to size_var's current value
        size_var = agent,      -- Set size_var to agent's current value
        agent = @temp;         -- Set agent to the original referer value
END //
DELIMITER ;

Why This Works #

• The @temp variable is explicitly marked as a session variable, so MySQL won't confuse it with a table column.
• MySQL executes the SET clauses in order, so @temp captures the original referer value before it gets overwritten, allowing us to safely assign it to agent at the end.

If You Need to Swap Specific Rows #

If you only want to swap values for a single row (e.g., filtered by a primary key), use local variables to first fetch the row's values, then update:

DELIMITER //
CREATE PROCEDURE px_specific_row(p_id INT)
BEGIN
    DECLARE v_referer VARCHAR(255);  -- Match your actual column type
    DECLARE v_size_var INT;          -- Adjust type to match size_var
    DECLARE v_agent VARCHAR(255);    -- Match agent's column type

    -- Fetch current values from the target row
    SELECT referer, size_var, agent 
    INTO v_referer, v_size_var, v_agent 
    FROM idsaccess 
    WHERE id = p_id;  -- Replace `id` with your primary key column

    -- Update the row with swapped values
    UPDATE idsaccess
    SET 
        referer = v_size_var,
        size_var = v_agent,
        agent = v_referer
    WHERE id = p_id;
END //
DELIMITER ;

Key Takeaways #

• Always use @ prefixes for session variables in UPDATE clauses to avoid column name conflicts.
• For bulk updates, leveraging the order of SET clauses with a session variable is the simplest approach.
• For targeted row updates, fetch values to local variables first to preserve the original data before overwriting.

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