SQL 在 go 中将“SELECT *”列读入 []string
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14477941/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
read "SELECT *" columns into []string in go
提问by Arne
I want to write a Go program to dump rows from from a database table into a csv file using SELECT *.
我想编写一个 Go 程序，使用SELECT *.
Go provides the excellent sqland csvapis, but csvexpects arrays of strings and the Scanmethod in Rows"fills" fields according to their types.
As I don't know the table before, I have no idea how many columns there are and what their types are.
Go 提供了优秀的sql和csvapi，但csv需要字符串数组和“填充”字段中的Scan方法Rows根据它们的类型。因为我之前不知道这个表，所以我不知道有多少列以及它们的类型是什么。
It's my first program in Go, so I'm struggling a little.
这是我在 Go 中的第一个程序，所以我有点挣扎。
How do I best read the columns from a Rowsinstance into a []string- and is that the "right" way?
我如何最好地将Rows实例中的列读入[]string- 这是“正确”的方式吗？
Thanks!
谢谢！
UPDATE
更新
I'm still struggling with the parameters. This is my code, for now I'm using panicinstead of returning an error, but I'm going to change that later. In my test, I'm passing the query result and os.Stdout.
我还在为参数苦苦挣扎。这是我的代码，现在我使用panic而不是返回一个error，但我稍后会更改它。在我的测试中，我传递了查询结果和os.Stdout.
func dumpTable(rows *sql.Rows, out io.Writer) error {
colNames, err := rows.Columns()
if err != nil {
panic(err)
}
if rows.Next() {
writer := csv.NewWriter(out)
writer.Comma = '\t'
cols := make([]string, len(colNames))
processRow := func() {
err := rows.Scan(cols...)
if err != nil {
panic(err)
}
writer.Write(cols)
}
processRow()
for rows.Next() {
processRow()
}
writer.Flush()
}
return nil
}
For this, I get cannot use cols (type []string) as type []interface {} in function argument(at the writer.Write(cols)line.
为此，我得到cannot use cols (type []string) as type []interface {} in function argument（writer.Write(cols)在线。
I then tested
然后我测试
readCols := make([]interface{}, len(colNames))
writeCols := make([]string, len(colNames))
processRow := func() {
err := rows.Scan(readCols...)
if err != nil {
panic(err)
}
// ... CONVERSION?
writer.Write(writeCols)
}
which lead to panic: sql: Scan error on column index 0: destination not a pointer.
这导致panic: sql: Scan error on column index 0: destination not a pointer.
UPDATE 2
更新 2
I independently arrived at ANisus' solution. This is the code I'm using now.
我独立地得出了ANisus的解决方案。这是我现在使用的代码。
func dumpTable(rows *sql.Rows, out io.Writer) error {
colNames, err := rows.Columns()
if err != nil {
panic(err)
}
writer := csv.NewWriter(out)
writer.Comma = '\t'
readCols := make([]interface{}, len(colNames))
writeCols := make([]string, len(colNames))
for i, _ := range writeCols {
readCols[i] = &writeCols[i]
}
for rows.Next() {
err := rows.Scan(readCols...)
if err != nil {
panic(err)
}
writer.Write(writeCols)
}
if err = rows.Err(); err != nil {
panic(err)
}
writer.Flush()
return nil
}
回答by ANisus
In order to directly Scanthe values into a []string, you must create an []interface{}slice pointing to each string in your string slice.
为了Scan将值直接放入 a []string，您必须创建一个[]interface{}指向字符串切片中每个字符串的切片。
Here you have a working example for MySQL (just change the sql.Open-command to match your settings):
这里有一个 MySQL 的工作示例（只需更改sql.Open-command 以匹配您的设置）：
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"database/sql"
)
func main() {
db, err := sql.Open("mysql", "user:pass@tcp(localhost:3306)/test?charset=utf8")
defer db.Close()
if err != nil {
fmt.Println("Failed to connect", err)
return
}
rows, err := db.Query(`SELECT 'one' col1, 'two' col2, 3 col3, NULL col4`)
if err != nil {
fmt.Println("Failed to run query", err)
return
}
cols, err := rows.Columns()
if err != nil {
fmt.Println("Failed to get columns", err)
return
}
// Result is your slice string.
rawResult := make([][]byte, len(cols))
result := make([]string, len(cols))
dest := make([]interface{}, len(cols)) // A temporary interface{} slice
for i, _ := range rawResult {
dest[i] = &rawResult[i] // Put pointers to each string in the interface slice
}
for rows.Next() {
err = rows.Scan(dest...)
if err != nil {
fmt.Println("Failed to scan row", err)
return
}
for i, raw := range rawResult {
if raw == nil {
result[i] = "\N"
} else {
result[i] = string(raw)
}
}
fmt.Printf("%#v\n", result)
}
}
回答by fmt.Println.MKO
to get the Number of Columns (and also the names) just use the Columns() Function
要获得列数（以及名称），只需使用 Columns() 函数
http://golang.org/pkg/database/sql/#Rows.Columns
http://golang.org/pkg/database/sql/#Rows.Columns
and as csv can only be a strings, just use a []byte type as dest type for Scanner. according to docu:
由于 csv 只能是字符串，因此只需使用 []byte 类型作为 Scanner 的 dest 类型。根据文档：
If an argument has type *[]byte, Scan saves in that argument a copy of the corresponding data. The copy is owned by the caller and can be modified and held indefinitely.
如果参数的类型为 *[]byte，则 Scan 会在该参数中保存相应数据的副本。副本归调用者所有，可以修改和无限期保留。
the data will not be transformed into its real type. and from this []byte you can then convert it to string.
数据不会被转换成它的真实类型。然后从这个 []byte 可以将它转换为字符串。
if your are sure your tables only use base types (string, []byte, nil, int(s), float(s), bool) you can directly pass string as dest
如果您确定您的表只使用基本类型（字符串、[]byte、nil、int(s)、float(s)、bool），您可以直接将字符串作为 dest 传递
but if you use other types like arrays, enums, or so on, then the data cant be transformed to string. but this also depends how the driver handles this types. (some months ago as example, the postgres driver was not able to handle arrays, so he returned always []byte where i needed to transform it by my own)
但如果您使用其他类型，如数组、枚举等，则数据无法转换为字符串。但这也取决于驱动程序如何处理这种类型。（例如几个月前，postgres 驱动程序无法处理数组，所以他总是返回 []byte 我需要自己转换它的地方）
回答by hygull
The following code prettily statisfies your requirement, you can get this code at https://gist.github.com/hygull/645c3dc39c69b6b69c06f5ea9deee41f. The table data has been also provided.
以下代码很好地满足了您的要求，您可以在https://gist.github.com/hygull/645c3dc39c69b6b69c06f5ea9deee41f获取此代码。还提供了表数据。
/**
{
"created_on": "26 may 2017",
"todos": [
"go get github.com/go-sql-driver/mysql"
],
"aim": "Reading fname column into []string(slice of strings)"
}
*/
/*
mysql> select * from users;
+----+-----------+----------+----------+-------------------------------+--------------+
| id | fname | lname | uname | email | contact |
+----+-----------+----------+----------+-------------------------------+--------------+
| 1 | Rishikesh | Agrawani | hygull | [email protected] | 917353787704 |
| 2 | Sandeep | E | sandeep | [email protected] | 919739040038 |
| 3 | Darshan | Sidar | darshan | [email protected] | 917996917565 |
| 4 | Surendra | Prajapat | surendra | [email protected] | 918385894407 |
| 5 | Mukesh | Jakhar | mukesh | [email protected] | 919772254140 |
+----+-----------+----------+----------+-------------------------------+--------------+
5 rows in set (0.00 sec)
mysql>
*/
package main
import "fmt"
import "log"
import (
_"github.com/go-sql-driver/mysql"
"database/sql"
)
func main() {
// db, err := sql.Open("mysql", "<username>:<password>@tcp(127.0.0.1:<port>)/<dbname>?charset=utf8" )
db, err := sql.Open("mysql", "hygull:admin@67@tcp(127.0.0.1:3306)/practice_db?charset=utf8")
if err != nil {
log.Fatal(err)
}
rows, err := db.Query("select fname from users")
if err != nil {
log.Fatal(err)
}
firstnames:=[]string{}
for rows.Next() {
var fname string
rows.Scan(&fname)
firstnames = append(firstnames, fname)
}
fmt.Println(firstnames)
db.Close()
}
/*
[Rishikesh Sandeep Darshan Surendra Mukesh]
*/
回答by farhany
Couldn't this be done instead? Simplified below.
不能这样做吗？简化如下。
var tmpErrors string
_ = row.Scan(&tmpErrors)
actualVarHere := strings.Split(tmpErrors, "\n")
Would there be a problem or performance issue I am not seeing?
会不会有我没有看到的问题或性能问题？
