5.2 MySQL

The LAMP stack has been very popular on the internet in recent years, and the M in LAMP stand for MySQL. MySQL is famous because it’s open source and easy to use. As such, it has become the de-facto database in the back-ends of many websites.

MySQL drivers

There are a couple of drivers that support MySQL in Go. Some of them implement the database/sql interface, and others use their own interface standards.

I’ll use the first driver in the following examples (I use this one in my personal projects too), and I also recommend that you use it for the following reasons:

  • It’s a new database driver and supports more features.
  • It fully supports database/sql interface standards.
  • Supports keep-alive, long connections with thread-safety.

Samples

In the following sections, I’ll use the same database table structure for different databases, then create SQL as follows:

  1.     CREATE TABLE `userinfo` (
  2.         `uid` INT(10) NOT NULL AUTO_INCREMENT,
  3.         `username` VARCHAR(64) NULL DEFAULT NULL,
  4.         `department` VARCHAR(64) NULL DEFAULT NULL,
  5.         `created` DATE NULL DEFAULT NULL,
  6.         PRIMARY KEY (`uid`)
  7.     );

The following example shows how to operate on a database based on the database/sql interface standards.

  1.     package main
  3.     import (
  4.         _ "github.com/go-sql-driver/mysql"
  5.         "database/sql"
  6.         "fmt"
  7.     )
  9.     func main() {
  10.         db, err := sql.Open("mysql", "astaxie:astaxie@/test?charset=utf8")
  11.         checkErr(err)
  13.         // insert
  14.         stmt, err := db.Prepare("INSERT userinfo SET username=?,department=?,created=?")
  15.         checkErr(err)
  17.         res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
  18.         checkErr(err)
  20.         id, err := res.LastInsertId()
  21.         checkErr(err)
  23.         fmt.Println(id)
  24.         // update
  25.         stmt, err = db.Prepare("update userinfo set username=? where uid=?")
  26.         checkErr(err)
  28.         res, err = stmt.Exec("astaxieupdate", id)
  29.         checkErr(err)
  31.         affect, err := res.RowsAffected()
  32.         checkErr(err)
  34.         fmt.Println(affect)
  36.         // query
  37.         rows, err := db.Query("SELECT * FROM userinfo")
  38.         checkErr(err)
  40.         for rows.Next() {
  41.             var uid int
  42.             var username string
  43.             var department string
  44.             var created string
  45.             err = rows.Scan(&uid, &username, &department, &created)
  46.             checkErr(err)
  47.             fmt.Println(uid)
  48.             fmt.Println(username)
  49.             fmt.Println(department)
  50.             fmt.Println(created)
  51.         }
  53.         rows.Close()
  55.         // delete
  56.         stmt, err = db.Prepare("delete from userinfo where uid=?")
  57.         checkErr(err)
  59.         res, err = stmt.Exec(id)
  60.         checkErr(err)
  62.         affect, err = res.RowsAffected()
  63.         checkErr(err)
  65.         fmt.Println(affect)
  67.         db.Close()
  69.     }
  71.     func checkErr(err error) {
  72.         if err != nil {
  73.             panic(err)
  74.         }
  75.     }

Let me explain a few of the important functions here:

  • sql.Open() opens a registered database driver. The Go-MySQL-Driver registered the mysql driver here. The second argument is the DSN (Data Source Name) that defines information pertaining to the database connection. It supports following formats:

    1.   user@unix(/path/to/socket)/dbname?charset=utf8
    2.   user:password@tcp(localhost:5555)/dbname?charset=utf8
    3.   user:password@/dbname
    4.   user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname

  • db.Prepare() returns a SQL operation that is going to be executed. It also returns the execution status after executing SQL.

  • db.Query() executes SQL and returns a Rows result.
  • stmt.Exec() executes SQL that has been prepared and stored in Stmt.

Note that we use the format =? to pass arguments. This is necessary for preventing SQL injection attacks.