How to UPDATE data in a MySQL database using Golang

A guide on how to update data in a MySQL database using Golang with Prepared Statements involving multiple parameters for enhanced security and efficiency.

In this article, you will learn how to use Golang to perform an UPDATE query in a MySQL database using Prepared Statements with multiple parameters. This method helps prevent security vulnerabilities like SQL Injection.

package main

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

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

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

    // Prepare the UPDATE statement
    stmt, err := db.Prepare("UPDATE students SET name=?, age=? WHERE id=?")
    if err != nil {
        log.Fatal(err)
    }
    defer stmt.Close()

    // Execute the statement with multiple parameters
    res, err := stmt.Exec("John Doe", 22, 1)
    if err != nil {
        log.Fatal(err)
    }

    // Check the number of rows affected
    rowsAffected, err := res.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("Rows updated: %d\n", rowsAffected)
}

Detailed explanation:

1. import (...): Imports the required packages such as database/sql and fmt.
2. db, err := sql.Open(...): Connects to the MySQL database using connection details.
3. defer db.Close(): Ensures the database connection is closed after completion.
4. stmt, err := db.Prepare(...): Prepares the UPDATE statement using a Prepared Statement.
5. defer stmt.Close(): Ensures the Prepared Statement is closed.
6. res, err := stmt.Exec(...): Executes the UPDATE statement with the specified parameters.
7. rowsAffected, err := res.RowsAffected(): Retrieves the number of rows affected and prints it.

System Requirements:

• Golang 1.16+
• MySQL driver 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 library:

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

Tips:

• Always use Prepared Statements to avoid SQL Injection.
• Ensure the database connection is correctly configured before running SQL commands.