英文:
How to fix golang sql string conversion scan error
问题
在查询sqlite3数据库中的一行时，我收到以下错误：
sql: 在第3列索引上扫描错误: 将字符串""转换为int64: strconv.ParseInt: 解析"": 无效的语法
查询本身是有效的，因为它能够返回查询的数据。我应该注意到数据库中的每一行都包含几个包含NULL数据的列（只有Id和Email列是有数据的）。我很难理解为什么会出现这个错误以及如何解决它。
SQL表格有以下九列：
`0,Id,INTEGER,0,,11,Email,TEXT,0,,02,Name,TEXT,0,,03,Rsvp,INTEGER,0,,04,Guests,INTEGER,0,,05,Meal0,INTEGER,0,,06,Meal1,INTEGER,0,,07,Comments,TEXT,0,,08,ModifiedAt,TEXT,0,,0`
这些列在server.go中用以下结构体定义（我添加了sql.NullString来处理空列）：
type User struct {Id intEmail stringName sql.NullStringRsvp sql.NullInt64Guests sql.NullInt64Meal0 sql.NullInt64Meal1 sql.NullInt64Comments sql.NullStringModifiedAt sql.NullString}
查询来自以下处理程序：
func Rsvp1Handler(w http.ResponseWriter, r *http.Request) {email := r.URL.Path[len("/rsvp/"):]var user Usererr := userStatement.QueryRow(email).Scan(&user.Id, &user.Email, &user.Name, &user.Rsvp, &user.Guests, &user.Meal0, &user.Meal1, &user.Comments, &user.ModifiedAt)switch {case err == sql.ErrNoRows:log.Println("没有找到该ID的用户。")w.Header().Set("Content-Type", "text/plain")fmt.Fprintf(w, "未找到电子邮件地址：%s", email)case err != nil:log.Println(err)fmt.Fprintf(w, "数据库错误：\n", err)default:log.Println("查询成功。电子邮件地址：", email)w.Header().Set("Content-Type", "application/json")json.NewEncoder(w).Encode(&user)}
它依赖于以下常量和变量：
const userSelect = "SELECT * FROM rsvp WHERE email = ?"var userStatement *sql.Stmt
非常感谢任何帮助或对错误的见解！
英文:
I'm receiving the following error when querying a row in an sqlite3 database:
sql: Scan error on column index 3: converting string "" to a int64: strconv.ParseInt: parsing "": invalid syntax
The query itself works, as it is able to return the queried data. I should note that each row in the database contains several columns with NULL data (only the Id and Email columns are populated). I am struggling to understand why the err occurs and how to resolve it.
The SQL table has the following nine columns:
`0,Id,INTEGER,0,,11,Email,TEXT,0,,02,Name,TEXT,0,,03,Rsvp,INTEGER,0,,04,Guests,INTEGER,0,,05,Meal0,INTEGER,0,,06,Meal1,INTEGER,0,,07,Comments,TEXT,0,,08,ModifiedAt,TEXT,0,,0`
which are defined in server.go with the following struct (I added the sql.NullString to handle the empty columns):
type User struct {Id intEmail stringName sql.NullStringRsvp sql.NullInt64Guests sql.NullInt64Meal0 sql.NullInt64Meal1 sql.NullInt64Comments sql.NullStringModifiedAt sql.NullString}
the query is from the following handler:
func Rsvp1Handler(w http.ResponseWriter, r *http.Request) {email := r.URL.Path[len("/rsvp/"):]var user Usererr := userStatement.QueryRow(email).Scan(&user.Id, &user.Email, &user.Name, &user.Rsvp, &user.Guests, &user.Meal0, &user.Meal1, &user.Comments, &user.ModifiedAt)switch {case err == sql.ErrNoRows:log.Println("No user with that ID.")w.Header().Set("Content-Type", "text/plain")fmt.Fprintf(w, "Email address not found: %s", email)case err != nil:log.Println(err)fmt.Fprintf(w, "Database error:\n", err)default:log.Println("Query success. Email address: ", email)w.Header().Set("Content-Type", "application/json")json.NewEncoder(w).Encode(&user)}
which relies on the following constant and var:
const userSelect = "SELECT * FROM rsvp WHERE email = ?"var userStatement *sql.Stmt
Any help or insight into the error is greatly appreciated!
答案1
得分: 3
似乎你的其中一列（很可能是 Rsvp）被存储为字符串类型而不是整数类型，并且它的值被设置为 "" 而不是 null。
所以你需要将 Rsvp（可能是其他列）更改为 sql.NullString，重新设计数据库以在数值字段中使用 null 而不是空字符串，或者如果你认为这是驱动程序的错误，可以在其问题跟踪器上提交一个问题。
英文:
It seems one of your columns (most likely Rsvp) is stored as a string type not int and it's set to "" instead of null.
So you either have to change Rsvp (might be a different column) to sql.NullString, redo the database to have nulls instead of empty strings for numeric fields or if you believe it's a bug in the driver, open an issue on their tracker.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论