mysql的批量新增和批量修改

一、mysql的批量新增

关键词：MySqlBulkLoader

使用方法：

1.数据准备

  public static void Data()         {            DataTable dt = new DataTable();            dt.Columns.Add("A", typeof(string));            dt.Columns.Add("B", typeof(string));            dt.Columns.Add("C", typeof(int));            for (int i = 0; i < 10000000; i++)            {                  DataRow dr = dt.NewRow();                        dr["A"] = 'A'+i;                        dr["B"] ='B'+i;                        dr["C"] = i;                                    }            DataSet ds = new DataSet();//支持多表批量插入            ds.Tables.Add(dt);            BatchInsert(ds);         }

2.核心方法

/// 
///将DataTable转换为标准的CSV/// 
/// 数据表/// 返回标准的CSVprivate static string DataTableToCsv(DataTable table){//以半角逗号（即,）作分隔符，列为空也要表达其存在。//列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。//列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。StringBuilder sb = new StringBuilder();DataColumn colum;foreach (DataRow row in table.Rows){for (int i = 0; i < table.Columns.Count; i++){colum = table.Columns[i];if (i != 0) sb.Append(",");if (colum.DataType == typeof(string) && row[colum].ToString().Contains(",")){sb.Append("\"" + row[colum].ToString().Replace("\"", "\"\"") + "\"");}else sb.Append(row[colum].ToString());}sb.AppendLine();}return sb.ToString();}public static void BatchInsert(DataSet ds){using (MySqlConnection conn = new MySqlConnection(AppConst.connectionString)){conn.Open();using (MySqlTransaction trans = conn.BeginTransaction()){MySqlCommand cmd = new MySqlCommand();try{foreach (DataTable dt in ds.Tables){//mysql批量插入int insertCount = 0;string tmpPath=filePath+DateTime.Now.ToString("yyMMddhhmmss")+".csv";string csv = DataTableToCsv(dt);File.WriteAllText(tmpPath, csv); MySqlBulkLoader bulk = new MySqlBulkLoader(conn){FieldTerminator = ",",FieldQuotationCharacter = '"',EscapeCharacter = '"',LineTerminator = "\r\n",FileName = tmpPath,NumberOfLinesToSkip = 0,TableName = "AntiFleeing_WareHousingDetails",};bulk.Columns.AddRange(dt.Columns.Cast().Select(colum => colum.ColumnName).ToList());insertCount = bulk.Load();File.Delete(tmpPath);}trans.Commit();}catch (System.Data.SqlClient.SqlException e){trans.Rollback();throw e;}finally{cmd.Dispose();conn.Close();}}}}

 二、mysql 批量修改

1.较为简单的，联表修改

UPDATE 表A A INNER JOIN 表B B ON A.字段= B.字段 SET A.修改字段= B.赋值字段

2.使用MEMORY 关键字（暂先不做介绍）

一般的，批量修改，可以使用联表修改很快很方便。但是，最近有一个需求是，先要根据条件查表，然后将查到的表数据A，在另一个表B中递归找到它所有的子级C，在将A和C作为条件更改B中的某个字段。哇~真的是超级绕啊！！！嗯，看代码..

-- DROP PROCEDURE proc_getBatchUpdate;CREATE PROCEDURE nbsecuritymain.proc_getBatchUpdate(in formCode VARCHAR(200),in tableName VARCHAR(200),in productId int,in formTable VARCHAR(200))BEGINcreate temporary table if not exists temp(code varchar(200),KEY pk(code)) ENGINE=MEMORY DEFAULT CHARSET=utf8;-- MAX_ROWS=100000000;-- 创建索引 听说可以提高速度10倍create temporary table if not exists temp0(code varchar(200),KEY pk(code)) ENGINE=MEMORY DEFAULT CHARSET=utf8;-- MAX_ROWS=100000000;create temporary table if not exists temp_return(code varchar(200),KEY pk(code)) ENGINE=MEMORY DEFAULT CHARSET=utf8;-- MAX_ROWS=100000000;truncate TABLE temp;  -- 使用前先清空临时表。 truncate TABLE temp0;truncate TABLE temp_return; -- 查询所有码set @_sql=CONCAT(' insert into temp0 select Code from ', tableName,' where ParentCode in (select code from temp)'); set @_sql_Update=CONCAT('update ',tableName ,' set ProductId=',productId,' where Code in (select code from temp_return)'); set @_sql_From=CONCAT('insert into temp  select code from  ',formTable,'  where FormCode=formCode'); PREPARE _ex_From FROM @_sql_From;-- 预处理 EXECUTE _ex_From; WHILE ROW_COUNT()>0 DOtruncate TABLE temp0;-- 清空insert into temp_return select code from temp;  PREPARE _ex_sql FROM @_sql;-- 预处理 EXECUTE _ex_sql;truncate TABLE temp;insert into temp select code from temp0; END WHILE;   PREPARE _ex_Update FROM @_sql_Update;-- 预处理   EXECUTE _ex_Update;END

这个存储过程用到了内存临时表，很容易出现内存溢出问题。那么对应的解决方案来了

找到mysql安装包里面的配置文件my.ini,

修改

1. tmp_table_size=1024M;  
2. max_heap_table_size=1024M; 

大小。

此外，这个批量操作的存储过程中还用到表的递归。单独的递归方法得等本宫勤快了在写。哈哈哈