MySQL 导出查询作为插入语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3786820/
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
MySQL export query as insert statements
提问by RailsSon
I have two mysql tables which both have a typeID in common. I am wanting to select everything from these two tables with the same typeID, query below:
我有两个 mysql 表，它们都有一个共同的 typeID。我想从这两个表中选择具有相同 typeID 的所有内容，查询如下：
SELECT ta.requiredTypeID, ta.typeID, ta.quantity
FROM `invtypes` as t, `typeactivitymaterials` as ta
WHERE volume > 0 AND t.typeID = ta.typeID;
This gives me the correct results but I am trying to export this query as insert statements. I have tried adding INTO OUTFILE "/path/" but this only exports the data as tab/comma delimited data, is it possible to export this data as insert statements?
这给了我正确的结果，但我试图将此查询导出为插入语句。我曾尝试添加 INTO OUTFILE "/path/" 但这仅将数据导出为制表符/逗号分隔的数据，是否可以将此数据导出为插入语句？
Cheers
干杯
Eef
埃夫
采纳答案by Spudley
You can combine INSERT with SELECT to insert records directly from the result of a select statement.
您可以将 INSERT 与 SELECT 结合使用，以直接从 select 语句的结果中插入记录。
The way you do it would be something like this:
你这样做的方式是这样的：
INSERT INTO newtable (requiredTypeID, typeID, quantity)
SELECT ta.requiredTypeID, ta.typeID, ta.quantity
FROM `invtypes` as t, `typeactivitymaterials` as ta
WHERE volume > 0 AND t.typeID = ta.typeID;
Here's a link to the relevant MySQL manual page.
这是相关MySQL 手册页的链接。
Note that this would actually do the insert right away; it wouldn't do quite what you asked (which is to generate the insert statements for use later on).
请注意，这实际上会立即进行插入；它不会完全按照您的要求执行（即生成插入语句以供稍后使用）。
回答by a'r
You might be able to use mysqldumpinstead. Check out the parameters --tablesand --where=.
你也许可以用它mysqldump来代替。检查参数--tables和--where=。
回答by Yodacheese
If you have MySQL Workbench it can generate insert statements for you.
如果您有 MySQL Workbench，它可以为您生成插入语句。
In the SQL editor run your query, then from the result set you can click the export button. Then in the "Save as Type" select as "SQL INSERT statements (*.sql)". Click Save, confirm the table you are exporting and click ok.
在 SQL 编辑器中运行您的查询，然后您可以从结果集中单击导出按钮。然后在“Save as Type”中选择“SQL INSERT statements (*.sql)”。单击保存，确认您要导出的表，然后单击确定。
If you open the file you should see your result set as insert statements with the column names.
如果您打开该文件，您应该看到您的结果集是带有列名的插入语句。
I only tested this on a simple select * from table. I haven't tested this with multiple tables. I hope this helps.
我只在一个简单的 select * from table 上测试了这个。我还没有用多个表测试过这个。我希望这有帮助。
EDIT: Seems like workbench is available for Windows, OSX, and Linux (Thanks vcardillo)
编辑：似乎工作台可用于 Windows、OSX 和 Linux（感谢 vcardillo）
回答by Anil Bhaskar
Just leaving it here, if someone comes looking for the exact query for creating MySQLexportquery as insertstatements. This answer uses mysqldumpas @a'r suggestion.
如果有人来寻找将查询创建MySQLexport为insert语句的确切查询，只需将其留在这里。这个答案 mysqldump用作@a'r 建议。
mysqldump -u<user-name> -p<password> mydb t1 t2 t3 > mydb_tables.sql
回答by CristiC
You can make something like this:
你可以做这样的事情：
select
concat(
concat(
concat(
concat(
concat(
concat('insert into <table> (requiredTypeID, typeID, quantity) values ("'
,ta.requiredTypeID)
,'", "')
,ta.typeID)
,'", "')
,ta.quantity)
,'")')
FROM
`invtypes` as t, `typeactivitymaterials` as ta
WHERE
volume > 0 AND t.typeID = ta.typeID;
INTO OUTFILE 'file_name'
I know it is ugly but I think it solves your case :)
我知道这很丑，但我认为它可以解决您的问题:)
