对MySQL数据库进行动态SQL查询

Question

I've never used golang with mysql before, so I'm reading about these for the first time. I'd like to do something like this:

if userId && gender && age
db.QueryRow("SELECT name FROM users WHERE userId=? AND gender=? AND age=?", userId,gender,age)
else if gender && age
db.QueryRow("SELECT name FROM users WHERE gender=? AND age=?", gender, age)
else if userId && gender
db.QueryRow("SELECT name FROM users WHERE userId=? AND gender=?", userId,gender)
else if userId && age
db.QueryRow("SELECT name FROM users WHERE userId=? AND age=?", userId, age)
else if gender
db.QueryRow("SELECT name FROM users WHERE gender=?", gender)
else if userId
db.QueryRow("SELECT name FROM users WHERE userId=?", userId)
else if age
db.QueryRow("SELECT name FROM users WHERE age=?", age)

This is far too much typing, especially if I have a dozen more variables I'd like to add to the WHERE condition.

If this were PHP, I'd do something like this:

$sql = "SELECT name FROM users ";
$where = array();
foreach(explode(",","userId,gender,age,name,height,weight,ethnicity" as $field)
{
    if(isset($arrayOfValues[$field]))
    {
        $where[count($where)] = $field." = ?".$field
        $db->bind("?".$field,$arrayOfValues[$field]);
    }
}

if(count($where)>0)
$sql = $sql . " WHERE ".implode(" AND ",$where);
$db->query($sql);

By using a foreach loop, I can dynamically generate queries and dynamically bind as many variables as needed.

Is something like this an option with golang and mysql? Or are there alternatives to not typing out every single variable combination for a query?

Answer 1

If you have a map with the field names and values like this:

m := map[string]interface{}{"UserID": 1234, "Age": 18}

then you can build the query like this:

var values []interface{}
var where []string
for _, k := range []string{"userId","gender","age","name","height","weight","ethnicity"}
if v, ok := m[k]; ok {
    values = append(values, v)
    where = append(where, fmt.Sprintf("%s = ?", k))
}
r, err := db.QueryRow("SELECT name FROM users WHERE " + strings.Join(where, " AND "), values...)

This is not susceptible to SQL injection because placeholders are used for parts of the query outside the application's direct control.

If the map keys are known to be allowed field names, then use this:

var values []interface{}
var where []string
for k, v := range m {
    values = append(values, v)
    where = append(where, fmt.Sprintf("%s = ?", k))
}
r, err := db.QueryRow("SELECT name FROM users WHERE " + strings.Join(where, " AND "), values...)