使用GoLang的database/sql包,可以通过列名检索列值。

huangapple go评论100阅读模式
英文:

Is it possible to retrieve a column value by name using GoLang database/sql

问题

我看到的所有关于使用sql.Row的示例都是通过位置访问查询的返回值的:sql.Rows.scan()需要一个正确类型的变量,并且在scan()参数中正确定位,以检索每个返回的列值,就像下面的示例中所示:

基于 GoDocs 的示例(稍作修改):

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\n", name, age)
}
if err := rows.Err(); err != nil {
    log.Fatal(err)
} 

&name&age 必须正确定位(列 0 和 1),以便 Rows.Scan() 使用正确的类型检索正确的值。

在我多年的生产系统开发经验中,我一直避免使用这种做法,因为它不够健壮:如果数据库中的列布局发生变化,基于列位置的代码很容易出错。

更健壮的做法是使用列名来检索值-这样可以避免数据库的更改导致基于位置的代码出错。例如,在 Delphi 和 C# 中,所有的数据集(包括从查询返回值的列)都支持 FieldByName('age').asIntegerfields['age'].value 等操作。

在 Go 中有没有实现这个功能的方法?如果没有,这是 Go 数据库支持的一个重大缺点,也是一个令人失望的地方-正如之前提到的,它并不安全。

编辑:

另外(也许这是一个新问题):我看到的示例似乎要求你检索查询返回的所有列,否则列的位置将会错位。

假设在一个无法修改或添加内容的受限数据库中有一个实用的查询,它检索了多个列,但是我当前的任务只需要其中一个列。根据当前的 sql.Rows.Scan() 模型,我必须在应用程序代码中检索查询的所有值,即使我不需要它们,而如果我可以通过“按列名检索”,那就不需要这样 - 我只需要将我需要的数据带入我的应用程序代码中。有没有解决这个问题的方法?

英文:

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\n", 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?

答案1

得分: 26

是的,可以在不手动匹配列位置的情况下实现这一点。你可以使用一些第三方库来完成,比如sqlxgorp。我建议使用其中之一,而不是自己编写代码。

命名匹配会稍微降低性能。命名匹配与手动匹配列位置没有区别,只是在运行时为你完成这项工作,可能在每次查询执行时都会进行。这在任何其他语言中都是如此。

为什么要在运行时进行?查询被写成字符串形式,必须解析以确定位置。

如果你要自己编写库,你该如何做到这一点呢?

好的,让我们看看这是如何工作的。

type Person struct {
    Id   int
    Name string
}
rows, err := db.Query("SELECT id, name FROM person;")
if err != nil {
    // 处理错误
    log.Fatal(err)
}
columnNames, err := rows.Columns() // []string{"id", "name"}
if err != nil {
    // 处理错误
    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 {
        // 处理错误
        log.Fatal(err)
    }
    // person == Person{1, "John Doe"}
    people = append(people, person)
}
英文:

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?

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)
}

答案2

得分: 2

唯一明智且干净的方法是使用:https://github.com/jmoiron/sqlx

假设你有一个 Place 结构体:

type Place struct {
    Country       string
    City          sql.NullString
    TelephoneCode int `db:"telcode"`
}

你可以轻松地进行扫描:

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.StructScan(&p)
}

更多信息:http://jmoiron.github.io/sqlx/

英文:

The only sane & clean way to do this is to use: https://github.com/jmoiron/sqlx

Let say you have a Place struct:

type Place struct {
    Country       string
    City          sql.NullString
    TelephoneCode int `db:"telcode"`
}

You scan it easily:

rows, err := db.Queryx("SELECT * FROM place")
for rows.Next() {
    var p Place
    err = rows.StructScan(&p)
}

More info: http://jmoiron.github.io/sqlx/

答案3

得分: 0

也许采用不同的方法处理数据会有所帮助,像这样的方法:

func GetFieldValues(res *sql.Rows) []string {
    m, err := res.ColumnTypes()
    if err != nil {
        return nil
    }
    var (
        //i    int = 0
        mLen int = len(m)
    )
    if res.Next() {
        mValStr := make([]sql.NullString, mLen)
        mVal := make([]any, mLen)
        for i := range mVal {
            mVal[i] = &mValStr[i]
        }

        res.Scan(mVal...) //super

        mmVal := make([]string, mLen)
        for i := 0; i < mLen; i++ {
            if mValStr[i].Valid {
                mmVal[i] = mValStr[i].String
            } else {
                mmVal[i] = "" // 空字符串在这个上下文中是nil --> 参见函数IsNull
            }
        }
        mVal = nil // 释放分配的内存
        return mmVal

    } else {
        return nil
    }
}

希望对你有帮助!

英文:

Maybe a different approach to the data would help, like something like this:

func GetFieldValues(res *sql.Rows) []string {
	m, err := res.ColumnTypes()
	if err != nil {
		return nil
	}
	var (
		//i    int = 0
		mLen int = len(m)
	)
	if res.Next() {
		mValStr := make([]sql.NullString, mLen)
		mVal := make([]any, mLen)
		for i := range mVal {
			mVal[i] = &amp;mValStr[i]
		}

		res.Scan(mVal...) //super

		mmVal := make([]string, mLen)
		for i := 0; i &lt; mLen; i++ {
			if mValStr[i].Valid {
				mmVal[i] = mValStr[i].String
			} else {
				mmVal[i] = &quot;&quot; // empty string is nil in this context --&gt; see func IsNull
			}
		}
		mVal = nil // Free allocated mem
		return mmVal

	} else {
		return nil
	}
}

huangapple
  • 本文由 发表于 2014年2月24日 19:44:34
  • 转载请务必保留本文链接:https://go.coder-hub.com/21986780.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定