Go操作常见数据库
Go操作常见数据库
sqlx连接mysql
新建sqlx_test数据库
package main
import(
"github.com/jmoiron/sqlx"
_ "github.com/go-sql-driver/mysql"
"fmt"
)
var db *sqlx.DB
func initDB() (err error) {
dsn := "root:qq8830013@tcp(127.0.0.1:3306)/sqlx_test?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 failed,err:%v\n",err)
}
fmt.Println("init db success...")
}
sqlx基本使用
package main
import (
"errors"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
type user struct {
ID int `db:"id"`
Age int `db:"age"`
Name string `db:"name"`
}
func initDB() (err error) {
dsn := "root:qq8830013@tcp(127.0.0.1:3306)/sqlx_test?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 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 insertRowDemo() {
sqlStr := "insert into user(id,name, age) values (?,?,?)"
ret, err := db.Exec(sqlStr, 3, "沙河小王子", 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, 25, 2)
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, 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)
}
// 事务
func transactionDemo2() (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=35 where id=?"
rs, err = tx.Exec(sqlStr2, 2)
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
}
// BatchInsertUsers3 使用NamedExec实现批量插入
func BatchInsertUsers3(users []*user) error {
_, err := db.NamedExec("INSERT INTO user (id,name, age) VALUES (:id,:name, :age)", users)
return err
}
// 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
}
func main() {
if err := initDB(); err != nil {
fmt.Printf("init failed,err:%v\n", err)
}
fmt.Println("init db success...")
//queryRowDemo()//id:1 name:alex age:18
//queryMultiRowDemo()//users:[]main.user{main.user{ID:1, Age:18, Name:"alex"}, main.user{ID:2, Age:22, Name:"yuan"}}
// insertRowDemo()
//updateRowDemo()//update success, affected rows:1
//deleteRowDemo()//delete success, affected rows:1
//transactionDemo2()//commit
// u1 := user{ID: 3, Name: "七米", Age: 18}
// u2 := user{ID: 4, Name: "q1mi", Age: 28}
// u3 := user{ID: 5, Name: "小王子", Age: 38}
// users3 := []*user{&u1, &u2, &u3}
// err := BatchInsertUsers3(users3)
// if err != nil {
// fmt.Printf("BatchInsertUsers3 failed, err:%v\n", err)
// }
users, err := QueryByIDs([]int{4, 5})
if err != nil {
fmt.Println("QueryByIDs错误")
}
for _, elem := range users {
fmt.Println(elem) //{0 28 q1mi} {0 38 小王子}
}
}
使用go-redis库连接redis
package main
import (
"context"
"fmt"
"time"
"github.com/go-redis/redis/v8"
)
// 声明一个全局的rdb变量
var rdb *redis.Client
// 初始化连接
func initClient() (err error) {
rdb = redis.NewClient(&redis.Options{
Addr: "localhost:6379",
Password: "", // no password set
DB: 0, // use default DB
PoolSize: 100, // 连接池大小
})
// 需要使用context库
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
_, err = rdb.Ping(ctx).Result()
return err
}
func main() {
if err:=initClient();err!=nil{
fmt.Println("initClient failed...")
return
}
fmt.Println("connect redis success...")
defer rdb.Close()
}
Go-redis的基本使用
package main
import (
"context"
"fmt"
"github.com/go-redis/redis/v8"
"time"
)
// 声明一个全局的rdb变量
var rdb *redis.Client
// 初始化连接
func initClient() (err error) {
rdb = redis.NewClient(&redis.Options{
Addr: "localhost:6379",
Password: "", // no password set
DB: 0, // use default DB
PoolSize: 100, // 连接池大小
})
// 需要使用context库
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
_, err = rdb.Ping(ctx).Result()
return err
}
// doCommand go-redis基本使用示例
func doCommand() {
ctx, cancel := context.WithTimeout(context.Background(), 500*time.Millisecond)
defer cancel()
// 执行命令获取结果
val, err := rdb.Get(ctx, "key").Result()
fmt.Println(val, err) //redis: nil
// 先获取到命令对象
cmder := rdb.Get(ctx, "key")
fmt.Println(cmder.Val()) // 获取值
fmt.Println(cmder.Err()) // 获取错误
// 直接执行命令获取错误
err = rdb.Set(ctx, "key", 10, time.Hour).Err()
// 直接执行命令获取值
value := rdb.Get(ctx, "key").Val()
fmt.Println(value) //10
}
// zsetDemo 操作zset示例
func zsetDemo() {
// key
zsetKey := "language_rank"
// value
languages := []*redis.Z{
{Score: 90.0, Member: "Golang"},
{Score: 98.0, Member: "Java"},
{Score: 95.0, Member: "Python"},
{Score: 97.0, Member: "JavaScript"},
{Score: 99.0, Member: "C/C++"},
}
ctx, cancel := context.WithTimeout(context.Background(), 500*time.Millisecond)
defer cancel()
// ZADD
err := rdb.ZAdd(ctx, zsetKey, languages...).Err()
if err != nil {
fmt.Printf("zadd failed, err:%v\n", err)
return
}
fmt.Println("zadd success")
// 把Golang的分数加10
newScore, err := rdb.ZIncrBy(ctx, zsetKey, 10.0, "Golang").Result()
if err != nil {
fmt.Printf("zincrby failed, err:%v\n", err)
return
}
fmt.Printf("Golang's score is %f now.\n", newScore)
// 取分数最高的3个
ret := rdb.ZRevRangeWithScores(ctx, zsetKey, 0, 2).Val()
for _, z := range ret {
fmt.Println(z.Member, z.Score)
}
// 取95~100分的
op := &redis.ZRangeBy{
Min: "95",
Max: "100",
}
ret, err = rdb.ZRangeByScoreWithScores(ctx, zsetKey, op).Result()
if err != nil {
fmt.Printf("zrangebyscore failed, err:%v\n", err)
return
}
for _, z := range ret {
fmt.Println(z.Member, z.Score)
}
}
func main() {
if err := initClient(); err != nil {
fmt.Println("initClient failed...")
return
}
fmt.Println("connect redis success...")
defer rdb.Close()
// doCommand()
zsetDemo()
}
浙公网安备 33010602011771号