database/sql

• http://golang.org/pkg/database/sql/

Golang提供了标准库database/sql用于和数据库交互，database/sql只是一套统一地抽象接口，真正与数据库打交道的是各个数据库对应的驱动实现，因此使用前需要先注册对应数据库的驱动，然后就可以使用SQL中定义的接口来统一地操作数据库了。

sql.DB

Golang中访问数据库时需要使用sql.DB类型的数据库句柄

• sql.DB不是一个数据库连接，而是数据库的接口和抽象。
• sql.DB只是一个抽象的接口，不同的驱动有着不同的实现。
• sql.DB可以创建语句和事务、执行查询、获取结果。

sql.DB是一个数据库句柄，代表着一个具有0到多个底层连接的连接池，可安全的被多个goroutine同时使用。由于sql.DB并非一个数据库连接，而且可以被多个goroutine并发使用。因此，程序中只需要拥有一个全局的实例即可。

database/sql包会自动创建和释放连接，也会维护一个闲置的连接池。如果数据库具有单连接状态的概念，该状态只有再事务中被观察时才可信。一旦调用BD.Begin返回的Tx会绑定到单个连接。当调用事务Tx的Commit或Rollback后，该事务使用的连接会归还到DB的限制连接池中。连接池的大小可以使用SetMaxIdleConns方法控制。

sql.DB句柄的作用

• 通过驱动程序打开和关闭实际底层数据库的连接
• 根据需要管理一个连接池

sql.DB让用户不必考虑如何管理并发访问底层数据库的问题。当一个连接在执行任务时会被标记为正在使用。使用完毕后会放回连接池。如果用户使用完毕后忘记释放则会产生大量连接，导致资源耗尽。

go-sqlite3

• 使用数据库时，除了导出database/sql包本身，还需引入所使用的特定数据库的驱动。

安装

• go-sqlite3库是Golang实现SQLite数据库的驱动
• go-sqlite3依赖于golang.org/x/net/context

$ go get github.com/mattn/go-sqlite3
exec: "gcc": executable file not found in %PATH%

由于SQLite3使用C语言开发，因此go-sqlite3需要GCC工具来编译C代码。

导入

• 导入驱动时可使用_别名来匿名导入，驱动的导出名字不会出现在当前作用域中。
• 导入时驱动初始化函数会调用sql.Register函数将自身注册到database/sql包的全局变量sql.drivers中，以便后续通过sql.Open访问。

import _ "github.com/mattn/go-sqlite3"

GCC

MinGW

MinGW全称Minimalist GNU on Windows，它实际上是将经典的开源C语言编译器GCC移植到Windows平台，包含Win32API，因此可将源代码编译为可在Windows中运行的可执行程序。简单来说，MinGW就是GCC的Windows版本。

进入 https://sourceforge.net/projects/mingw-w64/files/mingw-w64/mingw-w64-release/，下载 mingw-w64-install.exe 安装。

选择版本

将下载的mingw64文件夹下的bin目录添加到系统环境变量Path中。

$ gcc -v
gcc version 9.2.0 (tdm64-1)

TDM-GCC

TDM-GCC 衍生自 MinGW 和 MinGW-w64 的项目，TDM-GCC是 http://tdragon.net 搞的用于MinGW和mingw-w64的gcc分支，使用广泛。

• https://jmeubank.github.io/tdm-gcc/

下载安装后，将bin目录添加到系统环境变量Path中。

$ gcc -v
gcc version 9.2.0 (tdm64-1)

MySQL

MySQL驱动

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

MySQL数据源URL格式

"root:123456@tcp(127.0.0.1:3306)/test?charset=utf8"

sql.Open

• 加载驱动包后需使用sql.Open()函数来创建sql.DB数据库句柄
• sql.Open()只会验证参数而不会为数据库创建连接
• 若需验证数据源指定的连接是否有效需调用db.Ping()进行测试

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

绝大多数情况下都应当检查database/sql操作返回的错误，

//创建数据对象 当前并未创建实际连接
db, err := sql.Open("sqlite3", "test.db")
if err != nil {
    panic(err)
}

实际应该在Go文件的init函数中调用sql.Open来初始化全局的sql.DB对象，供程序中所有需要进行数据库操作的地方使用。

func init() {
    //创建数据对象 当前并未创建实际连接
    db, err := sql.Open("sqlite3", "test.db")
    if err != nil {
        panic(err)
    }
    //检查数据库是否可用
    err = db.Ping()
    if err != nil {
        panic(err)
    }
}

sql.Open发生错误时err只会在实际操作数据库或调用db.Ping()时才会报错

dsn := "root:@tcp23(localhost233:3306)/test?charset=utf8"
db, err := sql.Open("mysql", dsn)

sql.Register

• sql.Register接口用于注册数据库驱动
• 第三方开发的数据库驱动需在init初始化方法中调用sql.Register完成驱动的注册

func Register(name string, driver driver.Driver)

• 若Register被调用了两次注册相同的name的driver或driver为nil则会panic

db.Close

若确定sql.DB只会使用一次之后不再使用，可执行sql.DB的Close()方法在程序退出时释放掉数据库连接资源。如果其生命周期不超过函数的范围，则应当使用defer db.Close()。

defer func() {
    //延迟释放连接资源
    db.Close()
}()

db.Close()用于关闭数据库并释放任何打开的资源，由于sql.DB句柄会被多个goroutine共享并长期活跃，一般是不需要关闭的。如果确认sql.DB只会被使用一次，之后不会再使用，则应该调用db.Close()。

sql.DB句柄是为长连接而设计，因此不要频繁地Open()和Close()数据库。而应该为每个特定访问的数据库创建一个sql.DB句柄，并在用完前一直保留，需要时可将其作为参数传递或注册为全局对象。

若将sql.DB句柄当成长期对象而频繁开关启停，可能会遭遇到各种错误，比如无法复用和共享连接，耗尽网络资源，TCP连接保持在TIME_WAIT状态而间接性失败等等...

db.Ping

• 执行sql.Open()并未实际建立起数据库连接，也不会验证驱动参数。
• 第一个实际的连接会惰性求值，延迟到第一次需要时建立。
• 用户可通过db.Ping()来检查数据库是否实际可用。
• db.Ping()函数用于验证到数据库的连接是否还处于active活跃状态，若连接非active则建立连接。

//检查数据库是否可用
err = db.Ping()
if err != nil {
    panic(err)
}

db.SetMaxIdleConns

默认情况下数据库连接池没有数量限制，但机器一般有TCP数量限制，为了不拖死机器不推荐无限量去使用。

database/sql包提供的连接池配置参数

示例：根据配置文件读取配置初始化数据库句柄

配置文件采用JSON格式

$ vim ./config/database.json

{
    "default":{
        "alias":"default",
        "driver":"mysql",
        "database":"test",
        "user":"root",
        "password":"root",
        "charset":"utf8",
        "host":"127.0.0.1",
        "port":"3306",
        "max_idle_conns":5,
        "max_open_conns":10
    },
    "local":{
        "alias":"local",
        "driver":"sqlite3",
        "database":"test.db"
    }  
}

读取配置

读取配置需要首先获取配置文件地址，配置文件地址可根据当前文件所在地址重组拼接后生成，因此需要获取当前文件地址与获取上级文件地址。

//pwd 获取当前文件所在路径
func pwd() string {
    str, err := os.Getwd() //当前的路径
    if err != nil {
        panic(err)
    }
    fmt.Printf("%v\n", str)
    str, err = filepath.Abs(str)
    if err != nil {
        panic(err)
    }
    fmt.Printf("%v\n", str)
    str = strings.Replace(str, "\\", "/", -1)
    return str
}

//获取上级路径
func dir(path string) string {
    pos := 0
    last := strings.LastIndex(path, "/")
    l := pos + last

    runes := []rune(path)
    if l > len(runes) {
        l = len(runes)
    }

    return string(runes[pos:l])
}

在main.go文件测试

str := pwd()
fmt.Printf("%v\n", str)

str = path.Join(str, "./config/database.json")
fmt.Printf("%v\n", str)

加载配置文件，配置文件为JSON，可能存在注释，因此获取的配置文件必须过滤掉提示。

//load 加载JSON文件解析为字典映射
func load(filepath string) map[string]interface{} {
    //读取文件
    buf, err := ioutil.ReadFile(filepath)
    if err != nil {
        panic(err)
    }
    //转化为字符串
    str := string(buf[:])
    //去除注释
    reg := regexp.MustCompile(`/\*.*\*/`)
    str = reg.ReplaceAllString(str, "")
    //反序列化
    obj := make(map[string]interface{})
    bs := []byte(str)
    err = json.Unmarshal(bs, &obj)
    if err != nil {
        panic(err)
    }
    return obj
}

根据配置文件获取数据源地址

// dsn 获取数据源URL
func dsn(config map[string]interface{}) (string, string) {
    var url string
    driver := config["driver"].(string)
    if driver == "postgres" {
        host := config["host"]
        port := config["port"]
        user := config["user"]
        password := config["password"]
        database := config["database"]
        url = fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, database)
    } else if driver == "mysql" {
        host := config["host"]
        port := config["port"]
        user := config["user"]
        password := config["password"]
        database := config["database"]
        url = fmt.Sprintf("%s:%s@tcp(%s:%s)/%s", user, password, host, port, database)
    } else if driver == "sqlite3" {
        database := config["database"].(string)
        url = database
    }
    return driver, url
}

通过配置获取数据库句柄

//全局变量
var (
    config map[string]interface{}
    dbs    map[string]*sql.DB
    db     *sql.DB
    query  string
)
// DB 根据配置获取不同的数据库句柄
func DB(key, dialect, dsn string) *sql.DB {
    if dbs == nil {
        dbs = make(map[string]*sql.DB)
    }

    db, ok := dbs[key]
    if ok {
        return db
    }
    fmt.Printf("%v\n", key)

    db, err := sql.Open(dialect, dsn)
    if err != nil {
        panic(err)
    }

    if err = db.Ping(); err != nil {
        panic(err)
    }

    dbs[key] = db
    return db
}

对外提供快捷接口

//GetDB 根据指定标识获取数据库句柄
func GetDB(key string) *sql.DB {
    //获取当前文件所在路径
    str := pwd()
    //拼接配置文件路径
    str = path.Join(str, "./config/database.json")
    //加载配置文件
    data := load(str)
    //获取指定键的配置
    config = data[key].(map[string]interface{})
    //生成数据源URL
    dialect, url := dsn(config)
    //获取数据库句柄
    db := DB(key, dialect, url)
    //返回句柄
    return db
}
//init 初始化
func init() {
    db = GetDB("local")
}

查询操作

db.Query

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

db.Query方法本身支持变参args ...interface{}，因此可传入0或多个参数。

db.Query查询操作会返回sql.Rows结果集，但不会主动释放连接，调用完或仍然会占有连接，它会将连接的所属权转移给sql.Rows，因此需手动调用rows.Close()归还连接，即使不用sql.Rows也需要关闭连接，否则会导致后续使用出错。

field := "name"
table := "users"
id := 1
query = fmt.Sprintf("SELECT %s FROM `%s` WHERE 1=1 AND `id`=?", field, table)
rows, err := db.Query(query, id)
if err != nil {
    panic(err)
}
log.Println(rows)
defer rows.Close()

• 在MySQL中参数占位符为?问号，在PostgreSQL中为$N，SQLite两则都适用。

sql.Rows

var name string
for rows.Next() {
    err := rows.Scan(&name)
    if err != nil {
        log.Fatal(err)
    }
    log.Println(name)
}

err = rows.Err()
if err != nil {
    log.Fatal(err)
}

defer rows.Close()

db.QueryRow

• db.QueryRow用于最多只有一行返回的数据库查询操作

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

field := "name"
table := "users"
query = fmt.Sprintf("SELECT %s FROM `%s` WHERE 1=1 AND `id`=?", field, table)

id := 1
row := db.QueryRow(query, id)

• db.QueryRow只会返回一条非nil单行操作结果sql.Row
• 当查询错误或结果为空时，只有sql.Row执行db.Scan方法时才会体现。

var name string
err := row.Scan(&name)
if err != nil {
    log.Fatal(err)
}

log.Println(name)

支持连缀方式

err := db.QueryRow(query, id).Scan(&name)

db.Exec

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

• db.Exec返回结果集sql.Result可获得查询影响行数
• db.Exec适用于创建、插入、更新、删除操作

例如：删除数据表

//drop 删除数据表
func drop(query string) int64 {
    //执行语句获取结果集
    result, err := db.Exec(query)
    if err != nil {
        log.Fatal(err)
    }
    //从结果集中获取受影响行数
    affected_rows, err := result.RowsAffected()
    if err != nil {
        log.Fatal(err)
    }
    return affected_rows
}
func main() {
    query = "DROP TABLE IF EXISTS users"
    n := drop(query)
    log.Println(n)
}

例如：插入数据库

//insert 插入数据表
func insert(query string) int64 {
    //执行语句获取结果集
    result, err := db.Exec(query)
    if err != nil {
        log.Fatal(err)
    }
    //获取插入的主键
    last_insert_id, err := result.LastInsertId()
    if err != nil {
        log.Fatal(err)
    }
    log.Println(last_insert_id)
    return last_insert_id
}

sql.Result

type Result interface {
    LastInsertId() (int64, error)
    RowsAffected() (int64, error)
}

db.Prepare

每个代码段的执行都会经历“词法分析->语法分析->编译->执行”，采用预编译可实现一次编译多次执行，可解决一条SQL语句频繁执行以提高执行效率。

预处理

当提交一条SQL语句时，SQL到达数据库服务，首先会解析SQL语句，比如语法检查、查询条件先后优化等，然后才会执行。对于预处理，就是将客户端与数据库服务一次交互分为两个步骤。

1. 提交语句，数据库服务解析语句。
2. 提交参数，调用语句并执行。

对于多次重复执行的语句，提交并解析一次SQL即可，然后不断地调用刚刚解析过的SQL并执行。这样就会省去多次解析同一条SQL的时间，从而达到提高效率的目的。

占位符

• 预处理语句支持占位符（place holder），通过绑定占位符的方式提交参数。
• 能与占位符绑定的只能是值，不能是SQL语句的关键词。

预处理机制三步骤

1. 将语句预处理
2. 执行语句
3. 析构预处理语句

func (db *DB) Prepare(query string) (*Stmt, error)

例如：

type User struct {
    id   int
    name string
}

func main() {
    query = "SELECT id,name FROM users WHERE 1=1 AND id > ?"
    stmt, err := db.Prepare(query)
    if err != nil {
        log.Fatal(err)
    }

    id := 0
    rows, err := stmt.Query(id)
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    users := []*User{}
    user := &User{}
    for rows.Next() {
        err := rows.Scan(&user.id, &user.name)
        if err != nil {
            log.Fatal(err)
        }
        users = append(users, user)
    }

    if err := rows.Err(); err != nil {
        log.Fatal(err)
    }
    log.Printf("%v", users)

    defer rows.Close()
}

sql.Stmt

type Stmt struct {
    db        *DB    
    query     string 
    stickyErr error  

    closemu sync.RWMutex 
    cg   stmtConnGrabber
    cgds *driverStmt
    parentStmt *Stmt

    mu     sync.Mutex 
    closed bool
    css []connStmt
    lastNumClosed uint64
}