在MySQL中使用相同选择查询的数据运行多个插入

I am migrating a database from one project to another and the tables don't have the same format, so I have to format the data properly.

我正在将数据库从一个项目迁移到另一个项目,并且表格格式不同,因此我必须正确格式化数据。

Here is a couple of queries example:

这里有几个查询示例:

INSERT INTO db1.first (a,b,c)
SELECT    x.a, z.b, x.c
FROM      db2.t2 x, db2.t3 z
WHERE     x.id = z.id

INSERT INTO db1.second (q,w)
SELECT    x.d, z.e
FROM      db2.t2 x, db2.t3 z
WHERE     x.id = z.id

What happens here is that I am using the same SELECT query (which in my actual case is much uglier than this example) in order to import some data. Only difference is that I don't use all the columns for every insert, but the rows fetched are exactly the same (the conditions applied are the same just like in the example).

这里发生的是我使用相同的SELECT查询(在我的实际情况下比这个例子更加丑陋)以便导入一些数据。唯一的区别是我没有为每个插入使用所有列,但是获取的行完全相同(应用的条件与示例中的相同)。

As you can clearly see, this has lead in a lot of copy-pasting and inconvenience, is there a way to run multiple inserts into different tables without copy-pasting my select query like that ?

正如您可以清楚地看到的,这导致了大量的复制粘贴和不便,有没有办法在不同的表中运行多个插入而不复制粘贴我的选择查询?

Thanks in advance !

提前致谢 !

1 个解决方案

#1

You could try creating a temporary table for that session:

您可以尝试为该会话创建临时表:

CREATE TEMPORARY TABLE IF NOT EXISTS temp AS
SELECT
    x.a, z.b, x.c
FROM
    db2.t2 x
INNER JOIN db2.t3 z
    ON x.id = z.id

And then use this table for both inserts:

然后将此表用于两个插入:

INSERT INTO db1.first (a,b,c)
SELECT a, b, c FROM temp

INSERT INTO db1.second (q,w)
SELECT a, b FROM temp

Note that I also replaced your implicit table join with an explicit one using INNER JOIN with an ON clause. This is the generally accepted way of doing joins currently.

请注意,我还使用INNER JOIN和ON子句将隐式表连接替换为显式连接。这是目前普遍接受的连接方式。

If you were using another database, such as SQL Server or Oracle, the best approach might be to use a common table expression (CTE) for the common query used for inserting. But since MySQL doesn't have support for CTE, using a temp table is an alternative.

如果您使用的是其他数据库,例如SQL Server或Oracle,最好的方法可能是使用公用表表达式(CTE)作为用于插入的公共查询。但由于MySQL不支持CTE,因此使用临时表是另一种选择。

Note that temporary tables in MySQL only exist for that session, and will be automatically deleted when the session ends. So there is no need to drop it explicitly, unless you want to remove it within the same session where you created it.

请注意,MySQL中的临时表仅存在于该会话中,并在会话结束时自动删除。因此,除非您想在创建它的同一会话中删除它,否则无需显式删除它。

#1