MySQL

Before You Start

• Applies to: Go 1.21+ (examples verified with Go 1.22) · Prerequisites: Std: net · Read time: about 15 min

MySQL is currently the most popular open-source relational database. This article will not elaborate on specific SQL knowledge; if you don't know SQL, please learn it first. This article only briefly explains how to use Go to perform SQL operations. In projects, you generally don't use the driver directly for database operations but instead use an ORM framework. Here we use the sqlx library, which enhances the standard sql library without the rich ORM features but wins in simplicity. If you want to use an ORM, you can look into libraries like Gorm, Xorm, or Ent.

Dependencies

Download the sqlx library:

$ go get github.com/jmoiron/sqlx

sqlx and the standard library database/sql support not only MySQL but any database that implements the driver.Driver interface, such as:

• PostgreSQL
• Oracle
• MariaDB
• SQLite
• And other relational databases

To use a specific database, you need to implement a database driver. The driver can be one you wrote yourself or a third-party library. Before using it, you must register the driver with sql.Register before you can use it. However, most downloaded driver libraries will automatically register the driver, so you don't need to register it manually.

func Register(name string, driver driver.Driver)

Since MySQL is popular and simple, this article uses MySQL for explanation. Other relational databases operate similarly. Download the MySQL driver library:

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

Connecting to the Database

Using the sqlx.Open function, you can open a database connection. It takes two parameters: the first is the driver name, and the second is the data source (commonly abbreviated as DSN).

func Open(driverName, dataSourceName string) (*DB, error)

The driver name is the name used when registering the driver and must be consistent. The DSN is the database connection address, which may vary for each database. For MySQL, it looks like this:

db,err := sqlx.Open("mysql","root:123456@tcp(127.0.0.1:3306)/test")

Preparing Data

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  `age` tinyint(0) NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('12132', '张三', 35, '北京市');
INSERT INTO `user` VALUES ('16162', '王五', 22, '上海市');

SET FOREIGN_KEY_CHECKS = 1;

Query

Query and map results to a struct:

var db *sqlx.DB

type Person struct {
   UserId   string `db:"id"`
   Username string `db:"name"`
   Age      int    `db:"age"`
   Address  string `db:"address"`
}

func init() {
    conn, err := sqlx.Open("mysql", "root:wyh246859@tcp(127.0.0.1:3306)/test")
   if err != nil {
      fmt.Println("Open mysql failed", err)
      return
   }

   db = conn
}

func main() {
   query()
   defer db.Close()
}

func query() {
   var person Person
   // Get for single, Select for multiple
   err := db.Get(&person, "select * from user where id = ?", "12132")
   if err != nil {
      fmt.Println("query failed:", err)
      return
   }
   fmt.Printf("query succ:%+v", person)
}

func list() {
  var perons []Person
  err := db.Select(&perons, "select * from user")
  if err != nil {
    fmt.Println("list err", err)
    return
  }
  fmt.Printf("list succ,%+v", perons)
}

Insert

Insert data:

func insert() {
   result, err := db.Exec("insert into user value (?,?,?,?)", "120230", "李四", 12, "广州市")
   if err != nil {
      fmt.Println("insert err:", err)
      return
   }
   id, err := result.LastInsertId()
   if err != nil {
      fmt.Println("insert err:", err)
      return
   }
   fmt.Println("insert succ:", id)
}

Update

Update data:

func update() {
   res, err := db.Exec("update user set name = ? where id = ?", "赵六", "120230")
   if err != nil {
      fmt.Println("update err:", err)
      return
   }
   eff, err := res.RowsAffected()
   if err != nil || eff == 0 {
      fmt.Println("update err:", err)
      return
   }
   fmt.Println("Update succ")
}

Delete

Delete data:

func delete() {
   res, err := db.Exec("delete from user where id = ?", "120230")
   if err != nil {
      fmt.Println("delete err:", err)
      return
   }
   eff, err := res.RowsAffected()
   if err != nil || eff == 0 {
      fmt.Println("delete err:", err)
      return
   }
   fmt.Println("delete succ")
}

Transactions

func (db *DB) Begin() (*Tx, error) // Start a transaction
func (tx *Tx) Commit() error // Commit a transaction
func (tx *Tx) Rollback() error // Rollback a transaction

When starting a transaction, for safety, it's common to add defer tx.Rollback(). If an error occurs during the process, it will rollback. If the transaction is successfully committed, this rollback becomes ineffective.

func main() {

  transation, err := db.Begin()
  if err != nil {
    fmt.Println("transation err")
  }
    defer transation.Rollback()

  insert()
  query()
  update()
  query()
  delete()
   transation.Commit()
}

Complete Example

The program below runs as-is: copy it into a file and execute go run main.go, or paste it into the Go Playground.

package main

import (
	"fmt"
	"net/url"
	"strings"
)

func main() {
	params := url.Values{}
	params.Set("charset", "utf8mb4")
	params.Set("parseTime", "true")

	dsn := "user:pass@tcp(127.0.0.1:3306)/shop?" + params.Encode()
	fmt.Println("dsn has parseTime:", strings.Contains(dsn, "parseTime=true"))

	query := "SELECT id, name FROM users WHERE id = ? AND status = ?"
	fmt.Println("placeholders:", strings.Count(query, "?"))

	table := "users"
	fmt.Println("quoted table:", "`"+table+"`")
}

Expected output:

dsn has parseTime: true
placeholders: 2
quoted table: `users`

Common Pitfalls

• Always use placeholders (?). They prevent SQL injection and let the driver reuse prepared statements; identifiers must be quoted instead.
• Add parseTime=true to the DSN. Otherwise DATE/DATETIME columns fail to scan into time.Time.
• Configure the pool. Set SetMaxOpenConns, SetMaxIdleConns and SetConnMaxLifetime; the defaults are not production values.
• Close rows and check rows.Err(). A missing Close leaks the connection back to nobody.
• Keep transactions short. Use defer tx.Rollback() and commit explicitly; never do network I/O inside a transaction.

Change Log

• 2026-10-14: Added "Before You Start" notes, a complete runnable example with expected output, common pitfalls, and this change log. Closed a code block that was left unterminated.