简单聊聊Golang数据库编程的那些事

MySQLSQLServerPostgres

抛开其他编程语言不谈，在这篇文章中，我们就来聊一聊Go语言数据库编程的那些事，了解如何使用Go语言提供的标准库，编写通用的操作数据库的代码。

数据库连接与驱动

database/sql和database/sql/driver

database/sqldatabase/sql/driver

database/sql

import database/sql

SQL ServerMySQL

Go支持的数据库驱动包

database/sql/driverdatabase/sql/driver

下面是支持Golang的数据库驱动列表：

安装第三方驱动包

以MySQL数据库驱动包为例：

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

导入驱动包

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

sql.DB结构体

sql.DBsql/database

DSN

DSNData Source Namego-sql-driver/sql

//[用户名[:密码]@][协议(数据库服务器地址)]]/数据库名称?参数列表
[username[:password]@][protocol[(address)]]/dbname[?param1=value1&...&paramN=valueN]

初始化sql.DB

database/sql*sql.DB

//driverName表示驱动名，如mysql,dataSourceName为上文介绍的DSN
func Open(driverName, dataSourceName string) (*DB, error)
func OpenDB(c driver.Connector) *DB

下面演示如何使用这两个函数：

Open()*sql.DBOpen()

import "database/sql"
import _ "github.com/go-sql-driver/mysql" //注意前面有_

func open(){
    const DRIVER = "mysql"
    var DSN = "root:123456@tcp(localhost:3306)/test?charset=utf8&parseTime=True&loc=Local"
    var err error
    db, err = sql.Open(DRIVER, DSN)
    if err != nil {
        panic(err)
    }
    if err = db.Ping();err != nil{
        panic(err)
    }
}

OpenDB()sql/database/driverConnectormysqldriver.Connector*sql.DB

import "database/sql"
import "github.com/go-sql-driver/mysql"//注意前面没有_

func openConnector() {
    Connector, err := mysql.NewConnector(&mysql.Config{
        User:   "root",
        Passwd: "123456",
        Net:    "tcp",
        Addr:   "localhost:3306",
        DBName: "test",
        AllowNativePasswords:true,
        Collation:"utf8_general_ci",
        ParseTime:true,
        Loc:time.Local,
    })
    if err != nil {
        panic(err)
    }
    db = sql.OpenDB(Connector)
    if err = db.Ping();err != nil{
        panic(err)
    }
}

*sql.DB

var db *sql.DB

//在init方法初始化`*sql.DB`
func init(){
    open()
    //或者
    openConnector()
}

sql.DBsql/databasesql.DB

func (db *DB) SetMaxIdleConns(n int)//设置连接池中最大空闲数据库连接数，<=0表示不保留空闲连接，默认值2
func (db *DB) SetMaxOpenConns(n int)//设置连接池最大打开数据库连接数，<=表示不限制打开连接数，默认为0
func (db *DB) SetConnMaxLifetime(d time.Duration)//设置连接超时时间

代码演示

db.SetMaxOpenConns(100)//设置最多打开100个数据连连接
db.SetMaxIdleConns(0)//设置为0表示
db.SetConnMaxLifetime(time.Second * 5)//5秒超时

数据库基本操作

usersSQL

CREATE TABLE users(
    id       INT         NOT NULL AUTO_INCREMENT COMMENT 'ID',
    username VARCHAR(32) NOT NULL                COMMENT '用户名',
    moeny    INT         DEFAULT 0               COMMENT '账户余额',
    PRIMARY KEY(id)
);
INSERT INTO users VALUES(1,'小明',1000);
INSERT INTO users VALUES(2,'小红',2000);
INSERT INTO users VALUES(3,'小刚',1400);

查询

sql.DBQuery()QueryContext()

func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
func (db *DB) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error)

Query()QueryContext()sql.Rowssql.Rows

type Rows struct {
    //contains filtered or unexported fields
}
func (rs *Rows) Close() error //关闭结果集
func (rs *Rows) ColumnTypes() ([]*ColumnType, error)//返回数据表的列类型
func (rs *Rows) Columns() ([]string, error)//返回数据表列的名称
func (rs *Rows) Err() error//错误集
func (rs *Rows) Next() bool//游标，下一行
func (rs *Rows) NextResultSet() bool
func (rs *Rows) Scan(dest ...interface{}) error //扫描结构体

sql.RowsNext()Scan

func query() {
    selectText := "SELECT * FROM users WHERE id = ?"
    rows, _ := db.Query(selectText, 2)
    defer rows.Close()
    for rows.Next() {
        var (
            id       int
            username string
            money    int
        )
        _ = rows.Scan(&id, &username,&money)
        fmt.Println(id, username,money)
    }
}

sql.DBQueryRow()QueryRowContext()

func (db *DB) QueryRow(query string, args ...interface{}) *Row
func (db *DB) QueryRowContext(ctx context.Context, query string, args ...interface{}) *Row

QueryRowQueryRowContextsql.Rowsql.Rowsql.RowScan()sql.Row

type Row struct{
}
func (r *Row) Scan(dest ...interface{}) error

代码演示

func queryRow(){
    selectText := "SELECT * FROM users WHERE id = ?"
    row := db.QueryRow(selectText, 2)
    var (
        id       int
        username string
        money    int
    )
    _ = row.Scan(&id, &username,&money)
    fmt.Println(id, username,money)
}

sql.DBPrepare()PrepareContext()sql.Stmt

Prepare()PrepareContext()

func (db *DB) Prepare(query string) (*Stmt, error)
func (db *DB) PrepareContext(ctx context.Context, query string) (*Stmt, error)

sql.Stmtsql.DB

func queryStmt(){
    stmt,err := db.Prepare("SELECT * FROM users WHERE id = ?")
    if err != nil{
        return
    }
    defer stmt.Close()
    rows,err := stmt.Query(2)
    defer rows.Close()
    for rows.Next() {
        var (
            id       int
            username string
            money    int
        )
        _ = rows.Scan(&id, &username,&money)
        fmt.Println(id, username,money)
    }
}

添加

sql.DBExec()ExecContext()

func (db *DB) Exec(query string, args ...interface{}) (Result, error)
func (db *DB) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)

代码示例：

func insert(){
    insertText := "INSERT INTO users values(?,?,?)"
    rs,err := db.Exec(insertText,4,"juejin",1000)
    if err != nil{
        fmt.Println(err)
        return
    }
    if id,_ := rs.LastInsertId();id > 0 {
        fmt.Println("插入成功")
    }
    /*也可以这样判断是否插入成功
    if n,_ := rs.RowsAffected();n > 0 {
        fmt.Println("插入成功")
    }
    */
}

Exec()ExecContext()sql.Resultsql.Result

LastInsertId()

type Result interface {
    LastInsertId() (int64, error)//使用insert向数据插入记录，数据表有自增id时，该函数有返回值
    RowsAffected() (int64, error)//表示影响的数据表行数
}

sql.ResultLastInsertId()RowsAffected()SQL

sql.DBExec()ExecContext()Prepare()PrepareContext()sql.Stmtsql.StmtExec()

sql.Stmt

func insertStmt(){
    stmt,err := db.Prepare("INSERT INTO users VALUES(?,?,?)")
    defer stmt.Close()
    if err != nil{
        return
    }
    rs,err := stmt.Exec(5,"juejin",1000)
    if id,_ := rs.LastInsertId(); id > 0 {
        fmt.Println("插入成功")
    }
}

sql.StmtExec()ExecContext()SQL

更新

sql.DBExec()ExecContext()UPDATEsql.ResultRowsAffected()

func update()  {
    updateText := "UPDATE users SET username = ? WHERE id = ?"
    rs,err := db.Exec(updateText,"database",2)
    if err != nil{
        fmt.Println(err)
        return
    }
    if n,_ := rs.RowsAffected();n > 0 {
        fmt.Println("更新成功")
    }
}

删除

DELETE

func del()  {
    delText := "DELETE FROM users WHERE id = ?"
    rs,err := db.Exec(delText,1)
    if err != nil{
        fmt.Println(err)
        return
    }
    fmt.Println(rs.RowsAffected())
}

事务

SQLSQL

SQL

开启事务

sql.DBBegin()BeginTx()

func (db *DB) Begin() (*Tx, error)
func (db *DB) BeginTx(ctx context.Context, opts *TxOptions) (*Tx, error)

BeginTx()TxOptions

type TxOptions struct {
    // Isolation is the transaction isolation level.
    // If zero, the driver or database's default level is used.
    Isolation IsolationLevel
    ReadOnly  bool
}

TxOptionsIsolationIsolationLevelIoslation

const (
    LevelDefault IsolationLevel = iota
    LevelReadUncommitted
    LevelReadCommitted
    LevelWriteCommitted
    LevelRepeatableRead
    LevelSnapshot
    LevelSerializable
    LevelLinearizable
)

Begin()BeginTxt()sql.Txsql.Tx

tx, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: sql.LevelSerializable})

//Begin方法实际上是调用BeginTx()方法,db.BeginTx(context.Background(), nil)
tx, err := db.Begin()

sql.Tx支持的基本操作

sql.Tx

func (tx *Tx) Exec(query string, args ...interface{}) (Result, error)
func (tx *Tx) ExecContext(ctx context.Context, query string, args ...interface{}) (Result, error)
func (tx *Tx) Query(query string, args ...interface{}) (*Rows, error)
func (tx *Tx) QueryContext(ctx context.Context, query string, args ...interface{}) (*Rows, error)
func (tx *Tx) QueryRow(query string, args ...interface{}) *Row
func (tx *Tx) QueryRowContext(ctx context.Context, query string, args ...interface{}) *Row

事务提交

sql.Txsql.TxCommit()sql.TxRollback()

func (tx *Tx) Commit() error
func (tx *Tx) Rollback() error

预编译

sql.TxStmt()StmtContext()sql.Stmtsql.Stmt

func (tx *Tx) Stmt(stmt *Stmt) *Stmt
func (tx *Tx) StmtContext(ctx context.Context, stmt *Stmt) *Stmt

sql.TxPrepare()PrepareContext()sql.Stmt

func (tx *Tx) Prepare(query string) (*Stmt, error)
func (tx *Tx) PrepareContext(ctx context.Context, query string) (*Stmt, error)

示例

//修改
func txUpdate(){
    tx,_ := db.Begin()
    rs,err := tx.Exec("UPDATE users SET username = ? WHERE id = ?","sssss",2)
    if err != nil{
        panic(err)
    }
    err = tx.Commit()
    if err != nil{
        panic(err)
    }
    if n,_ := rs.RowsAffected();n > 0{
        fmt.Println("成功")
    }
}
//使用Stmt修改
func txStmt(){
    tx,err := db.Begin()
    if err != nil{
        panic(err)
    }
    stmt,err := db.Prepare("UPDATE users SET username = ? WHERE id = ?")
    stmtTx := tx.Stmt(stmt)
    defer stmtTx.Close()
    rs,_ := stmtTx.Exec("test",2)
    _ = tx.Commit()
    if n,_ := rs.RowsAffected();n > 0{
        fmt.Println("成功")
    }
}

相关ORM框架

ORMObject Relational MappingORMSQLStructMapSQL

下面几个比较常用的ORM框架：

GORM

GORM

MySQLSQLite3SQL ServerPostgres

Xorm

XormGORMGORMMySQLSQL ServerSQLite3PostgresMyMysqlTidbOracle

Beego ORM

Beego ORMBeegoBeegoBeego ORMMySQLSQLite3Postgres

除了上面我们介绍的三个ORM框架，其实还很多很好的ORM的框架，大家有空可以看看。

小结

sql/databasesql/databasesql.DBsql.Rowssql.Stmtsql.Tx