C# 操作mysql大批量插入语句，一次几万条，可以同时跳过指定的重复字段继续插入。

Question

我现在用的是这个语句很卡

执行SQL语句

            DataTable dt = (dSkinGridList1.DataSource as DataTable);
            this.label1.Visible = true;
            pidJiazai.Visible = true;
            List<string> SQLStringLis = new List<string>();
            for (int i = 0; i < dt.Rows.Count - 1; i++)
            {
                SQLStringLis.Add("insert ignore into shopsList values('" + dt.Rows[i][0].ToString() + "','','" + dt.Rows[i][1].ToString() + "','" + dt.Rows[i][2].ToString() + "','" + dt.Rows[i][3].ToString() + "','" + dt.Rows[i][4].ToString() + "','0','0','0','" + DateTime.Now.ToString() + "')");
            }

插入执行

        public static bool insertPai(List<string> SQLStringList)
        {
            mysqlClass sqlL = new mysqlClass();
            using (MySqlConnection conn = sqlL.getmysqlcon())
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                MySqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                        //后来加上的
                        if (n > 0 && (n % 500 == 0 || n == SQLStringList.Count - 1))
                        {
                            tx.Commit();
                            tx = conn.BeginTransaction();
                        }
                    }
                    return true;
                    //tx.Commit();//原来一次性提交
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }

请教更好的，办法，速度更快。这个语句现在插入3000条就卡主了。在线等。谢谢！

Answer 1

MySqlBulkLoader
https://blog.csdn.net/zhou2s_101216/article/details/50875211