go---修改数据库数据

1.需求说明:

由于其他原因，导致数据库中有一段时间的数据异常了，而不想删掉此数据，需要修改相关字段的数据内容；

2.思路说明

a.由于此字段的数据内容的格式是：时间,值;…;时间,值;
b.所以先从数据库查询拿到数据
c.然后先按分号(;)拆成切片，再对每个元素按逗号(,)拆成最小单元，修改其中的值内容；
d.将改完后的值内容拼接回去；
e.更新数据库

3.sql语句

a.查询语句

select write_time,position_data,avg_distance,max_distance,min_distance from his_draw_position where vehicle_id=1121 and write_time>'2018-12-14 13:00:23' and write_time<'2018-12-15 02:23:50';

b.更新语句

update his_draw_position set position_data= NEW_VALUE1, avg_distance = NEW_VALUE2,max_ditance=NEW_VALUE3,min_distance=NEW_VALUE3
where vehilce_id=1121 and write_time=QUERY_VALUE;

4.代码

main.go

package main

import (
	"fmt"
	"time"
)

func main() {
	UpdateData()
}

modifyDbData.go

package main

import (
	db "ImportData/database"
	"fmt"
	"log"
	"strconv"
	"strings"
)

//查询数据基本结构体
type HisDrawPosition struct {
	WriteTime    string
	PositionData string
	AvgDistance  float64
	MaxDistance  float64
	MinDistance  float64
}

func UpdateData() {
	db.OpenDB()

	var queryDatas = make([]HisDrawPosition, 0)

	querySql := "select write_time,position_data,avg_distance,max_distance,min_distance from his_draw_position where vehicle_id=1121 and write_time>'2018-12-14 13:00:23' and write_time<'2018-12-15 02:23:50';"
	rows := db.Query(querySql)
	counts := 0
	for rows.Next() {
		var writeTime string
		var positionData string
		var avgDistance float64
		var maxDistance float64
		var minDistance float64

		err := rows.Scan(&writeTime, &positionData, &avgDistance, &maxDistance, &minDistance)
		if err != nil {
			log.Println(err)
		}

		var hdp HisDrawPosition = HisDrawPosition{WriteTime: writeTime, PositionData: positionData, AvgDistance: avgDistance, MaxDistance: maxDistance, MinDistance: minDistance}

		queryDatas = append(queryDatas, hdp)
		counts += 1
	}
	log.Printf("records: %d, len: %d.\n", counts, len(queryDatas))
	modifyData(&queryDatas)
	importDataToDb(&queryDatas)
	db.Close()
}

func modifyData(datas *[]HisDrawPosition) {
	diffValue := 8979.6771
	for k, v := range *datas {
		oldPositionData := v.PositionData
		var newPositionData string
		newPositionData = ""
		counts := 1.0
		newMaxDistance := 0.0
		newMinDistance := 999999.0
		totalDistance := 0.0
		meta := strings.Split(oldPositionData, ";")
		for _, mv := range meta {
			if len(mv) < 2 {
				continue
			}
			m := strings.Split(mv, ",")
			value, _ := strconv.ParseFloat(m[1], 64)
			if value > 9000 {
				value = value - diffValue
			}
			if value > newMaxDistance {
				newMaxDistance = value
			}
			if value < newMinDistance {
				newMinDistance = value
			}

			totalDistance += value
			counts += 1
			newPositionData += fmt.Sprintf("%s,%.4f;", m[0], value)
		}
		(*datas)[k].PositionData = newPositionData
		(*datas)[k].AvgDistance = totalDistance / counts
		(*datas)[k].MaxDistance = newMaxDistance
		(*datas)[k].MinDistance = newMinDistance
	}
}

func importDataToDb(datas *[]HisDrawPosition) {
	for _, v := range *datas {
		updateSql := fmt.Sprintf("update his_draw_position set position_data= '%s', avg_distance = %.4f,max_distance=%.4f,min_distance=%.4f where vehicle_id=1121 and write_time='%s'",
			v.PositionData,
			v.AvgDistance,
			v.MaxDistance,
			v.MinDistance,
			v.WriteTime)
		db.Query(updateSql)
	}
}

5.结论

1.经测试，完成了其中his_draw_position内容的修改。
2.其中importdata/database中放的是个人简单封装操作sql的内容；