英文:
MySQL NULL value issue on SelectInt gorp
问题
我正在尝试使用gorp库和MySQL运行以下查询：
query := "SELECT SUM(outputoctets) FROM monthlyacct WHERE date >= ? AND date < ? AND location = ?"
count, err := dbMap.SelectInt(query, constrains.From, constrains.To, constrains.LocationId)
这个查询会抛出以下错误：
在列索引0上扫描错误：将字符串"\u003cnil\u003e"转换为int64时出错：strconv.ParseInt：解析"\u003cnil\u003e"时出现无效语法
这里的outputoctets列的类型是BIGINT。我还尝试将SelectInt更改为SelectStr，然后它抛出以下错误：
在列索引0上扫描错误：不支持的驱动程序 -> 扫描对：\u003cnil\u003e -> *string
当我在没有location过滤器的情况下运行查询时，它可以正常工作。看起来这是由于location列中存在一些NULL值引起的。然而，我仍然需要在查询中使用location过滤器。我该如何解决这个问题？
英文:
I'm trying to run the following query using gorp library with mysql
query = "SELECT SUM(outputoctets) FROM monthlyacct where date >= ? AND date < ? AND location = ?"
count, err := dbMap.SelectInt(query , constrains.From, constrains.To, constrains.LocationId)
This query throws the following error,
> Scan error on column index 0: converting string "\u003cnil\u003e" to
> a int64: strconv.ParseInt: parsing "\u003cnil\u003e": invalid syntax
Here column outputoctets is type BIGINT. I also tried changing SelectInt to SelectStr, then it threw the following error;
> Scan error on column index 0: unsupported driver -\u003e Scan pair:
> \u003cnil\u003e -\u003e *string
When I ran the query without location filter, it worked. Seems like this is due to some NULL values present in the location column. However I need to have the location filter on the query. How can I solve this?
答案1
得分: 1
看起来附加的位置条件正在过滤掉所有结果，所以sum(outputoctets)为空。\u003cnil\u003e是字符串"<nil>"，Go将其拒绝为无效的整数。
使用一个'nullable'类型来保存sum，例如NullInt64
或者，使用mysql的IFNULL函数来避免从查询中返回null，例如IFNULL(sum(outputoctets),0)
有关更多信息，请参阅Go数据库教程
英文:
Looks like the additional location criteria is filtering out all results, so the sum(outputoctets) is null. \u003cnil\u003e is the string "<nil>", which go rejects as an invalid integer.
Use a 'nullable' type to hold the sum, eg, NullInt64
Alternatively, use the mysql IFNULL function to avoid returning a null from the query, eg, IFNULL(sum(outputoctets),0)
See the Go database tutorial for more information
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论