golang 查询mysql,在mysql和golang中建立动态查询

How can I build a dynamic query depending on the parameters that I get?

This example is stupid and the syntax is wrong but you will get the idea of what I want.

I guess that I need to add a slice of variables to the end of the query.

I know how to do it in PHP, but not in golang.

db := OpenDB()

defer db.Close()

var filter string

if name != "" {

filter = filter " AND name = ?"

}

if surname != "" {

filter = filter + " AND surname = ?"

}

if address != "" {

filter = filter + " AND address = ?"

}

err = db.Query("SELECT id FROM users WHERE login = ?" +

filter, login)

解决方案

To answer your question on how to format the string, the simple answer is to use fmt.Sprintf to structure your string. However see further down for a quick note on using fmt.Sprintf for db queries:

Sprintf formats according to a format specifier and returns the resulting string.

Example:

query := fmt.Sprintf("SELECT id FROM users WHERE login='%s'", login)

err = db.Query(query)

// Equivalent to:

rows, err := db.Query("SELECT id FROM users WHERE login=?", login)

Using this for queries, you're safe from injections. That being said, you might be tempted to modify this and use db.Exec for creations/updates/deletions as well. As a general rule of thumb, if you use db.Exec with fmt.Sprintf and do not sanitize your inputs first, you open yourself up to sql injections.

GoPlay with simple example of why fmt.Sprintf with db.Exec is bad:

https://play.golang.org/p/-IWyymAg_Q

You should use db.Query or db.Prepare in an appropriate way to avoid these sorts of attack vectors. You might have to modify the code sample above to come up with a injection-safe snippet, but hopefully I gave you enough to get started.