mysql - 如何根据具有特定值的字段逐行递增 id

Question

我正在导入数据，其中需要为行组提供 ID，但传入数据中的行没有任何独特和共同之处。组的第一行有一个已知的指示符，并且数据是有序的，因此我们可以逐行设置 id，然后在找到该指示符时递增该 id。我已经这样做了，但是速度非常慢，所以有没有更好的方法在 mysql 中执行此操作，或者我最好最好逐行预处理文本数据以添加 id。

传入数据的示例，每当我们看到“NEW”时，我就需要增加一个 id

id,linetype,number,text
1,NEW,1234,sometext
2,CONTINUE,2412,anytext
3,CONTINUE,1,hello
4,NEW,2333,bla bla
5,CONTINUE,333,hello
6,NEW,1234,anything

所以我最终会得到

id,linetype,number,text,group_id
1,NEW,1234,sometext,1
2,CONTINUE,2412,anytext,1
3,CONTINUE,1,hello,1
4,NEW,2333,bla bla,2
5,CONTINUE,333,hello,2
6,NEW,1234,anything,3

我尝试过一个存储过程，我可以在其中逐行更新，但速度非常慢。

select count(*) from mytable into n;
set i=1;
while i<=n do
  select linetype into l_linetype from mytable where id = i;
  if l_linetype = "NEW" then
    set l_id = l_id + 1;
  end if;
  update mytable set group_id = l_id where id = i;
end while;

没有错误，这只是我可以逐行读取和写入文本文件并在一秒钟内完成的事情，而在 mysql 中需要 100 秒，如果 mysql 中有一种方法可以做到这一点，那就太好了相当快，因此不需要单独的预处理。

Answer 1

如果没有 MySQL 8+(窗口函数不可用)，您可以使用相关子查询来代替:

编辑:正如@Paul在评论中指出的，

SELECT t1.*, 
       (SELECT COUNT(*) 
        FROM your_table t2
        WHERE t2.id <= t1.id 
          AND t2.linetype = 'NEW'
       ) group_id
FROM your_table t1

如果我们定义以下复合索引(linetype, id)，上面的查询可以性能更高。列的顺序很重要，因为我们在 id 上有一个 Range 条件。

以前:

SELECT t1.*, 
       (SELECT SUM(t2.linetype = 'NEW') 
        FROM your_table t2
        WHERE t2.id <= t1.id
       ) group_id
FROM your_table t1

以上查询需要在 id 上建立索引。

<小时/>

使用用户定义变量( session 变量)的另一种方法是:

SELECT 
  t1.*, 
  @g := IF(t1.linetype = 'NEW', @g + 1, @g) AS group_id
FROM your_table t1 
CROSS JOIN (SELECT @g := 0) vars 
ORDER BY t1.id

这就像一种循环技术，我们使用 Session Variables在 SELECT 期间计算下一行时可以访问其先前的值。因此，我们将变量@g初始化为0，然后逐行计算。如果我们遇到带有 NEW 线型的行，我们将其递增，否则使用前一行的值。您还可以查看https://stackoverflow.com/a/53465139/2469308使用此方法时需要进行更多讨论和注意事项。