将行插入 MySQL 数据库的最有效方法 [英] Most efficient way to insert Rows into MySQL Database
问题描述
我已经阅读了很多关于此的问题,但我找不到一个足够快的问题.我认为有更好的方法可以在 MySQL 数据库中插入大量行
I've read a lot of questions about that but i couldn't find one that is fast enough. I think there are better ways to insert a lot of rows into a MySQL Database
我使用以下代码将 100k 插入到我的 MySQL 数据库中:
I use the following code to insert 100k into my MySQL-Database:
public static void CSVToMySQL()
{
string ConnectionString = "server=192.168.1xxx";
string Command = "INSERT INTO User (FirstName, LastName ) VALUES (@FirstName, @LastName);";
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
mConnection.Open();
for(int i =0;i< 100000;i++) //inserting 100k items
using (MySqlCommand myCmd = new MySqlCommand(Command, mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.Parameters.AddWithValue("@FirstName", "test");
myCmd.Parameters.AddWithValue("@LastName", "test");
myCmd.ExecuteNonQuery();
}
}
}
这需要 100k 行大约 40 秒.我怎样才能使它更快或更高效?
This takes for 100k rows about 40 seconds. How can i make this faster or a little more efficient?
通过 DataTable/DataAdapter 或一次插入多行可能会更快:
Might be faster to insert multiple rows via a DataTable/DataAdapter or at once:
INSERT INTO User (Fn, Ln) VALUES (@Fn1, @Ln1), (@Fn2, @Ln2)...
由于安全问题,我无法将数据加载到文件和 MySQLBulkLoad 中.
Due to security issues i can't load the data into a file and MySQLBulkLoad it.
推荐答案
这是我的多次插入"代码.
Here is my "multiple inserts"-code.
插入 10 万行只用了 40 秒而不是 40 秒3 秒!!
The insertion of 100k rows took instead of 40 seconds only 3 seconds!!
public static void BulkToMySQL()
{
string ConnectionString = "server=192.168.1xxx";
StringBuilder sCommand = new StringBuilder("INSERT INTO User (FirstName, LastName) VALUES ");
using (MySqlConnection mConnection = new MySqlConnection(ConnectionString))
{
List<string> Rows = new List<string>();
for (int i = 0; i < 100000; i++)
{
Rows.Add(string.Format("('{0}','{1}')", MySqlHelper.EscapeString("test"), MySqlHelper.EscapeString("test")));
}
sCommand.Append(string.Join(",", Rows));
sCommand.Append(";");
mConnection.Open();
using (MySqlCommand myCmd = new MySqlCommand(sCommand.ToString(), mConnection))
{
myCmd.CommandType = CommandType.Text;
myCmd.ExecuteNonQuery();
}
}
}
创建的 SQL 语句如下所示:
The created SQL-statement looks like this:
INSERT INTO User (FirstName, LastName) VALUES ('test','test'),('test','test'),... ;
更新:谢谢Salman A 我添加了MySQLHelper.EscapeString
以避免代码注入,这是在使用参数时内部使用的.
Update: Thanks Salman A I added MySQLHelper.EscapeString
to avoid code injection which is internally used when you use parameters.
这篇关于将行插入 MySQL 数据库的最有效方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!