Mysql: 对换（替换）两条记录的同一个字段值

需求：

前端进行展示数据的时候,经常有这么一个需求,就是将已经排序的列表进行调整顺序,比如
现在想把id为1的数据, 向下移一位,也就是和id为2的major_id字段进行对调

 

第一种方案：

1.第一个值  =  查询出来第一条

2.第二个值  =  查询出来第二条


3.更新  第一条记录

4.更新  第二条记录



对数据库操作了4次。这种方案很容易被上司骂个半死

第二种方案：

UPDATE student set major_id = (
  CASE
		WHEN id=1 THEN (SELECT major_id FROM student WHERE id=2)
		WHEN id=2 THEN (SELECT major_id FROM student WHERE id=1)
	END)
WHERE id IN (1,2)



这种mysql  是不支持的。先查在更新。无法同时进行。

第三种方案：

UPDATE student a
JOIN student b
ON	(a.id =1 AND b.id=2) OR (a.id=2 AND b.id=1)
SET
	a.major_id =b.major_id,b.major_id = a.major_id



同一个表用内连接拼接,把第二个表的major_id给第一个表,第一个表的major_id给第二个表,也避免了重新包装一层select…from 假表的步骤