多次插入单个数据集mysql [英] insert single set of data multiple times mysql

问题描述

我必须多次插入单个数据集，例如n行.

I have to insert single set of data multiple times , say n rows.

INSERT INTO MyTable VALUES ("John", 123, "US");

我可以在一条SQL语句中插入所有n行吗?

Can I insert all n rows in a single SQL statement?

这里n是动态值n是用户输入，如何进行插入查询n次，任何想法.

here n is dynamic value n is user input , how to make insert query n times , any idea.

$sql = "INSERT INTO `mytable` VALUES(`field1`,`field2`,`field3`) VALUES ";
$count = 5;
for($i=0;$i<$coutn;$i++)
{
$sql .= " ('john','123','us' )";
}

这是正确的方法.

推荐答案

是的，很容易做到，它看起来应该像这样:

Yep, this can be done easily, it should look something like this:

INSERT INTO MyTable VALUES ("John", 123, "US"), ("Carl", 123, "EU"), ("Jim", 123, "FR");

但是，在查询中指定表的列是一种好的编程习惯，例如:

However, it is good programming practice to specify the columns of your table in the query, for example:

INSERT INTO MyTable (Column1, Column2, Column3) 
VALUES ("John", 123, "US"), ("Carl", 123, "EU"), ("Jim", 123, "FR");

您可以像这样构建查询(在for周期中)，$total是您的用户输入:

EDIT : You can build your query like this (in for cycle), the $total is your user input:

$sql = "INSERT INTO MyTable (Column1, Column2, Column3) VALUES";

//Build SQL INSERT query
for ($i = 1; $i <= $total; $i++) {
  $sql .= " ($value1, $value2, $value3), ";
}
//Trim the last comma (,)
$sql = rtrim($sql,",");
//Now, the $sql var contains the complex query. 
$result = mysql_query($sql);

您可以看到我们不在循环中执行INSERT语句，但是我们将构建SQL查询文本，然后然后一次执行经过.

As you can see we do not execute the INSERT statement in the loop, but rather we build the SQL query text and then we will execute it in one pass.

这篇关于多次插入单个数据集mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！