是否可以在mysql中进行批量复制

2022-01-21 00:00:00 dataset database mysql

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

I need to insert multiple rows in my Mysql database.my rows are available in my dataset.

我正在使用for循环逐一发送行是正确的方式吗?...

i am using for loop to send the row one by one is that right way?...

推荐答案

您可以使用单个 SQL 语句插入多行，如下所示:

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');

更新:

MarkR 在他的评论中是正确的 - 如果您正在从用户那里收集数据，或者您正在编译信息，您可以使用以下内容动态构建查询:

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();

需要注意的两点:

  1. 如果您接受来自用户的输入，那么清理所有用户输入非常重要，否则恶意用户可能会修改/删除/删除您的整个数据库.有关详细信息，请在 Google 上搜索SQL 注入".
  2. 我使用的是 StringBuilder 类，而不是使用字符串原语并简单地追加(即字符串 s = "Insert..."; s+="blah blah blah")，因为它的 StringBuilder 追加速度更快，因为它不被视为一个数组，因此在附加到它时不需要调整自身的大小.
  1. 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."
  2. 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.

相关文章