拖动排序的后端和数据库实现-黄运鑫的博客

需求
实现
- 传统排序字段实现
- 双向链表实现

需求

有一个文档列表，前端需要拖动排序到某一个文档之前或之后
测试数据库使用MySQL 5.7，建表语句如下：

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(32) DEFAULT NULL COMMENT '名称',
    `sort` INT(1) DEFAULT NULL COMMENT '排序',
    `prev_id` BIGINT(20) DEFAULT NULL COMMENT '上一个id',
    `next_id` BIGINT(20) DEFAULT NULL COMMENT '下一个id',
    PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user` (`id`, `name`, `sort`, `prev_id`, `next_id`) VALUES('1','张三','1',NULL,'2');
INSERT INTO `user` (`id`, `name`, `sort`, `prev_id`, `next_id`) VALUES('2','李四','2','1','3');
INSERT INTO `user` (`id`, `name`, `sort`, `prev_id`, `next_id`) VALUES('3','王五','3','2','4');
INSERT INTO `user` (`id`, `name`, `sort`, `prev_id`, `next_id`) VALUES('4','赵六','4','3','5');
INSERT INTO `user` (`id`, `name`, `sort`, `prev_id`, `next_id`) VALUES('5','孙七','5','4','6');
INSERT INTO `user` (`id`, `name`, `sort`, `prev_id`, `next_id`) VALUES('6','周八','6','5',NULL);

实现

实现方式有两种：
1.使用传统排序方式，用一个数字类型字段sort实现排序
- 优点：查询方便，支持分页查询
- 缺点：数据量越大，则每次更新的数据量也越大，性能低
2.使用双向链表实现，需要两个字段prev_id、next_id分别记录前一个和后一个数据的id
- 优点：执行效率高，无论数据量多大，每次移动最多更新5条数据（数据原来的前一条和后一条数据、数据插入位置的前一条和后一条数据、数据本身）
- 缺点：1.分页查询困难，此方式适用于每次全量查询后在代码中排序；2.容错率低，比如数据删除或恢复时，必须保证前后指针的连贯，否则排序失效
需要注意的是，两种方式都需要加锁保证事务一致性，防止脏读产生错误数据

传统排序字段实现

使用字段sort排序，查询时执行SELECT * FROM user ORDER BY sort ASC来实现排序
移动某条数据时，比如将孙七移动到王五之前，需要操作如下：
- 1.将孙七排序设置为等于王五的排序
- 2.将除了孙七的，大于或等于王五排序的所有数据，排序值+1
执行的语句如下：

#将孙七排序设置为等于王五的排序
UPDATE user SET sort = 3 WHERE name = '孙七'

#将除了孙七的，大于或等于王五排序的所有数据，排序值+1
UPDATE user SET sort = sort + 1 WHERE sort >= 3 AND name != '孙七'

双向链表实现

使用字段prev_id和next_id实现双向链表排序，查询时需要查询出全部数据，在业务代码中对数据排序
移动某条数据时，比如将孙七移动到王五之前，需要操作如下：
- 1.将孙七的上一个数据赵六的next_id改为孙七的next_id
- 2.将孙七的下一个数据周八的prev_id改为孙七的prev_id
- 3.将王五上一个数据李四的next_id改为孙七的id
- 4.将王五的prev_id改为孙七的id
- 5.将孙七的prev_id改为李四的id，next_id改为王五的id
其中1、2步是对数据移出的处理，3、4、5是对数据插入的处理，保证链表前后指针的连贯，执行的语句如下：

#将孙七的上一个数据赵六的next_id改为孙七的next_id
UPDATE user SET next_id = 6 WHERE id = 4;

#将孙七的下一个数据周八的prev_id改为孙七的prev_id
UPDATE user SET prev_id = 4 WHERE id = 6;

#将王五上一个数据李四的next_id改为孙七的id
UPDATE user SET next_id = 5 WHERE id = 2;

#将王五的prev_id改为孙七的id
UPDATE user SET prev_id = 5 WHERE id = 3;

#将孙七的prev_id改为李四的id，next_id改为王五的id
UPDATE user SET prev_id = 2,next_id = 3 WHERE id = 5;

需要注意的是，第一条数据的prev_id和最后一条数据的next_id为NULL，排序时需要额外的判断处理

做你害怕做的事情，然后你会发现，不过如此。

目录

需求

实现

传统排序字段实现

双向链表实现