空值的游标分页(prev / next)

Question

我有一个使用MySQL (version 8.0)实现的游标分页，只要不涉及null值，它就可以正常工作。

下面是我的示例数据(id是随机UUID,date是日期，time是时间):

id | date       | time
--------------------------
68 | 2017-10-28 | 22:00:00
d3 | 2017-11-03 | null
dd | 2017-11-03 | 21:45:00
62 | 2017-11-04 | 14:00:00
a1 | 2017-11-04 | 19:40:00

我使用的cursor总是由所有三列组成。

我使用这个查询来获取下一个结果(cursor之后):

SELECT * FROM table
WHERE (date > cursor.date)
OR (date = cursor.date AND time > cursor.time)
OR (date = cursor.date AND time = cursor.time AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC

prevresults (cursor之前):

SELECT * FROM table
WHERE (date < cursor.date)
OR (date = cursor.date AND time < cursor.time)
OR (date = cursor.date AND time = cursor.time AND id < cursor.id)
ORDER BY date DESC, time DESC, id DESC

使用prev时查询cursor [id = dd, date = 2017-11-03, time = 21:45:00]，它不会返回id = d3的行，因为time是null，这不会被time < cursor.time选中。

虽然我尝试使用time < cursor.time OR time IS NULL而不是time < cursor.time来包含null值的行。这似乎解决了这个问题，但又产生了一个新问题:当使用prev时使用cursor [id = d3, date = 2017-11-03, time = null]查询，因为现在结果包含所提供游标的行。

我希望有一个简单的解决办法。网络上似乎没有例子或教程来处理光标分页中的null值。

注意:对于解决方案，null将在non-null值之前或之后排序并不重要，只要它是一致的。(MySQL默认的顺序是null < non-null)

Answer 1

我来晚了一点，但请尝试以下内容。当游标行为时间列返回空值时，需要为可空列调整游标逻辑。

下面列出了两个例子:

declare @cursorID nvarchar(2)
declare @cursorDate date
declare @cursorTime time(0)

declare @table table(id nvarchar(2), date date, time time(0))
insert into @table
values
('68', '2017-10-28', '22:00:00'),
('d3', '2017-11-03', NULL),
('dd', '2017-11-03', '21:45:00'),
('62', '2017-11-04', '14:00:00'),
('a1', '2017-11-04', '19:40:00')
--IF SELECTING A ROW WITH A NON-NULL VALUE FOR TIME
set @cursorID = 'dd'
set @cursorDate = (select date from @table where id = @cursorID)
set @cursorTime = (select time from @table where id = @cursorID)
--ASCENDING CURSOR VALUES
select * from @table
where
date >= @cursorDate
and (date > @cursorDate or (time >= @cursorTime
and (time > @cursorTime or id > @cursorID)))
order by
date asc, time asc, id asc
--DESCENDING CURSOR VALUES
select * from @table
where
date <= @cursorDate
and (date < @cursorDate or ((time <= @cursorTime or time is null)
and ((time < @cursorTime or time is null) or id < @cursorID)))
order by
date desc, time desc, id desc

--IF SELECTING A ROW WITH A NON VALUE FOR TIME
set @cursorID = 'd3'
set @cursorDate = (select date from @table where id = @cursorID)
set @cursorTime = (select time from @table where id = @cursorID)
--ASCENDING CURSOR VALUES
select * from @table
where
date >= @cursorDate
and (date > @cursorDate or (time is not null
or (time is null and id > @cursorID)))
order by
date asc, time asc, id asc
--DESCENDING CURSOR VALUES
select * from @table
where
date <= @cursorDate
and (date < @cursorDate or (time is null
and (id < @cursorID)))
order by
date desc, time desc, id desc

Answer 2

向表中添加另一列。让它成为DATETIME。当date和time不为NULL时，将其合并;将date与NULL时的某个特定时间组合。然后游标有两列要处理，并且没有空。

如果你使用的是最新版本的MySQL，你可以使用"，从而避免任何代码更改。

一定要有INDEX(datetime, id).

Answer 3

我不打算讨论使用游标进行分页的主题。也有其他选择，如limit/offset。

但是我建议您的查询是使用coalesce()，为比较分配一个假时间。MySQL使这个过程变得简单，因为它支持超过24小时的time值。对于date/time组合，这些值是无效的。

:

SELECT *
FROM table
WHERE (date > cursor.date) OR
(date = cursor.date AND COALESCE(time, '24:00:00') > COALESCE(cursor.time, '24:00:00')) OR
(date = cursor.date AND COALESCE(time, '24:00:00') = COALESCE(cursor.time, '24:00:00') AND id > cursor.id)
ORDER BY date ASC, time ASC, id ASC

一个更简洁的WHERE子句是:

WHERE (date, COALESCE(time, '24:00:00'), id) > (cursor.date, COALESCE(cursor.time, '24:00:00'), cursor.id)

Answer 4

如果你使用的是MySQL 8.0，那么你可以考虑使用row_number()窗口函数为每一行创建一个唯一的顺序id (rn)。然后只需传递当前行的rn以获得前一行。

Schema和insert语句:

create table cursortable( id varchar(10), date date, time time);
insert into cursortable values('68' , '2017-10-28' , '22:00:00');
insert into cursortable values('d3' , '2017-11-03' ,  null);
insert into cursortable values('dd' , '2017-11-03' , '21:45:00');
insert into cursortable values('62' , '2017-11-04' , '14:00:00');
insert into cursortable values('a1' , '2017-11-04' , '19:40:00');

第一次查询结果:

select *,row_number()over(order by date,time,id)rn from cursortable

输出:

3

我的解决方案是将我想用作分页游标的可空列转换为具有默认值的NOT NULL列。

对于文本列，我将默认值设置为一个空字符串，使所有这些记录以升序排序的方式出现在顶部，对于日期时间，我将默认值设置为最古老的可能值:"1970-01-01 00:00:00"这使得它们出现在降序排序的末尾。

然后，当检查datetime "null "在你的应用中，在TS/JS中，它就像这样简单:

const isNullDate = someDate <= new Date(0)

如果您的系统依赖于这些空值，那么这将不是适合您的正确解决方案，但这是在我的情况下最简单/优雅/减少头痛的解决方案。