MySQL 8.0.33
我有一个存储函数来计算给定日期一系列事件中所有时间间隔的总和。此函数使用窗口函数 LEAD(...) OVER() 来确定一个事件结束与下一个事件开始之间的间隔。
如果我多次运行查询本身,结果总是一样的。如果使用该函数,第一次执行有正确的值,其余返回NULL。
为什么???
编辑:
我已经更正了下面块中代码中的一些错误并制作了一个Fiddle。
现在我的问题变成了:“为什么它在 Fiddle 中可以正常工作,但在我的机器上却不能?什么设置会影响它?”
编辑 2:
在两个函数调用之间添加FLUSH TABLES events;
可以解决我服务器上的问题。再一次,为什么会这样?
架构:
CREATE TABLE `events` (
`id` int UNSIGNED NOT NULL AUTO_INCREMENT,
`start` datetime DEFAULT NULL,
`end` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `start` (`start`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `events` (`id`, `start`, `end`) VALUES
(1, '2023-02-06 08:14:00', '2023-02-06 10:30:00'),
(2, '2023-02-06 11:57:00', '2023-02-06 12:25:00'),
(3, '2023-02-06 14:00:00', '2023-02-06 14:15:00'),
(4, '2023-02-06 15:00:00', '2023-02-06 16:49:00');
CREATE FUNCTION fx_timegap (dt DATE)
RETURNS DECIMAL(4,2) NOT DETERMINISTIC READS SQL DATA
RETURN (
WITH
C1 AS (
SELECT
(UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
FROM events
WHERE DATE(start) = dt
)
SELECT SUM(gap)
FROM C1
);
这有效:
WITH
C1 AS (
SELECT
(UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
FROM events
WHERE DATE(start) = '2023-02-06'
)
SELECT SUM(gap)
FROM C1;
>>> 3.7833
WITH
C1 AS (
SELECT
(UNIX_TIMESTAMP(LEAD(start) OVER (ORDER BY start ASC)) - UNIX_TIMESTAMP(end)) / 3600 AS gap
FROM events
WHERE DATE(start) = '2023-02-06'
)
SELECT SUM(gap)
FROM C1;
>>> 3.7833
这不会:
SELECT fx_timegap('2023-02-06');
>>> 3.78
SELECT fx_timegap('2023-02-06');
>>> NULL
返回一个值,而不是
SELECT
.这个问题见: https://bugs.mysql.com/bug.php? id=110983 https://bugs.mysql.com/bug.php?id=110847
如果您有更多信息,请写信。