内容简介:发布订阅
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() }
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Mathematica演示项目笔记
吴飞 / 清华大学出版社 / 2010-7 / 39.00元
Mathematica是由美国科学家斯蒂芬·沃尔夫勒姆(Stephen Wolfram)领导的Wolfram Research Inc.研究公司所开发的一体化计算引擎。《Mathematica演示项目笔记》结合Mathematica演示项目以及第6版和第7版的最新功能(动态交互性、即时三维图形、数值模拟和仿真、高效实时计算、集成语言系统、多核并行计算和数字图像处理等),讲解了符号计算、程序设计、算......一起来看看 《Mathematica演示项目笔记》 这本书的介绍吧!