需要用到根据唯一索引来进行批量更新和插入,这里利用ON DUPLICATE KEY UPDATE 来进行更新,遇到的问题:
1)因为刚接触go 不太清楚如何将切片转换为字符串,所以自己写了一个 方法 MapTranslateString
2)因为刚接触go 不太清楚orm的 sql的占位符除了‘?’之外还能是什么,导致打印出来的sql语句一堆问号,如果有错误也不好排查
// o orm 对象, dataList 需要传入参数 fieldList dataList 的 map的字符串键值 (更新的字段),tableName 表名称 ,size 一次插入长度func (oh *OrmHelper) OnDuplicateKeyUpdate(O orm.Ormer, dataList []map[string]interface{}, fieldList []string, tableName string, size int) { if size < 100 { size = 100 } tempList := dataList for dataList != nil { if len(dataList) > size { tempList = dataList[:size] temp := append(dataList[:0], dataList[size:]...) dataList = temp } else { tempList = dataList dataList = nil } sql := "INSERT INTO " + tableName + "(" + MapTranslateString(fieldList, ",") + ") VALUES" rawInterface := make([]interface{}, 0) for _, data := range tempList { var rawInterfaceTemp = make([]interface{}, len(fieldList)) if data != nil { sql += "(" var i = 0 for _, field := range fieldList { if data[field] == nil { panic("批量插入失败!传入的数据中不存在字段" + field) } sql += "?," rawInterfaceTemp[i] = data[field] i++ } rawInterface = append(rawInterface, rawInterfaceTemp) sql = string([]byte(sql[:len(sql)-1])) + ")," } } sql = string([]byte(sql[:len(sql)-1])) sql += " ON DUPLICATE KEY UPDATE " for _, field := range fieldList { sql += "`" + field + "`=values(`" + field + "`)," } sql = string([]byte(sql[:len(sql)-1])) _, err := O.Raw(sql, rawInterface).Exec() if err != nil { panic(err) } }} func MapTranslateString(mapString []string, op string) string { str := "" length := len(mapString) if length > 0 { for i, value := range mapString { if i == 0 { str += value } else if i < length { str += op + value } } } return str}
文章来源:智云一二三科技
文章标题:beego orm 批量插入并更新
文章地址:https://www.zhihuclub.com/6969.shtml