当我在 db（此处为 postgres）中的结构未知时，如何检索 GoLang 数据库/sql

Question

我使用 github.com/lib/pq 将 Go 与 PostgreSQL 一起使用，并且在我的结构已知时能够成功获取记录。现在我的查询是当我的结构动态变化时如何获取记录？

通过rows.columns我可以获取列名，但是您能帮我获取所有行的这些列的值吗？我引用了@Luke 回答的这个链接，不过，这里的人已经定义了一个结构。 是否可以使用 GoLang 数据库/sql 按名称检索列值

type Person struct {

    Id int

    Name string

}

同时，我没有固定的结构，所以我将如何遍历所有列，对所有行再次进行迭代。我的方法是先用一个指针循环遍历所有列，然后再循环遍历下一行。仍然无法对此进行编码，请您帮我解决这个问题，例如如何继续并获取值。

Answer 1

由于您事先不知道结构，您可以将行作为空接口的二维切片返回。但是，要使行扫描起作用，您需要将值预分配给适当的类型，为此您可以使用ColumnTypes方法和reflect包。请记住，并非每个驱动程序都提供对列类型的访问，因此请确保您使用的驱动程序提供。rows, err := db.Query("select * from foobar")if err != nil {&nbsp; &nbsp; return err}defer rows.Close()// get column type infocolumnTypes, err := rows.ColumnTypes()if err != nil {&nbsp; &nbsp; return err}// used for allocation & dereferencingrowValues := make([]reflect.Value, len(columnTypes))for i := 0; i < len(columnTypes); i++ {&nbsp; &nbsp; // allocate reflect.Value representing a **T value&nbsp; &nbsp; rowValues[i] = reflect.New(reflect.PtrTo(columnTypes[i].ScanType()))}resultList := [][]interface{}{}for rows.Next() {&nbsp; &nbsp; // initially will hold pointers for Scan, after scanning the&nbsp; &nbsp; // pointers will be dereferenced so that the slice holds actual values&nbsp; &nbsp; rowResult := make([]interface{}, len(columnTypes))&nbsp; &nbsp; for i := 0; i < len(columnTypes); i++ {&nbsp; &nbsp; &nbsp; &nbsp; // get the **T value from the reflect.Value&nbsp; &nbsp; &nbsp; &nbsp; rowResult[i] = rowValues[i].Interface()&nbsp; &nbsp; }&nbsp; &nbsp; // scan each column value into the corresponding **T value&nbsp; &nbsp; if err := rows.Scan(rowResult...); err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return err&nbsp; &nbsp; }&nbsp; &nbsp; // dereference pointers&nbsp; &nbsp; for i := 0; i < len(rowValues); i++ {&nbsp; &nbsp; &nbsp; &nbsp; // first pointer deref to get reflect.Value representing a *T value,&nbsp; &nbsp; &nbsp; &nbsp; // if rv.IsNil it means column value was NULL&nbsp; &nbsp; &nbsp; &nbsp; if rv := rowValues[i].Elem(); rv.IsNil() {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rowResult[i] = nil&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // second deref to get reflect.Value representing the T value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // and call Interface to get T value from the reflect.Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rowResult[i] = rv.Elem().Interface()&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; resultList = append(resultList, rowResult)}if err := rows.Err(); err != nil {&nbsp; &nbsp; return err}fmt.Println(resultList)分享编辑跟随由于您事先不知道结构，您可以将行作为空接口的二维切片返回。但是，要使行扫描起作用，您需要将值预分配给适当的类型，为此您可以使用ColumnTypes方法和reflect包。请记住，并非每个驱动程序都提供对列类型的访问，因此请确保您使用的驱动程序提供。rows, err := db.Query("select * from foobar")if err != nil {&nbsp; &nbsp; return err}defer rows.Close()// get column type infocolumnTypes, err := rows.ColumnTypes()if err != nil {&nbsp; &nbsp; return err}// used for allocation & dereferencingrowValues := make([]reflect.Value, len(columnTypes))for i := 0; i < len(columnTypes); i++ {&nbsp; &nbsp; // allocate reflect.Value representing a **T value&nbsp; &nbsp; rowValues[i] = reflect.New(reflect.PtrTo(columnTypes[i].ScanType()))}resultList := [][]interface{}{}for rows.Next() {&nbsp; &nbsp; // initially will hold pointers for Scan, after scanning the&nbsp; &nbsp; // pointers will be dereferenced so that the slice holds actual values&nbsp; &nbsp; rowResult := make([]interface{}, len(columnTypes))&nbsp; &nbsp; for i := 0; i < len(columnTypes); i++ {&nbsp; &nbsp; &nbsp; &nbsp; // get the **T value from the reflect.Value&nbsp; &nbsp; &nbsp; &nbsp; rowResult[i] = rowValues[i].Interface()&nbsp; &nbsp; }&nbsp; &nbsp; // scan each column value into the corresponding **T value&nbsp; &nbsp; if err := rows.Scan(rowResult...); err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return err&nbsp; &nbsp; }&nbsp; &nbsp; // dereference pointers&nbsp; &nbsp; for i := 0; i < len(rowValues); i++ {&nbsp; &nbsp; &nbsp; &nbsp; // first pointer deref to get reflect.Value representing a *T value,&nbsp; &nbsp; &nbsp; &nbsp; // if rv.IsNil it means column value was NULL&nbsp; &nbsp; &nbsp; &nbsp; if rv := rowValues[i].Elem(); rv.IsNil() {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rowResult[i] = nil&nbsp; &nbsp; &nbsp; &nbsp; } else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // second deref to get reflect.Value representing the T value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // and call Interface to get T value from the reflect.Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; rowResult[i] = rv.Elem().Interface()&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; resultList = append(resultList, rowResult)}if err := rows.Err(); err != nil {&nbsp; &nbsp; return err}fmt.Println(resultList)

Answer 2

此函数在不知道列类型和计数的情况下打印查询结果。这是不使用包的先前答案的变体。reflectfunc printQueryResult(db *sql.DB, query string) error {&nbsp; &nbsp; rows, err := db.Query(query)&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return fmt.Errorf("canot run query %s: %w", query, err)&nbsp; &nbsp; }&nbsp; &nbsp; defer rows.Close()&nbsp; &nbsp; cols, _ := rows.Columns()&nbsp; &nbsp; row := make([]interface{}, len(cols))&nbsp; &nbsp; rowPtr := make([]interface{}, len(cols))&nbsp; &nbsp; for i := range row {&nbsp; &nbsp; &nbsp; &nbsp; rowPtr[i] = &row[i]&nbsp; &nbsp; }&nbsp; &nbsp; fmt.Println(cols)&nbsp; &nbsp; for rows.Next() {&nbsp; &nbsp; &nbsp; &nbsp; err = rows.Scan(rowPtr...)&nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fmt.Println("cannot scan row:", err)&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; fmt.Println(row...)&nbsp; &nbsp; }&nbsp; &nbsp; return rows.Err()}诀窍是rows.Scan可以将值扫描到*interface{}，但您必须将其包装起来interface{}才能将其传递给Scanusing ...。

Answer 3

此函数在不知道列类型和计数的情况下打印查询结果。这是不使用包的先前答案的变体。reflectfunc printQueryResult(db *sql.DB, query string) error {&nbsp; &nbsp; rows, err := db.Query(query)&nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; return fmt.Errorf("canot run query %s: %w", query, err)&nbsp; &nbsp; }&nbsp; &nbsp; defer rows.Close()&nbsp; &nbsp; cols, _ := rows.Columns()&nbsp; &nbsp; row := make([]interface{}, len(cols))&nbsp; &nbsp; rowPtr := make([]interface{}, len(cols))&nbsp; &nbsp; for i := range row {&nbsp; &nbsp; &nbsp; &nbsp; rowPtr[i] = &row[i]&nbsp; &nbsp; }&nbsp; &nbsp; fmt.Println(cols)&nbsp; &nbsp; for rows.Next() {&nbsp; &nbsp; &nbsp; &nbsp; err = rows.Scan(rowPtr...)&nbsp; &nbsp; &nbsp; &nbsp; if err != nil {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; fmt.Println("cannot scan row:", err)&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; fmt.Println(row...)&nbsp; &nbsp; }&nbsp; &nbsp; return rows.Err()}诀窍是rows.Scan可以将值扫描到*interface{}，但您必须将其包装起来interface{}才能将其传递给Scanusing ...。