使用Golang实现对mysql数据库批量插入随机数据

      修改执行以下程序：只需修改DoInsert函数中的

    InsertBuf += fmt.Sprintf( " (%d, '%s','%s',%f, %d, '%s') ", id, name, birthday,salary,distence,description)中的格式及相关数据即可

      无论linux还是Windows环境，解压修改程序后直接运行即可。

 

例子：

建表语句：

CREATE DATABASE db1;

CREATE TABLE `db1.t1` (
  `id` int DEFAULT NULL,
  `name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `salary` double DEFAULT NULL,	
  `distence` mediumint DEFAULT NULL,
  `description` longtext
);

程序：

package main

import (
	"database/sql"
	"fmt"
	"math/rand"
	"strconv"
	"strings"
	"time"
	_ "github.com/go-sql-driver/mysql"
)

//mysql连接信息配置： 用户名、密码、ip、端口、库名、表名
const (
	strUserName = "root"
	strPassword = "root123"
	strIP = "127.0.0.1"
	strPort = "3306"
	strDBName = "db1"
	strTableName = "t1"
)

//插入数据量配置
const(
	TOTAL_INSERT_NUM =1200000	//共插入多少行数据
	PER_INSERT_NUM=5000		//单次向mysql插入多少行数据
	MAX_FAILNUM=10			//最大容许插入失败次数
)

//mysql整型范围 供预置数据范围使用
const (
	BIGINT_MIN = -9223372036854775808
	BIGINT_MAX = 9223372036854775807

	INT_MIN = -2147483648
	INT_MAX = 2147483647

	MEDIUMINT_MIN = -8388608
	MEDIUMINT_MAX = 8388607

	SMALLINT_MIN = -32768
	SMALLINT_MAX = 32767

	TINYINT_MIN = -128
	TINYINT_MAX = 127
)

//随机字符串种子
const STRCHAR = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789!#$%&*+-./:;<=>?@[]^_{|}~"
var CITYS = []string {"ChengDu", "KunMing", "XiAn", "LaSa", "JiNan", "NanJing", "HangZhou", "FuZhou", "GuangZhou",
	"HaiKou", "HaErBin", "ChangChun", "ShenYang", "ZhengZhou", "HeFei", "WuHan", "ChongQing", "BeiJing", "ShangHai"}

//随机生成一个整型数据
func MakeRandInt(min, max int64) int64 {
	rand.Seed(time.Now().UnixNano())
	return min + rand.Int63n(max-min)
}

//随机生成一个浮点型数据
func MakeRandFloat(base int64) float32{
	return rand.Float32() * float32(base)
}

//随机生成一个双精度浮点型数据
func MakeRandDouble(base int64) float64{
	return rand.Float64() * float64(base)
}

//随机生成一个汉字字符串  入参：字符串长度
func MakeChineseString(length int) string{
	a:=make([]rune,length)
	for i:=range a {
		a[i]=rune( MakeRandInt(19968,40869))
	}
	return string(a)
}

//随机生成一个字符串（指定字符种子） 入参：（字符串长度，长度是否随机）
func MakeRandString(length int64, bRegular bool) string {
	var size int64
	if bRegular{
		size = length
	}else{
		size = MakeRandInt(1,length)
	}

	str := make([]byte, size)
	for i:=0; i< int(size); i++{
		index :=  MakeRandInt(0, int64(len(STRCHAR)))
		str[i] = STRCHAR[index]
	}
	return string(str)
}

//随机生成一个字符串（任意字符） 入参：字符串长度
func MakeRandString2(length int) string {
	str := make([]byte, length)
	for i:=0; i MAX_FAILNUM){
					return
				}

				InsertBuf = strInsert
				continue
			}

			//重新初始化insert插入语句
			InsertBuf = strInsert
			CurTime := time.Now().Unix()
			fmt.Printf("complete:[%d]   failnum:[%d]  consume:[%ds]\n"  , i, failnum*PER_INSERT_NUM, CurTime-startTime)
		}else{
			InsertBuf += ","
		}
	}

	endTime := time.Now().Unix()
	fmt.Printf("finished: totalnum:[%d]  consum:[%d]s\n", TOTAL_INSERT_NUM, endTime-startTime)
}

func main(){
	dbConn := InitDB()

	DoInsert(dbConn)
}

运行结果：

      程序性能一般，使用了批量插入，没有使用多连接并发，大概每秒可插入1000多行，功能也很少，勉强可以使用，欢迎随意优化修改。