mysql - sql:列索引 19 上的扫描错误,名称“L2Name”:不支持扫描,存储驱动程序。值类型输入 *string
问题描述
使用 Golang 和内置的 database/sql 库和 postgres lib/pq 库,我试图从在某些记录中具有一些空值的数据库中读取。代码可以编译,但是当我尝试运行它时,出现以下错误:
sql: Scan error on column index 19, name "L2Name": unsupported Scan, storing driver.Value type <nil> into type *string
我有这样的结构:
// Assets Info Dataset
type AssetInfo struct {
Asset_id string
Asset_name string
Organisation_id string
LastCheckIn string
Asset_Status string
Asset_latitude string
Asset_longitude string
Organisation_name string
CurrentDevice_name string
AssetActiveDeviceType string
AssetSafetyTimer float32
TemplateName string
TemplateL2name string
TemplateL2contact string
TemplateL3name string
TemplateL3contact string
TemplateL4name string
TemplateL4contact string
TemplateEscalationNotes string
}
这是我的代码:
db, err := sql.Open("mysql", "stevejc:19939c@tcp(127.0.0.1:3306)/johntcw_loneworker?charset=utf8")
checkErr(err)
defer db.Close()
for {
sqlstatement := "SELECT" +
" assets.ID, assets.Name, assets.LastCheckIn, assets.Status, assets.OffTimer," +
" assets.SafetyTimer, assets.HazardTimer, assets.HazardTimerStartedTime, assets.LastSignedOn," +
" assets.Latitude, assets.Longitude, assets.TemplateID, assets.ActiveDeviceType, assets.CurrentDeviceID," +
" assets.OffTimerTemp, assets.OrganisationID," +
" organisations.Name As OrganisationName," +
" devices.Label As CurrentDeviceName," +
" templates.Name As TemplateName, templates.L2Name, templates.L2Contact, templates.L3Name, templates.L3Contact," +
" templates.L4Name, templates.L4Contact, templates.Note" +
" FROM assets" +
" LEFT JOIN organisations ON assets.OrganisationID = organisations.ID" +
" LEFT JOIN devices ON assets.CurrentDeviceID = devices.ID" +
" JOIN templates ON assets.TemplateID = templates.ID" +
" WHERE assets.Status != 'Not monitoring' AND assets.AssetStatus = 'Active' AND assets.Display != '0'"
// select monitoring assets
rows, err := db.Query(sqlstatement)
checkErr(err)
for rows.Next() {
var assetid string
var name string
var lastcheckin string
var status string
var offtimer float32
var offtimertemp float32
var safetytimer float32
var hazardtimer float32
var hazardstarttime string
var lastsignedon string
var lat string
var lon string
var templateid string
var activedevicetype string
var currentdeviceid string
var organisationid string
var organisationname string
var currentdevicename string
var templatename string
var l2name string
var l2contact string
var l3name string
var l3contact string
var l4name string
var l4contact string
var escalationnotes string
err = rows.Scan(&assetid,
&name,
&lastcheckin,
&status,
&offtimer,
&safetytimer,
&hazardtimer,
&hazardstarttime,
&lastsignedon,
&lat,
&lon,
&templateid,
&activedevicetype,
¤tdeviceid,
&offtimertemp,
&organisationid,
&organisationname,
¤tdevicename,
&templatename,
&l2name,
&l2contact,
&l3name,
&l3contact,
&l4name,
&l4contact,
&escalationnotes)
checkErr(err)
assetinfo := new(AssetInfo)
assetinfo.Asset_id = assetid
assetinfo.Asset_name = name
assetinfo.LastCheckIn = lastcheckin
assetinfo.Asset_Status = status
assetinfo.Organisation_id = organisationid
assetinfo.Asset_longitude = lon
assetinfo.Asset_latitude = lat
assetinfo.Organisation_name = organisationname
assetinfo.CurrentDevice_name = currentdevicename
assetinfo.AssetActiveDeviceType = activedevicetype
assetinfo.AssetSafetyTimer = safetytimer
assetinfo.TemplateName = templatename
assetinfo.TemplateL2name = l2name
assetinfo.TemplateL2contact = l2contact
assetinfo.TemplateL3name = l3name
assetinfo.TemplateL3contact = l3contact
assetinfo.TemplateL4name = l4name
assetinfo.TemplateL4contact = l4contact
assetinfo.TemplateEscalationNotes = escalationnotes
代码的输出是ok的,我可以从数据库中得到我想要的所有数据。除了 SQL 错误也会打印在控制台上。并且这里的 L2Name 不是空值,我可以在控制台上打印该值。所以不知道为什么显示类型的错误?
解决方案
最简单的解决方法是使用 COALESCE(templates.L2Name, '') 将可以为空的 cols 包装在您的 sql 语句中,感谢@pmk 的帮助。
sqlstatement := "SELECT" +
" assets.ID, assets.Name, assets.LastCheckIn, assets.Status, assets.OffTimer," +
" assets.SafetyTimer, assets.HazardTimer, assets.HazardTimerStartedTime, assets.LastSignedOn," +
" assets.Latitude, assets.Longitude, assets.TemplateID, assets.ActiveDeviceType, assets.CurrentDeviceID," +
" assets.OffTimerTemp, assets.OrganisationID," +
" organisations.Name As OrganisationName," +
" devices.Label As CurrentDeviceName," +
" templates.Name As TemplateName, COALESCE(templates.L2Name, ''), COALESCE(templates.L2Contact, '')," +
" COALESCE(templates.L3Name, ''), COALESCE(templates.L3Contact, '')," +
" COALESCE(templates.L4Name, ''), COALESCE(templates.L4Contact, ''), templates.Note" +
" FROM assets" +
" LEFT JOIN organisations ON assets.OrganisationID = organisations.ID" +
" LEFT JOIN devices ON assets.CurrentDeviceID = devices.ID" +
" JOIN templates ON assets.TemplateID = templates.ID" +
" WHERE assets.Status != 'Not monitoring' AND assets.AssetStatus = 'Active' AND assets.Display != '0'"
推荐阅读
- node.js - 使用 Node.js 从连接的 FTP 下载文件
- vba - Excel VBA - 单击页面上的 Javascript 按钮
- python - 如何返回在 Pandas 数据框中共享命令值的组?
- css - 如何使用 CSS 将不同 div 行中的所有项目(图像、标题、文本、按钮)对齐?
- webgl - 在片段着色器中绘制椭圆曲线
- reporting-services - SSRS - 柱形图 - 标签样式
- r - R 代码 - 应用日期格式后返回 NULL 的日期
- javascript - 避免在 TypeScript / JavaScript 代码中删除“forEach”、“filter”等的换行符
- java - 从字符串模式中提取数字
- c++ - Boost Graph Library astar_search 用于隐式图