是否可以使用GoLang数据库/ SQL按名称检索列值

Question

All of the examples I've seen for using sql.Row, access return values from queries by position:sql.Rows.scan() requires a correctly typed variable correctly positioned in the scan() arguments corresponding to the appropriate column, to retrieve each column value returned, such as in the following example:

Example Based on GoDocs (with small mod):

rows, err := db.Query("SELECT name,age FROM users WHERE age>=50")
if err != nil {
    log.Fatal(err)
}
for rows.Next() {
    var name string
    var age int

    if err := rows.Scan(&name,&age); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%s is %d
", name, age)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
} 

&name and &age must be positioned correctly (columns 0 and 1) for Rows.Scan() to retrieve the correct values with the correct types.

Over my years of development for production systems, I have come to studiously avoid this practice because it's not robust: A database change in the layout of the columns will easily break your code if it's based on column positions.

It is far more robust to use column names for retrieving values - this insulates you from changes to the database that add or remove columns that screw up your position based code. For example, in Delphi and C#, all dataSets, including columns returning values from queries, support FieldByName('age').asInteger or fields['age'].value, etc.

Any way to accomplish this in Go? If not, this is a big drawback in Go database support and a serious disappointment - not at all safe, as mentioned.

Edit:

Also (perhaps this is a new question): The examples I've seen seem to require you to retrieve all the columns returned by the query, or the positions of the columns will be skewed.

Suppose there is utility query in a locked-down database that I cannot modify or add to, and it retrieves several columns, but I only need one of them for my current task. Based on the current sql.Rows.Scan() model, I have to retrieve all the values from the query in my application code, even though I don't need them, whereas if I could query "columnByName" that would not be necessary - I could just bring into my application code the data I need. Any work-around for this?

Answer 1

Yes, it is possible to do this without having to manually match up the column positions. There are some third-party libraries you can use to do this, such as sqlx or gorp. I would recommend sticking with one of these instead of rolling your own.

Named matching does have a slight penalty. Named matching is no different than matching up the column positions yourself. It just does this work for you at runtime - possibly on every query execution. This is true in any other language.

Why at runtime? The query is written as a string. It has to be parsed to determine the position.

If you were to make your own library, how do you do this on your own?

• Rows.Columns to get column names and positions.

• Passing a slice of pointers []interface{} to Rows.Scan to get the values.

• reflect.Value and Value.Addr to get a pointer to the destination value.

• Value.FieldByName to get the Value of a struct field if you want to map to struct fields.

Ok, so lets see how this works.

type Person struct {
    Id int
    Name string
}
rows, err := db.Query("SELECT id, name FROM person;")
if err != nil {
    // handle err
    log.Fatal(err)
}
columnNames, err := rows.Columns() // []string{"id", "name"}
if err != nil {
    // handle err
    log.Fatal(err)
}
people = make([]Person, 0, 2)
for rows.Next() {
    person := Person{}
    // person == Person{0, ""}
    pointers := make([]interface{}, len(columnNames))
    // pointers == `[]interface{}{nil, nil}`
    structVal := reflect.ValueOf(person)
    for i, colName := range columnNames {
        fieldVal := structVal.FieldByName(strings.Title(colName))
        if !fieldVal.IsValid() {
            log.Fatal("field not valid")
        }
        pointers[i] = fieldVal.Addr().Interface()
    }
    // pointers == `[]interface{}{&int, &string}`
    err := rows.Scan(pointers...)
    if err != nil {
        // handle err
        log.Fatal(err)
    }
    // person == Person{1, "John Doe"}
    people = append(people, person)
}