How to INSERT data into a MySQL database using Golang

A guide on how to use Prepared Statements in Golang to insert data into a MySQL database with multiple parameters.

In this article, you'll learn how to use Prepared Statements in Golang to insert data into a MySQL table with multiple parameters, enhancing efficiency and security when handling data.

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/go-sql-driver/mysql"
)

func main() {
    // Connect to the MySQL database
    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/test_db")
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Check the connection
    if err := db.Ping(); err != nil {
        log.Fatal(err)
    }

    // Prepare the INSERT statement with multiple parameters
    stmt, err := db.Prepare("INSERT INTO students(name, age, email) VALUES (?, ?, ?)")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    // Insert data
    data := [][]interface{}{
        {"Nguyen Van A", 20, "[email protected]"},
        {"Tran Thi B", 22, "[email protected]"},
        {"Le Van C", 21, "[email protected]"},
    }

    for _, record := range data {
        _, err = stmt.Exec(record...)
        if err != nil {
            log.Printf("Error inserting record %v: %v", record, err)
        } else {
            fmt.Printf("Inserted record: %v\n", record)
        }
    }
}

Detailed explanation:

1. import: Imports necessary packages, including database/sql and github.com/go-sql-driver/mysql for MySQL connection.
2. sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/test_db"): Connects to the MySQL database using user credentials and address.
3. db.Ping(): Checks the connection to the database.
4. db.Prepare(...): Prepares the INSERT statement with multiple parameters (?) to prevent SQL injection.
5. data := [][]interface{}{...}: Creates sample data to be inserted using multiple parameters.
6. stmt.Exec(record...): Executes the INSERT statement with data from record.

System Requirements:

• Golang 1.15+
• Library: github.com/go-sql-driver/mysql

How to install the libraries needed to run the Golang code above:

Use the following command to install the MySQL driver library:

go get -u github.com/go-sql-driver/mysql

Tips:

• Using Prepared Statements ensures better security and prevents SQL injection attacks.
• Always check for errors at every step to detect issues early.