【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方法控制。

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

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

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

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

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

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

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

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

  1. 创建与数据库的连接

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中的占位参数。

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

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

  1. 返回的 Rows 稍后再说

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

  1. 返回的 Row 稍后再说

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

  1. 预处理,多用于防止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。

  1. 使用row.scan来获取一条记录
  2. 记录中的每一行会逐个匹配到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
...
  1. 可以理解为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,会进行数据的拷贝,调用者可以安全使用该值。

  1. 用来获取一条数据

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 同样拥有 execqueryqueryRow方法,同时增加了提交事务和回滚事务的方法:

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

这里就不演示了

小结

golang-database.xmind

程序幼儿员-龚学鹏
请先登录后发表评论
  • latest comments
  • 总共0条评论