go语言 gorm 查询结果内容全是空字符串的解决方案

对go语言不熟悉, 执行mysql查询, 发现返回结果是有记录的,跟sql执行的条数相同. 但是字段对应都是空字符串.

后来看了官方文档
http://gorm.book.jasperxu.com/models.html#c

原来, gorm默认给按照约定的名字改名后才能对上号.
例如: 我结构体中叫 BaseKey 对应的sql字段必须是 base_key 否则就不会填值进去.

package main

/**
Author:郑桂良
Date:20201216
*/

import (
	"fmt"

	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
) 
// 演示环境
const (
	mysql_user   = "**"
	mysql_passwd = "**"
	mysql_ip     = "**"
	mysql_port   = "3306"
	mysql_robot  = "robot"
	//mysql_qyDB   = "**"
	mysql_qyDB = "***"
)

type DTOBaseModel struct {
	BaseKey    string  `json:"BaseKey"`
	AddrHuHao  string  `json:"AddrHuHao"`  //#户号(身份ID,卡号)
	AddrSheng  string  `json:"AddrSheng"`  //= "浙江省" #省份
	AddrShi    string  `json:"AddrShi"`    //= "宁波市" #城市
	AddrQu     string  `json:"AddrQu"`     //= "鄞州区" #区域
	AddrJieDao string  `json:"AddrJieDao"` //= "姜山镇" #街道
	AddrXiaoQu string  `json:"AddrXiaoQu"` //= "望府" #小区名
	TanJiFen   float32 `json:"TanJiFen"`   //= "80"  #碳积分
	Timestamp  int64   `json:"Timestamp"`  //更新时间戳(数据版本)

	JiLuID       string `json:"JiLuID"`       //= "1" #记录ID用来在WCMS中查具体的投递内容
	TouDiWu      string `json:"TouDiWu"`      //= "馒头:20" #投递物
	TouDiShiJian string `json:"TouDiShiJian"` //= "2020-08-19 15:13" #最后投递时间
	ZhongLiang   int    `json:"ZhongLiang"`   //= "2"#重量(克)
}

// 查询审核通过但是没有上传到BSN的投递记录
// 代码参考 https://www.cnblogs.com/zhangweizhong/p/10794646.html
func QueryNotUp2BSNRecord(quitionID string) []DTOBaseModel {
	//打开数据库
	db, errOpen := gorm.Open("mysql", mysql_user+":"+mysql_passwd+"@tcp("+mysql_ip+":"+mysql_port+")/"+mysql_qyDB+"?charset=utf8&parseTime=True&loc=Local")
	defer db.Close()

	// 启用Logger，显示详细日志
	db.LogMode(true)

	if errOpen != nil {
		//TODO，这里只是打印了一下，并没有做异常处理
		fmt.Println("Mysql Open is error")
		return nil
	}

	// 创建表
	// if !db.HasTable(&DTOBaseModel{}) {
	// 	if err := db.Set("gorm:table_options", "ENGINE=InnoDB DEFAULT CHARSET=utf8").CreateTable(&DTOBaseModel{}).Error; err != nil {
	// 		fmt.Println(err)
	// 	}
	// }

	var records = []DTOBaseModel{} 
	sql := "SELECT " +
		"\n r.`card_code` as base_key, " +
		"\n r.`card_code` as addr_hu_hao, " +
		"\n p.`province` as addr_sheng, " +
		"\n c.`city` as addr_shi, " +
		"\n a.`area` as addr_qu, " +
		"\n s.`name` as addr_jie_dao, " +
		"\n q.`name` as addr_xiao_qu, " +
		"\n r.`ai` as tan_ji_fen, " +
		"\n 0 as timestamp, " +
		"\n CAST(r.`record_id` as CHAR) as ji_lu_id, " +
		"\n r.`AiDiscernGoods` as tou_di_wu, " +
		"\n r.addtime as tou_di_shi_jian, " +
		"\n 500 as zhong_liang " +
		"\n FROM `record` r" +
		"\n	left join `provinces` p on p.provinceid= r.`provinceid` " +
		"\n	left join `citys` c on c.cityid= r.`cityid` " +
		"\n	left join `areas` a on a.areaid= r.`areaid` " +
		"\n	left join `street` s on s.id= r.`streetid` " +
		"\n	left join `quarters` q on q.id= r.`quartersid` " +
		"\n where " +
		"\n	`ai` is not null " +
		"\n	and `is_up_to_BSN` =0 " +
		"\n	and `status` in (1,4) " +
		"\n limit 100 "
	fmt.Println(sql)
	err := db.Raw(sql).Scan(&records).Error
	if err != nil {
		//TODO，这里只是打印了一下，并没有做异常处理
		fmt.Printf("QueryNotUp2BSNRecord 查询表记录失败 is error%#v\n", err)
	} 
	//关闭数据库 
	return records
}

func main() {
	//此示例传入一个参数，返回两个参数
	var qID = "L400000006"
	//读取mysql指定表的指定字段内容
	records := QueryNotUp2BSNRecord(qID)
	//读取mysql成功
	fmt.Printf("%#v\n", records)
}