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的内容;