MySQL is a commonly used relational database. This paper introduces how to operate MySQL database in go language.

Go Manipulates MySQL

Connect

The database/sql package in Go language provides a wide range of interfaces to guarantee SQL or quasi-SQL databases, and does not provide specific database drivers. A database driver must be injected when using the database/sql package.

Download dependency

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

Using MySQL Driver

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

Open opens a database specified by driverName, dataSourceName specifies a data source, and a generic package includes at least the database file name and possible connection information.

package main

import (
    "database/sql"
)

func main() {
    dsn := "user:password@tcp(127.0.0.1:3306)/dbname"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        panic(err)
    }
    defer db.Close()
}

Initialize connection

The Open function may simply validate its parameters, rather than create a connection to the database. If you want to check the validity of the name of the data source, you should call the ping method of the return value.
The returned DB can be safely invoked by multiple goroutine s at the same time and maintains its own idle connection pool. In this way, the Open function only needs to be called once. There is little need to close DB.

var db *sql.DB

func initDB() (err error) {
    dsn := "user:password@tcp(127.0.0.1:3306)/test"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        return err
    }
    err = db.Ping()
    if err != nil {
        return err
    }
    return nil
}
func main() {
    err := initDB()
    if err != nil {
        fmt.Printf("init db failed,err:%v\n", err)
        return
    }
}

sql.DB is a database operation handle, representing a connection pool with zero to read underlying connections. It can be safely used by multiple goroutine s at the same time. The database/sql package automatically creates and releases connections; it also maintains an idle connection pool.

SetMaxOpenConns

func (db *DB) SetMaxOpenConns(n int)

SetMax OpenConns sets the maximum number of connections to the database. If n is greater than 0 and less than the maximum number of idle connections, the maximum number of idle connections will be reduced to the limit of matching the maximum number of open connections. If n<=0, the maximum number of open connections will not be limited. The default is 0.

SetMaxIdleConns

func (db *DB)SetMaxIdleConns(n int)

SetMaxIdleConns sets the maximum number of idle connections in the connection pool. If n is greater than the maximum number of open connections, the new maximum number of idle connections will be reduced to the limit of matching the maximum number of open connections. If n<=0, the restricted connection is not reserved.

CRUD

Establishment of database and tables

Let's first create a database called sql_test in MySQL.

CREATE DATABASE sql_test;

Enter the database:

use sql_test;

Execute the following command to create a data table for testing:

CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

query

Single line query

A single-line query db.QueryRow() executes a query and expects to return up to one row of results (that is, Row). QueryRow always returns a non-nil value until the Scan method of the return value is called, and then returns a delayed error (e.g., no result is found).

func (db *DB)QueryRow(query string, args ...interface{})*Row

Specific example code:

func queryRowDeme() {
    sqlstr := "select id ,name,age from user where id =?"
    var u user
    err := db.QueryRow(sqlstr, 1).Scan(&u.id, &u.name, &u.name)
    if err != nil {
        fmt.Printf("scan failed,err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)

}

Multi-line query

Multi-line query db.Query() executes a query and returns a multi-line result (Rows), which is generally used to execute the select command. The parameter args represents the placeholder parameter in query.

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)

Specific example code:

func queryMultiRowDemo() {
    sqlstr := "select id,name,age from user where id >?"
    rows, err := db.Query(sqlstr, 0)
    if err != nil {
        fmt.Printf("query failed,err:%v\n", err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan failed,err:%v\n", err)
            return
        }
        fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
    }
}

insert data

Insert, update and delete operations all use methods:

func (db *DB) Exec(query string, args ...interface{})(Result,err)

Exec executes a command (including queries, deletions, updates, inserts, etc.), and the Result returned is a summary of the executed SQL commands. The args parameter represents the placeholder parameter in query.
The sample code for inserting data is as follows:

func insertRowDemo(){
    sqlstr := "insert into user(name, age) values (?,?)"
    ret,err :db.Exec(sqlstr,"Zhang Yi"，18)
    if err != nil{
        fmt.Printf("insert failed,err:%v\n",err)
        return
    }

    theID,err := ret.LastInsertId()
    if err != nil{
        fmt.Printf("get lastinsert ID failed,err:%v\n",err)
        return 
    }
    fmt.Printf("insert success,the id is %d.\n",theID)
}

Update data

Specifically update the data sample code as follows:

func updateRowDemo(){
    sqlstr := "update user set age=? where id =?"
    ret ,err := db.Exec(sqlstr,39,3)
    if err != nil{
        fmt.Printf("update failed,err:%v\n",err)
        return 
    }
    n, err := ret.RowsAffected()
    if err != nil{
        fmt.Printf("get Rowaffected failed,err:%v\n",err)
        return
    }
    fmt.Printf("update success,affected rows:%d\n",n)
}

Delete data

The sample code for deleting data is as follows:

func deleteRowDemo(){
    sqlstr = "delete from user where id =?"
    ret,err := db.Exec(sqlstr,3)
    if err != nil{
        fmt.Printf("delete failed,err:%v\n",err)
        return 
    }
    n ,err := ret.RowsAffected()
    if err != nil{
        fmt.Printf("get RowsAffected failed,err:%v\n",err)
        return
    }
    fmt.Printf("delete success,affected rows:%d\n",n)
}