MySQL 5.6环境运行LAG/LEAD窗口函数报错问题咨询

问题原因 #

你遇到的报错本质是版本不兼容导致的：

• 你原来使用的MariaDB 10.3已经原生支持窗口函数（LAG/LEAD）和WINDOW子句，所以SQL可以正常运行
• GoDaddy使用的MySQL 5.6是老旧版本，完全不支持窗口函数和WINDOW语法，所以解析阶段直接抛出大量未知关键字错误

可行解决方案 #

方案1：升级MySQL版本（优先推荐） #

如果可以调整主机环境，将MySQL版本升级到8.0及以上，原有SQL不需要做任何修改即可直接运行，是成本最低的解决方案。如果使用的是GoDaddy共享主机无法自主升级，可以尝试联系平台客服咨询是否可以切换到支持更高MySQL版本的主机套餐。

方案2：用兼容MySQL 5.6的语法改写SQL #

可以通过用户自定义变量+子查询的方式模拟LAG/LEAD的效果，和你原有逻辑完全等价的改写后的SQL如下：

SELECT 
  t.id,
  t.lag,
  t.lead
FROM (
  SELECT 
    c.id,
    @prev AS lag,
    @prev := c.id AS tmp,
    (SELECT MIN(id) FROM Clients WHERE id > c.id AND custno IS NOT NULL) AS lead
  FROM Clients c, (SELECT @prev := NULL) init
  WHERE c.custno IS NOT NULL
  ORDER BY c.id
) t

上述写法的逻辑和你原SQL完全一致：过滤custno不为空的行，按id升序排序，取上一行的id作为lag值，下一行的id作为lead值。

方案3：应用层计算相邻值 #

如果你的查询数据量不大，可以先把所有符合custno IS NOT NULL条件、按id排序的行查出来，在业务代码里遍历数组，手动给每一行赋值上一行/下一行的id值，这种方案完全不依赖数据库版本特性，适配性最高。

内容的提问来源于stack exchange，提问作者jcoulter105