Golang操作MySQL

操作mysql常用的库有github.com/astaxie/beego/orm和database/sql
orm库支持映射数据结构到表结构，标准化增删改查操作，多用于单表的操作；同时支持原生sql，多用于复杂的多表查询操作，sql库也多用于此场景，有点是灵活，但缺少标准化；
关于orm更多使用场景个人感觉不错的参考文档有：

• GoWeb开发实战(Beego框架实现项目)

记录一下项目开发中用到原生sql的案例
1、查询操作返回多行，并处理返回数据，构建结果集列表
orm 包操作方式

QueryHostInfo() (HostListInfoMap, error) {
    sql := fmt.Sprintf("select ip,mem_total,v_max memory_used_percent,cpu_max_usage,count(*) cnt,sum(mem) mem,sum(t2.cluster_host_avg_storage) host_avg_storage,sum(cnt) redis_cnt,any_value(department) department  from (select hm.v_max mem_total,h.v_max,100-hc.v_min cpu_max_usage ,i.department,h.ip,r.cluster_name,round(sum(memory_size),2) mem,count(*) cnt,ceil(c.storage*2/t.host_cnt) cluster_host_avg_storage    from host_statistic h    join host_statistic hc on h.ip=hc.ip and hc.metric_name='CPU_IDLE' join host_statistic hm on h.ip=hm.ip and hm.metric_name='MEM_TOTAL' left join redis_instance r on r.ip=h.ip left join cluster_order_info c on r.cluster_name=c.cluster_name  left join cluster_info i on r.cluster_name=i.cluster_name  left join (select cluster_name,count(distinct(ip)) host_cnt from redis_instance group by cluster_name)t on t.cluster_name=r.cluster_name where h.metric_name='MEM_USED_PERCENT' group by ip,cluster_name,department,storage order by ip,mem)t2  where %s %s group by ip order by host_avg_storage,mem,cnt;", departCon, hostTagCon)
	var maps []orm.Params
	ptrOrmer = orm.NewOrm()
	rawSet := ptrOrmer.Raw(sql, args)
	defer DoDaoException(rawSet)

	// query all data and map to []map[string]interface.
	retNum, err := rawSet.Values(rst)
	if err != nil {
		common.Log.Warn("Execute query sql failed! Sql=[%v] args=[%v] Err=[%v]", sql, args, err)
	}
	// 处理查询返回的数据集，构建自定义结构体列表
	for _, row := range *maps {
		memTotal, _ := strconv.Atoi(row["mem_total"].(string))
		memUsedPercent, _ := strconv.Atoi(row["memory_used_percent"].(string))
		applyDataSize, _ := strconv.Atoi(row["host_avg_storage"].(string))
		redisRealMem, _ := strconv.Atoi(row["mem"].(string))
		redisRealMem = redisRealMem + 1 // 小数字符串转int会向下取整
		cpuMaxUsage, _ := strconv.Atoi(row["cpu_max_usage"].(string))
		serverCnt, _ := strconv.Atoi(row["cnt"].(string))
		//计算主机的avilibleName：主机的大小*50%-max(redisRealMem,applyDataSize)
		var MemCapacity int
		if applyDataSize >= redisRealMem {
			MemCapacity = (memTotal*HostMemAllocatePercent)/10 - applyDataSize
		} else {
			MemCapacity = (memTotal*HostMemAllocatePercent)/10 - redisRealMem
		}
		ip := row["ip"].(string) //类型断言
		//获取主机上redis agent port
		agentPort, err := GetAgentPort(ip)
		if err != nil {
			common.Log.Error("fail to get redis agent port,ip=[%s],err=[%v]", ip, err)
			return hostListInfoMap, err
		}
		// 获取主机上redis的最大端口,用于部署编排
		maxRedisPort, err := GetMaxRedisPort(ip)
		if err != nil {
			common.Log.Error("fail to get max redis port,ip=[%s],err=[%v]", ip, err)
			return hostListInfoMap, err
		}
		hostInfo := &HostInfo{
			IP:             ip,
			Tag:            tag,
			MemCapacity:    MemCapacity,
			MemTotal:       memTotal,
			MemUsePercent:  memUsedPercent,
			RedisDataSize:  redisRealMem,
			ApplyDataSize:  applyDataSize,
			CpuMaxUsage:    cpuMaxUsage,
			ServerCnt:      serverCnt,
			RedisAgentPort: agentPort,
			MaxRedisPort:   maxRedisPort,
		}
		hostListInfo = append(hostListInfo, hostInfo)
	}
	hostListInfo.SortByMemCapacity()
	hostListInfoMap[tag] = hostListInfo
	return hostListInfoMap, nil
}

sql 包操作方式

func GetTableDataStruct(id string) (dataStruct, error) {
	dataStruct := DataStruct{} //DataStruct 用于映射表数据的结构体
	db, err := sql.Open("mysql", common.Config.MySQL)
	if err != nil {
		log.Error(err.Error())
		return dataStruct, err
	}
	defer db.Close()

	sql := fmt.Sprintf("SELECT `id`,  `instance`,`hostname`, db.t1 WHERE id='%s'", id)

	rows, err := db.Query(sql)
	if err != nil {
		log.Error(err.Error())
		return alarm, err
	}
	for rows.Next() {
		rows.Scan(
			&alarm.Id,
			&alarm.Instance,
			&alarm.Hostname,
		)
	}
	return dataStruct, err
}