是可能在mysql做批量复制

问题描述

我需要在我的Mysql数据库中插入多行。我的数据集中有我的行可用。

我使用for循环发送行...

解决方案

您可以使用单个SQL语句插入多行：

  INSERT INTO myTable（col1，col2，col3）VALUES（'myval1'，'myval2'，'myval3'），'myotherval1' myotherval2'，'myotherval3'），（'anotherval1'，'anotherval2'，'anotherval3'）; 
   
 
 
 
 更新：
 
 
 
 MarkR在他的评论是正确的 - 如果你从用户收集数据，或者你正在编译信息，你可以使用类似的动态建立查询：
  StringBuilder stringBuilder = new StringBuilder（）; 
 stringBuilder.Append（INSERT INTO myTable（col1，col2，col3）VALUES）; 
 for（int i = 0; i< myDataCollection.Count; i ++）{
 stringBuilder.Append（（+ myDataCollection [i] .Col1 +，+ myDataCollection [i] .Col2 + ，+ myDataCollection [i] .Col3 +））; 
 if（i< myDataCollection.Count-1）{
 stringBuilder.Append（，）; 
} else {
 stringBuilder.Append（;）; 
} 
} 
 
 string insertStatement = stringBuilder.ToString（）; 
  
 需要注意的两个要点： 
 
 
如果您接受用户的输入，则非常重要用于清除所有用户输入，否则恶意用户可以修改/删除/整个数据库。 
 
 
我使用StringBuilder类，而不是使用字符串基元和简单的追加（即。string s =Insert ... ; s + =blah blah blah），因为它的StringBuilder在追加时更快，因为它不被视为数组，所以不需要在你追加它时调整自己的大小。
 
 
 
I need to insert multiple rows in my Mysql database.my rows are available in my dataset.
i am using for loop to send the row one by one is that right way?...
 解决方案 
You can insert multiple rows using a single SQL statement like so:
INSERT INTO myTable (col1, col2, col3) VALUES ('myval1', 'myval2', 'myval3'), ('myotherval1', 'myotherval2', 'myotherval3'), ('anotherval1', 'anotherval2', 'anotherval3');
Update: 
MarkR is right in his comment - if you're collecting data from a user, or you're compiling information, you can build the query dynamically with something like:
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("INSERT INTO myTable (col1, col2, col3) VALUES ");
for(int i=0;i<myDataCollection.Count;i++) {
  stringBuilder.Append("(" + myDataCollection[i].Col1 + ", " + myDataCollection[i].Col2 + ", " + myDataCollection[i].Col3 + ")");
  if (i<myDataCollection.Count-1) {
    stringBuilder.Append(", ");
  } else {
    stringBuilder.Append(";");
  }
}

string insertStatement = stringBuilder.ToString();
Two important points to note:
If you are accepting input from a user, it is very important to sanitize all user inputs, otherwise malicious users could modify/delete/drop your entire database. For more info, google "SQL Injections."
I'm using the StringBuilder class, rather than using a string primitive and simply appending (ie. string s = "Insert..."; s+="blah blah blah") because it's StringBuilder is faster at appending, because it is not treated as an array, and so does not need to resize itself as you append to it.
                        
这篇关于是可能在mysql做批量复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！