mysql - 使用递增的文本值更新表中的所有行

Question

如何使用 SQL 使用递增的文本值更新表中每一行的列。

我有一个表，其中有一列称为 ej_number，它是一个唯一标识符。字段格式为EJnnnn，即EJ 后跟四位数字。我导入的数据不包含 ej_number 的值，但某些新行确实设置了该值。我想更新没有设置 ej_number 的每一行，从 EJ0001 开始。我稍后会解决重复问题。

我先是在 PHP 中循环执行，但意识到服务器会因为行数而超时，所以我决定在 SQL 中执行。

我的第一个想法是使用循环，但我的研究发现不推荐逐行更新，特别是因为我认为唯一的方法是使用游标，这也是不推荐的。

我能够在一条语句中完成 - 下面的代码有效，但它会生成警告(使用 MySQL Workbench)。

SET @next_number = 0;
UPDATE ej_details
    SET ej_number = CASE
        WHEN ej_number IS NULL THEN (
            CONCAT('EJ', LPAD((@next_number:=@next_number+1), 4, '0')))
        ELSE ej_number
    END;

语句做了我想要的，但生成了这个警告:

692 row(s) affected, 1 warning(s): 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Please set variables in separate statements instead. Rows matched: 692 Changed: 692 Warnings: 1

我想知道如何在不使用已弃用功能的情况下最好地做到这一点。我查看并找到了大量逐行解决方案，但看不到不是逐行解决方案的替代方案，可能是因为我知道的不够多，无法提出正确的问题。

Answer 1

我想你只是想:

SET @next_number = 0;

UPDATE ej_details
    SET ej_number = CONCAT('EJ',
                           LPAD( (@next_number:=@next_number+1), 4, '0'
                               )
                          )
    WHERE ej_number IS NULL;

这更简单，但不会改变错误。

如果您想在一次调用中执行此操作，则:

UPDATE ej_details CROSS JOIN
       (SELECT @next_number := 0) params
    SET ej_number = CONCAT('EJ',
                           LPAD( (@next_number:=@next_number+1), 4, '0'
                               )
                          )
    WHERE ej_number IS NULL;

不幸的是，如果该列被声明为unique，那么您将无法“稍后”解析重复值。

如果您想在不出现错误的情况下解决这个问题，您将需要一个主键/唯一列:

UPDATE ej_details ed JOIN
       (SELECT ed.*,
               ROW_NUMBER() OVER (ORDER BY ej_number) as seqnum
        FROM ej_details ed2
        WHERE ej_number IS NULL
       ) ed2
       ON ed2.? = ed.?  -- the primary key goes here
    SET ed.ej_number = CONCAT('EJ', LPAD(ed2.seqnum, 4, '0')
                             );

但是，此版本不向后兼容。

如果他们真的从 MySQL 9 中删除了变量，我会感到惊讶。它会破坏很多很多代码。