php - MySql 函数和 Mysql 函数内的 session /用户变量

Question

我想创建一个 MySql 函数，只要给定的 id 相同，它就会返回增量行计数，如果 id 发生变化，函数将从 1 开始重置计数。

下面是我要找的结果，你可以看到只要itemId(左栏)保持不变，右栏的Count就会递增，当itemId改变时，Count就会从1重新开始。

在我看来，像下面这样的 MySql 函数会进行增量计数和重置，但不幸的是它为每一行返回 1。我的想法是将当前的 itemId 提供给该函数，该函数会将发送的 id 与保存在最后一行的 @n session 变量中的 id 进行比较，只要 id 相同，该函数就会返回递增的行数，否则它将重置为 1。谁能指导我为什么这个功能不起作用？或者是否有更好的方法来实现我正在寻找的结果？

CREATE FUNCTION `nth`(id int) RETURNS tinyint(4)
BEGIN
declare ln tinyint;
if @saved_id = id then
    set @n := @n+1;
    set ln = @n;
else
    set @saved_id := id;
    set @n := 1;
    set ln = @n;
end if;
RETURN ln;
END

The Mysql version I am using is 5.7

这是我正在使用的示例查询，itemId 是外键

select id, itemId, started_at 'Start', stopped_at Stop, nth(started_at) 'Count'
from events
order by itemId, stopped_at

Answer 1

您不需要为此定义 UDF。您可以在 SELECT 查询本身中实现这一点。在较新版本的 MySQL ( 8.0.2 and above ) 中，可以使用 ROW_NUMBER() OVER (PARTITION BY itemId ORDER BY id) 来实现

在旧版本中，我们可以使用用户定义的变量。在 Derived Table (FROM 子句中的子查询)，我们对数据进行排序，使所有具有相同 itemId 值的行聚集在一起，并根据 id< 在它们之间进一步排序.

现在，我们使用这个结果集并使用条件 CASE..WHEN 表达式来评估编号(“计数”)。它就像一种循环技术(我们在应用程序代码中使用它，例如:PHP)。我们会将前一行的值存储在用户定义的变量中，然后对照前一行检查当前行的值。最终，我们将相应地分配行号(“计数”)。

SELECT
  dt.id, 
  dt.Start,
  dt.Stop, 
  @rn := CASE WHEN dt.itemId = @itm THEN @rn + 1
              ELSE 1 
         END AS Count, 
  @itm := dt.itemId AS itemId 
FROM
(
  SELECT 
    id, 
    itemId, 
    started_at AS Start, 
    stopped_at AS Stop 
  FROM events
  ORDER BY itemID, id
) AS dt
CROSS JOIN (SELECT @itm := 0, @rn := 0) AS user_init_vars