GoLang 操作数据库 有更新!

2019-07-23
0 评论 447 浏览

GoLang 数据库

database/sql

database/sql是Golang的标准库之一,它提供了一系列接口方法,用于访问关系型数据库。它并不会提供数据库特有的方法,所有特有的方法交给数据库驱动去实现
database/sql库提供了一些type,这些类型对了解使用他的用法非常重要
type类型有:
* DB 数据库对象,sql.DB类型代表了数据库,和其他语言不一样,它并不是数据库的连接,golang中的连接来自内部实现的连接池,连接的建立是惰性的,并不是一直处于连接状态,当需要连接的时候,连接池会自动创建连接,一般来说不需要我们操作连接池,golang已经帮我们完成这一切了。
* Results 结果集。数据库查询的时候,都会有结果集,sql.Rows类型代表查询返回多行数据的结果集,sql.Row则表示单行查询结果的结果集,对于插入更新和删除,返回的结果集类型为sql.Result
* Statements语句,sql.stmt类型代表sql查询语句,例如DDL,DML等类似的sql语句,

使用go连接操作mysql数据库

我们使用go-sql-dricer/mysql驱动。
首先我们先安装驱动:go get -u github.com/go-sql-driver/mysql
在程序中引用这个包

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

_表示可以使用包中所有成员,而不用添加包名
对于其他语言,查询数据库的时候需要创建一个连接,对于Go而言则是需要创建一个数据库抽象对象。连接将会在查询需要的时候,由连接池创建并维护,使用sql.Open函数创建数据库对象,他的第一个参数是数据库驱动名,第二个参数是一个连接字符串,举例说明:

import (
    "database/sql"
    "log"
    _ "github.com/go-sql-driver/mysql"
)
func main() {
    db,err := sql.Open("mysql","user@unix(/path/to/socket)/dbname?charset=utf8")
    // unix系统的,我一般不用
    db,err := sql.Open("mysql","user:password@tcp(localhost:5555)/dbname?charset=utf8")
    // 涉及到远程链接调用的
    db,err := sql.Open("mysql","user:password@/dbname ")
    // 本地使用的
    db,err := sql.Open("mysql","user:password@tcp([de:ad:be:ef::ca:fe]:80)/dbname")
    // 涉及到IPV6的

    if err != nil {
        panic(err)
    }
    defer db.Close()
    //当函数结束前关闭连接池
}

简单操作数据库

创建一张表

package main

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

func CheckErr(err error) {
	if err != nil {
		fmt.Println(err)
	}
}
func Use_Sql() {
	//连接数据库
	db, err := sql.Open("mysql", "root:toolwiz.mysql.admin@tcp(192.168.1.254:3307)/test?charset=utf8")
	CheckErr(err)
	defer db.Close()

	_, err = db.Exec("CREATE TABLE `test`.`student`(`student_id` int(10) NOT NULL," +
		"`grade` int(10) NOT NULL," +
		"`name` varchar(20) NOT NULL," +
		"`gender` varchar(5) NOT NULL," +
		"`age` int(4) NOT NULL," +
		"PRIMARY KEY (`student_id`));")
	CheckErr(err)
}

func main() {
	Use_Sql()
}

插入数据

package main

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

func CheckErr(err error) {
	if err != nil {
		fmt.Println(err)
	}
}
func Use_Sql() {
	//连接数据库
	db, err := sql.Open("mysql", "root:toolwiz.mysql.admin@tcp(192.168.1.254:3307)/test?charset=utf8")
	CheckErr(err)
	defer db.Close()
   
   //向表中插入数据
	inset, err := db.Exec("insert into test.student(student_id,name,gender,age) values(2,'zhujun','nv',30),(3,'haha','nan',20)")
	CheckErr(err)
	//获取插入成功的条数
	rowCount, err := inset.RowsAffected()
	CheckErr(err)
	fmt.Println(rowCount)
}

func main() {
	Use_Sql()
}

查询

package main

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

func CheckErr(err error) {
	if err != nil {
		fmt.Println(err)
	}
}
func Use_Sql() {
	//连接数据库
	db, err := sql.Open("mysql", "root:toolwiz.mysql.admin@tcp(192.168.1.254:3307)/test?charset=utf8")
	CheckErr(err)
	defer db.Close()
	
	rows, err := db.Query("select * from student where name='zhangshoufu'")
	for rows.Next() {
		var student_id int
		var grade int
		var name string
		var gender string
		var age int
		err := rows.Scan(&student_id, &grade, &name, &gender, &age)
		CheckErr(err)
		fmt.Printf("student_id: %d\t grade: %d\tname: %s\tgender: %s\tage: %d\n", student_id, grade, name, gender, age)
	}
    rows.Close()
}

func main() {
	Use_Sql()
}

我们调用Query方法执行select语句,返回的是一个sql.Rows类型的结果集。迭代后者的Next方法,然后使用Scan方法给变量S赋值,以便取出来结果。最后再把结果集关闭

解释

Sql.DB

sql.DB是数据库的抽象,虽然通常它容易被误认为是数据库连接。它提供了一些跟数据库交互的函数,同时管理维护一个数据库连接池,帮我们处理了单调而重复的管理工作,并且在多个goroutines也十分安全。
sql.DB表示数据库抽象,因此你有几个数据库就需要为每一个数据库创建一个sql.DB对象,因为它维护了一个连接池,因此不需要频繁的创建和销毁。它需要长时间保持,因此最后是设置成一个全局变量以便其他代码可以访问。
创建数据库对象需要引入标准库database/sql同时还需要引入驱动go-sql-dirver/mysql。使用_表示引入驱动的变量,这样做的目的是为了在代码中不至于和标准库的函数变量namespace冲突

连接池

只用sql.Open函数创建连接池,可是此时只是初始化了连接池,并没有创建任何连接。连接创建都是惰性的,只有当你真正使用到连接的时候,连接池才会创建连接。连接池很重要,它直接影响着你的程序行为。
连接池的工作原来却相当简单。当你的函数(例如Exec,Query)调用需要访问底层数据库的时候,函数首先会向连接池请求一个连接。如果连接池有空闲的连接,则返回给函数。否则连接池将会创建一个新的连接给函数。一旦连接给了函数,连接则归属于函数。函数执行完毕后,要不把连接所属权归还给连接池,要么传递给下一个需要连接的(Rows)对象,最后使用完连接的对象也会把连接释放回到连接池。
请求一个连接的函数有好几种,执行完毕处理连接的方式稍有差别,大致如下:

  • db.Ping() 调用完毕后会马上把连接返回给连接池。
  • db.Exec() 调用完毕后会马上把连接返回给连接池,但是它返回的Result对象还保留这连接的引用,当后面的代码需要处理结果集的时候连接将会被重用。
  • db.Query() 调用完毕后会将连接传递给sql.Rows类型,当然后者迭代完毕或者显示的调用.Clonse()方法后,连接将会被释放回到连接池。
  • db.QueryRow()调用完毕后会将连接传递给sql.Row类型,当.Scan()方法调用之后把连接释放回到连接池。
  • db.Begin() 调用完毕后将连接传递给sql.Tx类型对象,当.Commit()或.Rollback()方法调用后释放连接。

因为每一个连接都是惰性创建的,如何验证sql.Open调用之后,sql.DB对象可用呢?通常使用db.Ping()方法初始化:

package main

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

func CheckErr(err error) {
	if err != nil {
		fmt.Println(err)
	}
}
func Use_Sql() {
	//连接数据库
	db, err := sql.Open("mysql", "root:toolwiz.mysql.admin@tcp(192.168.1.254:3307)/test?charset=utf8")
	CheckErr(err)
	defer db.Close()

	err = db.Ping()
	CheckErr(err)
}
func main() {
    Use_Sql()
}

连接失败

关于连接池另外一个知识点就是你不必检查或者尝试处理连接失败的情况。当你进行数据库操作的时候,如果连接失败了,database/sql会帮你处理。实际上,当从连接池取出的连接断开的时候,database/sql会自动尝试重连10次。仍然无法重连的情况下会自动从连接池再获取一个或者新建另外一个。好比去买鸡蛋,售货员会从箱子里掏出鸡蛋,如果发现是坏蛋则连续掏10次,仍然找不到合适的就会换一个箱子招,或者从别的库房再拿一个给你。

连接池配置

配置连接池有两个的方法:

  • db.SetMaxOpenConns(n int) 设置打开数据库的最大连接数。包含正在使用的连接和连接池的连接。如果你的函数调用需要申请一个连接,并且连接池已经没有了连接或者连接数达到了最大连接数。此时的函数调用将会被block,直到有可用的连接才会返回。设置这个值可以避免并发太高导致连接mysql出现too many connections的错误。该函数的默认设置是0,表示无限制。
  • db.SetMaxIdleConns(n int) 设置连接池中的保持连接的最大连接数。默认也是0,表示连接池不会保持释放会连接池中的连接的连接状态:即当连接释放回到连接池的时候,连接将会被关闭。这会导致连接再连接池中频繁的关闭和创建。

对于连接池的使用依赖于你是如何配置连接池,如果使用不当会导致下面问题:
1, 大量的连接空闲,导致额外的工作和延迟。
2, 连接数据库的连接过多导致错误。
3, 连接阻塞。
4, 连接池有超过十个或者更多的死连接,限制就是10次重连。

数据库CURD 操作

我们了解了数据库连接与连接池。拿到了连接当然就是为了跟数据库交互。对于数据库交互,无怪乎两类操作,读和写。其中怎么读,怎么写,读和写的过程糅合一起就会遇到复杂的事务。本篇内容主要关注数据库的读写操作,后面再涉及事务的介绍。

读取数据

database/sql提供了Query和QueryRow方法进行查询数据库。对于Query方法的原理,正如前文介绍的主要分为三步:
1,从连接池中请求一个连接
2,执行查询的sql语句
3,将数据库连接的所属权传递给Result结果集

Query返回的结果集是sql.Rows类型。它有一个Next方法,可以迭代数据库的游标,进而获取每一行的数据,大概使用范式如下:

    rows, err := db.Query("SELECT world FROM test.hello")
    if err != nil{
        log.Fatalln(err)
    }

    for rows.Next(){
        var s string
        err = rows.Scan(&s)
        if err !=nil{
            log.Fatalln(err)
        }
        log.Printf("found row containing %q", s)
    }
    rows.Close()

上述代码我们已经见过好多次了,想必大家都轻车熟路啦。rows.Next方法设计用来迭代。当它迭代到最后一行数据之后,会触发一个io.EOF的信号,即引发一个错误,同时go会自动调用rows.Close方法释放连接,然后返回false。此时循环将会结束退出。
通常你会正常迭代完数据然后退出循环。可是如果并没有正常的循环而因其他错误导致退出了循环。此时rows.Next处理结果集的过程并没有完成,归属于rows的连接不会被释放回到连接池。因此十分有必要正确的处理rows.Close事件。如果没有关闭rows连接,将导致大量的连接并且不会被其他函数重用,就像溢出了一样。最终将导致数据库无法使用。
那么如何阻止这样的行为呢?上述代码已经展示,无论循环是否完成或者因为其他原因退出,都显示的调用rows.Close方法,确保连接释放。又或者使用defer指令在函数退出的时候释放连接,即使连接已经释放了,rows.Close仍然可以调用多次,是无害的。
使用defer的时候需要注意,如果一个函数执行很长的逻辑,例如main函数,那么rows的连接释放就会也很长,好的实践方案是尽可能的越早释放连接。
rows.Next循环迭代的时候,因为触发了io.EOF而退出循环。为了检查是否是迭代正常退出还是异常退出,需要检查rows.Err。例如上面的代码应该改成:

    rows, err := db.Query("SELECT world FROM test.hello")
    if err != nil{
        log.Fatalln(err)
    }
    defer rows.Close()

    for rows.Next(){
        var s string
        err = rows.Scan(&s)
        if err !=nil{
            log.Fatalln(err)
        }
        log.Printf("found row containing %q", s)
    }
    rows.Close()
    if err = rows.Err(); err != nil {
        log.Fatal(err)
    }

读取单条记录

Query方法是读取多行结果集,实际开发中,很多查询只需要单条记录,不需要再通过Next迭代。golang提供了QueryRow方法用于查询单条记录的结果集。

	var student_id int
	err = db.QueryRow("select student_id from student where name='zhangshoufu'").Scan(&student_id)
	if err != nil {
		if err == sql.ErrNoRows {
			fmt.Println(err)
		} else {
			fmt.Println(err)
		}
	}
	fmt.Println(student_id)

或者

	var student_id, age, grade int
	var name, gender string
	err = db.QueryRow("select * from student").Scan(&student_id, &grade, &name, &gender, &age)
	if err != nil {
		if err == sql.ErrNoRows {
			fmt.Println(err)
		} else {
			fmt.Println(err)
		}
	}
	fmt.Println(student_id, grade, name, gender, age)

QueryRow方法的使用很简单,它要么返回sql.Row类型,要么返回一个error,如果是发送了错误,则会延迟到Scan调用结束后返回,如果没有错误,则Scan正常执行。只有当查询的结果为空的时候,会触发一个sql.ErrNoRows错误。你可以选择先检查错误再调用Scan方法,或者先调用Scan再检查错误。

rows.Scan原理

结果集方法Scan可以把数据库取出的字段值赋值给指定的数据结构。它的参数是一个空接口的切片,这就意味着可以传入任何值。通常把需要赋值的目标变量的指针当成参数传入,它能将数据库取出的值赋值到指针值对象上。

var var1, var2 string
err = row.Scan(&var1, &var2)

Scan方法源代码:

func (nt *NullTime) Scan(value interface{}) (err error) {
	if value == nil {
		nt.Time, nt.Valid = time.Time{}, false
		return
	}

	switch v := value.(type) {
	case time.Time:
		nt.Time, nt.Valid = v, true
		return
	case []byte:
		nt.Time, err = parseDateTime(string(v), time.UTC)
		nt.Valid = (err == nil)
		return
	case string:
		nt.Time, err = parseDateTime(v, time.UTC)
		nt.Valid = (err == nil)
		return
	}

	nt.Valid = false
	return fmt.Errorf("Can't convert %T to time.Time", value)
}

在一些特殊案例中,如果你不想把值赋值给指定的目标变量,那么需要使用sql.RawBytes类型。如何使用sql.RawBytes需要参考更细的官方文档。大多数情况下我们不必这么做。但是还是需要注意在db.QueryRow().Scan()中不能使用 *sql.RawBytes。
Scan还会帮我们自动推断除数据字段匹配目标变量。比如有个数据库字段的类型是VARCHAR,而他的值是一个数字串,例如"1"。如果我们定义目标变量是string,则scan赋值后目标变量是数字string。如果声明的目标变量是一个数字类型,那么scan会自动调用strconv.ParseInt()或者strconv.ParseInt()方法将字段转换成和声明的目标变量一致的类型。当然如果有些字段无法转换成功,则会返回错误。因此在调用scan后都需要检查错误。

var world int
err = stmt.QueryRow(1).Scan(&world)

此时scan会把字段转变成数字整型的赋值给world变量

var world string
err = stmt.QueryRow(1).Scan(&world)

此时scan取出的字段就是字串。同样的如果字段是int类型,声明的变量是string类型,scan也会自动将数字转换成字串赋值给目标变量。

空值处理

数据库有一个特殊的类型,NULL空值。可是NULL不能通过scan直接跟普遍变量赋值,甚至也不能将null赋值给nil。对于null必须指定特殊的类型,这些类型定义在database/sql库中。例如sql.NullFloat64。如果在标准库中找不到匹配的类型,可以尝试在驱动中寻找。下面是一个简单的例子:

var (
   s1 string
    s2 sql.NullString i1 int
    f1 float64
    f2 float64
)
// 假设数据库的记录为 ["hello", NULL, 12345, "12345.6789", "not-a-float"]
err = rows.Scan(&s1, &s2, &i1, &f1, &f2) if err != nil {
log.Fatal(err) }

因为最后一个f2字段的值不是float,这会语法一个错误。

sql: Scan error on column index 4: converting string "not-a- oat" to a  oat64: strconv.ParseFloat: parsing "not-a- oat": invalid syntax

如果忽略err,强行读取目标变量,可以看到最后一个值转换错误会处理,而不是抛出异常:

err = rows.Scan(&s1, &s2, &i1, &f1, &f2)
log.Printf("%q %#v %d %f %f", s1, s2, i1, f1, f2)

// 输出
 "hello" sql.NullString{String:"", Valid:false} 12345 12345.678900
0.000000

可以看到,除了最后一个转换失败变成了零值之外,其他都正常的取出了值,尤其是null匹配了NullString类型的目标变量。

对于null的操作,通常仍然需要验证:

var world sql.NullString
err := db.QueryRow("SELECT world FROM hello WHERE id = ?", id).Scan(&world)
...
if world.Valid {
      wrold.String 
} else {
    // 数据库的value是不是null的时候,输出 world的字符串值, 空字符串   
    world.String
}

对应的,如果world字段是一个int,那么声明的目标变量类似是sql.NullInt64,读取其值的方法为world.Int64。
但是有时候我们并不关心值是不是Null,我们只需要吧他当一个空字符串来对待就行。这时候我们可以使用[]byte(null byte[]可以转化为空string)

var world []byte
err := db.QueryRow("SELECT world FROM hello WHERE id = ?", id).Scan(&world)
...
log.Println(string(real_name)) // 有值则取出字串值,null则转换成 空字串。

自动匹配字段

在执行查询的时候,我们定义了目标变量,同时查询的时候也写明了字段,如果不指名字段,或者字段的顺序和查询的不一样,都有可能出错。因此如果能够自动匹配查询的字段值,将会十分节省代码,同时也易于维护。
go提供了Columns方法用获取字段名,与大多数函数一样,读取失败将会返回一个err,因此需要检查错误。

cols, err := rows.Columns()
if err != nil{
   log.Fatalln(er)
}

对于不定字段查询,我们可以定义一个map的key和value用来表示数据库一条记录的row的值。通过rows.Columns得到的col作为map的key值。下面是一个例子

func main() {
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/test?parseTime=true")
    if err != nil{
        log.Fatal(err)
    }
    defer db.Close()

    rows, err := db.Query("SELECT * FROM user WHERE gid = 1")
    if err != nil{
        log.Fatalln(err)
    }
    defer rows.Close()


    cols, err := rows.Columns()
    if err != nil{
        log.Fatalln(err)
    }
    fmt.Println(cols)
    vals := make([][]byte, len(cols))
    scans := make([]interface{}, len(cols))

    for i := range vals{
        scans[i] = &vals[i]
    }

    var results []map[string]string

    for rows.Next(){
        err = rows.Scan(scans...)
        if err != nil{
            log.Fatalln(err)
        }

        row := make(map[string]string)
        for k, v := range vals{
            key := cols[k]
            row[key] = string(v)
        }
        results = append(results, row)
    }

    for k, v :=range results{
        fmt.Println(k, v)
    }
}

数据表user有三个字段,id(int),gid(int),real_name(varchar)。我们使用*取出所有的字段。使用rows.Columns()获取字段名,是一个string的数组。然后创建一个切片vals,用来存放所取出来的数据结果,类似是byte的切片。接下来还需要定义一个切片,这个切片用来scan,将数据库的值复制到给它。
完成这一步之后,vals则得到了scan复制给他的值,因为是byte的切片,因此在循环一次,将其转换成string即可。
转换后的row即我们取出的数据行值,最后组装到result切片中。
运行结果如下

[id gid real_name]
0 map[id:4 gid:1 real_name:瑟兰督依]
1 map[real_name:来格拉斯 id:5 gid:1]
2 map[id:15 gid:1 real_name:]

有一条记录的 real_name 值为空字串,因为其数据库存储的是NULL。

Exec

前面介绍了很多关于查询方面的内容,查询是读方便的内容,对于写,即插入更新和删除。这类操作与query不太一样,写的操作只关系是否写成功了。database/sql提供了Exec方法用于执行写的操作。
我们也见识到了,Eexc返回一个sql.Result类型,它有两个方法LastInsertId和RowsAffected。LastInsertId返回是一个数据库自增的id,这是一个int64类型的值。 RowsAffected返回的是执行的操作成功了几条
Exec执行完毕之后,连接会立即释放回到连接池中,因此不需要像query那样再手动调用row的close方法。
关于LastInsertId和RowsAffected方法执行错误的返回,这跟底层的数据库是有关系的。

 inset, err := db.Exec("insert into test.student(student_id,name,gender,age) values(2,'zhujun','nv',30),(3,'haha','nan',20)")
    CheckErr(err)
    //获取插入成功的条数
    rowCount, err := inset.RowsAffected()
    CheckErr(err)
    fmt.Println(rowCount)

总结

目前,我们大致了解了数据库的CURD操作。对于读的操作,需要定义目标变量才能scan数据记录。scan会智能的帮我们转换一些数据,取决于定义的目标变量类型。对于null的处理,可以使用database/sql或驱动提供的类型声明,也可以使用[]byte将其转换成空字串。除了读数据之外,对于写的操作,database/sql也提供了Exec方法,并且对于sql.Result提供了LastInsertId和RowsAffected方法用于获取写后的结果。
在实际应用中,与数据库交互,往往写的sql语句还带有参数,这类sql可以称之为prepare语句。prepare语句有很多好处,可以防止sql注入,可以批量执行等。但是prepare的连接管理有其自己的机制,也有其使用上的陷进,关于prepare的使用,我们将会在以后讨论。

参考:

在人间: https://www.jianshu.com/u/5qrPPM


标题:GoLang 操作数据库
作者:shoufuzhang
地址:https://www.zhangshoufu.com/articles/2019/07/23/1563876247893.html
名言:The master has failed more times than the beginner has tried.
评论
发表评论