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:
-
将所有更新参数(
citizen_id
和good_id
进行过滤,citizen_id
的新值以及要更新的行数)放入某个存储设备(专用表)中,或临时表.
Put all the parameters of the update (
citizen_id
andgood_id
to filter on, the new values ofcitizen_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屋!