后端Go框架：

这是我参与【第五届青训营】伴学笔记创作活动的第11天。

1.连接mysql

• database, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")

    database, err := sqlx.Open("mysql", "root:XXXX@tcp(127.0.0.1:3306)/test")

• 创建一个数据库类

type Person struct {
    UserId   int    `db:"user_id"`
    Username string `db:"username"`
    Sex      string `db:"sex"`
    Email    string `db:"email"`
}

2.Insert操作

• Db.Exec来实现插入
• r.LastInsertId()来获取id

var Db *sqlx.DB

func main(){

 r, err := Db.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")
 
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }
    id, err := r.LastInsertId()
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }

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

3.Select操作：

• Db.Select(&person, "select user_id, username, sex, email from person where user_id=?", 1)
• 用一个数组去接收，记得带上指针

var Db *sqlx.DB


func main(){

var person []Person
    err := Db.Select(&person, "select user_id, username, sex, email from person where user_id=?", 1)
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }

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

4.Update操作：

• Db.Exec("update person set username=? where user_id=?", "stu0003", 1)

func main() {

    res, err := Db.Exec("update person set username=? where user_id=?", "stu0003", 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)

}

5.Delete操作

res, err := Db.Exec("delete from person where user_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)

6.Mysql事务

1.mysql事务特性：

1. 一致性
2. 隔离性
3. 持久性
4. 原子性

2.golang MySQL事务应用

1） import (“github.com/jmoiron/sqlx")
2)  Db.Begin()        开始事务
3)  Db.Commit()        提交事务
4)  Db.Rollback()     回滚事务

3.例子

    func main() {
        conn, err := Db.Begin()
        if err != nil {
            fmt.Println("begin failed :", err)
            return
        }

        r, err := conn.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")
        if err != nil {
            fmt.Println("exec failed, ", err)
            conn.Rollback()
            return
        }
        id, err := r.LastInsertId()
        if err != nil {
            fmt.Println("exec failed, ", err)
            conn.Rollback()
            return
        }
        fmt.Println("insert succ:", id)

        r, err = conn.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")
        if err != nil {
            fmt.Println("exec failed, ", err)
            conn.Rollback()
            return
        }
        id, err = r.LastInsertId()
        if err != nil {
            fmt.Println("exec failed, ", err)
            conn.Rollback()
            return
        }
        fmt.Println("insert succ:", id)

        conn.Commit()
    }