插入多行的最快方法

Question

我有一个包含成千上万行数据的DataTable。 在我的过程中，我正在将几千行添加到这个DataTable中，这些行也需要添加到数据库中。 我不想为每条记录创建一个INSERT语句，而是希望尽快地插入它们。 MySQL的LOAD INTO命令不合适，因为我不想涉及任何外部CSV文件。目前，我使用了MySqlDataAdapter并调用'Update'方法，只传入插入更改，如下所示:

MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(adapter);
adapter.InsertCommand = commandBuilder.GetInsertCommand();
adapter.Update(myDataTable);

这个也运行得很慢，所以我怀疑它们也是逐行插入的。我有什么选择？难道只能建立包含所有值的长INSERT语句吗？

- Dot NET

---

在更新之前手动启动事务有帮助吗？ - Jens

@Thomas - 但是在MySQL中，似乎只有使用外部文件才能进行批量插入，对吗？ - Dot NET

Answer 1

像这样插入值：

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

为了优化插入速度，将许多小操作合并为一个大操作。

- Abhishek Sharma

---

但是如何使用参数化查询来完成呢？并且它能够支持数十万条记录吗？ - Dot NET

你的列上有索引吗？ - avrono

并非所有的@avrono都被索引。 - Dot NET

你可以使用参数化查询来实现，但代码会变得更加混乱。我不会在每个INSERT中使用非常大量的记录，因为这种方法的好处随着记录数量的增加而减少。通常我每次插入 ~250 条记录。在 PHP 中，我使用一个类来执行插入操作，将要插入的数据行传递给它。该类负责构建查询和何时插入，当对象析构函数被调用时，最终记录被插入。 - Kickstart

我强烈建议您测试一下，将其与创建CSV文件并删除它进行比较。执行大约400-1600个查询不会比在服务器端创建一个文件，使用MySqlBulkLoader，然后再删除它更快。再次强调，这应该被衡量。 - mybirthname

Answer 2

我看到的唯一解决方案是：

1）将DataTable转换为csv格式->你可以在谷歌上搜索。

2）将其保存在服务器端的临时目录中。

3）使用MySqlBulkLoader，这里有一篇关于它的文章link。加载保存在临时目录中的文件。

4）之后从临时目录中删除该文件。

- mybirthname

---

虽然我一直知道这个选项，但由于没有其他明显的方法来实现我想要达到的目标，所以最终采取了这种方法。因此感谢您确认这是正确的方法。 - Dot NET

Answer 3

我不确定这是否是一个好的带参数方法，但它运行良好。该方法接收一个“ParamDbList”列表（ParamDB的集合），并在每1000个寄存器或1900个参数（2000的限制）插入行。只需根据您的驱动进行适当调整即可。

  public bool InsertBatch(System.Collections.Generic.List<ParamDbLIST> dados, string tabela)
    {
        if (dados.Count == 0)
            return true;

        string campos = "";
        dados[0].ForEach(delegate(ParamDB p)
        {
            campos += (campos == "" ? "" : ", ") + "@" + p.sNOME + "#N#";
        });

        bool resultado = true;
        //Insere de 999 a 999, que é o máximo q o sql server permite por vez
        //Maximo de 2000 parametros
        int k = 0;
        while (k < dados.Count)
        {
            this.sql = new StringBuilder();
            List<String> vals = new List<string>();
            ParamDbLIST parametros_insert = new ParamDbLIST();
            int c_sqls = 0;
            int c_parametros = 0;
            while (k < dados.Count && c_sqls < 1000 && c_parametros < 1900)
            {
                c_sqls++;
                vals.Add("(" + campos.Replace("#N#", c_sqls.ToString()) + ")");
                foreach (ParamDB p in dados[k])
                {

                    p.sNOME += c_sqls.ToString();
                    parametros_insert.Add(p);
                    c_parametros++;
                }
                k++;
            }

            this.sql.Append("INSERT INTO " + tabela + "(" + campos.Replace("#N#", String.Empty).Replace("@", String.Empty) + ") VALUES " + String.Join(",", vals));

            resultado = resultado && this.RunSQL(sql.ToString(), parametros_insert);

        }

        return resultado;
    }






public class ParamDbLIST : System.Collections.ObjectModel.Collection<ParamDB>
{/*I have other stuff here, but this will work*/}

   public class ParamDB
{
    public string sNOME { get; set; }
    public Object sVALOR { get; set; }}

记住这个方法： INSERT INTO tbl_name (a,b,c) VALUES (1,2,3), (4,5,6), (7,8,9); 每个命令的行数限制为1000行。

我认为在这里使用事务（以确保安全）是一个好方法。

你应该改变的方法是RunSQL。

如果这个方法可以优化，请告诉我。

- Charles Stein

Answer 4

我不确定MySQL怎么做。 但在MSSQL Server中，我们使用XML。 我们创建一个带有大量数据的XML，然后使用数据库中的Insert With Xml。

INSERT INTO [TBL_USER_INFO](NAME, EMAIL)
   SELECT
      Result.value('value','varchar(25)') as NAME,
      Result.value('value','varchar(50)') as EMAIL
   FROM 
      @input.nodes('/EmployeList/Employee') AS ABCD(Result)

- Vermajai1995

Answer 5

对于 MySQL 我不是很确定，但在 SQL Server 中我发现最快的插入方式类似于 @Rahautos，即将值列表分成多个查询。 我知道这听起来很奇怪，但它将速度从每秒 1200 次提高到了每秒 12000 次，提升了 10 倍。 不确定差异在哪里。 此外，事务也非常有帮助。

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

示例代码

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication2
{
    class Program
    {
        static void Main(string[] args)
        {
            var constring = (new SqlConnectionStringBuilder
            {
                DataSource = "someserver",
                InitialCatalog = "12trunk",
                IntegratedSecurity = true
            }).ToString();
            using (var con = new SqlConnection(constring))
            {
                con.Open();
                using (var trans = con.BeginTransaction(isolationLevel: System.Data.IsolationLevel.ReadUncommitted) as SqlTransaction)
                using (var cmd = new SqlCommand())
                {
                    cmd.Transaction = trans;
                    cmd.Connection = con;
                    var start = DateTime.Now;
                    Console.WriteLine("Start = " + start);
                    const int inserts = 100000;
                    var builder = new StringBuilder();
                    cmd.CommandText = "delete from test";                    
                    for (int i = 0; i < inserts; i++)
                    {
                        Guid[] guids = new Guid[7];
                        for (int j = 0; j < 7; j++)
                        {
                            guids[j] = Guid.NewGuid();
                        }
                        var sql = $"insert into test (f0, f1, f2, f3, f4, f5, f6) values ('{guids[0]}', '{guids[1]}', '{guids[2]}','{guids[3]}', '{guids[4]}', '{guids[5]}', '{guids[6]}');\n";
                        builder.Append(sql);
                        if (i % 1000 == 0)
                        {
                            cmd.CommandText = builder.ToString();
                            cmd.ExecuteNonQuery();
                            builder.Clear();
                        }

                    }
                    cmd.CommandText = builder.ToString();
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                    var ms = (DateTime.Now - start).TotalMilliseconds;
                    Console.WriteLine("Ms to run = " + ms);
                    Console.WriteLine("inserts per sec = " + inserts / (ms / 1000));
                    Console.ReadKey();
                }
            }
        }
    }
}

- Justin