【Golang】数据库操作

龚学鹏
2019-07-29 14:37:57
Golang
golang

package sql

sql包提供了保证SQL或类SQL数据库的泛用接口。

使用sql包时必须注入（至少）一个数据库驱动。参见http://golang.org/s/sqldrivers 获取驱动列表。

驱动列表

Apache Ignite/GridGain: https://github.com/amsokol/ignite-go-client Apache Impala: https://github.com/bippio/go-impala Apache Avatica/Phoenix: https://github.com/apache/calcite-avatica-go Amazon AWS Athena: https://github.com/uber/athenadriver AWS Athena: https://github.com/segmentio/go-athena ClickHouse (uses native TCP interface): https://github.com/kshvakov/clickhouse ClickHouse (uses HTTP API): https://github.com/mailru/go-clickhouse CockroachDB: Use any PostgreSQL driver Couchbase N1QL: https://github.com/couchbase/go_n1ql DB2 LUW and DB2/Z with DB2-Connect: https://bitbucket.org/phiggins/db2cli (Last updated 2015-08) DB2 LUW (uses cgo): https://github.com/asifjalil/cli DB2 LUW, z/OS, iSeries and Informix: https://github.com/ibmdb/go_ibm_db Firebird SQL: https://github.com/nakagami/firebirdsql Google Cloud BigQuery: https://github.com/solcates/go-sql-bigquery MS ADODB: https://github.com/mattn/go-adodb MS SQL Server (pure go): https://github.com/denisenkom/go-mssqldb MS SQL Server (uses cgo): https://github.com/minus5/gofreetds MySQL: https://github.com/go-sql-driver/mysql/ [] MySQL: https://github.com/siddontang/go-mysql/ [**] (also handles replication) MySQL: https://github.com/ziutek/mymysql [] ODBC: https://bitbucket.org/miquella/mgodbc (Last updated 2016-02) ODBC: https://github.com/alexbrainman/odbc Oracle: https://github.com/mattn/go-oci8 Oracle: https://gopkg.in/rana/ora.v4 Oracle (uses cgo): https://github.com/godror/godror QL: http://godoc.org/github.com/cznic/ql/driver Postgres (pure Go): https://github.com/lib/pq [] Postgres (uses cgo): https://github.com/jbarham/gopgsqldriver Postgres (pure Go): https://github.com/jackc/pgx [**] Presto: https://github.com/prestodb/presto-go-client SAP HANA (uses cgo): https://help.sap.com/viewer/0eec0d68141541d1b07893a39944924e/2.0.03/en-US/0ffbe86c9d9f44338441829c6bee15e6.html SAP HANA (pure go): https://github.com/SAP/go-hdb SAP ASE (uses cgo): https://github.com/SAP/go-ase - package cgo (pure go package planned) Snowflake (pure Go): https://github.com/snowflakedb/gosnowflake SQLite (uses cgo): https://github.com/mattn/go-sqlite3 [] SQLite (uses cgo): https://github.com/gwenn/gosqlite - Supports SQLite dynamic data typing SQLite (uses cgo): https://github.com/mxk/go-sqlite SQLite: (uses cgo): https://github.com/rsc/sqlite SQL over REST: https://github.com/adaptant-labs/go-sql-rest-driver Sybase SQL Anywhere: https://github.com/a-palchikov/sqlago Sybase ASE (pure go): https://github.com/thda/tds TiDB: Use any MySQL driver Vertica: https://github.com/vertica/vertica-sql-go Vitess: https://godoc.org/vitess.io/vitess/go/vt/vitessdriver YQL (Yahoo! Query Language): https://github.com/mattn/go-yql Apache Hive: https://github.com/sql-machine-learning/gohive MaxCompute: https://github.com/sql-machine-learning/gomaxcompute

下载依赖

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

type DB

type DB struct {
    // 内含隐藏或非导出字段
}

DB是一个数据库（操作）句柄，代表一个具有零到多个底层连接的连接池。它可以安全的被多个go程同时使用。

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

go底层自动维护了一个连接池，等下可以测试一下。
按照文档上来说，每次curd之后都会将连接放回连接池。
想要在一段时间内持有同一接连可以使用事务。

func Open(driverName, dataSourceName string) (*DB, error) Open打开一个dirverName指定的数据库，dataSourceName指定数据源，一般包至少括数据库文件名和（可能的）连接信息。

大多数用户会通过数据库特定的连接帮助函数打开数据库，返回一个*DB。Go标准库中没有数据库驱动。参见http://golang.org/s/sqldrivers获取第三方驱动。

Open函数可能只是验证其参数，而不创建与数据库的连接。如果要检查数据源的名称是否合法，应调用返回值的Ping方法。

返回的DB可以安全的被多个go程同时使用，并会维护自身的闲置连接池。这样一来，Open函数只需调用一次。很少需要关闭DB。

open需要依赖第三方驱动
go的标准库中是没有提供驱动的
Open函数可能只是验证其参数，而不创建与数据库的连接。
如果要检查数据源的名称是否合法，应调用返回值的Ping方法。

func (db *DB) Driver() driver.Driver Driver方法返回数据库下层驱动。

func (db *DB) Ping() error Ping检查与数据库的连接是否仍有效，如果需要会创建连接。

创建与数据库的连接

func (db *DB) Close() error Close关闭数据库，释放任何打开的资源。一般不会关闭DB，因为DB句柄通常被多个go程共享，并长期活跃。

func (db *DB) SetMaxOpenConns(n int) SetMaxOpenConns设置与数据库建立连接的最大数目。

如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到匹配最大开启连接数的限制。

如果n <= 0，不会限制最大开启连接数，默认为0（无限制）。

func (db *DB) SetMaxIdleConns(n int) SetMaxIdleConns设置连接池中的最大闲置连接数。

如果n大于最大开启连接数，则新的最大闲置连接数会减小到匹配最大开启连接数的限制。

如果n <= 0，不会保留闲置连接。

func (db *DB) Exec(query string, args ...interface{}) (Result, error) Exec执行一次命令（包括查询、删除、更新、插入等），不返回任何执行结果。参数args表示query中的占位参数。

Result 结构体内有两个方法：
LastInsertId() (int64, error) 当插入新行时，一般来自一个"自增"列。
RowsAffected() (int64, error) RowsAffected返回被update、insert或delete命令影响的行数。
不是所有数据库都支持上面的两个功能

func (db *DB) Query(query string, args ...interface{}) (*Rows, error) Query执行一次查询，返回多行结果（即Rows），一般用于执行select命令。参数args表示query中的占位参数。

返回的 Rows 稍后再说

func (db *DB) QueryRow(query string, args ...interface{}) *Row QueryRow执行一次查询，并期望返回最多一行结果（即Row）。QueryRow总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回被延迟的错误。（如：未找到结果）

返回的 Row 稍后再说

func (db *DB) Prepare(query string) (*Stmt, error) Prepare创建一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。

预处理，多用于防止sql注入

func (db *DB) Begin() (*Tx, error) Begin开始一个事务。隔离水平由数据库驱动决定。

创建数据库连接，验证连接池

package main

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

const (
	dbDriverName      = "mysql"
	dbDsn             = "root:root@tcp(127.0.0.1:3306)/go"
	dbMaxIdleConns    = 2
	dbMaxOpenConns    = 4
	dbConnMaxLifetime = time.Second * 60 * 10
)

var (
	db *sql.DB
	wg sync.WaitGroup
)

func main() {
	err := dbInit()
	if err != nil {
		fmt.Println(err)
		return
	}
	defer closeDb()
	// 开启10个协程访问
	for i := 0; i < 10; i++ {
		wg.Add(1)
		go poolTest(i)
	}
	for  {
		time.Sleep(time.Second)
	}
}

func dbInit() (err error) {
	// 使用open验证连接
	db, err = sql.Open(dbDriverName, dbDsn)
	if err != nil {
		return err
	}

	// 使用ping创建连接
	err = db.Ping()
	if err != nil {
		return err
	}

	// 配置数据库连接池
	db.SetMaxIdleConns(dbMaxIdleConns)       // 最大空闲连接数
	db.SetMaxOpenConns(dbMaxOpenConns)       // 自大连接数
	db.SetConnMaxLifetime(dbConnMaxLifetime) // 一个连接最多存活600秒，有的时候数据库会清除长时间的连接
	return nil
}

func closeDb() {
	err := db.Close()
	if err != nil {
		fmt.Println(err)
	}
}

func poolTest(n int) {
	defer wg.Done()
	r, _ := db.Query("select sleep(1)")
	for r.Next() {
		var i interface{}
		err := r.Scan(&i)
		if err != nil {
			fmt.Println(err)
		} else {
			log.Println(n)
		}
	}
}

这里开了10个协程访问一个阻塞1秒的sql，mysql 内连接如下：

+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
| 22 | root | localhost:13249 | NULL | Query   |    0 | starting   | show processlist |
| 27 | root | localhost:13282 | go   | Query   |    1 | User sleep | select sleep(1)  |
| 28 | root | localhost:13284 | go   | Query   |    1 | User sleep | select sleep(1)  |
| 29 | root | localhost:13283 | go   | Query   |    1 | User sleep | select sleep(1)  |
| 30 | root | localhost:13285 | go   | Query   |    1 | User sleep | select sleep(1)  |
+----+------+-----------------+------+---------+------+------------+------------------+

+----+------+-----------------+------+---------+------+------------+------------------+
| Id | User | Host            | db   | Command | Time | State      | Info             |
+----+------+-----------------+------+---------+------+------------+------------------+
| 22 | root | localhost:13249 | NULL | Query   |    0 | starting   | show processlist |
| 27 | root | localhost:13282 | go   | Query   |    1 | User sleep | select sleep(1)  |
| 28 | root | localhost:13284 | go   | Sleep   |    2 |            | NULL             |
| 29 | root | localhost:13283 | go   | Sleep   |    2 |            | NULL             |
| 30 | root | localhost:13285 | go   | Query   |    1 | User sleep | select sleep(1)  |
+----+------+-----------------+------+---------+------+------------+------------------+

+----+------+-----------------+------+---------+------+----------+------------------+
| Id | User | Host            | db   | Command | Time | State    | Info             |
+----+------+-----------------+------+---------+------+----------+------------------+
| 22 | root | localhost:13249 | NULL | Query   |    0 | starting | show processlist |
| 28 | root | localhost:13284 | go   | Sleep   |    4 |          | NULL             |
| 29 | root | localhost:13283 | go   | Sleep   |    4 |          | NULL             |
+----+------+-----------------+------+---------+------+----------+------------------+

从mysql连接的层面来看，连接池确实有效，同一时间内最多4个活跃的连接，最多空闲连接为2个

命令行输出如下

2020/03/20 13:18:02 6
2020/03/20 13:18:02 4
2020/03/20 13:18:02 9
2020/03/20 13:18:02 0
2020/03/20 13:18:03 2
2020/03/20 13:18:03 8
2020/03/20 13:18:03 7
2020/03/20 13:18:03 5
2020/03/20 13:18:04 3
2020/03/20 13:18:04 1

从命令行输出也可以看出来，同一时间确实是有4个查询返回的。

Row和Rows

type Row struct {
    // 内含隐藏或非导出字段
}

QueryRow方法返回Row，代表单行查询结果。

func (r *Row) Scan(dest ...interface{}) error Scan将该行查询结果各列分别保存进dest参数指定的值中。如果该查询匹配多行，Scan会使用第一行结果并丢弃其余各行。如果没有匹配查询的行，Scan会返回ErrNoRows。

使用row.scan来获取一条记录
记录中的每一行会逐个匹配到dest参数指定的值中（传指针）

type Rows struct { // 内含隐藏或非导出字段 }

> Rows是查询的结果。它的游标指向结果集的第零行，使用Next方法来遍历各行结果：
```shell
rows, err := db.Query("SELECT ...")
...
defer rows.Close()
for rows.Next() {
    var id int
    var name string
    err = rows.Scan(&id, &name)
    ...
}
err = rows.Err() // get any error encountered during iteration
...

可以理解为Rows本身是个迭代器，使用Row.next相当于对迭代器使用了validator.next()+validator.void()

func (rs *Rows) Columns() ([]string, error) Columns返回列名。如果Rows已经关闭会返回错误。

func (rs *Rows) Scan(dest ...interface{}) error Scan将当前行各列结果填充进dest指定的各个值中。

如果某个参数的类型为*[]byte，Scan会保存对应数据的拷贝，该拷贝为调用者所有，可以安全的,修改或无限期的保存。如果参数类型为*RawBytes可以避免拷贝；参见RawBytes的文档获取其使用的约束。

如果某个参数的类型为*interface{}，Scan会不做转换的拷贝底层驱动提供的值。如果值的类型为[]byte，会进行数据的拷贝，调用者可以安全使用该值。

用来获取一条数据

func (rs *Rows) Next() bool Next准备用于Scan方法的下一行结果。如果成功会返回真，如果没有下一行或者出现错误会返回假。Err应该被调用以区分这两种情况。

每一次调用Scan方法，甚至包括第一次调用该方法，都必须在前面先调用Next方法。

func (rs *Rows) Close() error Close关闭Rows，阻止对其更多的列举。如果Next方法返回假，Rows会自动关闭，满足。检查Err方法结果的条件。Close方法时幂等的（多次调用无效的成功），不影响Err方法的结果。

func (rs *Rows) Err() error Err返回可能的、在迭代时出现的错误。Err需在显式或隐式调用Close方法后调用。

准备数据

这里准备了一张数据表和一组数据

mysql> desc test;
+-----------+--------------+------+-----+-------------------+-----------------------------+
| Field     | Type         | Null | Key | Default           | Extra                       |
+-----------+--------------+------+-----+-------------------+-----------------------------+
| id        | int(11)      | NO   | PRI | NULL              | auto_increment              |
| name      | varchar(255) | NO   |     |                   |                             |
| datetime1 | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| datetime2 | datetime     | NO   |     | NULL              |                             |
+-----------+--------------+------+-----+-------------------+-----------------------------+
4 rows in set (0.00 sec)

mysql> select * from test;
+----+------+---------------------+---------------------+
| id | name | datetime1           | datetime2           |
+----+------+---------------------+---------------------+
|  1 | a    | 2020-03-20 13:37:30 | 2020-03-20 13:37:30 |
|  2 | b    | 2020-03-20 13:37:51 | 2020-03-20 13:37:51 |
|  3 | c    | 2020-03-20 13:37:57 | 2020-03-20 13:37:57 |
|  4 | d    | 2020-03-20 13:38:02 | 2020-03-20 13:38:02 |
|  5 | e    | 2020-03-20 13:38:08 | 2020-03-20 13:38:08 |
+----+------+---------------------+---------------------+
5 rows in set (0.00 sec)

开始查询

func main() {
	err := dbInit()
	if err != nil {
		fmt.Println(err)
		return
	}
	defer closeDb()

	queryTest()
}

func queryTest() {
	type data struct {
		id        int
		name      string
		datetime1 string
		datetime2 string
	}

	r, err := db.Query("select * from test")
	if err != nil {
		log.Println(err)
	}
	for r.Next() {
		tmpData := &data{}
		err = r.Scan(&tmpData.id, &tmpData.name, &tmpData.datetime1, &tmpData.datetime2)
		if err != nil {
			log.Println(err)
		}
		fmt.Printf("%#v\r\n",tmpData)
	}
}

&main.data{id:1, name:"a", datetime1:"2020-03-20 13:37:30", datetime2:"2020-03-20 13:37:30"}
&main.data{id:2, name:"b", datetime1:"2020-03-20 13:37:51", datetime2:"2020-03-20 13:37:51"}
&main.data{id:3, name:"c", datetime1:"2020-03-20 13:37:57", datetime2:"2020-03-20 13:37:57"}
&main.data{id:4, name:"d", datetime1:"2020-03-20 13:38:02", datetime2:"2020-03-20 13:38:02"}
&main.data{id:5, name:"e", datetime1:"2020-03-20 13:38:08", datetime2:"2020-03-20 13:38:08"}

这里主要测试了下datetime和timestemp的数据应该用什么接，测试后发现go接受的数据和mysql的输出有关，这里不会关心mysql底层存储的数据类型

好吧其实是我想多了，毕竟交互靠的是和mysql的socket连接，收到的数据也是mysql返回的数据，类型应该也只有字符串，整型，布尔型和null这几种

增删改

上面有说到，增删改使用 func (db *DB) Exec(query string, args ...interface{}) (Result, error)

删除一个数据

func main() {
	err := dbInit()
	if err != nil {
		log.Println(err)
		return
	}
	defer closeDb()

	deleteTest()
}

func deleteTest() {
	r, err := db.Exec("delete from test where id = ?", 1)
	if err != nil {
		log.Println(err)
	}
	n, err := r.RowsAffected()
	if err != nil {
		log.Println(err)
	}
	log.Println("执行成功：", n)
}

2020/03/20 14:08:05 执行成功： 1

修改数据

func main() {
	err := dbInit()
	if err != nil {
		log.Println(err)
		return
	}
	defer closeDb()

	updateTest()
}

func updateTest()  {
	r,err := db.Exec("update test set name = CONCAT(name,\" add\") where id > ?",3)
	if err != nil {
		log.Println(err)
	}
	n, err := r.RowsAffected()
	if err != nil {
		log.Println(err)
	}
	log.Println("执行成功：", n)
}

2020/03/20 14:12:20 执行成功： 2

新增数据

func main() {
	err := dbInit()
	if err != nil {
		log.Println(err)
		return
	}
	defer closeDb()

	insertTest()
}

func insertTest() {
	r, err := db.Exec("insert into test (name,datetime2) values (?, ?)", "golang", time.Now().Format("2006-01-02 15:04:05"))
	if err != nil {
		log.Println(err)
	}
	n, err := r.RowsAffected()
	if err != nil {
		log.Println(err)
	}
	id, err := r.LastInsertId()
	if err != nil {
		log.Println(err)
	}
	log.Println("执行成功：", n)
	log.Println("返回id：", id)
}

2020/03/20 14:15:09 执行成功： 1
2020/03/20 14:15:09 返回id： 6

事务

使用 func (db *DB) Begin() (*Tx, error) 开启一个事务，返回一个当前事务对象 Tx

type Tx struct {
    // 内含隐藏或非导出字段
}

Tx代表一个进行中的数据库事务。

一次事务必须以对Commit或Rollback的调用结束。

调用Commit或Rollback后，所有对事务的操作都会失败并返回错误值ErrTxDone。

Tx 同样拥有 exec，query，queryRow方法，同时增加了提交事务和回滚事务的方法：

func (tx *Tx) Commit() error Commit递交事务。
func (tx *Tx) Rollback() error Rollback放弃并回滚事务。

这里就不演示了

小结

golang-database.xmind

本文为龚学鹏原创文章,转载无需和我联系,但请注明来自龚学鹏博客http://www.noobcoder.cn

上一篇：【Golang】测试包：testing
下一篇：【Gin】安装，初体验及计划

请先登录后发表评论

latest comments
总共0条评论

加入组织

1. 手Q扫左侧二维码

2. 搜Q群：599430960

3. 点击

docker学习[1]：什么是docker？

友情链接

怀老师的博客更多

【Golang】数据库操作

package sql

驱动列表

下载依赖

type DB

创建数据库连接，验证连接池

Row和Rows

准备数据

开始查询

增删改

删除一个数据

修改数据

新增数据

事务

小结

加入组织

热门标签

最新评论

友情链接

【Golang】数据库操作

package sql

驱动列表

下载依赖

type DB

创建数据库连接，验证连接池

Row和Rows

准备数据

开始查询

增删改

删除一个数据

修改数据

新增数据

事务

小结

加入组织

热门标签

置顶推荐

最新评论

友情链接