英文:
Storing data returned from a database query into a on the fly created generic Go data type
问题
使用struct时，它的数据字段数量、容量和字段类型是预定义的。相比之下，使用map更有优势，因为map是可扩展的，并且可以包含多种数据类型作为值。例如，在数据库方面，如果表的列名、列类型或列数量发生变化，与查询数据库相关的Go代码（例如，你将rows返回的数据结构放入其中的代码）不会受到影响。
在Golang的sql包或相关驱动中，是否有一种方法可以知道数据库查询返回的行中的数据类型，以便定义一个具有适当数量字段和类型的struct？
如果没有，如何使用map来实现这一点，其中返回的rows的列名是该map的键，行字段是该map的值？
英文:
Instead of using a struct, which is predefined in terms of its number of data fileds, capacity, and types of that fields, having a map, which is expandable and can contain several data types as value, will be more advantageous. For example, on database side, changes to column names, column types, or number of columns in a table will not affect the go code related to querying the database, such as go data structure you put the rows returned form your database query.
Is there a way in golang sql package or related drivers to know the types of the data, in rows, returned by a database query to define a struct with appropriate number of fields and types?
If not how can a map be used to accomplish this with column names of returned rows are keys and row fields are values of this map?
答案1
得分: 1
要做到这一点，您需要使用值类型为interface{}的映射，以便可以存储任何类型的值。如果您还需要列名，您需要使用rows.Columns()来提取所有数据和类型。
以下代码将从表中读取所有列，并将值以类型为interface{}的映射存储在其中，以列名作为键。基于此，您应该能够找出您需要的内容。
var myMap = make(map[string]interface{})
rows, err := db.Query("SELECT * FROM myTable")
defer rows.Close()
if err != nil {
log.Fatal(err)
}
colNames, err := rows.Columns()
if err != nil {
log.Fatal(err)
}
cols := make([]interface{}, len(colNames))
colPtrs := make([]interface{}, len(colNames))
for i := 0; i < len(colNames); i++ {
colPtrs[i] = &cols[i]
}
for rows.Next() {
err = rows.Scan(colPtrs...)
if err != nil {
log.Fatal(err)
}
for i, col := range cols {
myMap[colNames[i]] = col
}
// 使用映射进行操作
for key, val := range myMap {
fmt.Println("Key:", key, "Value Type:", reflect.TypeOf(val))
}
}
使用reflect包，您可以根据需要获取每个列的类型，就像最后的循环示例中所演示的那样。或者，您可以使用type switch来提取类型。
上述代码是通用的，适用于任意数量的列和类型。
英文:
To do that you'll have to use a map with values of type interface{} so they can store any type. If you also need the column names you'd have to use rows.Columns() to then extract all the data and types.
This will read all columns from a table and store the values in a map as type interface{} with the column names as keys. Based on that you should be able to work out what you need for your use.
var myMap = make(map[string]interface{})
rows, err := db.Query("SELECT * FROM myTable")
defer rows.Close()
if err != nil {
log.Fatal(err)
}
colNames, err := rows.Columns()
if err != nil {
log.Fatal(err)
}
cols := make([]interface{}, len(colNames))
colPtrs := make([]interface{}, len(colNames))
for i := 0; i < len(colNames); i++ {
colPtrs[i] = &cols[i]
}
for rows.Next() {
err = rows.Scan(colPtrs...)
if err != nil {
log.Fatal(err)
}
for i, col := range cols {
myMap[colNames[i]] = col
}
// Do something with the map
for key, val := range myMap {
fmt.Println("Key:", key, "Value Type:", reflect.TypeOf(val))
}
}
Using the reflect package you can then get the Type for each column as needed as demonstrated with the loop at the end. Or you can use a type switch to extract the types.
The above is generic and will work with any number of columns and types.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论