mysql 交换两条记录

-- 创建示例表
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

-- 插入示例数据
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO users (id, name) VALUES (2, 'Bob');

-- 交换记录
SET @temp_name = (SELECT name FROM users WHERE id = 1);
UPDATE users SET name = (SELECT name FROM users WHERE id = 2) WHERE id = 1;
UPDATE users SET name = @temp_name WHERE id = 2;

-- 验证结果
SELECT * FROM users;

START TRANSACTION;
SET @temp_name = (SELECT name FROM users WHERE id = 1 FOR UPDATE);
UPDATE users SET name = (SELECT name FROM users WHERE id = 2 FOR UPDATE) WHERE id = 1;
UPDATE users SET name = @temp_name WHERE id = 2;
COMMIT;

-- 使用临时变量
SET @temp_id = 1, @temp_name = (SELECT name FROM users WHERE id = 1);
UPDATE users SET id = 1, name = (SELECT name FROM users WHERE id = 2) WHERE id = 2;
UPDATE users SET id = 2, name = @temp_name WHERE id = @temp_id;