将结构映射到mysql表，并将行绑定到结构

Question

This is my first script using go-sql-driver.

My mysql table (PRODUCT) looks like:

id int
name varchar(255)
IsMatch tinyint(1)
created datetime

I want to simply load a row from a table, and bind it to a struct.

I have this so far:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type Product struct {
    Id    int64
  Name  string
  IsMatch ??????????
  Created ?????
}

func main() {
    fmt.Printf("hello, world!
")

    db, err := sql.Open("mysql", "root:@/product_development")
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }

    rows, err := db.Query("SELECT * FROM products where id=1")
    if err != nil {
        panic(err.Error()) // proper error handling instead of panic in your app
    }

}

Now I need to:

1. What datatype in Go do I use for tinyint and datetime?
2. How to I map the rows to a Product struct?

Answer 1

What datatype in Go do I use for tinyint and datetime?

For a hint as to the types that the database/sql package will be using, have a look at the documentation for database/sql.Scanner, which lists the Go types used within database/sql itself:

int64
float64
bool
[]byte
string
time.Time
nil - for NULL values

This would lead you to try int64 for IsMatch and time.Time for Created. I believe in reality you can use pretty much any sized int (maybe even bool, you'd have to check the source) for IsMatch because it can be stored "without loss of precision." The documentation for go-mysql-driver explains that you will need to add parseTime=true to your DSN in order for it to parse into a time.Time automatically or use NullTime.

How to I map the rows to a Product struct?

It should be something pretty strightforward, using Rows.Scan, like:

var products []*Product
for rows.Next() {
    p := new(Product)
    if err := rows.Scan(&p.ID, &p.Name, &p.IsMatch, &p.Created); err != nil { ... }
    products = append(products, p)
}
if err := rows.Err() { ... }

This scans the columns into the fields of a struct and accumulates them into a slice. (Don't forget to Close the rows!)