Go语言数据库操作深入讲解

天涯学馆
更新于 2024-10-12 17:02
阅读 263

目录go操作MySQLgo操作NoSQLgo操作PgSQLgo操作Redisgo操作ETCDzookeepergo操作kafkago操作RabbitMQgo操作ElasticSearchNSQgo操作MySQL使用第三方开源的mysql库:github.com/go

## 目录

- [go操作MySQL](#go操作MySQL)
- [go操作NoSQL](#go操作NoSQL)
- [go操作PgSQL](#go操作PgSQL)
- [go操作Redis](#go操作Redis)
- [go操作ETCD](#go操作ETCD)
- [zookeeper](#zookeeper)
- [go操作kafka](#go操作kafka)
- [go操作RabbitMQ](#go操作RabbitMQ)
- [go操作ElasticSearch](#go操作ElasticSearch)
- [NSQ](#NSQ)
 
---

## go操作MySQL

- 使用第三方开源的mysql库: github.com/go-sql-driver/mysql （mysql驱动）
  - github.com/jmoiron/sqlx （基于mysql驱动的封装）

命令行输入 ：

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

### Insert操作
```go
// 连接Mysql
database, err := sqlx.Open("mysql", "root:XXXX@tcp(127.0.0.1:3306)/test")
//database, err := sqlx.Open("数据库类型", "用户名:密码@tcp(地址:端口)/数据库名") 
package main
import (
    "fmt"
    _ "github.com/go-sql-driver/mysql"
    "github.com/jmoiron/sqlx"
)

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() {
    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判断的下面
}

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)
}
```
### Select操作
```go
package main

import (
    "fmt"

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

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() {

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判断的下面
}

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
package main

import (
    "fmt"

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

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() {

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判断的下面
}

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
package main

import (
    "fmt"

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

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() {

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判断的下面
}

func main() {

/*
    _, err := Db.Exec("delete from person where user_id=?", 1)
    if err != nil {
        fmt.Println("exec failed, ", err)
        return
    }
    */

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事务

```go
package main

import (
        "fmt"

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

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() {
        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
    }

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操作Redis

使用第三方开源的redis库: github.com/garyburd/redigo/redis: `go get github.com/garyburd/redigo/redis`

### Redis连接

```go
package main

import (
    "fmt"
    "github.com/garyburd/redigo/redis"
)