如何在 MySQL 中进行批量插入
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5526917/
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
How to do a batch insert in MySQL
提问by Genadinik
I have 1-many number of records that need to be entered into a table. What is the best way to do this in a query? Should I just make a loop and insert one record per iteration? Or is there a better way?
我有 1 条记录需要输入到表中。在查询中执行此操作的最佳方法是什么？我应该做一个循环并在每次迭代中插入一条记录吗？或者，还有更好的方法？
回答by nietaki
From the MySQL manual
来自MySQL 手册
INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:
使用 VALUES 语法的 INSERT 语句可以插入多行。为此，请包含多个列值列表，每个列表都用括号括起来并用逗号分隔。例子：
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
回答by Kamran
Most of the time, you are not working in a MySQL client and you should batch inserts together using the appropriate API.
大多数时候，您不是在 MySQL 客户端中工作，您应该使用适当的 API 一起批量插入。
E.g. in JDBC:
例如在 JDBC 中：
connection con.setAutoCommit(false);
PreparedStatement prepStmt = con.prepareStatement("UPDATE DEPT SET MGRNO=? WHERE DEPTNO=?");
prepStmt.setString(1,mgrnum1);
prepStmt.setString(2,deptnum1);
prepStmt.addBatch();
prepStmt.setString(1,mgrnum2);
prepStmt.setString(2,deptnum2);
prepStmt.addBatch();
int [] numUpdates=prepStmt.executeBatch();
回答by Mr Lou
Insert into table(col1,col2) select col1,col2 from table_2;
Please go to:http://dev.mysql.com/doc/refman/5.5/en/insert.html
请前往：http://dev.mysql.com/doc/refman/5.5/en/insert.html
回答by Syed Adeel Ali Rizvi
Load data infile query is much better option but some servers like godaddy restrict this option on shared hosting so , only two options left then one is insert record on every iteration or batch insert , but batch insert has its limitaion of characters if your query exceeds this number of characters set in mysql then your query will crash , So I suggest insert data in chunks withs batch insert , this will minimize number of connections established with database.best of luck guys
加载数据 infile 查询是更好的选择，但是像godaddy这样的一些服务器在共享主机上限制了这个选项，所以只剩下两个选项然后一个是在每次迭代或批量插入时插入记录，但是如果您的查询超过这个，则批量插入有其字符限制在 mysql 中设置的字符数然后您的查询将崩溃，所以我建议使用批量插入将数据插入块中，这将最大限度地减少与数据库建立的连接数。祝大家好运
回答by bensiu
mysql allows you to insert multiple rows at once INSERT manual
mysql 允许你一次插入多行INSERT 手册
