第五章、Go操作MySQL数据库
使用MySQL数据库
目前Internet上流行的网站构架方式是LAMP,其中的 M 即 MySQL, 作为数据库,MySQL以免费、开源、使用方便为优势成为了很多Web开发的后端数据库存储引擎。
1、MySQL驱动
Go中支持MySQL的驱动目前比较多,有如下几种,有些是支持database/sql标准,而有些是采用了自己的实现接口,常用的有如下几种:
https://github.com/go-sql-driver/mysql 支持database/sql,全部采用go写。
https://github.com/ziutek/mymysql 支持database/sql,也支持自定义的接口,全部采用go写。
https://github.com/philio/gomysql 不支持database/sql,自定义接口,全部采用go写。
接下来我们主要以第一个驱动为例,也推荐大家采用它,主要理由:
- 这个驱动比较新,维护的比较好
- 完全支持database/sql接口
- 支持keepalive,保持长连接,虽然 mymysql 也支持 keepalive,但不是线程安全的,这个从底层就支持了keepalive。
mac下下载mysql的驱动并安装(windows系统需要先安装git):
执行下面两个命令:
下载:go get github.com/go-sql-driver/mysql
安装:go install github.com/go-sql-driver/mysql
windows安装完成以后的文件截图:
接下来我们都将采用同一个数据库表结构:数据库mytest,用户表userinfo,关联用户信息表userdetail。
CREATE TABLE `userinfo` (
`uid` INT(10) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(64) NULL DEFAULT NULL,
`departname` VARCHAR(64) NULL DEFAULT NULL,
`created` DATE NULL DEFAULT NULL,
PRIMARY KEY (`uid`)
)
CREATE TABLE `userdetail` (
`uid` INT(10) NOT NULL DEFAULT '0',
`intro` TEXT NULL,
`profile` TEXT NULL,
PRIMARY KEY (`uid`)
)
mac下打开终端的命令:
/usr/local/mysql/bin/mysql -u root -p
然后输入密码。
windows以管理员身份运行命令提示符,开启数据库服务:
net start mysql mysql -u root -p 然后输入密码
2、连接数据库
要想使用go语言操作mysql,首先需要和mysql数据库建立连接,获取到DB对象。
2.1 导入包:
首先导入包:
import (
"database/sql"
_"github.com/Go-SQL-Driver/MySQL"
)
- database/sql,是golang的标准库之一,它提供了一系列接口方法,用于访问关系数据库。它并不会提供数据库特有的方法,那些特有的方法交给数据库驱动去实现。
- 我们正在加载的驱动是匿名的,将其限定符别名为_,因此我们的代码中没有一个到处的名称可见。
当导入了一个数据库驱动后, 此驱动会自行初始化并注册自己到Golang的database/sql上下文中, 因此我们就可以通过 database/sql 包提供的方法访问数据库了。
2.2 建立连接
使用Open函数:
Open函数:
func Open(driverName, dataSourceName string) (*DB, error)
其中的两个参数:
drvierName,这个名字其实就是数据库驱动注册到 database/sql 时所使用的名字.
"mysql"
dataSourceName,数据库连接信息,这个连接包含了数据库的用户名, 密码, 数据库主机以及需要连接的数据库名等信息.
用户名:密码@协议(地址:端口)/数据库?参数=参数值
db, err := sql.Open("mysql", "用户名:密码@tcp(IP:端口)/数据库?charset=utf8")
例如:
db, err := sql.Open("mysql", "root:111111@tcp(127.0.0.1:3306)/test?charset=utf8")
说明:
- sql.Open并不会立即建立一个数据库的网络连接, 也不会对数据库链接参数的合法性做检验, 它仅仅是初始化一个sql.DB对象. 当真正进行第一次数据库查询操作时, 此时才会真正建立网络连接;
- sql.DB表示操作数据库的抽象接口的对象,但不是所谓的数据库连接对象,sql.DB对象只有当需要使用时才会创建连接,如果想立即验证连接,需要用Ping()方法;
- sql.Open返回的sql.DB对象是协程并发安全的.
- sql.DB的设计就是用来作为长连接使用的。不要频繁Open, Close。比较好的做法是,为每个不同的datastore建一个DB对象,保持这些对象Open。如果需要短连接,那么把DB作为参数传入function,而不要在function中Open, Close。
新建go文件(demo02_mysql_open.go),示例代码:
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql" // 这一行必须有
)
func main() {
/*
连接数据库:func Open(driverName, dataSourceName string) (*DB, error)
Open打开一个dirverName指定的数据库,dataSourceName指定数据源,
一般包至少括数据库文件名和(可能的)连接信息。
driverName: 使用的驱动名. 这个名字其实就是数据库驱动注册到 database/sql 时所使用的名字.
dataSourceName: 数据库连接信息,这个连接包含了数据库的用户名, 密码, 数据库主机以及需要连接的数据库名等信息.
drvierName,"mysql"
dataSourceName,用户名:密码@协议(地址:端口)/数据库?参数=参数值
*/
//"root:hanru1314@tcp(127.0.0.1:3306)/ruby?charset=utf8"
db, err := sql.Open("mysql", ""root:root@tcp:(127.0.0.1:3306)/mytest?charset=utf8")
fmt.Println(db)
fmt.Println(err)
if err != nil {
fmt.Println("连接有误")
return
}
fmt.Println("连接成功")
db.Close()
}
运行结果:
3、DML操作:增删改
3.1 操作增删改
有两种方法: 1.直接使用Exec函数添加
func (db *DB) Exec(query string, args ...interface{}) (Result, error)
示例代码:
result, err := db.Exec("UPDATE userinfo SET username = ?, departname = ? WHERE uid = ?", "王二狗","行政部",2)
2.首先使用Prepare获得stmt,然后调用Exec添加。
建立连接后,通过操作DB对象的 Prepare() 方法,可以进行
func (db *DB) Prepare(query string) (*Stmt, error) {
return db.PrepareContext(context.Background(), query)
}
示例代码:
stmt,err:=db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)")
//补充完整sql语句,并执行
result,err:=stmt.Exec("韩茹","技术部","2018-11-21")
预编译语句(Prepared Statement) 预编译语句(PreparedStatement)提供了诸多好处, 因此我们在开发中尽量使用它. 下面列出了使用预编译语句所提供的功能:
- PreparedStatement 可以实现自定义参数的查询
- PreparedStatement 通常来说, 比手动拼接字符串 SQL 语句高效.
- PreparedStatement 可以防止SQL注入攻击
3.2 处理结果:影响数据库的行数
获取影响数据库的行数,可以根据该数值判断是否插入或删除或修改成功。
count, err := result.RowsAffected()
获得刚刚添加数据的自增ID
id, err := result.LastInsertId()
4、DQL操作:查询
4.1 查询一条
查询单条数据,QueryRow 函数
func (db *DB) QueryRow(query string, args ...interface{}) *Row
示例代码:
var username, departname, created string
err := db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3)
err := row.Scan(&uid, &username, &departname, &created)
//也可以简写:
err := db.QueryRow("SELECT username,departname,created FROM userinfo WHERE uid=?", 3).Scan(&uid, &username, &departname, &created)
扫描并复制当前行中每一列的值,但是要求行必须与行中的列数相同。
func (rs *Rows) Scan(dest ...interface{}) error
- rows.Scan 参数的顺序很重要, 需要和查询的结果的 column 对应. 例如 “SELECT * From user where age >=20 AND age < 30” 查询的行的 column 顺序是 “id, name, age” 和插入操作顺序相同, 因此 rows.Scan 也需要按照此顺序 rows.Scan(&id, &name, &age), 不然会造成数据读取的错位.
- 因为golang是强类型语言,所以查询数据时先定义数据类型,但是查询数据库中的数据存在三种可能:存在值,存在零值,未赋值NULL 三种状态, 因为可以将待查询的数据类型定义为sql.Nullxxx类型,可以通过判断Valid值来判断查询到的值是否为赋值状态还是未赋值NULL状态.
4.2 查询多条数据,并遍历
Query 获取数据,for xxx.Next() 遍历数据:
首先使用 Query() 方法进行查询,如果查询无误,返回Rows,就是所有行的信息,类似结果集。
func (db *DB) Query(query string, args ...interface{}) (*Rows, error)
我们可以通过 Next() 方法判断是否存在下一条数据,如果有,可以使用之前的 Scan() 方法读取一行,然后继续判断,继续获取。这个过程的本质就是迭代,所以通常要配合循环使用。
func (rs *Rows) Next() bool
每次 db.Query 操作后,都建议调用 rows.Close()。因为 db.Query() 会从数据库连接池中获取一个连接, 这个底层连接在结果集(rows)未关闭前会被标记为处于繁忙状态。当遍历读到最后一条记录时,会发生一个内部EOF错误,自动调用 rows.Close(),但如果提前退出循环,rows 不会关闭,连接不会回到连接池中,连接也不会关闭, 则此连接会一直被占用。因此通常我们使用 defer rows.Close() 来确保数据库连接可以正确放回到连接池中;不过阅读源码发现 rows.Close() 操作是幂等操作,即一个幂等操作的特点是其任意多次执行所产生的影响均与一次执行的影响相同,所以即便对已关闭的rows再执行close()也没关系。
5、示例代码:
5.1 插入数据
新建go文件(demo03_mysql_insert.go),示例代码:
package main
// step1:导入包
import (
"database/sql"
_"github.com/go-sql-driver/mysql"
"fmt"
)
func main() {
// step2:打开数据库,相当于和数据库建立连接:db对象
/*
func Open(driverName, dataSourceName string) (*DB, error)
drvierName,"mysql"
dataSourceName,用户名:密码@协议(地址:端口)/数据库?参数=参数值
*/
db, err := sql.Open("mysql","root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
if err !=nil{
fmt.Println("连接失败")
return
}
//step3:插入一条数据
stmt, err := db.Prepare("INSERT INTO userinfo(username,departname,created) values(?,?,?)")
if err !=nil{
fmt.Println("操作失败")
}
//补充完整sql语句,并执行
result, err := stmt.Exec("韩茹","技术部","2018-11-21")
if err !=nil{
fmt.Println("插入数据失败")
}
//step4:处理sql操作后的结果
lastInsertId, err := result.LastInsertId() // (补充:这里的err没有用到,可以用 _ 代替)
rowsAffected, err := result.RowsAffected() // ~
fmt.Println("lastInsertId: ", lastInsertId)
fmt.Println("影响的行数:", rowsAffected)
//再次插入数据:
result, _ = stmt.Exec("ruby","人事部","2018-11-11")
count, _ := result.RowsAffected()
fmt.Println("影响的行数:",count)
//step5:关闭资源
stmt.Close()
db.Close()
}
运行结果:
数据库显示:
5.2 更新数据
我们可以按照插入数据的方式,使用Prepare()方法,然后再Exec()。也可以直接执行Exec()。
我们修改uid为2的这条数据,将username由原来的ruby改为王二狗,department由原来的人事部改为行政部。
新建go文件(demo04_mysql_update.go),示例代码:
package main
// step1:导入包
import (
"database/sql"
_"github.com/go-sql-driver/mysql"
"fmt"
)
func main() {
// step2:打开数据库,相当于和数据库建立连接:db对象
/*
func Open(driverName, dataSourceName string) (*DB, error)
drvierName,"mysql"
dataSourceName,用户名:密码@协议(地址:端口)/数据库?参数=参数值
*/
db, err := sql.Open("mysql","root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
if err != nil{
fmt.Println("连接失败。。")
return
}
//step3:修改一条数据,我们直接使用Exec()方法。
//更新数据
result, err := db.Exec("UPDATE userinfo SET username = ?, departname = ? WHERE uid = ?", "王二狗", "行政部", 2)
if err != nil{
fmt.Println("更新数据失败。。", err)
}
//step4:处理sql操作后的结果
lastInsertId, err := result.LastInsertId()
rowsAffected, err := result.RowsAffected()
fmt.Println("lastInsertId", lastInsertId)
fmt.Println("影响的行数:", rowsAffected)
//step5:关闭资源
db.Close()
}
运行结果:
刷新数据库后,观察数据:
5.3 查询一条
使用QueryRow查询一条数据
新建go文件(demo05_mysql_query_one.go),示例代码:
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
func main() {
/*
查询一条
*/
db, _ := sql.Open("mysql", "root:hanru1314@tcp(127.0.0.1:3306)/mytest?charset=utf8")
row := db.QueryRow("SELECT uid,username,departname,created FROM userinfo WHERE uid=?", 1)
var uid int
var username, departname, created string
/*
row:Scan()-->将查询的结果从row取出
err对象
判断err是否为空,
为空,查询有结果,数据可以成功取出
不为空,没有数据,sql: no rows in result set
*/
err := row.Scan(&uid, &username, &departname, &created)
//fmt.Println(err)
if err != nil {
fmt.Println("查无数据。。")
} else {
fmt.Println(uid, username, departname, created)
}
db.Close()
}
运行结果:
5.4 查询多条
使用Query 获取数据,for xxx.Next() 遍历数据。
新建go文件(demo06_mysql_query.go),示例代码:
package main
//step1:导入包
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
type User struct {
uid int
username string
departname string
created string
}
func main() {
/*
查询操作:
*/
//step2:打开数据库,建立连接
db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
//stpt3:查询数据库
rows, err := db.Query("SELECT uid,username,departname,created FROM userinfo")
if err != nil {
fmt.Println("查询有误。。")
return
}
//fmt.Println(rows.Columns()) //[uid username departname created]
//创建slice,存入struct,
datas := make([]User, 0)
//step4:操作结果集获取数据
for rows.Next() {
var uid int
var username string
var departname string
var created string
if err := rows.Scan(&uid, &username, &departname, &created); err != nil {
fmt.Println("获取失败。。")
}
//每读取一行,创建一个user对象,存入datas2中
user := User{uid, username, departname, created}
datas = append(datas, user)
}
//step5:关闭资源
rows.Close()
db.Close()
for _, v := range datas {
fmt.Println(v)
}
}
/*
查询:处理查询后的结果:
思路一:创建结构体
思路二:将数据,存入到map中
*/
运行结果:
也可以操作map来存储查询的结果,这样就不用创建对应的结构体了。
新建go文件(demo07_mysql_query.go),示例代码:
package main
//step1:导入包
import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
func main() {
/*
查询操作:
*/
//step2:打开数据库,建立连接
db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
//stpt3:查询数据库
rows, err := db.Query("SELECT uid,username,departname,created FROM userinfo")
if err != nil {
fmt.Println("查询有误。。")
return
}
//fmt.Println(rows.Columns()) //[uid username departname created]
//定义一个map,用于存储从数据库中查询出来的数据,字段作为key,string,数据作为value,任意类型,空接口
datas := make([]map[string]interface{}, 0)
//step4:操作结果集获取数据
for rows.Next() {
var uid int
var username string
var departname string
var created string
if err := rows.Scan(&uid, &username, &departname, &created); err != nil {
fmt.Println("获取失败。。")
}
map1 := make(map[string]interface{})
//将读取到的数据,存入了map中
map1["uid"] = uid
map1["username"] = username
map1["departname"] = departname
map1["created"] = created
//将map存入切片中
datas = append(datas, map1)
}
//step5:关闭资源
rows.Close()
db.Close()
for _, v := range datas {
fmt.Println(v)
}
}
/*
查询:处理查询后的结果:
思路一:将数据,存入到map中
思路二:创建结构体:
*/
运行结果:
5.5 事务
事务操作是通过三个方法实现:
- Begin():开启事务
- Commit():提交事务(执行sql)
- Rollback():回滚
再创建一个表,并插入两条数据:
CREATE TABLE `account` (
`id` int(4) NOT NULL,
`name` varchar(30) DEFAULT NULL,
`money` float(8, 2) DEFAULT NULL,
PRIMARY KEY (`id`)
)
INSERT INTO `account` VALUES ('1', 'ruby', '3000.00');
INSERT INTO `account` VALUES ('2', '王二狗', '1000.00');
现在我们想让ruby转账给王二狗2000元。
如果转账成功,修改ruby和王二狗的金额。否则ruby和王二狗的金额保持不变。
新建go文件(demo08_mysql_transaction.go),示例代码:
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
func main() {
/*
事务:
4大特性:ACID
原子性:
一致性:
隔离性:
永久性:
*/
//ruby-->王二狗,2000元
db, _ := sql.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
//开启事务
tx, _ := db.Begin()
//提供一组sql操作
var aff1, aff2 int64 = 0, 0
result1, _ := tx.Exec("UPDATE account SET money=3000 WHERE id=?", 1)
result2, _ := tx.Exec("UPDATE account SET money=2000 WHERE id=?", 2)
//fmt.Println(result2)
if result1 != nil {
aff1, _ = result1.RowsAffected()
}
if result2 != nil {
aff2, _ = result2.RowsAffected();
}
fmt.Println(aff1)
fmt.Println(aff2)
if aff1 == 1 && aff2 == 1 {
//提交事务
tx.Commit()
fmt.Println("操作成功。。")
} else {
//回滚
tx.Rollback()
fmt.Println("操作失败。。。回滚。。")
}
}
运行结果:
刷新数据库:
由于事务是一个一直连接的状态,所以Tx对象必须绑定和控制单个连接。一个Tx会在整个生命周期中保存一个连接,然后在调用 commit() 或 Rollback() 的时候释放掉。在调用这几个函数的时候必须十分小心,否则连接会一直被占用直到被垃圾回收。
6、sqlx扩展包[扩展内容]
sqlx包是作为database/sql包的一个额外扩展包,在原有的database/sql加了很多扩展,如直接将查询的数据转为结构体,大大简化了代码书写,当然database/sql包中的方法同样起作用。
安装:
go get "github.com/jmoiron/sqlx"
连接数据库:
var Db *sqlx.DB
db, err := sqlx.Open("mysql","username:password@tcp(ip:port)/database?charset=utf8")
Db = db
处理类型(Handle Types)
sqlx 设计和 database/sql 使用方法是一样的。包含有4中主要的handle types:
- sqlx.DB - 和sql.DB相似,表示数据库。
- sqlx.Tx - 和sql.Tx相似,表示事物。
- sqlx.Stmt - 和sql.Stmt相似,表示prepared statement。
- sqlx.NamedStmt - 表示prepared statement(支持named parameters)
所有的handler types都提供了对database/sql的兼容,意味着当你调用sqlx.DB.Query时,可以直接替换为sql.DB.Query.这就使得sqlx可以很容易的加入到已有的数据库项目中。
此外,sqlx还有两个cursor类型:
- sqlx.Rows - 和sql.Rows类似,Queryx返回。
- sqlx.Row - 和sql.Row类似,QueryRowx返回。
相比database/sql方法还多了新语法,也就是实现将获取的数据直接转换结构体实现。
- Get(dest interface{}, …) error
- Select(dest interface{}, …) error
Get 和Select(非常常用)
Get和Select是一个非常省时的扩展,可直接将结果赋值给结构体,其内部封装了StructScan进行转化。Get用于获取单个结果然后Scan,Select用来获取结果切片。
新建go文件(demo09_kuozhan.go),示例代码:
package main
import (
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
"fmt"
)
var Db *sqlx.DB
type User struct {
Uid int
Username string
Departname string
Created string
}
func main() {
db, err := sqlx.Open("mysql", "root:root@tcp(127.0.0.1:3306)/mytest?charset=utf8")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = db
var users []User
err = Db.Select(&users, "SELECT uid,username,departname,created FROM userinfo")
if err != nil {
fmt.Println("Select error", err)
}
fmt.Printf("this is Select res:%v\n", users)
var user User
err1 := Db.Get(&user, "SELECT uid,username,departname,created FROM userinfo where uid = ?", 1)
if err1 != nil {
fmt.Println("GET error :", err1)
} else {
fmt.Printf("this is GET res:%v", user)
}
}
运行结果: