一、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
///
/// 数据表
///
返回标准的CSV
private 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))
BEGIN
create 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
DO
truncate 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,
修改
- tmp_table_size=1024M;
- max_heap_table_size=1024M;
大小。
此外,这个批量操作的存储过程中还用到表的递归。单独的递归方法得等本宫勤快了在写。哈哈哈