如何尽可能快地将大量记录插入MySql数据库 [英] How to insert a very large number of records into a MySql database as fast as possible

问题描述

我有一个如下的数据库表:

I have a database table like below:

create table temperature
(id int unsigned not null auto_increment primary key,
temperature double
);

在我的程序中，我将大约2000万的温度插入表中. 我在.Net环境中工作，使用连接器/网络连接到MySql.代码如下:

And in my program I got about 20 million temperature to insert into the table. I worke in .Net environment, use Connector/Net connecting to MySql. The code was like below:

List<double> temps = new List<double>();
...
string connStr = "server=localhost;user=name;database=test;port=3306;password=*****;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
    conn.Open();

    //temps.Count is about 20 million
    for (int i = 0; i < temps.Count; i++)
    {
        string sql1 = "INSERT INTO temperature VALUES (null, "+temps[i]+")";
        MySqlCommand cmd1 = new MySqlCommand(sql1, conn);
        cmd1.ExecuteNonQuery();
    }

}
catch (Exception ex)
{
    Console.WriteLine(ex.ToString());
}
conn.Close();

我如何尽快插入这么多行数据? (它每分钟只能在我的计算机中插入2000条记录.)

How can i insert so many lines data as fast as possible? (It can only insert 2000 records every minute in my computer.)

推荐答案

您可以使用bulk insert的概念，该概念可以同时执行许多插入操作，从而最大程度地减少了多次调用ExecuteNonQuery的开销.

you can use the concept of bulk insert which executes many inserts at the same time minimizing overhead of calling ExecuteNonQuery multiple times.

在MySQL中称为LOAD DATA，请在此处查看详细信息: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

in MySQL this is called LOAD DATA, check here for details: http://dev.mysql.com/doc/refman/5.5/en/load-data.html

称为bulk insert，它被称为bulk insert，这就是为什么我用此名称提及它的原因.

in MS SQL Server this is called bulk insert and it's known as such, that's why I've mentioned it with this name.

这篇关于如何尽可能快地将大量记录插入MySql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！