.Net Core導(dǎo)入千萬(wàn)級(jí)數(shù)據(jù)至Mysql的步驟

CREATE TABLE `trade` (
  `id` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  `trade_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',
  UNIQUE INDEX `id` (`id`),
  INDEX `trade_no` (`trade_no`)
)
COMMENT='訂單'
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;

//開(kāi)始時(shí)間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
​
    //插入10萬(wàn)數(shù)據(jù)
    for (var i = 0; i  100000; i++)
    {
        //插入
        var sql = string.Format("insert into trade(id,trade_no) values('{0}','{1}');",
            Guid.NewGuid().ToString(), "trade_" + (i + 1)
            );
        var sqlComm = new MySqlCommand();
        sqlComm.Connection = conn;
        sqlComm.CommandText = sql;
        sqlComm.ExecuteNonQuery();
        sqlComm.Dispose();
    }
​
    conn.Close();
}
​
//完成時(shí)間
var endTime = DateTime.Now;
​
//耗時(shí)
var spanTime = endTime - startTime;
Console.WriteLine("循環(huán)插入方式耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

//開(kāi)始時(shí)間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
​
    //插入10萬(wàn)數(shù)據(jù)
    var sql = new StringBuilder();
    for (var i = 0; i  100000; i++)
    {
        //插入
        sql.AppendFormat("insert into trade(id,trade_no) values('{0}','{1}');",
            Guid.NewGuid().ToString(), "trade_" + (i + 1)
            );
​
        //合并插入
        if (i % 1000 == 999)
        {
            var sqlComm = new MySqlCommand();
            sqlComm.Connection = conn;
            sqlComm.CommandText = sql.ToString();
            sqlComm.ExecuteNonQuery();
            sqlComm.Dispose();
            sql.Clear();
        }
    }
​
    conn.Close();
}
​
//完成時(shí)間
var endTime = DateTime.Now;
​
//耗時(shí)
var spanTime = endTime - startTime;
Console.WriteLine("循環(huán)插入方式耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

//開(kāi)始時(shí)間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
​
    //插入10萬(wàn)數(shù)據(jù)
    var sql = new StringBuilder();
    for (var i = 0; i  100000; i++)
    {
        if (i % 1000 == 0)
        {
            sql.Append("insert into trade(id,trade_no) values");
        }
​
        //拼接
        sql.AppendFormat("('{0}','{1}'),", Guid.NewGuid().ToString(), "trade_" + (i + 1));
​
        //一次性插入1000條
        if (i % 1000 == 999)
        {
            var sqlComm = new MySqlCommand();
            sqlComm.Connection = conn;
            sqlComm.CommandText = sql.ToString().TrimEnd(',');
            sqlComm.ExecuteNonQuery();
            sqlComm.Dispose();
            sql.Clear();
        }
    }
        
​
    conn.Close();
}
​
//完成時(shí)間
var endTime = DateTime.Now;
​
//耗時(shí)
var spanTime = endTime - startTime;
Console.WriteLine("合并數(shù)據(jù)插入方式耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");

//開(kāi)始時(shí)間
var startTime = DateTime.Now;
using (var conn = new MySqlConnection(connsql))
{
    conn.Open();
    var table = new DataTable();
    table.Columns.Add("id", typeof(string));
    table.Columns.Add("trade_no", typeof(string));
​
    //生成10萬(wàn)數(shù)據(jù)
    for (var i = 0; i  100000; i++)
    {
        if (i % 500000 == 0)
        {
            table.Rows.Clear();
        }
​
        //記錄
        var row = table.NewRow();
        row[0] = Guid.NewGuid().ToString();
        row[1] = "trade_" + (i + 1);
        table.Rows.Add(row);
​
        //50萬(wàn)條一批次插入
        if (i % 500000 != 499999  i  (100000 - 1))
        {
            continue;
        }
        Console.WriteLine("開(kāi)始插入:" + i);
​
        //數(shù)據(jù)轉(zhuǎn)換為csv格式
        var tradeCsv = DataTableToCsv(table);
        var tradeFilePath = System.AppDomain.CurrentDomain.BaseDirectory + "trade.csv";
        File.WriteAllText(tradeFilePath, tradeCsv);
​
        #region 保存至數(shù)據(jù)庫(kù)
        var bulkCopy = new MySqlBulkLoader(conn)
        {
            FieldTerminator = ",",
            FieldQuotationCharacter = '"',
            EscapeCharacter = '"',
            LineTerminator = "\r\n",
            FileName = tradeFilePath,
            NumberOfLinesToSkip = 0,
            TableName = "trade"
        };
​
        bulkCopy.Columns.AddRange(table.Columns.CastDataColumn>().Select(colum => colum.ColumnName).ToList());
        bulkCopy.Load();
        #endregion
    }
​
    conn.Close();
}
​
//完成時(shí)間
var endTime = DateTime.Now;
​
//耗時(shí)
var spanTime = endTime - startTime;
Console.WriteLine("MySqlBulk方式耗時(shí)：" + spanTime.Minutes + "分" + spanTime.Seconds + "秒" + spanTime.Milliseconds + "毫秒");