Go语言操作MySQL-KeepStudy

Go语言操作MySQL

##安装第三方库
```go
go get github.com/go-sql-driver/mysql 
go get github.com/jmoiron/sqlx
```

##链接数据库
```go
package main
import (
    "fmt"
	//使用第三方开源的mysql库
    _ "github.com/go-sql-driver/mysql" //mysql驱动
    "github.com/jmoiron/sqlx"  //基于mysql驱动的封装
)

type Person struct {
    UserId   int    `db:"user_id"`
    Username string `db:"username"`
    Sex      string `db:"sex"`
    Email    string `db:"email"`
}
type Place struct {
    Country string `db:"country"`
    City    string `db:"city"`
    TelCode int    `db:"telcode"`
}

var Db *sqlx.DB

func init() {
    // 链接mysql database, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名")
    database, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/test")
    if err != nil {
        fmt.Println("open mysql failed,", err)
        return
    }
    Db = database
    defer db.Close()  // 注意这行代码要写在上面err判断的下面
}
```

##Insert操作
```go
func main() {
   //Insert操作
    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)
}
```
##Select操作
```go
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)
}
```
## Update操作
```go
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)
}
```
## Delete操作
```go
func main() {
 
    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)
}
```

## MySQL事务
- mysql事务特性：
1) 原子性
2) 一致性
3) 隔离性
4) 持久性

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

```go
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()
}
```

顶部

Go数据操作

目录

相关推荐