MySQL:表中的批量更新

Question

我使用的是 MySQL 5.6，但遇到了这个问题。我正在尝试针对这种情况改进批量更新策略。

我有一个名为reserved_ids的表，由外部公司提供，用于为其发票分配唯一的 ID。没有其他方法可以做到这一点；我无法使用 auto_increment 字段或模拟序列。

我有这个 PL 伪代码来进行此分配:

    START TRANSACTION;

OPEN invoice_cursor;

read_loop: LOOP
    FETCH invoice_cursor INTO internalID;
    IF done THEN
        LEAVE read_loop;
    END IF;


    SELECT MIN(SECUENCIAL)
    INTO v_secuencial
    FROM RESERVED_IDS
    WHERE COUNTRY_CODE = p_country_id AND INVOICE_TYPE = p_invoice_type;

    DELETE FROM RESERVED_IDS WHERE SECUENCIAL = v_secuencial;

    UPDATE MY_INVOICE SET RESERVED_ID = v_secuencial WHERE INVOICE_ID = internalID;


END LOOP read_loop;

CLOSE invoice_cursor;

COMMIT;

因此，需要执行一个 - 删除 - 分配，然后执行下一个 - 删除 - 分配...等等。

这可行，但速度非常非常慢。我不知道是否有任何方法可以更快地完成这项任务。我正在寻找类似于 INSERT INTO SELECT... 的东西，但是使用 UPDATE 语句，直接分配 1000 或 2000 个 ID，而不是一一分配。

拜托，任何建议对我都非常有帮助。非常感谢。

编辑 1:我添加了 WHERE 子句详细信息，因为它是由用户 @vmachan 请求的。在 UPDATE...INVOICE 子句中，我不按其他条件进行筛选，因为我有要更新的直接发票 ID 和索引发票 ID。谢谢

Answer 1

终于，我有了这个解决方案。它比我最初的方法快得多。

更新查询是

set @a=0;
set @b=0;


UPDATE MY_INVOICE
INNER JOIN
(
    select 
        F.invoice_id,
        I.secuencial as RESERVED_ID, 
        CONCAT_WS(/* format your final invoice ID */) AS FINAL_MY_INVOICE_NUMBER
    FROM
    (
    select if(@a, @a:=@a+1, @a:=1) as current_row, internal_id
    from MY_INVOICE
    where reserved_id is null
    order by internal_id asc
    limit 2000
    ) F
    INNER JOIN
    (
    SELECT if(@b, @b:=@b+1, @b:=1) as current_row, secuencial
    from reserved_ids
    order by secuencial asc
    limit 2000
    ) I USING (CURRENT_ROW)
) TEMP MY_INVOICE.internal_id=TEMP.INTERNAL_ID
SET MY_INVOICE.RESERVED_ID = TEMP.RESERVED_ID, MY_INVOICE.FINAL_MY_INVOICE_NUMBER=TEMP.FINAL_MY_INVOICE_NUMBER

因此，通过自动生成且相关的连续编号 @a 和 @b，我们可以连接两个不同且不相关的表，例如 MY_INVOICE 和 RESERVED_ID。

如果您想检查此解决方案，请按照以下步骤执行此棘手的更新:

• 执行@a，然后以隔离方式执行第一个内部选择:select if(@a, @a:=@a+1, ...
• 执行@b，然后以隔离方式执行第二个内部选择:select if(@b, @b:=@b+1, ...
• 执行@a、@b以及构建TEMP辅助表的大选择:select F.invoice_id, ...
• 执行更新
• 最后，从 RESERVED_ID 表中删除分配的 ID。

分配时间大大减少。我最初的解决方案是一一解决；这样，您就可以在一次(好的，但有点棘手)更新中分配 2000(或更多)。

希望这有帮助。