使用MySql.Data.MySqlClient实现mysql批处理

最新推荐文章于 2025-06-03 14:06:59 发布

l清风明月

最新推荐文章于 2025-06-03 14:06:59 发布

阅读量890

点赞数

CC 4.0 BY-SA版权

分类专栏： mysql

本文链接：https://blog.csdn.net/l15738519366/article/details/106091333

mysql 专栏收录该内容

5 篇文章

订阅专栏

1、添加引用

using MySql.Data.MySqlClient;

2、创建数据库表

CREATE TABLE History202005(
ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT'唯一编号',
WYBH INT,
Name INT,
TYPE INT,
Tag INT,
UNIT INT,
DB  INT,
DDB INT,
GB INT,
GGB INT,
IsAlarm INT,
Value INT,
Time INT) AUTO_INCREMENT = 1;

2、实现代码

public static void InsertbatchData()
        {
            String connstr = "server=192.168.102.222;database=batchTest;user=root;password=123456;";
            Stopwatch watch = new Stopwatch();
            StringBuilder sb = new StringBuilder();
            var dt = "Test_" + DateTime.Now.ToString("yyyy_MM");
            String sql0 = "insert into " +dt+" (WYBH,Name,TYPE,Tag,UNIT,DB,DDB,GB,GGB,IsAlarm,Value,Time) values ";
            sb.Append(sql0);
            Random rnd = new Random();

            List<MySqlParameter> parmList = new List<MySqlParameter>();

            for (int index = 0; index < 5000; index++)
            {                
                sb.Append(String.Format(
                 "(@WYBH_index{0},@Name_index{0},@TYPE_index{0},@Tag_index{0},@UNIT_index{0},@DB_index{0},@DDB_index{0},@GB_index{0},@GGB_index{0},@IsAlarm_index{0},@Value_index{0},@Time_index{0}),",
                 index.ToString()));
                parmList.Add(new MySqlParameter("WYBH_index" + index.ToString(),index));
                parmList.Add(new MySqlParameter("Name_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("TYPE_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("Tag_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("UNIT_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("DB_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("DDB_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("GB_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("GGB_index" + index.ToString(), index));
                parmList.Add(new MySqlParameter("IsAlarm_index"+index.ToString(),index));
                parmList.Add(new MySqlParameter("Value_index" + index.ToString(),index));
                parmList.Add(new MySqlParameter("Time_index" + index.ToString(),index));
            }
            sb.Remove(sb.Length - 1, 1);
            String sql = sb.ToString();

            watch.Start();
            int effectedRows = MySqlHelper.ExecuteNonQuery(connstr, sql, parmList.ToArray());
            watch.Stop();
            Console.WriteLine(effectedRows.ToString() + "行受影响");
            Console.WriteLine("总计用时" + watch.Elapsed.ToString());
            Console.ReadLine();
        }

3、批处理插入过程中，如果数据量过大，会出现异常，这个重新设置一下允许接收数据量大小就可以解决。