MySQL批处理查询有限制 [英] MySQL batch queries with limit

问题描述

我需要更改我的MySQL表中几个财产的所有权.让我烦恼的是，有几种相同的商品，我不想全部更改.

I need to change the ownership of several possessions in my MySQL table. The thing that's tripping me up is that there are several identical goods and I don't want to change all of them.

现在，我正在执行类似这样的查询:

Right now, I'm doing it with queries like this:

UPDATE possessions SET citizen_id=1 WHERE citizen_id=2 AND good_id=8 LIMIT 1
UPDATE possessions SET citizen_id=2 WHERE citizen_id=4 AND good_id=2 LIMIT 1
UPDATE possessions SET citizen_id=4 WHERE citizen_id=3 AND good_id=5 LIMIT 2

有时候，它们很多，它们是如此相似，以至于我觉得应该有一种方法可以批量提交它们.我可以发现显示批处理更新的示例不允许像我需要的那样对每个更新设置单独的限制.

There are, some times, a lot of these and they're all so similar that I feel like there should be a way to submit them in a batch. The examples I can find that show batch updates don't allow setting individual limits on each update like I need to.

您知道我可以以何种方式使此过程更快吗?

Do you know of any way I can make this process faster?

推荐答案

以下方法依赖于possessions表具有主键而citizen_id不属于主键这一事实.这是主意:

The following method relies on the fact that the possessions table has a primary key and citizen_id is not part of it. Here's the idea:

1. 将所有更新参数(citizen_id和good_id进行过滤，citizen_id的新值以及要更新的行数)放入某个存储设备(专用表)中，或临时表.

1. Put all the parameters of the update (citizen_id and good_id to filter on, the new values of citizen_id and the numbers of rows to update) into some storage, a dedicated table, perhaps, or a temporary table.

将行号分配给在(citizen_id, good_id)上进行分区的possessions行，然后将排名的行集连接到参数表以过滤citizen_id和good_id上的原始全集以及该行号行.

Assign row numbers to possessions rows partitioning on (citizen_id, good_id), then join the ranked row set to the parameter table to filter the original full set on citizen_id and good_id, as well as the number of rows.

加入possessions以及先前对主键值进行加入的结果，并用新值更新citizen_id.

Join possessions and the result of the previous join on the primary key values and update citizen_id with the new values.

在MySQL的SQL中，以上内容可能看起来像这样:

In MySQL's SQL, the above might look like this:

UPDATE possessions AS p
INNER JOIN
(
  SELECT
    @r := @r * (@c = p.citizen_id AND @g = p.good_id) + 1 AS r,
    p.possession_id,
    @c := p.citizen_id AS citizen_id,
    @g := p.good_id AS good_id
  FROM
    possessions AS p
  CROSS JOIN
    (SELECT @r := 0, @c := 0, @g := 0) AS x
  ORDER BY
    p.citizen_id,
    p.good_id
) AS f ON p.possession_id = f.possession_id
INNER JOIN
  possession_updates AS u ON u.citizen_id = f.citizen_id AND u.good_id = f.good_id
SET
  p.citizen_id = u.new_citizen_id
WHERE
  f.r <= u.row_count
;

possessions_update是包含参数值的表.

查询使用行号的已知方法，该方法使用变量，该方法在f子查询中实现.

The query uses a known method of row numbering that employs variables, which is implemented in the f subquery.

我没有MySQL，所以我不能从性能角度正确测试它，但是至少您可以从

I don't have MySQL so I can't test this properly from the performance point of view, but at least you can see from this SQL Fiddle demo that the method works. (The UPDATE statement is in the schema script, because SQL Fiddle doesn't allow data modification statements in the right-side script for MySQL. The right side just returns the post-UPDATE contents of possessions.)

这篇关于MySQL批处理查询有限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！