GO语言生成批量插入数据SQL语句算法

4,996 阅读2分钟

完整代码

package main

import "fmt"

// Point 点名
type Point struct {
	PointID    uint64  `json:"pointID"`
	PathName   string  `json:"pathName"`
	PointValue float32 `json:"pointValue"`
	StoreTime  string  `json:"storeTime"`
}

// GetSQLList 数据量太大分成多个sql语句并返回多个sql语句的切片
// groupSize 按照个人需求进行变更,代表 每groupSize个记录合并成1条INSERT语句
func GetSQList(pointList []Point, groupSize int) []string {
	const insertHeader string = "INSERT INTO `local_data`(`point_id`,`path_name`,`point_value`,`store_time`) VALUES"
	sqlList := []string{}
	sql := ""
	for i := 0; i < len(pointList); i++ {
		if i%groupSize == 0 {
			if sql != "" {
				//把上次拼接的SQL结果存储起来
				sqlList = append(sqlList, sql)
			}
			//重置SQL
			sql = insertHeader
		}
		if sql != insertHeader {
			sql = sql + ","
		}
		sql = fmt.Sprintf("%s(%d,'%s',%f,'%s')",
                        sql,
			pointList[i].PointID,
			pointList[i].PathName,
			pointList[i].PointValue,
			pointList[i].StoreTime,
		)
	}

	//把最后一次生成的SQL存储起来

	sqlList = append(sqlList, sql)
	return sqlList
}

func main() {

	points := []Point{
		{1, "p1", 3.14, "2013-11-10 12:12:00"},
		{2, "p1", 3.14, "2013-12-10 12:12:00"},
		{3, "p1", 3.14, "2013-11-10 12:12:00"},
		{4, "p1", 3.14, "2013-12-10 12:12:00"},
		{5, "p1", 3.14, "2013-12-10 12:12:00"},
		{6, "p1", 3.14, "2013-11-10 12:12:00"},
		{7, "p1", 3.14, "2013-11-10 12:12:00"},
		{8, "p1", 3.14, "2013-12-10 12:12:00"},
		{9, "p1", 3.14, "2013-12-10 12:12:00"},
		{10, "p1", 3.14, "2013-11-12 12:12:00"},
		{11, "p1", 3.14, "2013-11-12 12:12:00"},
		{12, "p1", 3.14, "2013-11-12 12:12:00"},
		{13, "p1", 3.14, "2013-12-12 12:12:00"},
		{14, "p1", 3.14, "2013-11-12 12:12:00"},
		{15, "p1", 3.14, "2013-11-12 12:12:00"},
		{16, "p1", 3.14, "2013-12-12 12:12:00"},
		{17, "p1", 3.14, "2013-11-12 12:12:00"},
	}
	sqls := GetSQList(points, 5)
	for _, s := range sqls {
		fmt.Println(s)
	}
}