A brief explanation of how to use MySQL in the Go program

  • 2020-05-30 20:21:54
  • OfStack

go officially only provides database package, database package with two packages sql, sql/driver. The two packages are used to define the interfaces that operate on the database, which ensures that they operate in the same way no matter which database is used.

go official did not provide driver connect to the database, if you want to database operation, also need the 3rd party driver package, fortunately, the mainstream of the database driver have: https: / / code google. com/p/go - wiki/wiki/SQLDrivers

Among them, mysql has two packages, one is mymysql, one is go-sql-driver /mysql, and both package are pure go implementations. I chose the latter, because the latter is more efficient at 1 o 'clock, benchmark here: https: / / github com/go - sql - driver/sql - benchmark
The use of the sql package is clear and concise:
1. Establish a connection
The first is Open,

db, err := sql.Open( " mysql " ,  " user:password@/dbname " ) 

db is a pointer of type * sql.DB, which is used in all subsequent operations
After open, there is no actual connection to the database, the actual connection to the database is made through the Ping method. In addition, db should exist throughout the life cycle of the program, that is, when program 1 is started, db is obtained through Open, and then Close db is obtained until the program ends, instead of Open/Close often.
err = db.Ping()

2. Basic usage
The main methods of DB are:
Query performs Query operations for the database, such as 1 Select statement, returning *Rows

QueryRow performs an Query operation that returns at most 1 row of the database, returning *Row

PrePare prepares 1 database query operation and returns 1 *Stmt for subsequent query or execution. This Stmt can be executed multiple times, or concurrently

The number of Exec executions does not return any rows database statements, such as delete operations

3. Simple example

package main import (
 _ "github.com/go-sql-driver/mysql"
) type User struct {
 id                 int
 username, password string
} var sqldata map[interface{}]interface{} func main() {
 var u User
 db, err := sql.Open("mysql", "root:@/test?charset=utf8")
  Insert data
 stmt, err := db.Prepare("INSERT user SET username=?,password=?")
 res, err := stmt.Exec("xiaowei", "xiaowei")
 id, err := res.LastInsertId()
 // Query data
 rows, err := db.Query("SELECT * FROM user")
 check(err)  fmt.Println(rows.Columns())
 userinfo := make(map[interface{}]interface{})
 for rows.Next() {
  err := rows.Scan(&u.id, &u.username, &u.password)
  userinfo[u.id] = u
} func check(err error) {
 if err != nil {

Related articles: