如何交换mysql两条记录

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

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

-- 使用临时变量交换记录
SET @temp_id = (SELECT id FROM users WHERE id = 1);
SET @temp_name = (SELECT name FROM users WHERE id = 1);

UPDATE users SET id = 2, name = (SELECT name FROM users WHERE id = 2) WHERE id = 1;
UPDATE users SET id = @temp_id, name = @temp_name WHERE id = 2;

-- 验证结果
SELECT * FROM users;

-- 使用JOIN交换记录
UPDATE users u1
JOIN users u2 ON u1.id = 2 AND u2.id = 1
SET u1.id = u2.id, u2.id = u1.id, u1.name = u2.name, u2.name = u1.name;

-- 验证结果
SELECT * FROM users;

-- 使用CASE WHEN交换记录
UPDATE users
SET id = CASE id WHEN 1 THEN 2 WHEN 2 THEN 1 END,
    name = CASE id WHEN 1 THEN (SELECT name FROM users WHERE id = 2) WHEN 2 THEN (SELECT name FROM users WHERE id = 1) END;

-- 验证结果
SELECT * FROM users;

START TRANSACTION;

-- 执行交换操作
UPDATE users u1
JOIN users u2 ON u1.id = 2 AND u2.id = 1
SET u1.id = u2.id, u2.id = u1.id, u1.name = u2.name, u2.name = u1.name;

COMMIT;