MySQL 在mysql中插入多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6889065/
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
Inserting multiple rows in mysql
提问by Emma
Is the database query faster if I insert multiple rows at once:
如果我一次插入多行，数据库查询是否更快：
like
喜欢
INSERT....
UNION
INSERT....
UNION
(I need to insert like 2-3000 rows)
（我需要插入 2-3000 行）
回答by Nicola Cossu
INSERTstatements that useVALUESsyntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
INSERT使用VALUES语法的语句可以插入多行。为此，请包含多个列值列表，每个列表都用括号括起来并用逗号分隔。
Example:
例子：
INSERT INTO tbl_name
(a,b,c)
VALUES
(1,2,3),
(4,5,6),
(7,8,9);
回答by Jacob
If you have your data in a text-file, you can use LOAD DATA INFILE.
如果您的数据在文本文件中，则可以使用LOAD DATA INFILE。
When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.
从文本文件加载表时，请使用 LOAD DATA INFILE。这通常比使用 INSERT 语句快 20 倍。
You can find more tips on how to speed up your insert statements on the link above.
您可以在上面的链接中找到有关如何加快插入语句的更多提示。
回答by sunilsingh
BEGIN;
INSERT INTO test_b (price_sum)
SELECT price
FROM test_a;
INSERT INTO test_c (price_summ)
SELECT price
FROM test_a;
COMMIT;
回答by Meloman
Here is a PHP solution ready for use with a n:m (many-to-many relationship) table :
这是一个可与 an:m（多对多关系）表一起使用的 PHP 解决方案：
// get data
$table_1 = get_table_1_rows();
$table_2_fk_id = 123;
// prepare first part of the query (before values)
$query = "INSERT INTO `table` (
`table_1_fk_id`,
`table_2_fk_id`,
`insert_date`
) VALUES ";
//loop the table 1 to get all foreign keys and put it in array
foreach($table_1 as $row) {
$query_values[] = "(".$row["table_1_pk_id"].", $table_2_fk_id, NOW())";
}
// Implode the query values array with a coma and execute the query.
$db->query($query . implode(',',$query_values));
回答by Johirulla
// db table name / blog_post / menu / site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO product_cate (site_title, sub_title)
VALUES ('$site_title', '$sub_title')";
// db table name / blog_post / menu / site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO menu (menu_title, sub_menu)
VALUES ('$menu_title', '$sub_menu', )";
// db table name / blog_post / menu / site_title
// Insert into Table (column names separated with comma)
$sql = "INSERT INTO blog_post (post_title, post_des, post_img)
VALUES ('$post_title ', '$post_des', '$post_img')";
