对MySQL数据库进行动态SQL查询 [英] Making dynamic SQL queries to a MySQL DB

问题描述

我以前从未将golang与mysql一起使用，因此我是第一次阅读这些内容.我想做这样的事情:

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)

输入的内容太多了，尤其是当我想将更多变量添加到WHERE条件中时.

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

如果这是PHP，我会做这样的事情:

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

通过使用foreach循环，我可以动态生成查询并根据需要动态绑定尽可能多的变量.

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

像这样的东西是golang和mysql的选项吗?还是有一些替代方案，可以不为查询输入每个变量组合?

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?

推荐答案

如果您的地图具有这样的字段名称和值:

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

这不受SQL注入的影响，因为占位符用于应用程序直接控制范围之外的查询部分.

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

这篇关于对MySQL数据库进行动态SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！