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:[email protected](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:[email protected](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:[email protected](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()
- }
来源: http://www.bubuko.com/infodetail-3501827.html