golang操作数据库

介绍

Go语言中的database/sql包提供了保证SQL或类SQL数据库的泛用接口，并不提供具体的数据库驱动。使用database/sql包时必须注入相应的数据库驱动。常见的数据库驱动如下：

• Mysql: https://github.com/go-sql-driver/mysql
• MyMysql: https://github.com/ziutek/mymysql
• Postgres: https://github.com/lib/pq
• Tidb: https://github.com/pingcap/tidb
• SQLite: https://github.com/mattn/go-sqlite3
• MsSql: https://github.com/denisenkom/go-mssqldb
• Oracle: https://github.com/mattn/go-oci8 (试验性支持)

这里我们以pgsql为例

创建并初始化连接

package main

import (
	"database/sql"        //通用的接口，就用这个来操作
	"fmt"
	_ "github.com/lib/pq" //必须要有相应的驱动,python中，是不需要显示引用的，但是golang需要引用执行一下内部的Init函数,我这里是postgresql，其他数据库就换成其他的驱动
)

func main() {
	//传入数据库类型，和配置信息dbType://user:password@tcp(ip:port)/dbName?sslmode=disable
	//引擎这里结尾要加上一个sslmode=disable，不然会报出：pq: SSL is not enabled on the server
	db, err := sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable")
	if err != nil {
		panic(err)
	}
	defer func() {
		_ = db.Close()
	}()

	//这里的open函数只是验证参数是否合法，而不会创建和数据库的连接。
	//如果要检测数据库源的名称是否和法，即是否能连接到指定的数据库，需要调用返回值的Ping方法
	fmt.Println(db.Ping()) // <nil>
	//打印nil证明没有错误
}

调用db.Ping()没有问题之后，我们就可以使用db去操作了，注意的是，这个返回的db可以安全的被多个goroutine同时使用，并会维护自身的闲置连接池。这样一来，Open函数只需调用一次。很少需要关闭db。

package main

import (
	"database/sql"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

func main() {
	initDB()
}

SetMaxOpenConns

func (db *DB) SetMaxOpenConns(n int)

SetMaxOpenConns设置与数据库建立连接的最大数目。 如果n大于0且小于最大闲置连接数，会将最大闲置连接数减小到和最大开启连接数相等。 如果n<=0，不会限制最大开启连接数，默认为0（无限制）。

SetMaxIdleConns

func (db *DB) SetMaxIdleConns(n int)

SetMaxIdleConns设置连接池中的最大闲置连接数。 如果n大于最大开启连接数，则会将新的最大闲置连接数会减小到和最大开启连接数相等。 如果n<=0，不会保留闲置连接。

个人觉得这两个设置连接数，基本很少用，使用默认的即可

增删改查

下面就是喜闻乐见的增删改查环节了，说是增删改查，其实说白了还是体现在sql语句上。golang的话，则只是一个连接执行的过程。所以我们着重介绍查

查询单条数据

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

type heroes struct {
	id int64
	name string
	age string
	hp int64
	attack string
	role string
	ultimate string
	country string
}

func fetchOne(){
	var hero heroes
	//这里的$1表示占位符
	query := "select * from anime.overwatch where id = $1"
    //QueryRow查询单条数据，
	if err = db.QueryRow(query, 1).Scan(
		&hero.id,&hero.name, &hero.age, &hero.hp, &hero.attack, &hero.role,
		&hero.ultimate, &hero.country,
		); err != nil {
			panic(err)
	}
	fmt.Println(hero) // {1 麦克雷 37 200 远距离 突击 午时已到 美国}
}

func main() {
	initDB()
	fetchOne()
}

查询多条数据

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

type heroes struct {
	id int64
	name string
	age string
	hp int64
	attack string
	role string
	ultimate string
	country string
}

func fetchMany(){
	var hero heroes
	query := "select * from anime.overwatch where role = $1 and hp = $2"
	//只需要把QueryRow换成Query就可以了
	rows, err := db.Query(query, "突击", 200)
	if err != nil {
		panic(err)
	}
	defer func() {
		_ = rows.Close()
	}()
	//可以直接拿到返回的对应字段
	fmt.Println(rows.Columns()) // [id name age hp attack role ultimate country] <nil>
	for rows.Next() {
		if err = rows.Scan(
			&hero.id,&hero.name, &hero.age, &hero.hp, &hero.attack, &hero.role,
			&hero.ultimate, &hero.country); err != nil {
			panic(err)
		}
		fmt.Println(hero)
		/*
			{3 源氏 35 200 近距离 突击 斩 日本}
			{6 猎空 26 200 近距离 突击 脉冲炸弹 英国}
			{8 半藏 38 200 中远距离 突击 龙 日本}
			{9 狂鼠 25 200 近距离 突击 炸弹轮胎 澳大利亚}
			{15 黑影 30 200 近距离 突击 电磁脉冲 墨西哥}
			{17 法老之鹰 32 200 中远距离 突击 天降正义 埃及}
			{18 黑百合 33 200 远距离 突击 红外侦测 法国}
			{22 士兵76 55 200 远距离 突击 战术目镜 美国}
			{1 麦克雷 37 200 远距离 突击 午时已到 美国}
		*/
	}

}

func main() {
	initDB()
	fetchMany()
}

插入数据

插入、更新和删除操作都使用方法。

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

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

func insertData(){
	insertQuery := "insert into anime.overwatch(id, name) values ($1, $2)"
	ret, err := db.Exec(insertQuery, 100, "呱呱")
	if err != nil {
		panic(err)
	}

	//返回插入的记录id，但是报错，这个driver不支持，mysql是支持的
	if id, err := ret.LastInsertId(); err != nil {
		panic(err)
	} else {
		fmt.Println(id) 
	}

	//返回影响的行数
	if rowCount, err := ret.RowsAffected(); err != nil {
		panic(err)
	} else {
		fmt.Println(rowCount) //1
	}
}

func main() {
	initDB()
	insertData()
}

更新数据

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

func updateData(){
	updateQuery := "update  anime.overwatch set name = $1 where id = $2"
	ret, err := db.Exec(updateQuery, "嘎嘎", 100)
	if err != nil {
		panic(err)
	}

	//返回影响的行数
	if rowCount, err := ret.RowsAffected(); err != nil {
		panic(err)
	} else {
		fmt.Println(rowCount) //1
	}
}

func main() {
	initDB()
	updateData()
}

删除数据

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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


func deleteData(){
	deleteQuery := "delete from anime.overwatch where id = $1"
	ret, err := db.Exec(deleteQuery, 100)
	if err != nil {
		panic(err)
	}

	//返回影响的行数
	if rowCount, err := ret.RowsAffected(); err != nil {
		panic(err)
	} else {
		fmt.Println(rowCount) //1
	}
}

func main() {
	initDB()
	deleteData()
}

预处理

什么是预处理？

普通SQL语句执行过程：

1. 客户端对SQL语句进行占位符替换得到完整的SQL语句。
2. 客户端发送完整SQL语句到MySQL服务端
3. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

预处理执行过程：

1. 把SQL语句分成两部分，命令部分与数据部分。
2. 先把命令部分发送给MySQL服务端，MySQL服务端进行SQL预处理。
3. 然后把数据部分发送给MySQL服务端，MySQL服务端对SQL语句进行占位符替换。
4. MySQL服务端执行完整的SQL语句并将结果返回给客户端。

为什么要预处理？

1. 优化MySQL服务器重复执行SQL的方法，可以提升服务器性能，提前让服务器编译，一次编译多次执行，节省后续编译的成本。
2. 避免SQL注入问题。

Go实现MySQL预处理

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

type heroes struct {
	id int64
	name string
	age string
	hp int64
	attack string
	role string
	ultimate string
	country string
}

func insertData(){
	insertQuery := "insert into anime.overwatch(id, name, age) values($1, $2, $3)"
	//Prepare方法会先将sql语句发送给MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。
	stmt, _ := db.Prepare(insertQuery)
	defer stmt.Close()
	//传入参数
	ret, err := stmt.Exec(100, "hanser", 26)
	if err != nil {
		panic(err)
	}

	if rowCount, err := ret.RowsAffected(); err != nil {
		panic(err)
	} else {
		fmt.Println(rowCount) //1
	}
}

func main() {
	initDB()
	insertData()
}
//我们这里可以多次执行Exec，会多次插入记录

Go实现MySQL事务

什么是事务？

事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。

在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。

事务的ACID

通常事务必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

条件	解释
原子性	一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性	在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性	数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性	事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务相关方法

Go语言中使用以下三个方法实现MySQL中的事务操作。

• 开始事务

  func (db *DB) Begin() (*Tx, error)
  

• 提交事务

  func (tx *Tx) Commit() error
  

• 回滚事务

  func (tx *Tx) Rollback() error
  

事务示例

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/lib/pq"
)

var db *sql.DB
var err error

func initDB() {
	if db, err = sql.Open("postgres", "postgres://postgres:zgghyys123@127.0.0.1:5432/postgres?sslmode=disable"); err != nil {
		panic(err)
	}

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

func transAction(){
	//开启事务
	tx, err := db.Begin()
	if err != nil {
		if tx != nil {
			tx.Rollback() //回滚
		}
		panic(err)
	}
	query1 := "update anime.overwatch set age = 30 where id = $1"
	if _, err = tx.Exec(query1, 2);err != nil {
		tx.Rollback()
		panic(err)
	}

	query2 := "update anime.overwatch set age = 30 where id = $1"
	if _, err = tx.Exec(query2, 3);err != nil {
		tx.Rollback()
		panic(err)
	}

	query3 := "update anime.overwatch set age = 30 where id = $1"
	if _, err = tx.Exec(query3, 4);err != nil {
		tx.Rollback()
		panic(err)
	}

	//只要失败了就回滚

	//提交事务
	if err = tx.Commit(); err != nil {
		tx.Rollback()
		panic(err)
	}

	fmt.Println("事务完成")
}

func main() {
	initDB()
	transAction() //事务完成
}

SQL中的占位符

不同的数据库中，SQL语句使用的占位符语法不尽相同。

数据库	占位符语法
MySQL	?
PostgreSQL	$1, $2等
SQLite	? 和$1
Oracle	:name