golang操作mysql

1. 安装mysql驱动库和sqlx基于官方sql库的扩展库

1 2	`go` `get github.com/go-sql-driver/mysql` `go` `get github.com/jmoiron/sqlx`

2. 链接mysql

package main
 
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"  // 初始化mysql驱动
    "github.com/jmoiron/sqlx" // sqlx是一个库，为go标准库database/sql提供了一组扩展
)
 
var db *sqlx.DB
 
func init() {
    // 初始化
    database, err := sql.Open("mysql", "username:password@protocal(host:port)/db_name")
    if err != nil {
        fmt.Println("open mysql field", err)
        return
    }
 
    db = database
}

3. insert

func main() {
    defer db.Close()
 
    result, err := db.Exec("insert into person(username, sex, email)values(?,?,?)", "abc", "男", "myn_cn@163.com")
    if err != nil {
        fmt.Println("Exec failed", err)
        return
    }
 
    lastId, err := result.LastInsertId()
    if err != nil {
        fmt.Println("exec failed", err)
        return
    }
 
    fmt.Println("insert success", lastId)
 
}

4. select

type Person struct {
    Userid int `db:"user_id"`
    Username string `db:"username"`
    Sex string `db:"sex"`
    Email string `db:"email"`
}
 
func main() {
    defer db.Close()
 
    var person []Person
    if err := db.Select(&person, "select user_id, username, sex, email from person where user_id = ?;", 2); err != nil {
        fmt.Println("select failed", err)
        return
    }
 
    fmt.Println("select success", person)
 
}

5. update

func main() {
    result, err := db.Exec("update person set username = ? where user_id = ?", "嘿嘿2", 2)
    if err != nil {
        fmt.Println("exec failed", err)
        return
    }
 
    rows, err := result.RowsAffected()
    if err != nil {
        fmt.Println("exec failed", err)
    }
 
    fmt.Println("update success", rows)
 
}

6. delete

func main() {
    result, err := db.Exec("delete from person where user_id = ?", 2)
    if err != nil {
        fmt.Println("exec failed", err)
        return
    }
 
    rows, err := result.RowsAffected()
    if err != nil {
        fmt.Println("exec failed", err)
    }
 
    fmt.Println("delete success", rows)
 
}

7. mysql事务

func main() {
    conn, err := db.Begin()  // 开启事务
 
    if err != nil {
        fmt.Println("begin err", err)
        return
    }
 
    result, err := conn.Exec("insert into person(username, sex, email)values(?,?,?)", "张三", "女", "1341935532@qq.com")
    if err != nil {
        fmt.Println("exec failed", err)
        conn.Rollback()  // 回滚事务
        return
    }
 
    rows, err := result.RowsAffected()
    if err != nil {
        fmt.Println("exec failed", err)
        conn.Rollback()  // 回滚事务
        return
    }
 
    conn.Commit()  // 提交事务
    fmt.Println("delete success", rows)
 
}