Go与数据库
1. 第十章 Go Mysql
准备好mysql数据库服务端数据。
创建test数据库
MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.04 sec)
MariaDB [(none)]> use test;
Database changed
准备好数据表 person
MariaDB [test]> CREATE TABLE `person` (
-> `user_id` int(11) NOT NULL AUTO_INCREMENT,
-> `username` varchar(260) DEFAULT NULL,
-> `sex` varchar(260) DEFAULT NULL,
-> `email` varchar(260) DEFAULT NULL,
-> PRIMARY KEY (`user_id`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.09 sec)
准备好place表
CREATE TABLE place (
country varchar(200),
city varchar(200),
telcode int
)ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
查看表结构
MariaDB [test]> desc person;
+----------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| username | varchar(260) | YES | | NULL | |
| sex | varchar(260) | YES | | NULL | |
| email | varchar(260) | YES | | NULL | |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.05 sec)
MariaDB [test]> desc place;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| country | varchar(200) | YES | | NULL | |
| city | varchar(200) | YES | | NULL | |
| telcode | int(11) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
1.1. mysql驱动
mysql使用 使用第三方开源的mysql库: github.com/go-sql-driver/mysql （mysql驱动）github.com/jmoiron/sqlx （基于mysql驱动的封装）
命令行输入 ：
go get github.com/go-sql-driver/mysql
go get github.com/jmoiron/sqlx
1.2. go-mysql增删改查
插入数据
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Person struct {
UserId int `db:"user_id"`
Username string `db:"username"`
Sex string `db:"sex"`
Email string `db:"email"`
}
type Place struct {
Country string `db:"country"`
City string `db:"city"`
TelCode int `db:"telcode"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
r, err := Db.Exec("insert into person(username, sex, email)values(?, ?, ?)", "stu001", "man", "stu01@qq.com")
if err != nil {
fmt.Println("exec failed, ", err)
return
}
id, err := r.LastInsertId()
if err != nil {
fmt.Println("exec failed, ", err)
return
}
fmt.Println("insert succ:", id)
}
查询数据
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Person struct {
UserId int `db:"user_id"`
Username string `db:"username"`
Sex string `db:"sex"`
Email string `db:"email"`
}
type Place struct {
Country string `db:"country"`
City string `db:"city"`
TelCode int `db:"telcode"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
var person []Person
err := Db.Select(&person, "select user_id, username, sex, email from person where user_id=?", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
fmt.Println("select succ:", person)
}
更新数据
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Person struct {
UserId int `db:"user_id"`
Username string `db:"username"`
Sex string `db:"sex"`
Email string `db:"email"`
}
type Place struct {
Country string `db:"country"`
City string `db:"city"`
TelCode int `db:"telcode"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
res, err := Db.Exec("update person set username=? where user_id=?", "stu0003", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
row, err := res.RowsAffected()
if err != nil {
fmt.Println("rows failed, ",err)
}
fmt.Println("update succ:",row)
}
删除数据
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Person struct {
UserId int `db:"user_id"`
Username string `db:"username"`
Sex string `db:"sex"`
Email string `db:"email"`
}
type Place struct {
Country string `db:"country"`
City string `db:"city"`
TelCode int `db:"telcode"`
}
var Db *sqlx.DB
func init() {
database, err := sqlx.Open("mysql", "root:redhat@tcp(123.206.16.61:3306)/test")
if err != nil {
fmt.Println("open mysql failed,", err)
return
}
Db = database
}
func main() {
/*
_, err := Db.Exec("delete from person where user_id=?", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
*/
res, err := Db.Exec("delete from person where user_id=?", 1)
if err != nil {
fmt.Println("exec failed, ", err)
return
}
row, err := res.RowsAffected()
if err != nil {
fmt.Println("rows failed, ", err)
}
fmt.Println("delete succ: ", row)
}
1.3. mysql事务
mysql事务特性：
1) 原子性
2) 一致性
3) 隔离性
4) 持久性
golang MySQL事务应用：
1） import (“github.com/jmoiron/sqlx") 2) Db.Begin() 开始事务 3) Db.Submit() 提交事务 4) Db.Rollback() 回滚事务
1. 10.2 Go redis
redis是NoSQL数据， 不是传统的关系型数据库。linux，windows环境皆可安装。
https://redis.io
http://www.redis.cn
redis（Remote Dictionary Server）远程字典服务器，性能非常高，单机15W QPS，适合缓存，持久化数据。
1.1. Go操作redis
使用第三方开源的 redis 库: github.com/garyburd/redigo/redis
go get github.com/garyburd/redigo/redis
获取、设置redis的key-value
string类型
package main
import (
"fmt"
"github.com/garyburd/redigo/redis"
)
func main() {
conn, err := redis.Dial("tcp", "127.0.0.1:6379")
if err != nil {
fmt.Println("连接redis出错，", err)
return
}
defer conn.Close()
//连接redis，写入数据 string
res, err := conn.Do("Set", "name", "alexdsb")
if err != nil {
fmt.Println("写入数据出错,", err)
return
}
fmt.Println(res)
//读取redis数据
data, err := redis.String(conn.Do("Get", "name"))
if err != nil {
fmt.Println("读取数据出错,", err)
return
}
fmt.Println("读取出redis数据：", data)
}
Go操作redis hash类型
127.0.0.1:6379> HSET key field value
package main
import (
"fmt"
"github.com/garyburd/redigo/redis"
)
func main() {
conn, err := redis.Dial("tcp", "127.0.0.1:6379")
if err != nil {
fmt.Println("连接redis出错，", err)
return //如果函数异常，直接退出
}
//函数推出前，关闭redis连接
defer conn.Close()
//写入hash类型数据
//写入哈希类型，新闻01，标题
_, err = conn.Do("HSet", "news01", "title", "golang")
if err != nil {
fmt.Println("HSet err:", err)
return //如果函数异常，直接退出
}
//写入哈希类型，新闻01，内容
_, err = conn.Do("HSet", "news01", "content", "life is short ,i use golang")
if err != nil {
fmt.Println("HSet err:", err)
return //如果函数异常，直接退出
}
//读取hash数据类型
data, err := redis.String(conn.Do("HGet", "news01", "title"))
if err != nil {
fmt.Println("HGet err:", err)
return
}
fmt.Println("HGet data is :", data)
//读取hash数据类型
d2, err := redis.String(conn.Do("HGet", "news01", "content"))
if err != nil {
fmt.Println("HGet err:", err)
return
}
fmt.Println("HGet data is :", d2)
}
go操作redis，hash类型，写入多个field-value
127.0.0.1:6379> HMSET key field value [field value ...]
package main
import (
"fmt"
"github.com/garyburd/redigo/redis"
)
func main() {
conn, err := redis.Dial("tcp", "127.0.0.1:6379")
if err != nil {
fmt.Println("连接redis出错，", err)
return //如果函数异常，直接退出
}
//函数推出前，关闭redis连接
defer conn.Close()
//写入多个字段
_, err = conn.Do("HMSet", "news02", "title", "gogogogogogog", "content", "pypypypypypy")
if err != nil {
fmt.Println("HMSet err:", err)
return
}
////读取多个数据
data, err := redis.Strings(conn.Do("HMGet", "news02", "title", "content"))
if err != nil {
fmt.Println("HMGet err:", err)
return
}
//for i, v := range data {
// fmt.Printf("data[%d]=%s\n", i, v)
//}
fmt.Println(data)
}
设置redis数据过期时间
package main
import (
"fmt"
"github.com/garyburd/redigo/redis"
)
func main() {
conn, err := redis.Dial("tcp", "127.0.0.1:6379")
if err != nil {
fmt.Println("连接redis出错，", err)
return //如果函数异常，直接退出
}
defer conn.Close()
//给redis的key设置过期时间，必选保证key存在！！
res, err := conn.Do("expire", "age2", 20)
if err != nil {
fmt.Println(err)
return
}
fmt.Println(res)
//data, err := redis.String(conn.Do("Get", "age2"))
//if err != nil {
// fmt.Println(err)
//}
//fmt.Println(data)
}
golang操作redis的list
package main
import (
"fmt"
"github.com/garyburd/redigo/redis"
)
func main() {
conn, err := redis.Dial("tcp", "127.0.0.1:6379")
if err != nil {
fmt.Println("连接redis出错，", err)
return //如果函数异常，直接退出
}
//go操作list
//注意坑，这里不能再次写入，数据追加写入队列
_, err = conn.Do("lpush", "duilie", "alex", "wupeiqi", "wenzhou", "大西瓜")
if err != nil {
fmt.Println(err)
return
}
data, err := redis.Strings(conn.Do("lrange", "duilie", "0", "-1"))
if err != nil {
fmt.Println(err)
return
}
fmt.Println(data)
}
1.2. redis链接池
1.初始化一定数量的链接放入到链接池
2.go需要操作redis时，直接从链接池取出链接
3.节省临时获取redis链接的时间，提高效率
package main import ( "fmt" "github.com/garyburd/redigo/redis" ) //全局变量类型声明 var pool *redis.Pool //初始化函数，进行链接池初始化 func init() { //redis.pool结构体中提供了参数用法 pool = &redis.Pool{ MaxIdle: 8, //最大空闲链接数 MaxActive: 0, //保持链接数，0是没限制 IdleTimeout: 100, //最大空闲时间 //初始化连接的代码,匿名函数 Dial: func() (redis.Conn, error) { return redis.Dial("tcp", "127.0.0.1:6379") }, } } func main() { //从pool中取出一个链接 conn := pool.Get() defer conn.Close() //设置redis数据 _, err := conn.Do("set", "name", "大狗子") if err != nil { fmt.Println(err) return } //取出redis数据 data, err := redis.String(conn.Do("get", "name")) if err != nil { fmt.Println(err) return } fmt.Println(data) //想要从pool取出redis连接，必须保证链接池没关闭 //pool.Close() //关闭Pool池后，就无法取出redis链接了 conn2 := pool.Get() _, err = conn2.Do("Set", "name2", "大狗子222") if err != nil { fmt.Println(err) return } //取出数据 //取出redis数据 data2, err := redis.String(conn.Do("get", "name2")) if err != nil { fmt.Println(err) return } fmt.Println(data2) }
浙公网安备 33010602011771号