mysql让自增字段重新排序(上移、下移、置顶时遇到问题解决方案)

1、背景说明：

    在做商城项目分类的时候，我们经常会遇到关于分类上移、下移、置顶和置底操作，至于上移和下移操作相对来说比较简单，上移将当前数据排序字段的值和上一调数据的排序字段值进行调换，下移也是同理，这里就不细说。至于置顶和置底要看是用的降序还是升序。我这里是用的数据库自增。

2、建表语句：

下面是我的分类建表语句的一部分，只抽取了适合本次要说明问题的一部分。

CREATE TABLE `t_category` (
  `id` varchar(64) NOT NULL COMMENT '类目ID',
  `cat_grade` varchar(20) DEFAULT NULL COMMENT '分类级别（一级	one 二级two 三级 three）',
  `name` varchar(50) DEFAULT NULL COMMENT '类目名称',
  `sort` int(20) NOT NULL AUTO_INCREMENT COMMENT '排序',
  `deleted` varchar(10) DEFAULT 'no' COMMENT '逻辑删',
  `describe` varchar(255) DEFAULT NULL COMMENT '描述',
  PRIMARY KEY (`id`),
  KEY `sort` (`sort`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=utf8mb4 COMMENT='分类级别';

接下来需要往表中填充数据，我这里就不提供。我的表主键id是java生成，所以另外使用了一个排序字段，采用数据库的自增。上移下移放到后面，先从置顶和置底开始说，我这种置底也比较方便直接将当前数据逻辑删重新插入数据库，当前数据自然就置底了。但是置顶却不一样，我参考了网上好多种做法，有设置固定值为置顶的，我觉得这种只能解决一次置顶，如果多次置顶还是会有问题，另一种是加一个更新时间字段，这种比上一种要好一点，但还是不能满足要求。后来我在评论中看到有一种是用负数的，我觉得比较符合我的需求，即获取排序字段最小值然后减1替换当前的值。

3、问题：

这种请款会让表中出现负数，虽然支持（数据库中一般的数字类型都可以存储负数，如int，numeric，decimal等，），但不好看，所以我想把这些数据重新排序。

4、自增字段重新排序：

这里的排序需要考虑之前的顺序，所以不能随意排序，需要根据原有顺序排序，我的方法是新增一个字段，字段名随意，但不能和原有字段冲突，然后按之前的顺序在这个字段中排序，删除原有排序字段，将新增字段改为之前的排序字段名，并设置成自增。

• 新增补助字段

ALTER TABLE `t_category`
ADD COLUMN `tid`  bigint(20) NULL AFTER `sort`;

• 将辅助字段tid按照原有sort的升序，从1自增

UPDATE t_category a,
(
SELECT
	@rownum := @rownum + 1 AS rownum,
	t_category.sort,
	t_category.id 
FROM
	( SELECT @rownum := 0 ) r,
	t_category 
ORDER BY
	sort 
	) b 
	SET a.tid = b.rownum 
WHERE
	a.id = b.id;

• 删除原有sort，将tid改为sort，设置为自增

ALTER TABLE `t_category` DROP COLUMN `sort`,
CHANGE COLUMN `tid` `sort` INT ( 20 ) NOT NULL AUTO_INCREMENT COMMENT '排序',
ADD KEY `sort` ( `sort` );

后记：

bigint
从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字）。存储大小为 8 个字节。
int
从 -2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字）。存储大小为 4 个字节。int 的 SQL-92 同义字为 integer。
smallint
从 -2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据。存储大小为 2 个字节。