go mysql 字段类型_记golang读取数据库mysql, 字段类型导致，诡异事件

读取一条SQL

SELECT

ufu.userid,

(CASE WHEN ttt.after_amount IS NULL THEN 0

ELSE ttt.after_amount END) AS after_amount,

(CASE WHEN payed.realpay_amount IS NULL THEN 0

ELSE payed.realpay_amount END) AS realpay_amount,

(CASE WHEN payed.pb_amount IS NULL THEN 0

ELSE payed.pb_amount AS signed END) as pb_amount

FROM

ux_first_user ufu

LEFT JOIN t_xp_users xu ON xu.id = ufu.userid

LEFT JOIN t_tmp_trade_tecord ttt ON xu.pbaccountid = ttt.accountid

LEFT JOIN (

SELECT

fo.user_id as user_id,

sum( fo.online_pay_amount ) as realpay_amount,

sum( fo.pay_wallet )as pb_amount

FROM

t_final_order fo

WHERE

fo.paystate = 'p201'

GROUP BY

fo.user_id

) payed ON ufu.userid = payed.user_id WHERE 1=1

AND ufu.bis_date >= '2019-07-01 00:00:00'

AND ufu.bis_date <= '2020-04-30 23:59:59'

本身 t_final_order 表中的 online_pay_amount，pay_wallet字段都是varchar类型

但数据已经保证是int数据，(就是没有null或empty数据)

使用golang查询以上sql，在读取时使用的是int类型接收

defer rows.Close()

for rows.Next() {

var v_userid string

var v_after_amount int

var v_realpay_amount int

var v_pb_amount int

rows.Scan(&v_userid, &v_after_amount, &v_realpay_amount, &v_pb_amount)

最终结构发现部分数据，本身有值，但也记为了0，查找问题无果的情况下，使用 string类型接受数据，发现，部分数据， 给出了一个30293+e..类似科学技术法的串，但通过工具查看，是正常的一个数(数并没要超过Int类型长度)

最终解决办法，修改sql，强行转换类型

SELECT

ufu.userid,

(CASE WHEN ttt.after_amount IS NULL THEN 0

ELSE CAST(ttt.after_amount AS signed) END) AS after_amount,

(CASE WHEN payed.realpay_amount IS NULL THEN 0

ELSE CAST(payed.realpay_amount AS signed) END) AS realpay_amount,

(CASE WHEN payed.pb_amount IS NULL THEN 0

ELSE CAST(payed.pb_amount AS signed) END) as pb_amount

FROM

完美解决。

实际情况是

WHEREAND ufu.bis_date >= ?

AND ufu.bis_date <= ?

原web项目是通过传递时间，bindvalue进行查询的！(出现问题)

项目代码通过console方式，where条件直接写死，查询(又没有问题)

总之，复制查询， sql最好确定类型。避免不必要的麻烦。