使用另一个 MySQL 表的值更新一个 MySQL 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5727827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update one MySQL table with values from another
提问by Superangel
I'm trying to update one MySQL table based on information from another.
我正在尝试根据另一个 MySQL 表的信息更新一个 MySQL 表。
My originaltable looks like:
我的original桌子看起来像：
id | value
------------
1 | hello
2 | fortune
3 | my
4 | old
5 | friend
And the tobeupdatedtable looks like:
而tobeupdated表如下所示：
uniqueid | id | value
---------------------
1 | | something
2 | | anything
3 | | old
4 | | friend
5 | | fortune
I want to update idin tobeupdatedwith the idfrom originalbased on value(strings stored in VARCHAR(32)field).
我想更新id在tobeupdated与id来自original基于value（存储在字符串VARCHAR(32)字段）。
The updated table will hopefully look like:
更新后的表格有望如下所示：
uniqueid | id | value
---------------------
1 | | something
2 | | anything
3 | 4 | old
4 | 5 | friend
5 | 2 | fortune
I have a query that works, but it's very slow:
我有一个有效的查询，但速度很慢：
UPDATE tobeupdated, original
SET tobeupdated.id = original.id
WHERE tobeupdated.value = original.value
This maxes out my CPU and eventually leads to a timeout with only a fraction of the updates performed (there are several thousand values to match). I know matching by valuewill be slow, but this is the only data I have to match them together.
这会最大化我的 CPU 并最终导致只执行一小部分更新的超时（有几千个值要匹配）。我知道匹配value会很慢，但这是我必须将它们匹配在一起的唯一数据。
Is there a better way to update values like this? I could create a third table for the merged results, if that would be faster?
有没有更好的方法来更新这样的值？我可以为合并的结果创建第三个表，如果这样会更快？
I tried MySQL - How can I update a table with values from another table?, but it didn't really help. Any ideas?
我尝试过MySQL - 如何使用另一个表中的值更新表？，但这并没有真正帮助。有任何想法吗？
Thanks in advance for helping a MySQL novice!
在此先感谢您帮助 MySQL 新手！
回答by wired00
UPDATE tobeupdated
INNER JOIN original ON (tobeupdated.value = original.value)
SET tobeupdated.id = original.id
That should do it, and really its doing exactly what yours is. However, I prefer 'JOIN' syntax for joins rather than multiple 'WHERE' conditions, I think its easier to read
那应该做到这一点，而且实际上它所做的正是您所做的。但是，我更喜欢连接的“JOIN”语法而不是多个“WHERE”条件，我认为它更容易阅读
As for running slow, how large are the tables? You should have indexes on tobeupdated.valueand original.value
至于跑慢，表有多大？你应该有指标tobeupdated.value和original.value
EDIT: we can also simplify the query
编辑：我们还可以简化查询
UPDATE tobeupdated
INNER JOIN original USING (value)
SET tobeupdated.id = original.id
USINGis shorthand when both tables of a join have an identical named keysuch as id. ie an equi-join - http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join
USING当连接的两个表具有相同的名称（key例如id. 即平等加入 - http://en.wikipedia.org/wiki/Join_(SQL)#Equi-join
回答by firegnom
It depends what is a use of those tables, but you might consider putting trigger on original table on insert and update. When insert or update is done, update the second table based on only one item from the original table. It will be quicker.
这取决于这些表的用途，但您可能会考虑在插入和更新时将触发器放在原始表上。插入或更新完成后，仅根据原始表中的一项更新第二个表。它会更快。
