使用切片执行预编译的 SQL 语句。

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

Execute sql prepared statement with slice

问题

我写了一个函数(当然是用Go语言),通过这个库将map[string]interface{}插入到MySQL中。

下面是代码的解释:

  1. 函数接收名为tablestring和名为datamap[string]interface{}
  2. 我将data分成键(变量名为columns)和值(变量名为values)。
  3. 我从columns变量生成了一个名为columns_text的字符串,它看起来像这样:first_name, last_name, birth_day, date_added
  4. 我从values变量生成了一个名为values_text的字符串,它看起来像这样:?, ?, ?, ?
  5. 我打开了MySQL连接:db, err := sql.Open("mysql", "user:pass@/database")
  6. 我创建了一个预处理语句:stmt, err := db.Prepare("INSERT INTO " + table + " ( " + columns_text + " ) VALUES ( " + values_text + " )")
  7. 我执行了预处理语句,但是我遇到了一个问题。列和值的数量总是在变化,而且stmt.Exec()命令不能接收像这样的切片(数组):stmt.Exec(values),只能接收单独的值,像这样:stmt.Exec(values[0], values[1], values[2]...)

问题:

我之前使用PHP,PDO::Statement可以在执行时接收一个数组。我该如何使用切片(数组)执行这个语句?(如果可以使用不同的库,请写出库的名称和使用方法,谢谢!)

代码:

func insertToDB(table string, data map[string]interface{}) {
    columns := make([]interface{}, 0, len(data))
    values := make([]interface{}, 0, len(data))

    for key, _ := range data {
        columns = append(columns, key)
        values = append(values, data[key])
    }

    columns_text := ""
    i := 0
    of := len(data)

    for i < of {
        column := columns[i].(string)

        if i == 0 {
            columns_text = column
        } else {
            columns_text = columns_text + ", " + column
        }

        i++
    }

    fmt.Println(columns_text + " = " + table)

    values_text := ""

    i = 0

    for i < of {

        if i == 0 {
            values_text = "?"
        } else {
            values_text = values_text + ", ?"
        }

        i++
    }

    fmt.Println(values_text)
    fmt.Println(values)
    fmt.Println(data)

    db, err := sql.Open("mysql", "root:root@/bacafe")
    if err != nil {
        return -1, err
    }
    defer db.Close()

    stmtIns, err := db.Prepare("INSERT INTO " + table + " ( " + columns_text + " ) VALUES ( " +  values_text + " )")
    if err != nil {
        return -1, err
    }
    defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

    result, err := stmtIns.Exec(values...)
    if err != nil {
        return -1, err
    } else {
        insertedID, err := result.LastInsertId()
        if err != nil {
            return -1, err
        } else {
            return int(insertedID), nil
        }
    }
}

编辑:我已经编辑了上面的函数,现在它完美地工作了。

谢谢!

英文:

I've wrote an function (In Go, of course) that inserting map[string]interface{} to mysql via this library.

Explanation of the code below:

  1. The functions receives string called table and map[string]interface{} called data.
  2. I separate the data to keys (variable called columns) and values (variable called values).
  3. I generate from the columns variable called column_text that will look like this: first_name, last_name, birth_day, date_added
  4. I generate from the values variable called variable_text that will look like this: ?, ?, ?, ?
  5. I open mysql connection: db, err := sql.Open(&quot;mysql&quot;, &quot;user:pass@/database&quot;)
  6. I create prepared statement: stmt, err := db.Prepare(&quot;INSERT INTO &quot; + table + &quot; ( &quot; + columns_text + &quot; ) VALUES ( &quot; + values_text + &quot; )&quot;)
  7. I execute the prepare statement. but I have a problem. the number of the columns and values changes all the time, and the stmt.Exec() command can't receive an slice (Array) like this: stmt.Exec(values), only the values alone like this: stmt.Exec(values[0], values[1], values[2]...)

The question:

I'm coming from PHP, where PDO::Statement could receive an array when executing.
How can I execute the statement with the slice (Array)? (If I can do it with different library, please write the name of the library and how to use it, thank you!)

The code:

func insertToDB(table string, data map[string]interface{}) {
columns := make([]interface{}, 0, len(data))
values := make([]interface{}, 0, len(data))
for  key, _ := range data {
columns = append(columns, key)
values = append(values, data[key])
}
columns_text := &quot;&quot;
i := 0
of := len(data)
for i &lt; of {
column := columns[i].(string)
if i == 0 {
columns_text = column
} else {
columns_text = columns_text + &quot;, &quot; + column
}
i++
}
fmt.Println(columns_text + &quot; = &quot; + table)
values_text := &quot;&quot;
i = 0
for i &lt; of {
if i == 0 {
values_text = &quot;?&quot;
} else {
values_text = values_text + &quot;, ?&quot;
}
i++
}
fmt.Println(values_text)
fmt.Println(values)
fmt.Println(data)
db, err := sql.Open(&quot;mysql&quot;, &quot;root:root@/bacafe&quot;)
if err != nil {
return -1, err
}
defer db.Close()
stmtIns, err := db.Prepare(&quot;INSERT INTO &quot; + table + &quot; ( &quot; + columns_text + &quot; ) VALUES ( &quot; +  values_text + &quot; )&quot;)
if err != nil {
return -1, err
}
defer stmtIns.Close() // Close the statement when we leave main() / the program terminates
result, err := stmtIns.Exec(values...)
if err != nil {
return -1, err
} else {
insertedID, err := result.LastInsertId()
if err != nil {
return -1, err
} else {
return int(insertedID), nil
}
}
}

EDIT: I've edited the function above and it works perfectly now.

Thank you!

答案1

得分: 7

你走在正确的道路上,然而Stmt.Exec接受args ...interface{}作为参数,所以对于你的具体示例,你需要改变两个地方:

......
values := make([]interface{}, 0, len(data))
......
//添加...可以展开values,可以将其类比为在JavaScript中的func.apply(this, array-of-values)。
_, err = stmtIns.Exec(values...)
英文:

You are on the right track however Stmt.Exec takes args ...interface{}, so for your specific example you need to change 2 things:

......
values := make([]interface{}, 0, len(data))
......
//adding ... expands the values, think of it like func.apply(this, array-of-values) in 
// javascript, in a way.
_, err = stmtIns.Exec(values...) 

huangapple
  • 本文由 发表于 2014年6月23日 01:10:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/24353646.html
匿名

发表评论

匿名网友

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

确定