MySQL拖拽排序

-- ----------------------------
-- Table structure for TestSort
-- ----------------------------
DROP TABLE IF EXISTS `TestSort`;
CREATE TABLE `TestSort`  (
  `Id` int(0) NOT NULL AUTO_INCREMENT,
  `Name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '',
  `OrderNum` int(0) NOT NULL,
  PRIMARY KEY (`Id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of TestSort
-- ----------------------------
INSERT INTO `TestSort` VALUES (1, '名称1', 1);
INSERT INTO `TestSort` VALUES (2, '名称2', 2);
INSERT INTO `TestSort` VALUES (3, '名称3', 3);
INSERT INTO `TestSort` VALUES (4, '名称4', 4);
INSERT INTO `TestSort` VALUES (5, '名称5', 5);
INSERT INTO `TestSort` VALUES (6, '名称6', 6);
INSERT INTO `TestSort` VALUES (7, '名称7', 7);
INSERT INTO `TestSort` VALUES (8, '名称8', 8);
INSERT INTO `TestSort` VALUES (9, '名称9', 9);
INSERT INTO `TestSort` VALUES (10, '名称10', 10);
INSERT INTO `TestSort` VALUES (11, '名称11', 11);
INSERT INTO `TestSort` VALUES (12, '名称12', 12);
INSERT INTO `TestSort` VALUES (13, '名称13', 13);

实现拖拽排序

-- 从下往上拖
-- 把第10个拖到第1位，范围[1-9],逻辑+=1


 UPDATE
         TestSort t1,
         TestSort t2
        SET t1.OrderNum = t1.OrderNum + 1,
         t2.OrderNum = 1
        WHERE
        t2.OrderNum = 10
 
 AND t1.OrderNum BETWEEN 1
                AND 10-1;


select * from TestSort order by Ordernum asc;

-- 从上往下拖拽
-- 把第1个拖到第10个，范围[1-9],逻辑-=1

 UPDATE
         TestSort t1,
         TestSort t2
        SET t1.OrderNum = t1.OrderNum +(-1),
         t2.OrderNum =10
        WHERE
        t2.OrderNum = 1
 AND t1.OrderNum BETWEEN 1
                AND 10;




select * from TestSort order by Ordernum asc;

原文链接：https://blog.csdn.net/Mr_LiYyang/article/details/103226949