连表更新，实现对表中记录进行重新排序更新（对表进行排序更新）

目的

• 现有表A，A中的记录按sort排序为：ABCDE
• 现在将D插入到AB之间得到重新的排序为：ADBCE
• 一条语句实现以上排序更新操作，避免进行多次更新语句调用

初始情况

实现目标

要点

• 利用连表进行筛选出要更新的记录后再对表进行更新
• 连表筛选可以避免更新的时候导致其他
• 实现原理：将D的sort更新为B的，然后将B,C的sort+1

实现方法

1. 两表

• 利用更新过滤

UPDATE
	kanban as a
	RIGHT JOIN
	kanban as b
	ON
		a.id = 4
		AND
		b.id = 17
SET
	a.sort = b.sort,
	b.sort = b.sort + 1
WHERE
		a.id = 4
		OR
		b.sort BETWEEN 2 AND (4-1)
;

2. 三表

UPDATE
	kanban as a
	RIGHT JOIN
	(
		kanban as b
		JOIN
		kanban as c
		ON
			b.id = c.id 
			AND
			c.sort BETWEEN 2 AND 3
	)
	ON
		a.id = 4
		AND
		b.id = 17
SET
	a.sort = b.sort,
	b.sort = b.sort + 1
;

封装

• 使用存储过程封装

CREATE PROCEDURE table_sort_procedure(IN tableName VARCHAR(255),IN fromId INT,IN oldId INT)
BEGIN
	-- 定义两个sort值变量
	DECLARE from_sort_val INT DEFAULT 0;
	DECLARE old_sort_val INT  DEFAULT 0;
	-- 定义sort重新排序后的步长方向，from比old大的时候+1，from比old小的时候-1
	DECLARE step INT DEFAULT 1;
	-- 定义范围大小值
	DECLARE minVal INT DEFAULT 0;
	DECLARE maxVal INT DEFAULT 0;
	
	-- 定义预处理sql语句
	SET @findSortByIdSql = CONCAT('set @sort_val = (select sort from ',tableName,' where id = ? )');
	-- 生成预处理语句
	PREPARE findSortByIdPre FROM @findSortByIdSql;
	-- 先将form的sort查出
	SET @sortId = fromId;
	EXECUTE findSortByIdPre USING  @sortId;
	SET from_sort_val = @sort_val; -- 将查出的sort赋值给对应的变量
	-- 将old的sort查出
	SET @sortId = oldId;
	EXECUTE findSortByIdPre USING  @sortId;
	SET old_sort_val = @sort_val; -- 将查出的sort赋值给对应的变量
	-- 根据值大小来确定步长,以及大小值
	IF from_sort_val < old_sort_val THEN
		SET step = -1;
		SET minVal = from_sort_val + 1;
		SET maxVal = old_sort_val;
	ELSE
		SET minVal = old_sort_val;
		SET maxVal = from_sort_val - 1; -- 排除当前值
	END IF;
	
	-- 采用更新过滤方式实现重新排序
	-- 定义更新预处理sql语句
	SET @updateSortSql = CONCAT(' UPDATE ',
									tableName,' as a RIGHT JOIN ',
									tableName,' as b ON ',
									' a.id = ',fromId,' AND ',' b.id = ',oldId,
									' SET a.sort = b.sort,b.sort = b.sort + ',step,
									' WHERE a.id = ',fromId,
									' OR b.sort BETWEEN ',minVal,' AND ',maxVal										
									);
	PREPARE updateSortPre FROM @updateSortSql;
	EXECUTE updateSortPre;
	
	-- 注销预处理语句
	DEALLOCATE PREPARE findSortByIdPre;
	DEALLOCATE PREPARE updateSortPre;
end;

-- 用法
call table_sort_procedure('kanban',4,17);
-- 删除存储过程
DROP PROCEDURE table_sort_procedure;