c# insert data bulkly into mysql per 10000 or 1000

复制代码
CREATE TABLE `person5` (
  `Id` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Name` longtext,
  `PId` int NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `person6` ( `Id` varchar(
128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL, `Name` longtext, `PId` int NOT NULL, PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
复制代码

 

 

复制代码
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.IO;
using System.Diagnostics;

namespace ConsoleApp27
{
    class Program
    {
        static void Main(string[] args)
        {
            Task t1 = Task.Run(() =>
             {
                 MySQLInsertDataDemo();
             });

            Task t2 = Task.Run(() =>
              {
                  MySQLInsertDataDemo2();
              }); 
            Task.WaitAll(new Task[] { t1, t2 }); 
        }

        static void MySQLInsertDataDemo()
        {
            string connString = "Server=localhost;Port=3306;Database=mydb;Uid=root;Pwd=Root;";
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                conn.Open();
                int k = 0;
                for (int i = 0; i < 1000; i++)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    int insertedRows = 0;
                    StringBuilder sqlBuilder = new StringBuilder();
                    sqlBuilder.Append("insert into person5 values");
                    for (int j = 0; j < 10000; j++)
                    {
                        sqlBuilder.Append("('" + Guid.NewGuid().ToString() + "','" + "Fred" + k + "','" + k + "'),");
                        k++;
                    }
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandTimeout = 0;
                        string insertSQL = sqlBuilder.ToString();
                        int lastCommaIndex = insertSQL.LastIndexOf(',');
                        cmd.CommandText = insertSQL.Remove(lastCommaIndex);
                        insertedRows = cmd.ExecuteNonQuery();                       
                    }
                    sw.Stop();
                    string msg = $"{DateTime.Now.ToString("yyyyMMddHHmmssffff")},time cost:{sw.ElapsedMilliseconds},insertedRows:{insertedRows},loop:{i}";
                    File.AppendAllText("Time5.txt", msg + Environment.NewLine + Environment.NewLine);
                    Console.WriteLine(msg);
                }
            }
        }

        static void MySQLInsertDataDemo2()
        {
            string connString = "Server=localhost;Port=3306;Database=mydb;Uid=root;Pwd=Root;";
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                conn.Open();
                int k = 0;
                for (int i = 0; i < 10000; i++)
                {
                    Stopwatch sw = new Stopwatch();
                    sw.Start();
                    int insertedRows = 0;
                    StringBuilder sqlBuilder = new StringBuilder();
                    sqlBuilder.Append("insert into person6 values");
                    for (int j = 0; j < 1000; j++)
                    {
                        sqlBuilder.Append("('" + Guid.NewGuid().ToString() + "','" + "Fred" + k + "','" + k + "'),");
                        k++;
                    }
                    using (MySqlCommand cmd = new MySqlCommand())
                    {
                        cmd.Connection = conn;
                        cmd.CommandTimeout = 0;
                        string insertSQL = sqlBuilder.ToString();
                        int lastCommaIndex = insertSQL.LastIndexOf(',');
                        cmd.CommandText = insertSQL.Remove(lastCommaIndex);
                        insertedRows = cmd.ExecuteNonQuery();                        
                    }
                    sw.Stop();
                    string msg = $"{DateTime.Now.ToString("yyyyMMddHHmmssffff")},time cost:{sw.ElapsedMilliseconds},insertedRows:{insertedRows},loop:{i}";
                    File.AppendAllText("Time6.txt", msg + Environment.NewLine + Environment.NewLine);
                    Console.WriteLine(msg);
                }
            }
        }
    }
}
复制代码

 

posted @   FredGrit  阅读(153)  评论(0编辑  收藏  举报
编辑推荐:
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
阅读排行:
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
历史上的今天:
2019-11-25 C# socket
点击右上角即可分享
微信分享提示