本节核心内容
- 介绍MySQL中的基于sql.DB的常用方法
- 介绍MySQL预处理的优势和流程原理
- 介绍基于预处理方式的CRUD代码如何编写
- 介绍一些开发中的注意事项
本小节视频教程和代码：百度网盘
可先下载视频和源码到本地，边看视频边结合源码理解后续内容，边学边练。
基于sql.DB的常用方法介绍
DB.Query查询操作, 用来查询数据
DB.QueryRow查询操作, 用来查询单行数据
DB.Exec执行操作, update，insert，delete
DB.Begin返回事物对象，可以用该对象执行CRUD
DB.Prepare返回预执行对象，可以用该对象执行CRUD
DB.Close用来关闭数据库，释放任何打开的资源。
预编译语句(Prepared Statement)
在MySQL中，存在着一个预编译语句(Prepared Statement)，它具有着诸多好处,而在GoLang中的DB.Prepare()和Tx.Prepare()也提供了对于这种预备表达式的支持。 因此我们在开发中尽量使用它.
预处理的优势:
- 可以实现自定义参数的查询
- 通常来说, 比手动拼接字符串 SQL 语句高效.
- 可以防止SQL注入攻击
预处理的流程:
- 将sql分为2部分.命令部分和数据部分.
- 首先将命令部分发送给mysql服务器,mysql进行预处理.(如生成AST)
- 然后将数据部分发送给mysql服务器,mysql进行占位符替换.
- mysql服务器执行sql语句,把执行结果发送给客户端.
一般用Prepared Statements和Exec()完成INSERT, UPDATE, DELETE操作。
注意事项：使用预处理进行查询操作时,不仅在defer时需要关闭结果集,而且还要关闭命令句柄,否则同样会占用连接,导致阻塞.
建立数据库表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT \'\',
`age` int(11) DEFAULT \'0\',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4
查询
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
type User struct {
Id int
Name sql.NullString
Age sql.NullInt64
}
func PrepareQuery(db *sql.DB, id int) {
stmt, err := db.Prepare("select id, name, age from user where id > ?")
if err != nil {
panic(err)
}
rows, err := stmt.Query(id)
if err != nil {
panic(err)
}
defer stmt.Close()
defer rows.Close()
for rows.Next(){
var user User
err := rows.Scan(&user.Id, &user.Name, &user.Age)
if err != nil {
panic(err)
}
fmt.Printf("User.Id: %v\nUser.Name: %s\nUser.Age: %d\n", user.Id,user.Name.String,user.Age.Int64)
}
}
func main() {
dns := "root:root@tcp(127.0.0.1:3306)/test"
db, err := sql.Open("mysql", dns)
if err != nil {
panic(err)
}
defer db.Close()
PrepareQuery(db, 0)
}
代码解析：
db.Prepare()函数用来返回准备要执行的sql操作，然后返回准备完毕的执行状态。db.Query()函数用来直接执行Sql返回Rows结果。stmt.Close()函数用来关闭stmt预执行语句的链接rows.Close()函数用来关闭rows行操作对象的链接rows.Scan()函数用来将从数据库读取到的值复制到指向的值中db.Close()函数用来关闭数据库连接，释放资源
增、删、改
在学习完查询方法后，咱们来学习一下如何增删改，下面给大家分享了一个基于预编译方式的通用的CRUD代码,代码如下：
package model
import (
"database/sql"
"log"
"mysql/config"
_ "github.com/go-sql-driver/mysql" //这个引用是必不可少的，因为需要调用driver.go文件里的init方法来提供一个数据库驱动程序
"github.com/spf13/viper"
"fmt"
)
var DB *sql.DB //全局变量，这样可以在别处调用
func Init() *sql.DB {
var err error
//加载配置文件
config.Init()
//这行代码的作用就是初始化一个sql.DB对象
DB ,err = sql.Open("mysql", viper.GetString("mysql.source_name"))
if nil != err {
panic(err)
}
//设置最大超时时间
DB.SetMaxIdleConns(viper.GetInt("mysql.max_idle_conns"))
//建立链接
err = DB.Ping()
if nil != err{
panic(err)
}else{
log.Println("Mysql Startup Normal!")
}
return DB
}
// Insert 插入操作
func Insert(sql string,args... interface{}) {
stmt, err := DB.Prepare(sql)
defer stmt.Close()
CheckErr(err, "SQL语句设置失败")
result, err := stmt.Exec(args...)
CheckErr(err, "参数添加失败")
id, err := result.LastInsertId()
CheckErr(err, "插入失败")
fmt.Printf("插入成功，ID为%v\n",id)
}
// Delete 删除操作
func Delete(sql string,args... interface{}) {
stmt, err := DB.Prepare(sql)
defer stmt.Close()
CheckErr(err, "SQL语句设置失败")
result, err := stmt.Exec(args...)
CheckErr(err, "参数添加失败")
num, err := result.RowsAffected()
CheckErr(err,"删除失败")
fmt.Printf("删除成功，删除行数为%d\n",num)
}
// Update 修改操作
func Update(sql string,args... interface{}) {
stmt, err := DB.Prepare(sql)
defer stmt.Close()
CheckErr(err, "SQL语句设置失败")
result, err := stmt.Exec(args...)
CheckErr(err, "参数添加失败")
num, err := result.RowsAffected()
CheckErr(err,"修改失败")
fmt.Printf("修改成功，修改行数为%d\n",num)
}
// CheckErr 用来校验error对象是否为空
func CheckErr(err error,msg string) {
if nil != err {
log.Panicln(msg,err)
}
}
代码解析：
db.Prepare()函数用来返回准备要执行的sql操作，然后返回准备完毕的执行状态。stmt.Close()函数用来关闭stmt预执行语句的链接stmt.LastInsertId()函数用来返回Insert操作之后获取到的主键idstmt.Exec()函数用来执行语句并返回结果result.RowsAffected()函数用来返回受影响的行数
示例代码：
package main
import (
_ "github.com/go-sql-driver/mysql"
"mysql/model"
)
func main() {
DB := model.Init()
model.Insert("insert into user(name,age) values(?,?) ","jeck",18)
model.Insert("insert into user(name,age) values(?,?) ","Lucy",16)
model.Insert("insert into user(name,age) values(?,?) ","David",22)
model.Update("update user set age = ? where name = ?",20,"Lucy")
model.Delete("delete from user where name = ? ", "David")
DB.Close()
}
运行结果
2019/01/23 19:32:39 Mysql Startup Normal!
插入成功，ID为3
插入成功，ID为4
插入成功，ID为5
修改成功，修改行数为1
删除成功，删除行数为1
注意事项
在使用sql.DB对MySQL进行操作时，有一些注意事项是需要我们注意的，下面将列出那些开发中的一些建议和问题思路
- 建议将rows.Scan 参数的顺序和需要查询的结果的column一一对应.
rows.Scan 参数的顺序很重要, 需要和查询的结果的column对应. 例如 “SELECT * From user where age >=20 AND age < 30” 查询的行的 column 顺序是 “id, name, age” 和插入操作顺序相同, 因此 rows.Scan 也需要按照此顺序 rows.Scan(&id, &name, &age), 不然会造成数据读取的错位.
- 建议将待查询的数据类型定义为sql.Nullxxx类型
因为golang是强类型语言，所以查询数据时先定义数据类型，但是查询数据库中的数据存在三种可能:存在值，存在零值，未赋值NULL 三种状态, 因为可以将待查询的数据类型定义为sql.Nullxxx类型，可以通过判断Valid值来判断查询到的值是否为赋值状态还是未赋值NULL状态.
- 建议在每次db.Query操作后, 都调用rows.Close().
因为 db.Query() 会从数据库连接池中获取一个连接, 这个底层连接在结果集(rows)未关闭前会被标记为处于繁忙状态。当遍历读到最后一条记录时，会发生一个内部EOF错误，自动调用rows.Close(),但如果提前退出循环，rows不会关闭，连接不会回到连接池中，连接也不会关闭, 则此连接会一直被占用. 因此通常我们使用 defer rows.Close() 来确保数据库连接可以正确放回到连接池中; 不过阅读源码发现rows.Close()操作是幂等操作，即一个幂等操作的特点是其任意多次执行所产生的影响均与一次执行的影响相同, 所以即便对已关闭的rows再执行close()也没关系.
小节
本小节主要讲解了MySQL的常用方法，其中主要讲了预编译方式的CRUD,以及预编译方式的优势和原理，最后总结了一些注意事项。
请发表评论