blocks|key|132465|text|使用VALUES语法的|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|132466|+INSERT语句可以插入多行。为此，需要包含多个列值列表，每个列表都用括号括起来，并用逗号分隔。|blockquote|132467|132468|132469|示例：|BOLD|132470|INSERT+INTO+tbl_name
++++(a,b,c)
VALUES
++++(1,2,3),
++++(4,5,6),
++++(7,8,9);|code-block|syntax|javascript|132471|Source|132472|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/insert.html^0|2|6|0|1|6|0|0|0|0|3|0|0|0|6|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|15|8|@$9|16|A|17|B|C]]|D|@]|E|$]]|$1|I|3|-4|5|6|7|18|8|@]|D|@]|E|$]]|$1|J|3|-4|5|6|7|19|8|@]|D|@]|E|$]]|$1|K|3|L|5|6|7|1A|8|@$9|1B|A|1C|B|M]]|D|@]|E|$]]|$1|N|3|O|5|P|7|1D|8|@]|D|@]|E|$Q|R]]|$1|S|3|T|5|6|7|1E|8|@]|D|@$9|1F|A|1G|1|1H]]|E|$]]|$1|U|3|-4|5|6|7|1I|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

<blockquote>
 <code>INSERT</code> statements that use <code>VALUES</code> syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas.
</blockquote>

Example:

<pre><code>INSERT INTO tbl_name
 (a,b,c)
VALUES
 (1,2,3),
 (4,5,6),
 (7,8,9);
</code></pre>

<a href="http://dev.mysql.com/doc/refman/5.5/en/insert.html" rel="noreferrer">Source</a>

blocks|key|3415394|text|如果你的数据在文本文件中，你可以使用LOAD+DATA+INFILE。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|3415395|3415396|从文本文件加载表时，请使用LOAD+DATA+INFILE。这通常比使用INSERT语句快20倍。|blockquote|3415397|3415398|3415399|Optimizing+INSERT+Statements|3415400|你可以在上面的链接上找到更多关于如何加速insert语句的技巧。|3415401|entityMap|0|LINK|mutability|MUTABLE|url|https://dev.mysql.com/doc/refman/5.7/en/load-data.html|1|https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html^0|I|G|0|0|0|0|0|0|0|S|1|0|0^^$0|@$1|2|3|4|5|6|7|X|8|@]|9|@$A|Y|B|Z|1|10]]|C|$]]|$1|D|3|-4|5|6|7|11|8|@]|9|@]|C|$]]|$1|E|3|F|5|G|7|12|8|@]|9|@]|C|$]]|$1|H|3|-4|5|6|7|13|8|@]|9|@]|C|$]]|$1|I|3|-4|5|6|7|14|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|15|8|@]|9|@$A|16|B|17|1|18]]|C|$]]|$1|L|3|M|5|6|7|19|8|@]|9|@]|C|$]]|$1|N|3|-4|5|6|7|1A|8|@]|9|@]|C|$]]]|O|$P|$5|Q|R|S|C|$T|U]]|V|$5|Q|R|S|C|$T|W]]]]

If you have your data in a text-file, you can use <a href="https://dev.mysql.com/doc/refman/5.7/en/load-data.html" rel="noreferrer">LOAD DATA INFILE</a>.

<blockquote>
 When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements.
</blockquote>

<a href="https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.html" rel="noreferrer">Optimizing INSERT Statements</a>

You can find more tips on how to speed up your insert statements on the link above.

blocks|key|132509|text|BEGIN;
INSERT+INTO+test_b+(price_sum)
++SELECT+price
++FROM+++test_a;
INSERT+INTO+test_c+(price_summ)+
++SELECT+price
FROM+++test_a;
COMMIT;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|132510|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>BEGIN;
INSERT INTO test_b (price_sum)
 SELECT price
 FROM test_a;
INSERT INTO test_c (price_summ) 
 SELECT price
FROM test_a;
COMMIT;
</code></pre>

blocks|key|1800547|text|下面是一个可与n:m+(多对多关系)表一起使用的PHP解决方案：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1800548|//+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));|code-block|syntax|javascript|1800549|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Here is a PHP solution ready for use with a n:m (many-to-many relationship) table :

<pre><code>// 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-&gt;query($query . implode(',',$query_values));
</code></pre>

blocks|key|3415417|text|//+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')";|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|3415418|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre class="lang-php prettyprint-override"><code>// 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')";
</code></pre>

Is the database query faster if I insert multiple rows at once:

like

<pre><code>INSERT....

UNION

INSERT....

UNION
</code></pre>

(I need to insert like 2-3000 rows)

Inserting multiple rows in mysql

33 天实现自己的 AI 进化论？发文瓜分机械键盘、耳机等万元奖池！点击参加活动：<a href="https://cloud.tencent.com/developer/article/2503022" target="_blank">https://cloud.tencent.com/developer/article/2503022</a> 
 <img src="https://qcloudimg.tencent-cloud.cn/raw/01543e0407db10898a7ee9f420794432.jpg"/>

技术创作特训营有奖征文

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋 

腾讯云AI代码助手

CODING DevOps

Cloud Studio

SDK中心

API中心

命令行工具

文章&问答评论现已支持表情

如果我一次插入多行，数据库查询速度是否会更快：喜欢INSERT....UNIONINSERT....UNION(我需要插入2-3000行)

问在mysql中插入多行
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在mysql中插入多行EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在mysql中插入多行
EN