Golang Mysql与sqlx库
连接Mysql示例
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"time"
)
// 定义一个全局对象db
var db *sql.DB
// 定义一个初始化数据库的函数
func initDB() (err error) {
// DSN:Data Source Name
dsn := "root:root@tcp(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True"
// 不会校验账号密码是否正确
// 注意!!!这里不要使用:=,我们是给全局变量赋值,然后在main函数中使用全局变量db
db, err = sql.Open("mysql", dsn)
if err != nil {
return err
}
// 尝试与数据库建立连接(校验dsn是否正确)
err = db.Ping()
if err != nil {
return err
}
fmt.Println("数据库成功初始化")
db.SetConnMaxLifetime(time.Second*10)
db.SetMaxIdleConns(10)
db.SetMaxOpenConns(20)
return nil
}
func main() {
err := initDB() // 调用输出化数据库的函数
if err != nil {
fmt.Printf("init db failed,err:%v\n", err)
return
}
}
查询
type user struct {
id int
age int
name string
}
// 查询单条数据示例
func queryRowDemo() {
sqlStr := "select id, name, age from user where id=?"
var u user
// 非常重要:确保QueryRow之后调用Scan方法,否则持有的数据库链接不会被释放
err := db.QueryRow(sqlStr, 1).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 queryMultiRowDemo() {
sqlStr := "select id, name, age from user where id > ?"
rows, err := db.Query(sqlStr, 0)
if err != nil {
fmt.Printf("query failed, err:%v\n", err)
return
}
// 非常重要:关闭rows释放持有的数据库链接
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 insertRowDemo() {
sqlStr := "insert into user(name, age) values (?,?)"
ret, err := db.Exec(sqlStr, "Duoduo", 28)
if err != nil {
fmt.Printf("insert failed, err:%v\n", err)
return
}
var theID int64
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, 29, 2)
if err != nil {
fmt.Printf("update failed, err:%v\n", err)
return
}
var n int64
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, 2)
if err != nil {
fmt.Printf("delete failed, err:%v\n", err)
return
}
var n int64
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预处理
// 预处理查询示例
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.")
}
SQL注入问题
// sql注入示例
func sqlInjectDemo(name string) {
sqlStr := fmt.Sprintf("select id, name, age from user where name='%s'", name)
fmt.Printf("SQL:%s\n", sqlStr)
var u user
err := db.QueryRow(sqlStr).Scan(&u.id, &u.name, &u.age)
if err != nil {
fmt.Printf("exec failed, err:%v\n", err)
return
}
fmt.Printf("user:%#v\n", u)
}
sqlInjectDemo("xxx' or 1=1#")
sqlInjectDemo("xxx' union select * from user #")
sqlInjectDemo("xxx' and (select count(*) from user) <10 #")
事务
// 事务操作示例
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=?"
ret1, err := tx.Exec(sqlStr1, 2)
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec sql1 failed, err:%v\n", err)
return
}
affRow1, err := ret1.RowsAffected()
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
return
}
sqlStr2 := "Update user set age=40 where id=?"
ret2, err := tx.Exec(sqlStr2, 3)
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec sql2 failed, err:%v\n", err)
return
}
affRow2, err := ret2.RowsAffected()
if err != nil {
tx.Rollback() // 回滚
fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
return
}
// 当affRow1 == 1 && affRow2 == 1
fmt.Println(affRow1, affRow2)
if affRow1 == 1 && affRow2 == 1 {
fmt.Println("事务提交啦...")
tx.Commit() // 提交事务
} else {
tx.Rollback()
fmt.Println("事务回滚啦...")
}
fmt.Println("exec trans success!")
}
sqlx库使用
连接数据库
package main
import (
"fmt"
"github.com/jmoiron/sqlx"
_ "github.com/go-sql-driver/mysql"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "root:root@tcp(127.0.0.1:3306)/db1?charset=utf8mb4&parseTime=True"
// 也可以使用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 main() {
if err :=initDB()
err!= nil{
fmt.Printf("init DB failed, err:%v\n", err)
return
}
fmt.Println("init DB success...")
}
查询数据
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
// 查询单条数据示例
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)
}
插入、更新和删除
// 查询多条数据示例
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)
}
// 插入数据
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)
}
NamedExec
func insertUserDemo()(err error){
sqlStr := "INSERT INTO user (name,age) VALUES (:name,:age)"
_, err = db.NamedExec(sqlStr,
map[string]interface{}{
"name": "七米",
"age": 28,
})
return
}
NamedQuery
func namedQuery(){
sqlStr := "SELECT * FROM user WHERE name=:name"
// 使用map做命名查询
rows, err := db.NamedQuery(sqlStr, map[string]interface{}{"name": "七米"})
if err != nil {
fmt.Printf("db.NamedQuery failed, err:%v\n", err)
return
}
defer rows.Close()
for rows.Next(){
var u user
err := rows.StructScan(&u)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
continue
}
fmt.Printf("user:%#v\n", u)
}
u := user{
Name: "七米",
}
// 使用结构体命名查询,根据结构体字段的 db tag进行映射
rows, err = db.NamedQuery(sqlStr, u)
if err != nil {
fmt.Printf("db.NamedQuery failed, err:%v\n", err)
return
}
defer rows.Close()
for rows.Next(){
var u user
err := rows.StructScan(&u)
if err != nil {
fmt.Printf("scan failed, err:%v\n", err)
continue
}
fmt.Printf("user:%#v\n", u)
}
}
事务操作
func transactionDemo()(err error) {
tx, err := db.Beginx() // 开启事务
if err != nil {
fmt.Printf("begin trans failed, err:%v\n", err)
return err
}
defer func() {
if p := recover(); p != nil {
tx.Rollback()
panic(p) // re-throw panic after Rollback
} else if err != nil {
fmt.Println("rollback")
tx.Rollback() // err is non-nil; don't change it
} else {
err = tx.Commit() // err is nil; if Commit returns error update err
fmt.Println("commit")
}
}()
sqlStr1 := "Update user set age=20 where id=?"
rs, err := tx.Exec(sqlStr1, 1)
if err!= nil{
return err
}
n, err := rs.RowsAffected()
if err != nil {
return err
}
if n != 1 {
return errors.New("exec sqlStr1 failed")
}
sqlStr2 := "Update user set age=50 where i=?"
rs, err = tx.Exec(sqlStr2, 3)
if err!=nil{
return err
}
n, err = rs.RowsAffected()
if err != nil {
return err
}
if n != 1 {
return errors.New("exec sqlStr1 failed")
}
return err
}
sqlx.In
使用sqlx.In实现批量插入
func (u user) Value() (driver.Value, error) {
return []interface{}{u.Name, u.Age}, nil
}
// BatchInsertUsers2 使用sqlx.In帮我们拼接语句和参数, 注意传入的参数是[]interface{}
func BatchInsertUsers2(users []interface{}) error {
query, args, _ := sqlx.In(
"INSERT INTO user (name, age) VALUES (?), (?), (?)",
users..., // 如果arg实现了 driver.Valuer, sqlx.In 会通过调用 Value()来展开它
)
fmt.Println(query) // 查看生成的querystring
fmt.Println(args) // 查看生成的args
_, err := db.Exec(query, args...)
return err
}
u1 := user{Name: "xx", Age: 18}
u2 := user{Name: "xxx", Age: 28}
u3 := user{Name: "xxxx", Age: 38}
users := []interface{}{u1, u2, u3}
BatchInsertUsers2(users)
使用NamedExec实现批量插入
- 注意 :该功能需1.3.1版本以上,并且1.3.1版本目前还有点问题,sql语句最后不能有空格和
;
,详见issues/690。 - 使用
NamedExec
实现批量插入的代码如下:
// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers3(users []*user) error {
_, err := db.NamedExec("INSERT INTO user (name, age) VALUES (:name, :age)", users)
return err
}
sqlx.In的查询
// QueryByIDs 根据给定ID查询
func QueryByIDs(ids []int)(users []user, err error){
// 动态填充id
query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?)", ids)
if err != nil {
return
}
// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
query = db.Rebind(query)
err = db.Select(&users, query, args...)
return
}
users , err := QueryByIDs([]int{7,5,6, 1})
if err != nil {
fmt.Printf("QueryByIDs failed, err:%v\n", err)
return
}
for _, user := range users{
fmt.Printf("user:%#v\n", user)
}
- in查询和FIND_IN_SET函数
- 查询id在给定id集合的数据并维持给定id集合的顺序。
// QueryAndOrderByIDs 按照指定id查询并维护顺序
func QueryAndOrderByIDs(ids []int)(users []user, err error){
// 动态填充id
strIDs := make([]string, 0, len(ids))
for _, id := range ids {
strIDs = append(strIDs, fmt.Sprintf("%d", id))
}
query, args, err := sqlx.In("SELECT name, age FROM user WHERE id IN (?) ORDER BY FIND_IN_SET(id, ?)", ids, strings.Join(strIDs, ","))
if err != nil {
return
}
// sqlx.In 返回带 `?` bindvar的查询语句, 我们使用Rebind()重新绑定它
query = db.Rebind(query)
err = db.Select(&users, query, args...)
return
}
// 1. 用代码去做排序
// 2. 让MySQL排序
fmt.Println("----")
users ,err = QueryAndOrderByIDs([]int{7,5,6, 1})
if err != nil {
fmt.Printf("QueryByIDs failed, err:%v\n", err)
return
}
for _, user := range users{
fmt.Printf("user:%#v\n", user)
}