Go访问MySQL
Go语言中的database/sql包提供了连接SQL数据库或类SQL数据库的泛用接口,但并不提供具体的数据库驱动程序。在使用database/sql包时,必须注入至少一个数据库驱动程序。
在Go语言中,常用的数据库基本都有完整的第三方包实现。在用Go语言访问M小ySQL之前,需要先创建数据库和数据表。
- 创建数据库和数据表
create database chapter05;
create table `user` (
`uid` bigint(20) not null auto_increment,
`name` varchar(20) default '',
`phone` varchar(20) default '',
primary key(`uid`)
) engine=InnoDB auto_increment=1 default charset=utf8mb4
- 下载MySQL的驱动程序
go get -u github.com/go-sql-driver/mysql
- 使用MySQL驱动程序
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)
在以上语句中,github.com/go-sql-driver/mysql就是依赖包。因为没有直接使用该包中的对象,所以在导入包前面被加上了下画线(_)。
Go语言database/sql包中提供了Open()函数,用来连接数据库。Open()函数的定义如下:
func Open(driverName,dataSourceName string)(*DB,error)
其中,dirverName参数用于指定的数据。dataSourceName参数用于指定数据源,一般至少包括数据库文件名和(可能的)连接信息。
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"log"
)
func main() {
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/chapter")
if err != nil {
log.Fatal(err)
}
defer db.Close()
}
- 初始化连接
在用Open()函数建立连接后,如果要检查数据源的名称是否合法,则可以调用Ping()方法。
返回的DB对象可以安全地被多个goroutine同时使用,并且它会维护自身的闲置连接池。这样Open()函数只需调用一次,因为一般启动后很少关闭DB对象。用Open()函数初始化连接的示例
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
//定义一个初始化数据库的函数
func initDB() (err error) {
db, err := sql.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/chapter")
if err != nil {
return err
}
//尝试与数据库建立连接
err = db.Ping()
if err != nil {
return err
}
return nil
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("init db failed, err: %v\n", err)
return
}
}
其中,sql.DB是一个数据库的操作句柄,代表一个具有零到多个底层连接的连接池。它可以安全地被多个goroutine同时使用。database/sql包会自动创建和释放连接,也会维护一个闲置连接的连接池。
- 设置最大连接数
database/sql包中SetMaxOpenConns()方法用于设置与数据库建立连接的最大数目,其定义如下:
func (db *DB) SetMaxOpenConns(n int)
其中参数n为整数类型。如果n大于0且小于“最大闲置连接数”,则将“最大闲置连接数”减小到与“最大开启连接数的限制”匹配。如果≤0,则不会限制最大开启连接数,默认为0(无限制)。
- 设置最大限制连接数
database/sql包中的SetMaxldleConns()方法用于设置连接池中的最大闲置连接数,其定义如下:
func (db *DB) SetMaxIdleConns(n int)
其中参数n为整数类型。如果n大于最大开启连接数,则新的最大闲置连接数会以最大开启连接数为准。如果n≤0,则将不会保留闲置连接。
- SQL查询
QueryRow()方法进行单行查询:
根据本节之前创建的uSer表,定义一个User结构体来存储数据库返回的数据:
type User struct {
Uid int
Name string
Phone string
}
database/sql包,中单行查询方法的定义如下:
func (db *DB)QueryRow(query string,args ...interface(}) *Row
QueryRow()方法执行一次查询,并返回最多一行(Row)结果。QueryRow()方法总是返回非nil的值,直到返回值的Scan()方法被调用时才会返回被延迟的错误。示例代码如下。
//单行测试
func queryRow(uid int) (user User) {
//应确保在QueryRow()方法之后调用Scan方法,否则持有的数据库连接不会被释放
err := DB.QueryRow("select uid, name, phone from `user` where uid = ?", uid).Scan(&user.Uid, &user.Name, &user.Phone)
if err != nil {
log.Fatal(err)
}
fmt.Printf("uid:%d name:%s phone:%s\n", user.Uid, user.Name, user.Phone)
return
}
2. 用Query()方法进行多行查询
Quey()方法执行一次查询,返回多行(Rows)结果,一般用于执行SELECT类型的SQL命令。Quey()方法的定义如下:
func (db *DB)Query(query string,args ...interface(})(*Rows, error)
其中,参数query表示SQL语句,参数args表示query查询语句中的占位参数。
//查询多条数据示例
func queryMultiRow() {
rows, err := DB.Query("select uid, name, phone from `user` where uid > ?", 0)
if err != nil {
log.Fatal(err)
}
//关闭rows,释放持有的数据库连接
defer rows.Close()
//循环读取结果集中的数据
var user User
for rows.Next() {
err := rows.Scan(&user.Uid, &user.Name, &user.Phone)
if err != nil {
fmt.Printf("scan failed, err: %v\n", err)
return
}
fmt.Printf("uid:%d name:%s phone:%s\n", user.Uid, user.Name, user.Phone)
}
}
3. 用Exec()方法插入数据
EXec()方法的定义如下:
func (db *DB)Exec(query string,args ...interface())(Result,error)
Exec()方法用于执行一次命令(包括查询、删除、更新、插入等),返回的Result是对已执行的SQL命令的执行结果。其中,参数query表示SQL语句,参数args表示query参数中的占位参数。
//插入数据
func insertRow() {
ret, err := DB.Exec("insert into `user`(name, phone) values (?, ?)", "王五", 12345676789)
if err != nil {
fmt.Printf("insert failed, err: %v\n", err)
return
}
uid, err := ret.LastInsertId() //新插入数据的uid
if err != nil {
fmt.Printf("get lastinsert ID failed, err: %v\n", err)
return
}
fmt.Printf("insert success, the id is %d.\n", uid)
}
4. 更新数据
//更新数据
func updateRow() {
ret, err := DB.Exec("update `user` set name = ? where uid = ?", "张三", 3)
if err != nil {
fmt.Printf("update failed, err: %v\n", err)
return
}
n, err := ret.RowsAffected() //影响行数
if err != nil {
fmt.Printf("get RowsAffected failed, err: %v\n", err)
return
}
fmt.Printf("update success, affected rows: %d\n", n)
}
5. 删除数据
//删除数据
func deleteRow() {
ret, err := DB.Exec("delete from `user` where uid = ?", 3)
if err != nil {
fmt.Printf("delete failed, err: %v\n", err)
return
}
n, err := ret.RowsAffected()
if err != nil {
fmt.Printf("get RowsAffected failed, err: %v\n", err)
return
}
fmt.Printf("delete success, affected rows: %d\n", n)
}
- MySQL预处理
1. 什么是预处理。
要了解预处理,需要首先了解普通SQL语句的执行过程:
- 客户端对SQL语句进行占位符替换,得到完整的SQL语句;
- 客户端发送完整的SQL语句到MySQL服务器端;
- MySQL服务器端执行完整的SQL语句,并将结果返给客户端。
2. 预处理执行过程。
- 把SQL语句分成两部分——命令部分与数据部分:
- 把命令部分发送给MySQL服务器端,MySQL服务器端进行SQL预处理;
- 把数据部分发送给MySQL服务器端,MySQL服务器端对SQL语句进行占位符替换;
- MySQL服务器端执行完整的SQL语句,并将结果返回给客户端。
3. 为什么要预处理。
预处理用于优化小ySQL服务器重复执行SQL语句的问题,可以提升服务器性能。提前让服务器编译,一次编译多次执行,可以节省后续编译的成本,避免SQL注入问题。
4. Go语言中的MySQL预处理。
在Go语言中,Prepare()方法会将SQL语句发送给MySQL服务器端,返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。Prepare()方法的定义如下:
func (db *DB)Prepare(query string)(*Stmt,error)
用Prepare()方法进行预处理查询的示例代码如下。
//预处理查询示例
func prepareQuery() {
stmt, err := DB.Prepare("select uid, name, phone from `user` where uid > ?")
if err != nil {
fmt.Printf("prepare failed, err: %v\n", err)
return
}
defer stmt.Close()
rows, err := stmt.Query(0)
if err != nil {
fmt.Printf("query failed, err: %v\n", err)
return
}
defer rows.Close()
var u User
for rows.Next() {
err := rows.Scan(&u.Uid, &u.Name, &u.Phone)
if err != nil {
fmt.Printf("scan failed, err: %v\n", err)
return
}
fmt.Printf("uid:%d name:%s phone:%s\n", u.Uid, u.Name, u.Phone)
}
}
插入、更新和删除操作的预处理语句十分类似,这里以插入操作的预处理为例:
//预处理插入示例
func prepareInsert() {
stmt, err := DB.Prepare("insert into `user`(name, phone) values (?, ?)")
if err != nil {
fmt.Printf("prepare failed, err: %v\n", err)
return
}
defer stmt.Close()
_, err = stmt.Exec("barry", 12345678900)
if err != nil {
fmt.Printf("insert failed, err: %v\n", err)
return
}
_, err = stmt.Exec("jim", 23451234567)
if err != nil {
fmt.Printf("insert failed, err: %v\n", err)
return
}
fmt.Println("insert success.")
}
- Go实现MySQL事务
事务相关方法
Go语言使用以下3个方法实现MySOL中的事务操作。
- Begin()方法用于开始事务,定义如下:
func (db *DB)Begin()(*Tx,error)
Commit()方法用于提交事务,定义如下:
func (tx *Tx)Commit()error
Rollback()方法用于回滚事务,定义如下:
func (tx *Tx)Rollback()error
下面的代码演示了一个简单的事务操作,该事务操作能够确保两次更新操作要么同时成功要么同时失败,不会存在中间状态:
//事务操作示例
func transaction() {
tx, err := DB.Begin() //开启事务
if err != nil {
if tx != nil {
tx.Rollback() //回滚
}
fmt.Printf("begin trans failed, err: %v\n", err)
}
_, err = tx.Exec("update user set name = 'james' where uid = ?", 1)
if err != nil {
tx.Rollback() //回滚
fmt.Printf("exec sql1 failed, err: %v\n", err)
return
}
_, err = tx.Exec("update user set name = 'james' where uid = ?", 3)
if err != nil {
tx.Rollback() //回滚
fmt.Printf("exec sql2 failed, err: %v\n", err)
return
}
tx.Commit() //提交事务
fmt.Println("exec transaction success!")
}
- SQL注入与防御
SQL注入是一种攻击手段,通过执行恶意SQL语句,进而将任意SQL代码插入数据库查询中,从而使攻击者完全控制Web应用程序后台的数据库服务器。
攻击者可以使用SQL注入漏洞绕过应用程序验证,比如绕过登录验证登绿、Wb身份验证和授权页面;也可以绕过网页,直接检索数据库的所有内容;还可以恶意修改、删除和增加数据库内容。
//SQL注入示例
func sqlInject(name string) {
sqlStr := fmt.Sprintf("select uid, name, phone from user where name = '%s'", name)
fmt.Printf("SQL:%s\n", sqlStr)
ret, err := DB.Exec(sqlStr)
if err != nil {
fmt.Printf("update failed, err: %v\n", err)
return
}
n, err := ret.RowsAffected()
if err != nil {
fmt.Printf("get RowsAffected failed, err: %v\n", err)
return
}
fmt.Printf("get success, affected rows: %d\n", n)
}
//此时用sqlInject()方法输入字符串可以引发SQL注入问题
sqlInject("xxx' or 1=1#")
sqlInject("xxx' union select * from user #")
sqlInject("xxx' and (select count(*) from user) < 10 #")
针对SQL注入问题,常见的防御措施有:
- 禁止将变量直接写入SQL语句。
- 对用户进行分级管理,严格控制用户的权限。
- 对用户输入进行检查,确保数据输入的安全性。在具体检查输入或提交的变量时,对单引号、双引号、冒号等字符进行转换或者过滤。
- 对数据库信息进行加密。