How Can I Efficiently Insert Multiple Rows of Data into a Go Database Using Prepared Statements?

Efficient Multiple Data Insertion in Go

Inserting multiple data rows into a database in a single operation can significantly improve efficiency. In Go, one common way to accomplish this is through the use of prepared statements. While using string concatenation as demonstrated in your question may seem convenient, it's less secure and can lead to SQL injection vulnerabilities.

Using Prepared Statements

To insert multiple rows using prepared statements, you can follow these steps:

1. Create a template SQL statement with placeholders for the data values:

INSERT INTO test(n1, n2, n3) VALUES (?, ?, ?)

2. Prepare the statement using the db.Prepare method:

stmt, err := db.Prepare(sqlStr)
if err != nil {
    // Handle error
}

3. Construct a slice of values for each row:

vals := []interface{}{}

for _, dataRow := range data {
    vals = append(vals, dataRow["v1"], dataRow["v2"], dataRow["v3"])
}

4. Execute the statement with the values:

res, err := stmt.Exec(vals...)
if err != nil {
    // Handle error
}

This approach ensures that the SQL statement is only parsed once and the values are securely inserted as parameters.

Example:

Here's an example of inserting multiple rows into a "test" table using prepared statements:

import (
    "database/sql"
    "fmt"
)

type DataRow struct {
    v1 string
    v2 string
    v3 string
}

func main() {
    data := []DataRow{
        {v1: "1", v2: "1", v3: "1"},
        {v1: "2", v2: "2", v3: "2"},
        {v1: "3", v2: "3", v3: "3"},
    }

    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database")
    if err != nil {
        // Handle error
    }

    sqlStr := "INSERT INTO test(n1, n2, n3) VALUES (?, ?, ?)"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        // Handle error
    }

    vals := []interface{}{}
    for _, dataRow := range data {
        vals = append(vals, dataRow.v1, dataRow.v2, dataRow.v3)
    }

    res, err := stmt.Exec(vals...)
    if err != nil {
        // Handle error
    }

    affectedRows, err := res.RowsAffected()
    if err != nil {
        // Handle error
    }

    fmt.Printf("Inserted %d rows", affectedRows)
}

The above is the detailed content of How Can I Efficiently Insert Multiple Rows of Data into a Go Database Using Prepared Statements?. For more information, please follow other related articles on the PHP Chinese website!