mysql bulk大批量入库操作
数据库批量插入Oracle中有 OracleBulkCopy,SQL当然也有个SqlBulkCopy .这里有介绍就不说,网上有非常详细的例子,大家可去搜索下,可是MySql确没有MySqlBulkCopy这个,网上找了很久也没找到。找到了一个 MySqlBulkLoader
/// <summary> /// 批量插入 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="db"></param> /// <param name="dataList"></param> /// <returns></returns> public static ValidateResult BulkInsert<T>(List<T> dataList) { int insertCount = 0; ValidateResult result = new ValidateResult(); if (dataList == null || dataList.Count == 0) { result.Success = true; result.Info = "传入的dataList没有数据"; return result; } string[] tableNames = typeof(T).ToString().Split('.'); string tableName = tableNames[tableNames.Length - 1]; string tmpPath = AppDomain.CurrentDomain.BaseDirectory + SystemUtil.OsPathSplitChar + "InTemp"; if (!Directory.Exists(tmpPath)) { Directory.CreateDirectory(tmpPath); } //csv文件临时目录 tmpPath = Path.Combine(tmpPath, tableName + "_Temp_" + Guid.NewGuid().ToString("N") + ".csv"); List<PropertyInfo> propertys = dataList[0].GetType().GetProperties().Where(c => c.PropertyType.Namespace == "System").ToList(); string csv = ListToCsv(dataList, propertys); File.WriteAllText(tmpPath, csv); string connString = System.Configuration.ConfigurationManager.ConnectionStrings["AssessConnection"].ToString(); using (MySqlConnection conn = new MySqlConnection(connString)) { try { Stopwatch stopwatch = new Stopwatch(); stopwatch.Start(); conn.Open(); MySqlBulkLoader bulk = new MySqlBulkLoader(conn) { CharacterSet = "UTF8", FieldTerminator = ",", FieldQuotationCharacter = '"', EscapeCharacter = '"', LineTerminator = SystemUtil.IsLinux() ? "\n" : "\r\n", FileName = tmpPath, NumberOfLinesToSkip = 0, TableName = tableName, }; bulk.Columns.AddRange(propertys.Select(c => c.Name).ToList());//根据标题列对应插入 insertCount = bulk.Load(); stopwatch.Stop(); if (insertCount > 0) { result.Success = true; result.Info = "提交成功"; } else { result.Success = false; result.Info = "提交失败"; } } catch (MySqlException ex) { result.Success = true; result.Info = "插入内部错误" + ex.ToString(); return result; } } //File.Delete(tmpPath); return result; } /// <summary> /// list转csv /// </summary> /// <param name="list"></param> /// <param name="propertys"></param> /// <returns></returns> private static string ListToCsv(IList list, List<PropertyInfo> propertys) { StringBuilder sb = new StringBuilder(); foreach (var item in list) { for (int i = 0; i < propertys.Count; i++) { var prop = propertys[i]; if (i != 0) sb.Append(","); string val = Convert.ToString(prop.GetValue(item, null)); if (prop.PropertyType == typeof(bool)) { val = val == "True" ? "1" : "0"; sb.Append(val); } else if (prop.PropertyType == typeof(DateTime)) { val = Convert.ToDateTime(val).ToString("yyyy-MM-dd HH:mm:ss"); sb.Append(val); } else if (prop.PropertyType == typeof(DateTime?)) { if (val == null) { val = "Null"; sb.Append(val); } else { val = Convert.ToDateTime(val).ToString("yyyy-MM-dd HH:mm:ss"); sb.Append(val); } } else if (prop.PropertyType == typeof(string) && val.Contains(",")) { sb.Append("\"" + val.Replace("\"", "\"\"") + "\""); } else { sb.Append(val); } } sb.AppendLine(); } return sb.ToString(); }