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()
}