Golang操作MySQL
数据库
很多时候我们都是把数据存储在文件中，例如：xxx.txt，读取都需要对文件进行操作，那如果数据特别多，我们就得创建N个文件，并且非常不好管理，性能也差，所以就出来了数据库这个概念，数据库本生也是一套软件系统，它存储数据的本质还是文件，有了数据库这套系统，我们只需要学会怎么去使用这套系统即可
常见的数据库：SQLlite、MySQL、Mongodb、Oracle
MySQL简介
MySQL是一个开源的关系型数据库，它拥有很强大的SQL语句，我们通过SQL语句实现数据的增删改查。
SQL语句
DDL：操作数据库
DML：表的增删改查
DCL：用户及权限
存储引擎
MySQL支持插件式的存储引擎，常见的有：MyISAM和InnoDB。
MyISAM:
- 查询速度快
- 只支持表锁
- 不支持事务
InnoDB： - 整体速度快
- 支持表锁和行锁
- 支持事务（多个SQL操作当成一个整体）
Go操作MySQL
Go语言中的database/sql包提供了保证SQL或类SQL数据库的范接口，并不提供具体的数据库驱动。使用database/sql包时必须注入（至少）一个数据库驱动，原生支持连接池，是并发安全的。
下载驱动
go get -u github.com/go-sql-driver/mysql
使用MySQL驱动
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql" // init()
)
var db *sql.DB // 是一个连接池对象
func initDB()(err error){
// 数据库信息
dsn := "root:123456@tcp(10.4.7.51:3306)/test_sql"
// 连接数据库
db, err = sql.Open("mysql",dsn) // 不会校验用户名和密码是否正确
if err != nil { // dsn 格式不正确的时候会报错
return
}
err = db.Ping() // 尝试连接数据库
if err != nil {
return
}
return
}
// Go连接MySQL示例
func main(){
err := initDB()
if err !=nil {
fmt.Printf("init DB failed,err: %v\n",err)
return
}
fmt.Println("连接数据库成功")
}
设置数据库最大连接数
db.SetMaxOpenConns(100)
设置数据库最大闲置连接数
db.SetMaxIdleConns(10)
CRUD
建库建表
我们先在MySQL中创建一个名为test_sql的数据库
CREATE DATABASE test_sql
进入数据库
user test_sql
执行以下命令创建用于测试的表结构
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=1 DEFAULT CHARSET=utf8mb4;
插入多条测试数据
insert into user(name,age) values("Jack",9000);
insert into user(name,age) values("Xander",31);
insert into user(name,age) values("Eric",25);
insert into user(name,age) values("Alim",21);
insert into user(name,age) values("小明",22);
insert into user(name,age) values("大力哥",30);
查询
单行查询
单行查询db.QueryRow()执行一次查询，并最多返回去一行结果(ROW)。QueryRow总是返回非nil的值，直到返回值的Scan方法被调用时，才会返回去被延迟的错误(如：未找到结果)
func queryOnceDemo(id int){
var u1 user
// 写查询单条记录的SQL语句
sqlStr := `select id,name,age from user where id=?`
// 执行
db.QueryRow(sqlStr,id).Scan(&u1.id,&u1.name,&u1.age) // 从连接池里拿一个连接出来去数据库查询单条记录
// 打印结构
fmt.Printf("u1:%#v\n",u1)
}
多行查询
多行查询db.Query()执行一次查询，返回多行结果(即Rows),一般用于运行select命令。
func queryMultDemo(n int){
// sql语句
sqlStr := `select id,name,age from user where id > ?;`
// 执行
rows, err := db.Query(sqlStr,n)
if err != nil{
fmt.Sprintf("exec %s query failled,err:%v\n",sqlStr,err)
return
}
// 一定要关闭rows
defer rows.Close()
// 循环取值
for rows.Next() {
var u1 user
err := rows.Scan(&u1.id,&u1.name,&u1.age)
if err != nil {
fmt.Printf("scan failed,err:%v\n",err)
}
fmt.Printf("u1:%#v\n",u1)
}
}
插入数据
Exec执行一次命令(包括查询、删除、更新、插入等)，返回的Result是对已执行的SQL命令的总结
func insertRowDemo(){
// 写SQL语句
sqlStr := `insert into user(name,age) values("老王",28)`
// exec
ret, err := db.Exec(sqlStr)
if err != nil {
fmt.Printf("insert failed,err:%v\n",err)
return
}
// 如果是插入数据的操作，能够拿到插入数据的id
id, err := ret.LastInsertId()
if err != nil {
fmt.Printf("get last id failed,err:$v",err)
return
}
fmt.Println("id:",id)
}
更新数据
func updateRowDemo(newAge,id int){
sqlStr := `update user set age=? where id =?`
ret, err := db.Exec(sqlStr,newAge,id)
if err != nil {
fmt.Printf("update failed,err:%v\n",err)
return
}
n, err := ret.RowsAffected()
if err != nil {
fmt.Printf("get last id failed,err:$v",err)
return
}
fmt.Printf("update row :%v\n",n)
}
删除数据
func deleteRowDemo(id int){
sqlStr := `delete from user where id=?`
ret, err := db.Exec(sqlStr,id)
if err != nil {
fmt.Printf("delete failed,err:%v\n",err)
return
}
n, err := ret.RowsAffected()
if err != nil {
fmt.Printf("get last id failed,err:$v",err)
return
}
fmt.Printf("delete row :%v\n",n)
}
MySQL预处理
优化MySQL服务端重复执行SQL的方法，可以提升服务器性能，提前让服务端编译，一次编译多次执行，节省后续编译的成本，也能避免SQL注入的问题。
Go中的Prepare方法会先将sql语句发送到MySQL服务端，返回一个准备好的状态用于之后的查询和命令。返回值可以同时执行多个查询和命令。
查询操作的预处理示例代码如下：
// 预处理查询示例
func prepareQueryDemo() {
sqlStr := "select id, name, age from user where id > ?"
stmt, err := db.Prepare(sqlStr)
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()
// 循环读取结果集中的数据
for rows.Next() {
var u user
err := rows.Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.id, u.name, u.age)
}
}
插入、更新和删除操作的预处理十分类似，这里以插入操作的预处理为例：
// 预处理插入示例
func prepareInsertDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
stmt, err := db.Prepare(sqlStr)
if err != nil {
fmt.Printf("prepare failed, err:%v\n", err)
return
}
defer stmt.Close()
_, err = stmt.Exec("小王子", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
_, err = stmt.Exec("沙河娜扎", 18)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
fmt.Println("insert success.")
}
MySQL事务操作
什么是事务？
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)，同时这个完整的业务需要执行多次的DML(insert、update、delete)语句共同联合完成。A转账给B，这里面就需要执行两次update操作。
在MySQL中只有使用了Innodb数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行。
事务相关方法
Go语言中使用以下三个方法实现MySQL中的事务操作。 开始事务
func (db *DB) Begin() (*Tx, error)
提交事务
func (tx *Tx) Commit() error
回滚事务
func (tx *Tx) Rollback() error
事务示例
下面的代码演示了一个简单的事务操作，该事物操作能够确保两次更新操作要么同时成功要么同时失败，不会存在中间状态。
// 事务操作示例
func transactionDemo() {
tx, err := db.Begin() // 开启事务
if err != nil {
if tx != nil {
tx.Rollback() // 回滚
}
fmt.Printf("begin trans failed, err:%v\n", err)
return
}
sqlStr1 := "Update user set age=30 where id=?"
_, err = tx.Exec(sqlStr1, 2)
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec sql1 failed, err:%v\n", err)
return
}
sqlStr2 := "Update user set age=40 where id=?"
_, err = tx.Exec(sqlStr2, 4)
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec sql2 failed, err:%v\n", err)
return
}
err = tx.Commit() // 提交事务
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("commit failed, err:%v\n", err)
return
}
fmt.Println("exec trans success!")
}
sqlx使用
第三方库sqlx能够简化操作，提高开发效率。
安装
go get github.com/jmoiron/sqlx
连接数据库
var db *sqlx.DB
func initDB() (err error) {
dsn := "user:password@tcp(127.0.0.1:3306)/test"
// 也可以使用MustConnect连接不成功就panic
db, err = sqlx.Connect("mysql", dsn)
if err != nil {
fmt.Printf("connect DB failed, err:%v\n", err)
return
}
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(10)
return
}
查询
查询单行数据示例代码如下：
// 查询单条数据示例
func queryRowDemo() {
sqlStr := "select id, name, age from user where id=?"
var u user
err := db.Get(&u, sqlStr, 1)
if err != nil {
fmt.Printf("get failed, err:%v\n", err)
return
}
fmt.Printf("id:%d name:%s age:%d\n", u.ID, u.Name, u.Age)
}
查询多行数据示例代码如下：
// 查询多条数据示例
func queryMultiRowDemo() {
sqlStr := "select id, name, age from user where id > ?"
var users []user
err := db.Select(&users, sqlStr, 0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
fmt.Printf("users:%#v\n", users)
}
插入、更新和删除
sqlx中的exec方法与原生sql中的exec使用基本一致：
// 插入数据
func insertRowDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db.Exec(sqlStr, "沙河小王子", 19)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
theID, err := ret.LastInsertId() // 新插入数据的id
if err != nil {
fmt.Printf("get lastinsert ID failed, err:%v\n", err)
return
}
fmt.Printf("insert success, the id is %d.\n", theID)
}
// 更新数据
func updateRowDemo() {
sqlStr := "update user set age=? where id = ?"
ret, err := db.Exec(sqlStr, 39, 6)
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)
}
// 删除数据
func deleteRowDemo() {
sqlStr := "delete from user where id = ?"
ret, err := db.Exec(sqlStr, 6)
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)
}
事务操作
对于事务操作，我们可以使用sqlx中提供的db.Beginx()和tx.MustExec()方法来简化错误处理过程。示例代码如下：
func transactionDemo() {
tx, err := db.Beginx() // 开启事务
if err != nil {
if tx != nil {
tx.Rollback()
}
fmt.Printf("begin trans failed, err:%v\n", err)
return
}
sqlStr1 := "Update user set age=40 where id=?"
tx.MustExec(sqlStr1, 2)
sqlStr2 := "Update user set age=50 where id=?"
tx.MustExec(sqlStr2, 4)
err = tx.Commit() // 提交事务
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("commit failed, err:%v\n", err)
return
}
fmt.Println("exec trans success!")
}
