sql：扫描具有未知列数的行（选择* from ...） [英] sql: scan row(s) with unknown number of columns (select * from ...)

问题描述

我有一个包含很多列的表 t ，我的sql如下所示： select * from t 。现在我只想从广泛的返回行集合中扫描一列或两列。但是，sql.Scan接受 dest ... interface {} 作为参数。是否意味着我必须扫描所有内容并只使用我需要的列？

I have a table t containing a lot of columns, and my sql is like this: select * from t. Now I only want to scan one column or two from the wide returned row set. However, the sql.Scan accepts dest ...interface{} as arguments. Does it mean I have to scan everything and use only the column I needed?

我知道我可以从 select * 到 select my_favorite_rows ，但是，在这种情况下，我没有办法改变sql。

I know I could change the sql from select * to select my_favorite_rows, however, in this case, I have no way to change the sql.

感谢，
Elgs

Thanks, Elgs

推荐答案

您可以使用Rows.Columns，例如

You can make use of Rows.Columns, e.g.

package main

import (
    "database/sql"
    "fmt"

    "github.com/lib/pq"
)

type Vehicle struct {
    Id     int
    Name   string
    Wheels int
}

// VehicleCol returns a reference for a column of a Vehicle
func VehicleCol(colname string, vh *Vehicle) interface{} {
    switch colname {
    case "id":
        return &vh.Id
    case "name":
        return &vh.Name
    case "wheels":
        return &vh.Wheels
    default:
        panic("unknown column " + colname)
    }
}

func panicOnErr(err error) {
    if err != nil {
        panic(err.Error())
    }
}

func main() {
    conn, err := pq.ParseURL(`postgres://docker:docker@172.17.0.2:5432/pgsqltest?schema=public`)
    panicOnErr(err)

    var db *sql.DB
    db, err = sql.Open("postgres", conn)
    panicOnErr(err)

    var rows *sql.Rows
    rows, err = db.Query("select * from vehicle")
    panicOnErr(err)

    // get the column names from the query
    var columns []string
    columns, err = rows.Columns()
    panicOnErr(err)

    colNum := len(columns)

    all := []Vehicle{}

    for rows.Next() {
        vh := Vehicle{}

        // make references for the cols with the aid of VehicleCol
        cols := make([]interface{}, colNum)
        for i := 0; i < colNum; i++ {
            cols[i] = VehicleCol(columns[i], &vh)
        }

        err = rows.Scan(cols...)
        panicOnErr(err)

        all = append(all, vh)
    }

    fmt.Printf("%#v\n", all)
}

这篇关于sql：扫描具有未知列数的行（选择* from ...）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！