golang使用sqlx操作MySQL
package main //sqlx示例 import ( "errors" "fmt" _ "github.com/go-sql-driver/mysql" "github.com/jmoiron/sqlx" ) var DB *sqlx.DB type User struct { ID int `db:"id"` Name string `db:"name"` Age int `db:"age"` } func initDB() (err error) { dsn := "user:password@tcp(xx.xx.xx.xx:3306)/go_db" DB, err = sqlx.Connect("mysql", dsn) if err != nil { return } return nil } //单条查询操作 func queryDemo() { sqlStr := "select id,name,age from info where id = ?" user := User{} err := DB.Get(&user, sqlStr, 1) if err != nil { fmt.Printf("query failed,err:%v\n", err) return } fmt.Printf("user:%#v\n", user) fmt.Printf("user:%v\n", user) } //查询多条 func queryMultiDemo() { sqlStr := "select id,name,age from info where id > ?" var users []User err := DB.Select(&users, sqlStr, 0) if err != nil { fmt.Println("query multi failed,err:%v\n", err) return } fmt.Printf("users: %v\n", users) for _, user := range users { fmt.Printf("user:%v\n", user) } } // 插入数据 func insertRowDemo() { sqlStr := "insert into info(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 info set age=? where id = ?" ret, err := DB.Exec(sqlStr, 39, 1) 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 info where id = ?" ret, err := DB.Exec(sqlStr, 1) 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) } //事务操作 func transDemo() { tx, err := DB.Beginx() //开启事务 // tx.MustExec() //带Must的一般标识出错就直接panic if err != nil { if tx != nil { tx.Rollback() } fmt.Printf("begin trans failed,err:%v\n", err) return } sql1 := "update info set age=age-? where id=?" tx.MustExec(sql1, 2, 1) err = tx.Commit() if err != nil { tx.Rollback() fmt.Printf("commit failed,err:%v\n", err) } fmt.Println("数据更新成功") } //事务操作2 func transDemo2() (err error) { tx, err := DB.Beginx() if err != nil { fmt.Println("begin trans failed,err:", err) return } defer func() { if p := recover(); p != nil { tx.Rollback() panic(p) } else if err != nil { fmt.Println("rollback") tx.Rollback() } else { err = tx.Commit() fmt.Println("sql commit") } }() sqlStr1 := "update info set age=99 where id = ?" r, err := tx.Exec(sqlStr1, 1) if err != nil { fmt.Println("sqlstr1执行失败,err:", err) return } n, err := r.RowsAffected() if err != nil { return } if n != 1 { return errors.New("exec sqlStr1 failed") } return err } func main() { err := initDB() if err != nil { fmt.Printf("init DB failed,err:%v\n", err) return } //查询单条 // queryDemo() // queryMultiDemo() // insertRowDemo() // updateRowDemo() // transDemo() transDemo2() }
package main
//sqlx示例
import (
"errors"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var DB *sqlx.DB
type User struct {
ID int `db:"id"`
Name string `db:"name"`
Age int `db:"age"`
}
func initDB() (err error) {
dsn := "TestVenus:xxxxxxxxx@tcp(172.23.102.xx:3306)/go_db"
DB, err = sqlx.Connect("mysql", dsn)
if err != nil {
return
}
return nil
}
//单条查询操作
func queryDemo() {
sqlStr := "select id,name,age from info where id = ?"
user := User{}
err := DB.Get(&user, sqlStr, 1)
if err != nil {
fmt.Printf("query failed,err:%v\n", err)
return
}
fmt.Printf("user:%#v\n", user)
fmt.Printf("user:%v\n", user)
}
//查询多条
func queryMultiDemo() {
sqlStr := "select id,name,age from info where id > ?"
var users []User
err := DB.Select(&users, sqlStr, 0)
if err != nil {
fmt.Println("query multi failed,err:%v\n", err)
return
}
fmt.Printf("users: %v\n", users)
for _, user := range users {
fmt.Printf("user:%v\n", user)
}
}
// 插入数据
func insertRowDemo() {
sqlStr := "insert into info(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 info set age=? where id = ?"
ret, err := DB.Exec(sqlStr, 39, 1)
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 info where id = ?"
ret, err := DB.Exec(sqlStr, 1)
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)
}
//事务操作
func transDemo() {
tx, err := DB.Beginx() //开启事务
// tx.MustExec() //带Must的一般标识出错就直接panic
if err != nil {
if tx != nil {
tx.Rollback()
}
fmt.Printf("begin trans failed,err:%v\n", err)
return
}
sql1 := "update info set age=age-? where id=?"
tx.MustExec(sql1, 2, 1)
err = tx.Commit()
if err != nil {
tx.Rollback()
fmt.Printf("commit failed,err:%v\n", err)
}
fmt.Println("数据更新成功")
}
//事务操作2
func transDemo2() (err error) {
tx, err := DB.Beginx()
if err != nil {
fmt.Println("begin trans failed,err:", err)
return
}
defer func() {
if p := recover(); p != nil {
tx.Rollback()
panic(p)
} else if err != nil {
fmt.Println("rollback")
tx.Rollback()
} else {
err = tx.Commit()
fmt.Println("sql commit")
}
}()
sqlStr1 := "update info set age=99 where id = ?"
r, err := tx.Exec(sqlStr1, 1)
if err != nil {
fmt.Println("sqlstr1执行失败,err:", err)
return
}
n, err := r.RowsAffected()
if err != nil {
return
}
if n != 1 {
return errors.New("exec sqlStr1 failed")
}
return err
}
func main() {
err := initDB()
if err != nil {
fmt.Printf("init DB failed,err:%v\n", err)
return
}
//查询单条
// queryDemo()
// queryMultiDemo()
// insertRowDemo()
// updateRowDemo()
// transDemo()
transDemo2()
}
