需求说明

数据上一条下一条，是个老需求了 ，大多数是用在新闻类的功能上，一个数据列表按照一定条件然后按固定的一些字段排序，并且只给文章id参数，查过很多文章，多数都是用id进行排序的，不太符合我得需求，于是自己搞了几个小时，测试出来一个sql ，比较稳妥，扩展性强，理论可以随便写附加条件。

操作

• 表

图片总是上传失败，这里就上字段了

`id` bigint(32) unsigned NOT NULL AUTO_INCREMENT COMMENT ' 主键 ',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  `is_delete` int(1) NOT NULL DEFAULT '0' COMMENT '数据开启状态,',
  `type` varchar(64) NOT NULL DEFAULT '' COMMENT '类型new:新闻;certificate:消息;encyclopedia:百科;staggerer:事件;',
  `title` varchar(250) NOT NULL DEFAULT '' COMMENT '标题',
  `text_body` longtext NOT NULL COMMENT '正文',
  `remark` varchar(1023) NOT NULL DEFAULT '' COMMENT '摘要',
  `release_time` datetime DEFAULT NULL COMMENT '发布时间',
  `source` varchar(255) NOT NULL DEFAULT '' COMMENT '来源',
  `notice_img` varchar(500) NOT NULL DEFAULT '' COMMENT '缩略图'

我要根据类型查询条件并按发布时间倒序

– 上一条

select id, type, title, remark, release_time, source
from notice nt,
     (select if(@cur = 0 and id = 35467, @cur := id, 0)                tc,
             if(@cur <> 0 and @pre = 0, @pre := @tpre, 0)              tp,
             if(@pre <> 0 and @nex = 0 and id <> 35467, @nex := id, 0) tn,
             @tpre := id
      from notice,
           (select @cur := 0, @tpre := 0, @pre := 0, @nex := 0) zz
      where is_delete = 0
        and release_time <= curdate()
        and `type` = 'staggerer'
      order by release_time desc) d
where nt.id = d.tp
  and d.tp <> 0
  and d.tp <> 35467;

这里35467是具体数据的id，语句大概意思是:

• 1.先把数据列表的查询sql写出来按照条件和字段排好序，
• 2.然后通过mysql 添加数据编号语法来的灵感，定义了4个变量，4个变量分别是当前行@cur,上一条@pre,下一条@nex,以及全程记录上一条数据id的@tpre，这4个变量可以算出上一条、下一条、和当前数据的id。
• 3.最后再把结果与原表id关联即可，具体怎么就算出来了,各位需要自己好好研究这个sql，找表多查询几次就明白了。

另外 当数据是列表的头一条是，@pre算出来的结果是当前id,所以末尾要排除当前的id，这样，首条数据的上一条就是空的了。

– 下一条

select id, type, title, remark, release_time, source
from notice nt,
     (select if(@cur = 0 and id = 35467, @cur := id, 0)                tc,
             if(@cur <> 0 and @pre = 0, @pre := @tpre, 0)              tp,
             if(@pre <> 0 and @nex = 0 and id <> 35467, @nex := id, 0) tn,
             @tpre := id
      from notice,
           (select @cur := 0, @tpre := 0, @pre := 0, @nex := 0) zz
      where is_delete = 0
        and release_time <= curdate()
        and `type` = 'staggerer'
      order by release_time desc) d
where nt.id = d.tn
  and d.tn <> 0;

下一条同理，

但是当前数据是列表最后一条时，@nex是没有机会得到值的，所以不用单独加排除当前条的条件

理论上这两条sql 适合mysql 所有上一条下一条的场景，但是有个问题是这个sql在计算时按条件全表计算，就算已经提前计算出结果，也依然要把所有符合条件的结果全部计算才会到外层来关联，所以数据足够多得时候，可能会有性能问题。

就当前的需求来说，上一条下一条需要的字段 最主要的是id，title，release_time，这三个字段都很小，只要不查正文，应该都没什么大问题。这么写的主要原因是计算上一条下一条整个逻辑我只能拿到当前数据id一个参数，否则能拿到分页参数的话，性能应该更好。

另外这是基于mysql的写法，这里也提供了一个思路，其它的数据库若是能定义出这样的动态参数，应该也是可以这样写的。

如果有大佬能提供优化，请评论留言。如果方法有错，也可以留言 ，我及时改正。

2023-08-17可行的优化方案==============

通过数据自增id优化，连不连续无所谓，但一定是有id。

• 1：先用上一篇sql 或者下一篇sql 查出一部分数据

• 2：评估两条之间相差的值，比如2条数据间的id基本上相差200-300.好，那么我们我们的大概范围就出来了，就是说我们推荐新闻数据的id,上一条下一条的浮动范围就是id±300

• 3：在sql中加上id的取值范围条件，比如浮动是范围是±300，那么我们±500，那推荐95%是不会错的。

例:

select id, type, title, remark, release_time, source
from notice nt,
     (select if(@cur = 0 and id = 35467, @cur := id, 0)                tc,
             if(@cur <> 0 and @pre = 0, @pre := @tpre, 0)              tp,
             if(@pre <> 0 and @nex = 0 and id <> 35467, @nex := id, 0) tn,
             @tpre := id
      from notice,
           (select @cur := 0, @tpre := 0, @pre := 0, @nex := 0) zz
      where is_delete = 0 and id>35467-500 and id<35467+500
        and release_time <= curdate()
        and `type` = 'staggerer'
      order by release_time desc) d
where nt.id = d.tp
  and d.tp <> 0
  and d.tp <> 35467;

因为id是有索引的，速度极快，我们在内部数据集里限制了范围大小，就避免了全表数据都编号后才使用，大大缩小了数据量，查询时间至少减1/3。

下一条也是一样的逻辑，当然范围500可以在根据情况加大，就算50000的差值也可以，只要能缩小范围就行