Tengo una paginación de cursor implementada con MySQL (versión 8.0), que funciona bien siempre que no haya valores null involucrados.
Aquí están mis datos de ejemplo (la id es un UUID aleatorio, la date es la fecha, la time es la hora):
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 El cursor que uso siempre consta de las tres columnas.
Uso esta consulta para obtener los siguientes resultados (después del 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 Y esta consulta de resultados anteriores (antes del 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 Al usar la consulta anterior con el cursor [id = dd, date = 2017-11-03, time = 21:45:00] no devolverá la fila con id = d3 , porque el time es null , y esto no ser seleccionado por time < cursor.time .
Aunque intenté usar time < cursor.time OR time IS NULL en lugar de time < cursor.time para incluir filas con valores null . Lo que parece solucionar este problema en particular, pero luego crea un nuevo problema: cuando se usa la consulta anterior con el cursor [id = d3, date = 2017-11-03, time = null] , porque ahora el resultado contiene la fila de la proporcionada cursor.
Espero que haya una solución fácil para esto. Parece que no hay ejemplos o tutoriales en la web que traten con valores null en la paginación del cursor.
Nota: Para la solución, no importa si el null se ordenará antes o después non-null , siempre que sea coherente. (El orden predeterminado de MySQL es null < non-null )
No voy a tocar el tema del uso de cursores para la paginación. Hay alternativas, como limit / offset .
Pero mi recomendación para sus consultas es usar coalesce() , asignando un tiempo falso para la comparación. MySQL hace que esto sea algo simple, porque admite valores de time superiores a 24 horas. Y esos no serían valores válidos para una combinación de date / time .
Entonces:
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 Una cláusula WHERE más concisa sería:
WHERE (date, COALESCE(time, '24:00:00'), id) > (cursor.date, COALESCE(cursor.time, '24:00:00'), cursor.id)Agregue otra columna a la tabla. Conviértalo en DATETIME . Combine la date y la time cuando no sea NULL; combine la date con una hora particular cuando NULL. Entonces su cursor tiene dos columnas para trabajar y no tiene valores nulos.
Si tiene una versión razonablemente reciente de MySQL, puede usar una columna "almacenada generada", evitando así cualquier cambio de código.
Y asegúrese de tener INDEX(datetime, id) .
Si está utilizando MySQL 8.0, puede considerar usar la función de ventana row_number () para crear una identificación secuencial única (rn) para cada fila. Luego, simplemente pase el rn para la fila actual para obtener las filas anteriores.
Esquema e insertar sentencias:
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');Consulta para obtener el resultado por primera vez:
select *,row_number()over(order by date,time,id)rn from cursortableProducción:
| identificación | fecha | hora | rn |
|---|---|---|---|
| 68 | 2017-10-28 | 22:00:00 | 1 |
| d3 | 2017-11-03 | nulo | 2 |
| dd | 2017-11-03 | 21:45:00 | 3 |
| 62 | 2017-11-04 | 14:00:00 | 4 |
| a1 | 2017-11-04 | 19:40:00 | 5 |
Consulta para obtener las filas anteriores para el cursor [id = dd, date = 2017-11-03, time = 21:45:00, rn=3] con solo el cursor [rn=3] :
with cte as ( select *,row_number()over(order by date,time,id)rn from cursortable ) select * from cte where rn<3Producción:
| identificación | fecha | hora | rn |
|---|---|---|---|
| 68 | 2017-10-28 | 22:00:00 | 1 |
| d3 | 2017-11-03 | nulo | 2 |
db<>violín aquí
Si no desea introducir una columna calculada en su código, pruebe la siguiente solución considerando el cursor [id = dd, date = 2017-11-03, time = 21:45:00]
Consulta:
with cte as ( select *,row_number()over(order by date,time,id)rn from cursortable ) ,cte2 as ( select * from cte where id='dd' and date= '2017-11-03' and time= '21:45:00' ) select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rnProducción:
| identificación | fecha | hora |
|---|---|---|
| 68 | 2017-10-28 | 22:00:00 |
| d3 | 2017-11-03 | nulo |
db<>violín aquí
Su código sería como a continuación:
with cte as ( select *,row_number()over(order by date,time,id)rn from cursortable ) ,cte2 as ( select * from cte where id=cursor.id and date= cursor.date and time= cursor.time ) select cte.id,cte.date,cte.time from cte inner join cte2 on cte.rn<cte2.rn