MySQL 8.0游标分页（prev/next）存在null值时查询异常解决方案问询

方案一：SQL条件适配（无表结构改动，兼容性最优） #

核心逻辑是显式对齐MySQL默认null < 非null的排序规则，补充null值的比较判断，等值比较使用MySQL专属<=>运算符支持null值匹配。

修改后查询下一页SQL #

SELECT * FROM table
WHERE (date > cursor.date)
    OR (
        date = cursor.date 
        AND (
            (time > cursor.time) OR (time IS NOT NULL AND cursor.time IS NULL)
            OR (
                time <=> cursor.time 
                AND id > cursor.id
            )
        )
    )
ORDER BY date ASC, time ASC, id ASC
LIMIT 每页条数;

修改后查询上一页SQL #

SELECT * FROM table
WHERE (date < cursor.date)
    OR (
        date = cursor.date 
        AND (
            (time < cursor.time) OR (time IS NULL AND cursor.time IS NOT NULL)
            OR (
                time <=> cursor.time 
                AND id < cursor.id
            )
        )
    )
ORDER BY date DESC, time DESC, id DESC
LIMIT 每页条数;

逻辑验证 #

• 游标为[id = dd, date = 2017-11-03, time = 21:45:00]查询上一页时，d3行的time为null，满足time IS NULL AND cursor.time IS NOT NULL的判断，会正常返回
• 游标为[id = d3, date = 2017-11-03, time = null]查询上一页时，d3行本身的time与游标time相等，仅会进入id判断，不满足id < cursor.id，不会被返回

方案二：生成列转义null（可改表场景，SQL更简洁） #

如果允许修改表结构，可以新增排序专用的生成列，将null值转换为符合排序规则的默认值，后续排序和查询直接使用生成列即可，无需额外判断null：

新增生成列 #

ALTER TABLE `table` 
ADD COLUMN time_sort TIME GENERATED ALWAYS AS (COALESCE(`time`, '00:00:00')) STORED,
ADD INDEX idx_sort (date, time_sort, id);

查询下一页SQL #

SELECT * FROM table
WHERE (date > cursor.date)
    OR (date = cursor.date AND time_sort > cursor.time_sort)
    OR (date = cursor.date AND time_sort = cursor.time_sort AND id > cursor.id)
ORDER BY date ASC, time_sort ASC, id ASC
LIMIT 每页条数;

查询上一页SQL #

SELECT * FROM table
WHERE (date < cursor.date)
    OR (date = cursor.date AND time_sort < cursor.time_sort)
    OR (date = cursor.date AND time_sort = cursor.time_sort AND id < cursor.id)
ORDER BY date DESC, time_sort DESC, id DESC
LIMIT 每页条数;

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