go 语言实现Mysql的批量DML，含insert、update、delete

习惯于java jdbc驱动中batch的操作方式，在go语言mysql开发中，缺少batch方式，每条Exec方式执行，性能极低。经测试 单条循环Exec，大约200条/秒，若包装成事务，最多提升到800条/秒,性能实在难以忍受，便自己封装实现类似的方式，经过测试，速度超过1w+/秒。并且支持多字段作为where条件（例如 联合主键）

依赖驱动: github.com/go-sql-driver/mysql@1.7.1

直接上源码，文末附有测试示例代码

Insert 操作

基本原理: 将加入批次的数据最终拼装为类似 insert into t_test1 (id,name,content) values (?,?,?), (?,?,?) ,(?,?,?)

type MysqlInsertBatch struct {
	sqlText string
	params  []any
	db      *sql.DB
	sqlItem string
	Cnt     int
}

func NewMysqlInsertBatch(db *sql.DB, sqlText string) *MysqlInsertBatch {
	return &MysqlInsertBatch{
		sqlText: sqlText,
		db:      db,
		params:  make([]any, 0),
	}
}
func (m *MysqlInsertBatch) AddBatch(param []any) {
	if m.sqlItem == "" {
		var s strings.Builder
		s.WriteString(",(")
		for i := 0; i < len(param); i++ {
			if i > 0 {
				s.WriteRune(',')
			}
			s.WriteRune('?')
		}
		s.WriteRune(')')
		m.sqlItem = s.String()
	}
	m.params = append(m.params, param...)
	m.Cnt++
}
func (m *MysqlInsertBatch) ExecuteBatch() {
	if m.Cnt == 0 {
		return
	}
	var sqlText string = m.sqlText
	if m.Cnt > 1 {
		var s strings.Builder
		s.WriteString(m.sqlText)
		for i := 1; i < m.Cnt; i++ {
			s.WriteString(m.sqlItem)
		}
		sqlText = s.String()
	}
	_, err := m.db.Exec(sqlText, m.params...)
	if err != nil {
		log.Errorf("写库异常:%v", err)
	}
}
func (m *MysqlInsertBatch) clearBatch() {
	m.params = make([]any, 0)
	m.Cnt = 0
}

Update操作:

基本原理: 将加入批次的数据最终拼装为类似

update t_test1 set content=case when id=? and name=? then ?  when id=? and name=? then ? end  where (id,name) in ((?,?),(?,?))  // where条件为多字段，测试示例是1个字段

type MysqlUpdateBatch struct {
	TableName    string
	WhereCols    []string
	SetCols      []string
	sqlText      string
	setSingleSql string
	setSql       []*strings.Builder
	whereItem    string
	whereSql     *strings.Builder
	setVals      [][]any
	params       []any
	db           *sql.DB
	Cnt          int
}

func NewMysqlUpdateBatch(db *sql.DB, tableName string, setCols []string, whereCols []string) (*MysqlUpdateBatch, error) {
	if setCols == nil || len(setCols) <= 0 || whereCols == nil || len(whereCols) <= 0 {
		return nil, errors.New("set字段或where字段均不能为空")
	}
	mysqlUpd := MysqlUpdateBatch{
		TableName: tableName,
		WhereCols: whereCols,
		SetCols:   setCols,
		params:    make([]any, 0),
		whereSql:  &strings.Builder{},
		db:        db,
	}
	var setSingle = &strings.Builder{}
	var whereItem = &strings.Builder{}
	for i := 0; i < len(whereCols); i++ {
		if i > 0 {
			setSingle.WriteString(" and ")
			whereItem.WriteRune(',')
		} else {
			setSingle.WriteString(" when ")
			whereItem.WriteRune('(')
		}
		setSingle.WriteString(whereCols[i])
		setSingle.WriteString("=?")

		whereItem.WriteRune('?')
	}
	setSingle.WriteString(" then ?")
	whereItem.WriteRune(')')
	mysqlUpd.setSingleSql = setSingle.String()
	mysqlUpd.whereItem = whereItem.String()
	mysqlUpd.sqlText = "update %s set %s where (%s) in (%s)"

	setSql := make([]*strings.Builder, 0, len(setCols))
	for i := 0; i < len(setCols); i++ {
		setSql = append(setSql, &strings.Builder{})
	}
	mysqlUpd.setSql = setSql
	mysqlUpd.setVals = make([][]any, len(mysqlUpd.SetCols), len(mysqlUpd.SetCols))
	for i := 0; i < len(mysqlUpd.SetCols); i++ {
		mysqlUpd.setVals[i] = make([]any, 0)
	}
	return &mysqlUpd, nil
}

func (mu *MysqlUpdateBatch) ClearBatch() {
	for i := 0; i < len(mu.setSql); i++ {
		mu.setSql[i].Reset()
	}
	mu.params = make([]any, 0)
	mu.setVals = make([][]any, len(mu.SetCols), len(mu.SetCols))
	for i := 0; i < len(mu.SetCols); i++ {
		mu.setVals[i] = make([]any, 0)
	}
	mu.whereSql.Reset()
	mu.Cnt = 0
}

func (mu *MysqlUpdateBatch) AddBatch(setVals []any, params []any) {
	if mu.Cnt > 0 {
		mu.whereSql.WriteString(",")
	}
	mu.Cnt++
	mu.whereSql.WriteString(mu.whereItem)

	for i := 0; i < len(mu.SetCols); i++ {
		mu.setVals[i] = append(mu.setVals[i], params...)
		mu.setVals[i] = append(mu.setVals[i], setVals[i])
		mu.setSql[i].WriteString(mu.setSingleSql)
	}
	mu.params = append(mu.params, params...)
}
func (mu *MysqlUpdateBatch) ExecuteBatch() {
	if mu.Cnt == 0 {
		return
	}
	var sqlText string = mu.toSql()
	var vals []any
	for i := 0; i < len(mu.SetCols); i++ {
		vals = append(vals, mu.setVals[i]...)
	}
	vals = append(vals, mu.params...)
	//fmt.Println(mu.toSql())
	//fmt.Printf("%v\n", vals)
	_, err := mu.db.Exec(sqlText, vals...)
	if err != nil {
		log.Errorf("写库异常:%v", err)
	}
}
func (mu *MysqlUpdateBatch) toSql() string {
	var setSql strings.Builder
	for idx, col := range mu.SetCols {
		if idx > 0 {
			setSql.WriteRune(',')
		}
		setSql.WriteString(col)
		setSql.WriteRune('=')
		setSql.WriteString(" case ")
		setSql.WriteString(mu.setSql[idx].String())
		setSql.WriteString(" end ")
	}
	sqlStr := fmt.Sprintf(mu.sqlText, mu.TableName, setSql.String(), strings.Join(mu.WhereCols, ","), mu.whereSql.String())
	return sqlStr
}

Delete操作:

基本原理: 将加入批次的数据最终拼装为类似

delete from t_test1 where (id,name) in ((?,?),(?,?))  // where条件为多字段，测试示例是1个字段

type MysqlDeleteBatch struct {
	tableName string
	whereCols []string
	params    []any
	db        *sql.DB
	sqlText   string
	sqlItem   string
	Cnt       int
}

// NewMysqlDeleteBatch 批量删除数据
func NewMysqlDeleteBatch(db *sql.DB, tableName string, whereCols []string) (*MysqlDeleteBatch, error) {
	if whereCols == nil || len(whereCols) <= 0 {
		return nil, errors.New("set字段或where字段均不能为空")
	}
	md := MysqlDeleteBatch{
		tableName: tableName,
		whereCols: whereCols,
		params:    make([]any, 0),
		db:        db,
	}
	var whereItem = &strings.Builder{}
	whereItem.WriteRune('(')
	for i := 0; i < len(whereCols); i++ {
		if i > 0 {
			whereItem.WriteRune(',')
		}
		whereItem.WriteRune('?')
	}
	whereItem.WriteRune(')')
	md.sqlItem = whereItem.String()
	var deleteSql strings.Builder
	deleteSql.WriteString("delete from ")
	deleteSql.WriteString(md.tableName)
	deleteSql.WriteString(" where (")
	deleteSql.WriteString(strings.Join(whereCols, ","))
	deleteSql.WriteString(") in (")
	md.sqlText = deleteSql.String()
	return &md, nil
}

func (md *MysqlDeleteBatch) ClearBatch() {
	md.params = make([]any, 0)
	md.Cnt = 0

}

func (md *MysqlDeleteBatch) AddBatch(params []any) {
	md.Cnt++
	md.params = append(md.params, params...)
}
func (md *MysqlDeleteBatch) ExecuteBatch() {
	if md.Cnt == 0 {
		return
	}
	var deleteSql strings.Builder
	deleteSql.WriteString(md.sqlText)

	for i := 0; i < md.Cnt; i++ {
		if i > 0 {
			deleteSql.WriteRune(',')
		}
		deleteSql.WriteString(md.sqlItem)
	}
	deleteSql.WriteRune(')')
	//fmt.Println(deleteSql.String())
	//fmt.Printf("%v\n", md.params)
	_, err := md.db.Exec(deleteSql.String(), md.params...)
	if err != nil {
		log.Errorf("写库异常:%v", err)
	}
}

Test示例:

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"os"
	"time"
	"yunhuan.com/go/YhCommon.git/yhdb"
)
// yhdb是放置组件的package，可自行修改

func main() {
	dsn := "yansp:****@tcp(192.168.1.*:3306)/****?multiStatements=true"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		os.Exit(1)
	}
	defer db.Close()
	/**
	  create table t_test1 (
		id int,
		name varchar(32),
		content varchar(32),
		primary key (id)
		);
	*/
	testInsert(db)
	testUpd(db)
	testDel(db)
}

func testInsert(db *sql.DB) {
	num := 50000
	batchNum := 1000
	preSql := "insert into t_test1 values (?,?,?)"
	tm := time.Now()
	batchInsert := yhdb.NewMysqlInsertBatch(db, preSql)
	for i := 0; i < num; i++ {
		var param []any
		param = append(param, i)
		param = append(param, "sdsdsd")
		param = append(param, "sdsdsd2")
		batchInsert.AddBatch(param)
		if batchInsert.Cnt >= batchNum {
			batchInsert.ExecuteBatch()
			batchInsert.ClearBatch()
		}
	}
	if batchInsert.Cnt > 0 {
		batchInsert.ExecuteBatch()
	}
	dm := time.Since(tm).Milliseconds()
	fmt.Printf("耗时:%d,速度:%d", dm, int64(num)*1000/dm)

}
func testUpd(db *sql.DB) {
	whereCols := []string{"id"}
	setCols := []string{"name", "content"}
	mu, _ := yhdb.NewMysqlUpdateBatch(db, "t_test1", setCols, whereCols)
	setVals := []any{"asd1", "fv1"}
	whereVals := []any{1}
	mu.AddBatch(setVals, whereVals)
	setVals = []any{"asd2", "fv2"}
	whereVals = []any{2}
	mu.AddBatch(setVals, whereVals)
	mu.ExecuteBatch()
}

func testDel(db *sql.DB) {
	whereCols := []string{"id"}
	md, _ := yhdb.NewMysqlDeleteBatch(db, "t_test1", whereCols)
	whereVals := []any{1}
	md.AddBatch(whereVals)
	whereVals = []any{2}
	md.AddBatch(whereVals)
	md.ExecuteBatch()
}