why's blog

Go：mysql

时间：Feb. 16, 2020 分类：Golang

Go操作数据库

Go语言中database/sql中提供了类SQL数据库的泛用接口，并不提供具体的数据库驱动，使用database/sql包的时候必须注入一个数据库驱动

依赖下载

$ go get -u github.com/go-sql-driver/mysql

使用MySQL驱动

database/sql中有Open方法

func Open(driverName, dataSourceName string) (*DB, error)

driverName 驱动名称
dataSourceName 数据源字符串

package main

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    dsn := "root:123456@tcp(172.17.0.2:3306)/test"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        panic(err)
    }   
    defer db.Close()
}

这一步只是会校验字符串格式

初始化连接

db.Ping()用于尝试与数据库进行连接，校验用户名密码等是否正确

package main

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

var db *sql.DB

func initDB() (err error) {
    dsn := "root:123456@tcp(172.17.0.2:3306)/test"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return
    }
    defer db.Close() 
    err = db.Ping()
    if err != nil {
        return
    }
    fmt.Println("连接数据库成功")
    return nil 
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Print("init db failed, err: %v\n", err)
        return
    }
}

这时的sql.DB就是一个数据库的操作句柄，维护着连接池，自动创建和释放连接

SetMaxOpenConns(n int)方法可以设置数据库建立连接的最大数量，如果n小于0不会开启最大连接数限制，默认为0无限制
SetMaxIdleConns(n int)方法可以设置最大的空闲连接数

CURD操作

创建测试数据

CREATE DATABASE test;
use test;
CREATE TABLE `user` (
    `id` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(20) DEFAULT '',
    `age` INT(11) DEFAULT '0',
    PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
INSERT INTO user (name, age) VALUES ('why', 26);
INSERT INTO user (name, age) VALUES ('pqt', 26);
INSERT INTO user (name, age) VALUES ('mb', 28);

查询

package main


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

var db *sql.DB

type user struct {
    id int
    name string
    age int
}

func initDB() (err error) {
    dsn := "root:123456@tcp(172.17.0.2:3306)/test"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        return
    }
    err = db.Ping()
    if err != nil {
        return
    }
    fmt.Println("连接数据库成功")
    db.SetMaxOpenConns(10)
    return nil 
}

func queryOne(n int) {
    var u user
    sqlStr := `select id, name, age from user where id=?`
    // Scan会关闭Row的
    err := db.QueryRow(sqlStr, n).Scan(&u.id, &u.name, &u.age) 
    if err != nil {
        fmt.Printf("scan failed, err: %v\n", err)
        return
    }
    fmt.Printf("u:%#v\n", u)
}


func queryMore(n int) {
     sqlStr := `select id, name, age from user where id>?`
     rows, err := db.Query(sqlStr, n)
     if err != nil {
         fmt.Printf("exec %s query failed, err:%v\n", sqlStr, err)
         return
     }
     defer rows.Close()
     for rows.Next() {
         var u user
         err := rows.Scan(&u.id, &u.name, &u.age)
         if err != nil {
             fmt.Printf("scan failed, err:%v\n", err)
         }
         fmt.Printf("u:%#v\n", u)
     }
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Print("init db failed, err: %v\n", err)
        return
    }
    queryOne(1)
    queryMore(1)
    defer db.Close()
}

对于单行查询，QueryRow总会返回非nil的值，直到被调用Scan

增删改

package main


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

var db *sql.DB

type user struct {
    id int
    name string
    age int
}

func initDB() (err error) {
    dsn := "root:123456@tcp(172.17.0.2:3306)/test"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        return
    }
    err = db.Ping()
    if err != nil {
        return
    }
    fmt.Println("连接数据库成功")
    db.SetMaxOpenConns(10)
    return nil 
}

func queryOne(n int) {
    var u user
    sqlStr := `select id, name, age from user where id=?`
    // Scan会关闭Row的
    err := db.QueryRow(sqlStr, n).Scan(&u.id, &u.name, &u.age) 
    if err != nil {
        fmt.Printf("scan failed, err: %v\n", err)
        return
    }
    fmt.Printf("u:%#v\n", u)
}


func queryMore(n int) {
     sqlStr := `select id, name, age from user where id>?`
     rows, err := db.Query(sqlStr, n)
     if err != nil {
         fmt.Printf("exec %s query failed, err:%v\n", sqlStr, err)
         return
     }
     defer rows.Close()
     for rows.Next() {
         var u user
         err := rows.Scan(&u.id, &u.name, &u.age)
         if err != nil {
             fmt.Printf("scan failed, err:%v\n", err)
         }
         fmt.Printf("u:%#v\n", u)
     }
}

func insertRow() {
    sqlStr := "insert into user(name, age) values (?,?)"
    ret, err := db.Exec(sqlStr, "a", 33)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId()
    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

func updataRow() {
    sqlStr := "update user set age=? where id=?"   
    ret, err := db.Exec(sqlStr, 39, 3)
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

func deleteRow() {
    sqlStr := "delete from user where id = ?"
    ret, err := db.Exec(sqlStr, 3)
    if err != nil {
    fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Print("init db failed, err: %v\n", err)
        return
    }
    // queryOne(1)
    // queryMore(1)
    insertRow()
    queryMore(1)
    updataRow()
    queryMore(1)
    deleteRow()
    queryMore(1)

    defer db.Close()
}

执行命令

连接数据库成功
insert success, the id is 4.
u:main.user{id:2, name:"pqt", age:26}
u:main.user{id:3, name:"mb", age:28}
u:main.user{id:4, name:"a", age:33}
update success, affected rows:1
u:main.user{id:2, name:"pqt", age:26}
u:main.user{id:3, name:"mb", age:39}
u:main.user{id:4, name:"a", age:33}
delete success, affected rows:1
u:main.user{id:2, name:"pqt", age:26}
u:main.user{id:4, name:"a", age:33}

MySQL事务

事务相关的方法

Begin 开启事务
Commit 提交事务
Rollback 回滚事务

package main


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

var db *sql.DB

type user struct {
    id int
    name string
    age int
}

func initDB() (err error) {
    dsn := "root:123456@tcp(172.17.0.2:3306)/test"
    db, err = sql.Open("mysql", dsn)
    if err != nil {
        return
    }
    err = db.Ping()
    if err != nil {
        return
    }
    fmt.Println("连接数据库成功")
    db.SetMaxOpenConns(10)
    return nil 
}

func transaction() {
    // 开启事务
    tx, err := db.Begin()
    if err != nil {
        if tx != nil {
            tx.Rollback()
        }
        fmt.Printf("begin trans failed, err:%v\n", err)
        return
    }
    sqlStr1 := "update user set age=30 where id=?"
    _, err = tx.Exec(sqlStr1, 2)
    if err != nil {
        tx.Rollback()
        fmt.Printf("exec sql failed, err:%v\n", err)
        return
    }
    sqlStr2 := "update user set age=50 where id=?"
    _, err = tx.Exec(sqlStr2, 4)
    if err != nil {
        tx.Rollback()
        fmt.Printf("exec sql failed, err:%v\n", err)
        return
    }
    // 提交事务
    err = tx.Commit()
    if err != nil {
        tx.Rollback()
        fmt.Printf("commit failed, err:%v\n", err)
        return
    }
    fmt.Println("exec trans success!")
}

func main() {
    err := initDB()
    if err != nil {
        fmt.Print("init db failed, err: %v\n", err)
        return
    }
    transaction()

    defer db.Close()
}

SQL占位符

对于不同的数据库引擎，占位符也不尽相同

数据库	占位符语法
MySQL	?
PostgreSQL	$1, $2
SQLite	$1和?
Oracle	:name

对于SQL注入问题，不要使用fmt.Sprintf拼接

sqlx

sqlx可以简化操作

安装方式

go get github.com/jmoiron/sqlx

连接数据库

var db *sqlx.DB

func initDB() (err error) {
    dsn := "user:password@tcp(127.0.0.1:3306)/test"
    // 也可以使用MustConnect连接不成功就panic
    db, err = sqlx.Connect("mysql", dsn)
    if err != nil {
        fmt.Printf("connect DB failed, err:%v\n", err)
        return
    }
    db.SetMaxOpenConns(20)
    db.SetMaxIdleConns(10)
    return
}

CURD

// 查询单条数据示例
func queryRowDemo() {
    sqlStr := "select id, name, age from user where id=?"
    var u user
    err := db.Get(&u, sqlStr, 1)
    if err != nil {
        fmt.Printf("get failed, err:%v\n", err)
        return
    }
    fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}
// 查询多条数据示例
func queryMultiRowDemo() {
    sqlStr := "select id, name, age from user where id > ?"
    var users []user
    err := db.Select(&users, sqlStr, 0)
    if err != nil {
        fmt.Printf("query failed, err:%v\n", err)
        return
    }
    fmt.Printf("users:%#v\n", users)
}
// 插入数据
func insertRowDemo() {
    sqlStr := "insert into user(name, age) values (?,?)"
    ret, err := db.Exec(sqlStr, "沙河小王子", 19)
    if err != nil {
        fmt.Printf("insert failed, err:%v\n", err)
        return
    }
    theID, err := ret.LastInsertId() // 新插入数据的id
    if err != nil {
        fmt.Printf("get lastinsert ID failed, err:%v\n", err)
        return
    }
    fmt.Printf("insert success, the id is %d.\n", theID)
}

// 更新数据
func updateRowDemo() {
    sqlStr := "update user set age=? where id = ?"
    ret, err := db.Exec(sqlStr, 39, 6)
    if err != nil {
        fmt.Printf("update failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("update success, affected rows:%d\n", n)
}

// 删除数据
func deleteRowDemo() {
    sqlStr := "delete from user where id = ?"
    ret, err := db.Exec(sqlStr, 6)
    if err != nil {
        fmt.Printf("delete failed, err:%v\n", err)
        return
    }
    n, err := ret.RowsAffected() // 操作影响的行数
    if err != nil {
        fmt.Printf("get RowsAffected failed, err:%v\n", err)
        return
    }
    fmt.Printf("delete success, affected rows:%d\n", n)
}

事务操作

使用db.Beginx()和tx.MustExec()能简化一部分错误处理

func transactionDemo() {
    tx, err := db.Beginx() // 开启事务
    if err != nil {
        if tx != nil {
            tx.Rollback()
        }
        fmt.Printf("begin trans failed, err:%v\n", err)
        return
    }
    sqlStr1 := "Update user set age=40 where id=?"
    tx.MustExec(sqlStr1, 2)
    sqlStr2 := "Update user set age=50 where id=?"
    tx.MustExec(sqlStr2, 4)
    err = tx.Commit() // 提交事务
    if err != nil {
        tx.Rollback() // 回滚
        fmt.Printf("commit failed, err:%v\n", err)
        return
    }
    fmt.Println("exec trans success!")
}

火眼征信大数据工程师闫大佬