将文本从一个表插入另一个表

I'm pretty sure I did this right, but my query has been running for half an hour now on a MySQL database on a regular laptop. The "tweets" table is only 1 million records big.

我很确定我做对了,但我的查询现在已经在普通笔记本电脑上的MySQL数据库上运行了半个小时。 “推文”表只有100万条记录。

What do I want? Consider table "AAA" (left) and "BBB" (right)

我想要什么?考虑表“AAA”(左)和“BBB”(右)

id_str  text    id_str  text
------------    -------------------
13              13      foo bar baz
14              14      foobar
                13      foo bar baz
                17      foobaz

I want to fill the "text" column of table A with the text from table B:

我想用表B中的文本填充表A的“文本”列:

UPDATE AAA
SET `text` = (
    SELECT `text` AS `text`
    FROM BBB
    WHERE id_str = AAA.id_str
    LIMIT 1
)

So that table AAA would look like

所以表AAA看起来像

id_str  text
-------------------
13      foo bar baz
14      foobar

However, as said, this query is running far too long. Did I make a mistake in its syntax?

但是,如上所述,此查询运行时间太长。我的语法错了吗?

2 个解决方案

#1

If I understand your situation correctly, this should be much faster:

如果我理解你的情况,这应该快得多:

UPDATE `AAA`,`BBB` SET `AAA`.`text`=`BBB`.`text`
WHERE `AAA`.`id_str`=`BBB`.`id_str`

You should only have to run this query once. It may still take a few minutes, but doing it this way (with a JOIN) is still probably going to be a lot faster than running 1 million separate queries.

您只需运行此查询一次。它可能仍需要几分钟,但这样做(使用JOIN)仍然可能比运行100万个单独查询要快得多。

AAA.id_str and BBB.id_str should both be indexed and preferably of the same data type, e.g. both should be int(11). This allows MySQL to evaluate the equality relationship between them with maximum efficiency.

AAA.id_str和BBB.id_str都应该被索引并且最好具有相同的数据类型,例如两者都应该是int(11)。这允许MySQL以最高效率评估它们之间的相等关系。

EDIT: As I said, the query outlined above may still take some time, depending on your system configuration and your hardware (the operation could be disk-bound, i.e. limited by the speed of your hard disk). It could also be because MySQL is having to update indexes in table AAA - could you show us a CREATE TABLE statement for table AAA? Alternatively, you could try this:

编辑:正如我所说,上面概述的查询可能还需要一些时间,具体取决于您的系统配置和硬件(操作可能受磁盘限制,即受硬盘速度的限制)。这也可能是因为MySQL必须更新表AAA中的索引 - 你能告诉我们表AAA的CREATE TABLE语句吗?或者,你可以试试这个:

CREATE TABLE `CCC` LIKE `AAA`;
INSERT INTO `CCC` SELECT `AAA`.`str_id`,`BBB`.`text` FROM `AAA` JOIN `BBB` ON `AAA`.`id` = `BBB`.`id`;

This doesn't copy in any data from the old table AAA, though.

但是,这不会复制旧表AAA中的任何数据。

#2

UPDATE AAA, BBB
SET AAA.`text` = BBB.text
WHERE BBB.id_str = AAA.id_str

#1