go语言 mysql redis_Go语言入门（十） Mysql与Redis操作

Mysql与Redis操作

Mysql开发

安装mysql，创建test库

创建表

mysql> 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;

mysql> insert into user (name,age)values('jim',18)

SQL查询

单行查询: Db.QueryRole

多行查询: Db.Query

import (

"fmt"

_"github.com/go-sql-driver/mysql"

"database/sql"

)

type User struct {

Id int64 `db:"id"`

Name string `db:"name"`

Age int `db:"age"`

}

func connMysql() {

dns := "root:123456@tcp(localhost:3306)/test"

conn,err := sql.Open("mysql",dns)

if err != nil {

fmt.Printf("connect mysql err:%v\n",err)

return

}

err = conn.Ping()

if err != nil {

fmt.Printf("ping faild,err :%v\n",err)

}

fmt.Printf("connect mysql successfully!\n")

QueryRow(conn)

Query(conn)

defer conn.Close()

}

func QueryRow(Db *sql.DB) {

id := 1

//单行数据查询

row := Db.QueryRow("select id,name,age from user where id=?",id)

var user User

err := row.Scan(&user.Id,&user.Name,&user.Age)

if err == sql.ErrNoRows {

fmt.Printf("not found data by id:%v\n",id)

}

if err != nil {

fmt.Printf("scan faild,err: %v\n",err)

return

}

fmt.Printf("user:%#v\n",user)

}

func Query(Db *sql.DB) {

id := 0

//多行数据查询

rows,err := Db.Query("select id,name,age from user where id>?",id)

//一定要关闭结果集

defer func() {

if rows != nil {

rows.Close()

}

}()

//查询异常捕获

if err == sql.ErrNoRows {

fmt.Printf("not found data by id:%v\n",id)

}

if err != nil {

fmt.Printf("scan faild,err: %v\n",err)

return

}

//遍历所有数据

for rows.Next() {

var user User

err := rows.Scan(&user.Id,&user.Name,&user.Age)

if err == sql.ErrNoRows {

fmt.Printf("not found data by id:%v\n",id)

}

if err != nil {

fmt.Printf("scan faild,err: %v\n",err)

return

}

fmt.Printf("user:%#v\n",user)

}

}

func main() {

connMysql()

}

Mysql插入更新和删除

使用DB.Exec()

import (

"fmt"

_"github.com/go-sql-driver/mysql"

"database/sql"

)

type User struct {

Id int64 `db:"id"`

Name string `db:"name"`

Age int `db:"age"`

}

func Insert(DB *sql.DB) {

username := "alex"

age := 18

result,err := DB.Exec("insert into user(name,age) values(?,?)",username,age)

if err != nil {

fmt.Printf("sql exec insert faild:err:%v\n",err)

return

}

id,err := result.LastInsertId()

if err != nil {

fmt.Printf("last insert id faild,err:%v\n",err)

return

}

affectRows,err := result.RowsAffected()

if err != nil {

fmt.Printf("Rows affects faild,err:%v\n",err)

return

}

fmt.Printf("last insert id:%d, affect rows:%d\n",id,affectRows)

}

func Update(DB *sql.DB) {

username := "bbq"

age := 12

result,err := DB.Exec("update user set name=?,age=? where id=?",username,age,3)

if err != nil {

fmt.Printf("sql exec update faild:err:%v\n",err)

return

}

affectRows,err := result.RowsAffected()

if err != nil {

fmt.Printf("Rows affects faild,err:%v\n",err)

return

}

fmt.Printf("affect rows:%d\n",affectRows)

}

func Delete(DB *sql.DB) {

id := 5

result,err := DB.Exec("delete from user where id=?",id)

if err != nil {

fmt.Printf("sql exec delete faild:err:%v\n",err)

return

}

affectRows,err := result.RowsAffected()

if err != nil {

fmt.Printf("Rows affects faild,err:%v\n",err)

return

}

fmt.Printf("affect rows:%d\n",affectRows)

}

func connMysql() {

dns := "root:123456@tcp(localhost:3306)/test"

conn,err := sql.Open("mysql",dns)

if err != nil {

fmt.Printf("connect mysql err:%v\n",err)

return

}

err = conn.Ping()

if err != nil {

fmt.Printf("ping faild,err :%v\n",err)

}

fmt.Printf("connect mysql successfully!\n")

//QueryRow(conn)

//Query(conn)

//Insert(conn)

//Update(conn)

Delete(conn)

defer conn.Close()

}

mysql预处理

一般sql处理流程

客户端拼接好sql语句

客户端发送sql语句到mysql服务器

mysql服务器解析sql语句并执行，把输出结果返回给客户端

预处理流程

把sql拆分成两部分，命令部分和数据部分

首先把命令部分发送给mysql服务器，mysql进行sql预处理

然后把数据部分发送给mysql服务器，mysql进行占位符替换

mysql执行sql语句并返回结果给客户端

预处理的优势

同一条sql反复执行，性能会很高

避免sql注入问题

预处理实例

查询操作

Db.Prepare(sql string)(*sql.Stmt,error)

Stmt.Query()

func PrepareQuery(DB *sql.DB) {

//第一部分：发送命令和占位符

stmt,err := DB.Prepare("select id,name,age from user where id>?")

if err != nil {

fmt.Printf("prepare faild,error:%v\n",err)

return

}

//第二部分：发数据，并执行sql

id := 1

rows,err := stmt.Query(id)

//一定要关闭结果集

defer func() {

if rows != nil {

rows.Close()

}

if stmt != nil {

stmt.Close()

}

}()

//查询异常捕获

if err == sql.ErrNoRows {

fmt.Printf("not found data by id:%v\n",id)

}

if err != nil {

fmt.Printf("scan faild,err: %v\n",err)

return

}

//遍历所有数据

for rows.Next() {

var user User

err := rows.Scan(&user.Id,&user.Name,&user.Age)

if err == sql.ErrNoRows {

fmt.Printf("not found data by id:%v\n",id)

}

if err != nil {

fmt.Printf("scan faild,err: %v\n",err)

return

}

fmt.Printf("user:%#v\n",user)

}

}

更新操作(插入，更新，delete)

Db.Prepare(sql string)(*sql.Stmt,error)

Stmt.Exec()

func PrepareInsert(DB *sql.DB) {

//第一部分：发送命令和占位符

stmt,err := DB.Prepare("insert into user (name,age) values (?,?);")

if err != nil {

fmt.Printf("prepare faild,error:%v\n",err)

return

}

//第二部分：发数据，并执行sql

username := "zhangqiqi"

age := 29

result,err := stmt.Exec(username,age)

if err != nil {

fmt.Printf("sql exec insert faild:err:%v\n",err)

return

}

id,err := result.LastInsertId()

if err != nil {

fmt.Printf("last insert id faild,err:%v\n",err)

return

}

affectRows,err := result.RowsAffected()

if err != nil {

fmt.Printf("Rows affects faild,err:%v\n",err)

return

}

fmt.Printf("last insert id:%d, affect rows:%d\n",id,affectRows)

}

mysql事务实例

保证数据的一致性

mysql的事务操作

DB.Begin() 开启事务

DB.Commit() 提交事务

DB.Roback() 回滚事务

func Transaction(DB *sql.DB) {

tx,err := DB.Begin()

if err != nil {

fmt.Printf("begin faild,err:%v\n",err)

return

}

_,err = tx.Exec("insert into user (name,age)values (?,?)","jemmy",80)

if err != nil {

tx.Rollback()

return

}

_,err = tx.Exec("update user set name=?,age=? where id=6","jemmxiny",60)

if err != nil {

tx.Rollback()

return

}

err = tx.Commit()

if err != nil {

tx.Rollback() //数据异常就回滚

return

}

}

sqlx库的介绍和使用

sqlx的特点：

使用更简单

支持对数据库，mysql,postgresql,oracle,sqlit

sqlx的使用

查询：sqlx.DB.Get和sqlx.DB.Select

更新，插入和删除: sqlx.DB.Exex()

事务：sqlx.DB.Begin(),sqlx.DB.Commit(),sqlx.DB.Rollback

go get github.com/jmoiron/sqlx

使用实例

import (

"database/sql"

"fmt"

"github.com/jmoiron/sqlx"

_ "github.com/go-sql-driver/mysql"

)

type User struct {

Id int64 `db:"id"`

Name string `db:"name"`

Age int `db:"age"`

}

func connMysql() {

dns := "root:123456@tcp(localhost:3306)/test"

conn,err := sqlx.Connect("mysql",dns)

if err != nil {

fmt.Printf("connect mysql err:%v\n",err)

return

}

//超时测试

err = conn.Ping()

if err != nil {

fmt.Printf("ping faild,err :%v\n",err)

}

fmt.Printf("connect mysql successfully!\n")

//关闭连接

//QueryRow(conn)

//Query(conn)

Insert(conn)

defer conn.Close()

}

func QueryRow(Db *sqlx.DB) {

id := 100

//单行数据查询

var user User

err := Db.Get(&user,"select id,name,age from user where id=?",id)

//空行数据

if err == sql.ErrNoRows {

fmt.Printf("no record to found\n")

return

}

if err != nil {

fmt.Printf("get faild,err:%v\n",err)

return

}

fmt.Printf("user:%#v\n",user)

}

func Query(Db *sqlx.DB) {

var user []*User

id := 1

//多行数据查询

err := Db.Select(&user,"select id, name, age from user where id>?",id)

if err == sql.ErrNoRows {

fmt.Printf("no record found\n")

return

}

if err != nil {

fmt.Printf("select rows faild,err:%v\n",err)

return

}

//输出查询结果

fmt.Printf("user:%#v\n",user)

for _,v := range user {

fmt.Printf("%v\n",v)

}

}

func Insert(Db *sqlx.DB) {

username := "alex"

age := 18

result,err := Db.Exec("insert into user(name,age) values(?,?)",username,age)

if err != nil {

fmt.Printf("sql exec insert faild:err:%v\n",err)

return

}

id,err := result.LastInsertId()

if err != nil {

fmt.Printf("last insert id faild,err:%v\n",err)

return

}

affectRows,err := result.RowsAffected()

if err != nil {

fmt.Printf("Rows affects faild,err:%v\n",err)

return

}

fmt.Printf("last insert id:%d, affect rows:%d\n",id,affectRows)

}

func main() {

connMysql()

}

Redis开发

使用第三方库：github.com/garyburd/redigo/redis

redis的使用

func initRedis() (conn redis.Conn,err error) {

conn,err = redis.Dial("tcp","127.0.0.1:6379")

if err != nil {

fmt.Printf("conn redis error:%v\n",err)

return

}

fmt.Printf("conn redis succ\n")

return

}

func testSetGet(conn redis.Conn) {

key := "abc"

_,err := conn.Do("set",key,"this is a test!")

if err != nil {

fmt.Printf("set value faild,eror:%v\n",err)

return

}

data,err := redis.String(conn.Do("get",key))

if err != nil {

fmt.Printf("get faild,err:%v\n",err)

return

}

fmt.Printf("key:%s, value:%v\n",key,data)

}

func main() {

conn,err := initRedis()

if err != nil {

return

}

testSetGet(conn)

}

Hash表操作

func testSetGet(conn redis.Conn) {

key := "abc"

_,err := conn.Do("hset","books",key,"this is a test!")

if err != nil {

fmt.Printf("set value faild,eror:%v\n",err)

return

}

data,err := redis.String(conn.Do("hget","books",key))

if err != nil {

fmt.Printf("get faild,err:%v\n",err)

return

}

fmt.Printf("key:%s, value:%v\n",key,data)

}

Redis并发操作

func testMSetGet(conn redis.Conn) {

key := "abc"

key1 := "def"

_,err := conn.Do("mset",key,key1)

if err != nil {

fmt.Printf("set value faild,eror:%v\n",err)

return

}

//多值操作返回的数据用strings接收

data,err := redis.Strings(conn.Do("mget",key,key1))

if err != nil {

fmt.Printf("get faild,err:%v\n",err)

return

}

//循环取值

for _,val := range data {

fmt.Printf("key:%s, value:%v\n",key,val)

}

}

设置队列

发布订阅

func testQuenu(conn redis.Conn) {

_,err := conn.Do("lpush","book_list","this is a test!","daadada")

if err != nil {

fmt.Printf("lpush value faild,eror:%v\n",err)

return

}

data,err := redis.String(conn.Do("rpop","book_list"))

if err != nil {

fmt.Printf("get faild,err:%v\n",err)

return

}

fmt.Printf("value:%s\n",data)

}

连接池

func newPool(serverAddr string,passwd string) (pool *redis.Pool) {

return &redis.Pool{

MaxIdle: 16,

MaxActive: 1024,

IdleTimeout: 240,

Dial: func() (redis.Conn,error) {

conn,err := redis.Dial("tcp",serverAddr)

if err != nil {

return nil,err

}

if len(passwd) > 0 {

_,err := conn.Do("auth",passwd)

if err != nil {

return nil,err

}

}

return conn,err

},

TestOnBorrow: func(c redis.Conn,t time.Time) error {

if time.Since(t) < time.Minute {

return nil

}

_,err := c.Do("ping")

return err

},

}

}

func testRedisPool() {

pool := newPool("127.0.0.1:6379","")

conn := pool.Get()

conn.Do("set","abcd","23134534665437372132")

val,err := redis.String(conn.Do("get","abcd"))

if err != nil {

fmt.Printf("get faild,err:%v\n",err)

}

fmt.Printf("val:%v,err:%v\n",val,err)

//把连接归还到连接池

conn.Close()

}