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 became the defacto 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.
- https://github.com/go-sql-driver/mysql supports
database/sql
, written in pure Go. - https://github.com/ziutek/mymysql supports
database/sql
and user defined interfaces, written in pure Go. - https://github.com/Philio/GoMySQL only supports user defined interfaces, written in pure Go.
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.
- Fully supports
databse/sql
interface standards. - Supports keepalive, 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:
CREATE TABLE `userinfo` (
`uid` INT(10) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(64) NULL DEFAULT NULL,
`departname` VARCHAR(64) NULL DEFAULT NULL,
`created` DATE NULL DEFAULT NULL,
PRIMARY KEY (`uid`)
);
The following example shows how to operate on a database based on the database/sql
interface standards.
package main
import (
_ "github.com/go-sql-driver/mysql"
"database/sql"
"fmt"
)
func main() {
db, err := sql.Open("mysql", "astaxie:astaxie@/test?charset=utf8")
checkErr(err)
// insert
stmt, err := db.Prepare("INSERT userinfo SET username=?,departname=?,created=?")
checkErr(err)
res, err := stmt.Exec("astaxie", "研发部门", "2012-12-09")
checkErr(err)
id, err := res.LastInsertId()
checkErr(err)
fmt.Println(id)
// update
stmt, err = db.Prepare("update userinfo set username=? where uid=?")
checkErr(err)
res, err = stmt.Exec("astaxieupdate", id)
checkErr(err)
affect, err := res.RowsAffected()
checkErr(err)
fmt.Println(affect)
// query
rows, err := db.Query("SELECT * FROM userinfo")
checkErr(err)
for rows.Next() {
var uid int
var username string
var department string
var created string
err = rows.Scan(&uid, &username, &department, &created)
checkErr(err)
fmt.Println(uid)
fmt.Println(username)
fmt.Println(department)
fmt.Println(created)
}
// delete
stmt, err = db.Prepare("delete from userinfo where uid=?")
checkErr(err)
res, err = stmt.Exec(id)
checkErr(err)
affect, err = res.RowsAffected()
checkErr(err)
fmt.Println(affect)
db.Close()
}
func checkErr(err error) {
if err != nil {
panic(err)
}
}
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:user@unix(/path/to/socket)/dbname?charset=utf8 user:password@tcp(localhost:5555)/dbname?charset=utf8 user:password@/dbname 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.
Links
- Directory
- Previous section: database/sql interface
- Next section: SQLite