Go 操作MySQL

环境准备

# MySQL准备工作
$ sudo service mysql start 
 * Starting MySQL database server mysqld
   ...done.
$ mysql -uroot -e "create database test;"
$ mysql -uroot
MariaDB [test]> CREATE TABLE `person` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `name` varchar(260) DEFAULT NULL,
    ->     `age` int(28) DEFAULT NULL,
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.014 sec)

# 导包
$ go get github.com/go-sql-driver/mysql
$ go get github.com/jmoiron/sqlx

连接数据库

$ mkdir mysql-demo && cd mysql-demo && go mod init mysql-demo
$ vi main.go

// main.go
package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _"github.com/go-sql-driver/mysql"
)

func main(){
    db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test")
    if err != nil{
        panic(err)
    }
    defer db.Close()
    fmt.Println("connect to MySQL success...")
}

$ go run main.go        
connect to MySQL success...

Insert 操作

// main.go
package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _"github.com/go-sql-driver/mysql"
)

var (
    Db *sqlx.DB
)

func init(){
    db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test")
    if err != nil{
        fmt.Println("connect to MySQL failed...")
        panic(err)
    }
    // defer db.Close()

    Db = db
    fmt.Println("connect to MySQL success...")
}

func main(){
    defer Db.Close()
    r, err := Db.Exec("insert into person(name, age)values(?,?)","xiaoming","18")
    if err!=nil{
        fmt.Println("exec failed, ", err)
        panic(err)
    }

    id, err := r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }

    fmt.Println("insert succ:", id)
}

$ go run main.go           
connect to MySQL success...
insert succ: 1

Select 操作

// main.go
package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _"github.com/go-sql-driver/mysql"
)

var (
    Db *sqlx.DB
)

type Person struct {
    Id   int    `db:"id"`
    Name string `db:"name"`
    Age      string `db:"age"`
}

func init(){
    db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test")
    if err != nil{
        fmt.Println("connect to MySQL failed...")
        panic(err)
    }
    // defer db.Close()

    Db = db
    fmt.Println("connect to MySQL success...")
}

func main(){
    defer Db.Close()

    var p []Person
    err := Db.Select(&p, "select id,name,age from person where id = ?", 1)
    if err!=nil{
        fmt.Println("exec failed, ", err)
        panic(err)
    }

    fmt.Println("select succ:", p)
}

$ go run main.go   
connect to MySQL success...
select succ: [{1 xiaoming 18}]

Update操作

package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _"github.com/go-sql-driver/mysql"
)

var (
    Db *sqlx.DB
)

func init(){
    db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test")
    if err != nil{
        fmt.Println("connect to MySQL failed...")
        panic(err)
    }
    // defer db.Close()

    Db = db
    fmt.Println("connect to MySQL success...")
}

func main(){
    defer Db.Close()

    res, err := Db.Exec("update person set name=? where id=?", "xiaobai", 1)
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }
    row, err := res.RowsAffected()
    if err != nil {
        fmt.Println("rows failed, ",err)
    }
    fmt.Println("update succ:",row)
}

$ go run main.go          
connect to MySQL success...
update succ: 1

Delete 操作

package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _"github.com/go-sql-driver/mysql"
)

var (
    Db *sqlx.DB
)

func init(){
    db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test")
    if err != nil{
        fmt.Println("connect to MySQL failed...")
        panic(err)
    }
    // defer db.Close()

    Db = db
    fmt.Println("connect to MySQL success...")
}

func main(){
    defer Db.Close()

    res, err := Db.Exec("delete from person where id=?", 1)
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }
    row, err := res.RowsAffected()
    if err != nil {
        fmt.Println("rows failed, ",err)
    }
    fmt.Println("delete  succ:",row)
}

$ go run main.go  
connect to MySQL success...
delete  succ: 1

MySQL事务

事务具备四个特性(ACID)：原子性 一致性 隔离性 持久性
开启事务 Db.Begin()
提交事务 Db.Commit()
回滚事务 Db.Rollback()

package main

import(
    "fmt"
    "github.com/jmoiron/sqlx"
    _"github.com/go-sql-driver/mysql"
)

var (
    Db *sqlx.DB
)

func init(){
    db, err := sqlx.Open("mysql", "root:@tcp(localhost:3306)/test")
    if err != nil{
        fmt.Println("connect to MySQL failed...")
        panic(err)
    }
    // defer db.Close()

    Db = db
    fmt.Println("connect to MySQL success...")
}

func main(){
    defer Db.Close()

    // begin transaction
    conn, err := Db.Begin()


    if err != nil {
        fmt.Println("begin failed :", err)
        return
    }

    r, err := conn.Exec("insert into person(name, age)values(?, ?)", "xiaoming", 18)
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback() // rollback transaction
        return
    }
    id, err := r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback() // rollback transaction
        return
    }
    

    fmt.Println("insert succ:", id)

    r, err = conn.Exec("insert into person(name, age)values(?, ?)", "xiaohong", 18)
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback() // rollback transaction
        return
    }
    
    id, err = r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed, ", err)
        conn.Rollback() // rollback transaction
        return
    }
    fmt.Println("insert succ:", id)

    conn.Commit() // commit transaction
}

$ go run main.go       
connect to MySQL success...
insert succ: 2
insert succ: 3