Go语言操作MySql
2019-11-03 本文已影响0人
楚江云
golang-mysql.png
1. 知识储备
- Go语言基础知识
- 具备一般的SQL知识
- 测试机器上安装了数据库服务端,我们默认使用MySql
2. 安装ORM包
ORM：对象关系映射（Object Relational Mapping，简称ORM）,目的是想像操作对象一样操作数据库.因为数据库不是面向对象的,所以需要编程进行映射.
Go语言能用的ORM客户端包很多,我们依旧只看最常见的ORM包,这些ORM包在github上都能找到
包名 github地址 参考文档 gorm https://github.com/jinzhu/gorm http://gorm.io xorm https://github.com/go-xorm/xorm http://gobook.io/read/github.com/go-xorm/manual-en-US/ 这两个Golang的包都很常见,我们选择其中一个即可,此处我们选择gorm
go get -u github.com/jinzhu/gormgorm 这个包中已经包含了一些数据的驱动,通常来讲就不需要再下载数据库驱动,除非grom中没有包含的
import _ "github.com/jinzhu/gorm/dialects/mysql" import _ "github.com/jinzhu/gorm/dialects/postgres" import _ "github.com/jinzhu/gorm/dialects/sqlite" import _ "github.com/jinzhu/gorm/dialects/mssql"
3. 创建表
package main
import (
"crypto/md5"
"fmt"
"github.com/jinzhu/gorm"
"math/rand"
"strconv"
"time"
)
import _ "github.com/jinzhu/gorm/dialects/mysql"
const (
USER = "root"
PWD = "root"
DBIP = "127.0.0.1"
DBPORT = "3306"
DBNAME = "goproject"
)
type Admin struct {
ID int64
User string
Password string
}
type Finish struct {
ID int // GORM默认会使用ID字段作为表的主键
Callid string `gorm:"size:50;not null"` // 结构体标记,指定字段属性
Subid string `gorm:"size:100"`
Aid int64 `gorm:"index"`
CreateTime time.Time
}
type Account struct {
//gorm.Model 是一个包含了ID, CreatedAt, UpdatedAt, DeletedAt四个字段的结构体
gorm.Model
Appkey string `gorm:"type:varchar(15);index:idx_appkey;not null"`
Company string `gorm:"column:cpmpany_name;size:30"`
Status int8 `gorm:"default:1"` // 指定默认值
}
// 设置表名
func (Admin) TableName() string {
return "vn_admin"
}
// 指定表名
func (Finish) TableName() string {
return "vn_finish"
}
func (Account) TableName() string {
return "vn_account"
}
func main() {
info := USER + ":" + PWD + "@tcp(" + DBIP + ":" + DBPORT + ")/" + DBNAME + "?charset=utf8&parseTime=True&loc=Local&timeout=10ms"
fmt.Println(info)
db, err := gorm.Open("mysql", info)
defer db.Close()
defer func() {
if err := recover(); err != nil {
fmt.Println(err)
}
}()
if err != nil {
fmt.Printf("mysql connect error %v", err)
} else {
fmt.Println("mysql connect success")
}
// 对连接池的配置
// 设置最大空闲连接数
db.DB().SetMaxIdleConns(10)
// 设置最大打开连接数
db.DB().SetMaxOpenConns(100)
if !db.HasTable("vn_admin") {
// 运行给定模型的自动迁移，只会添加缺少的字段，不会删除/更改当前数据
db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&Admin{})
}
if !db.HasTable(&Finish{}) {
// 创建表
db.Set("gorm:table_options", "ENGINE=InnoDB").CreateTable(&Finish{})
}
if !db.HasTable(&Account{}) {
db.Set("gorm:table_options", "ENGINE=InnoDB").CreateTable(&Account{})
}
// 插入数据
rand.Seed(time.Now().UnixNano())
use1 := "Test" + strconv.Itoa(rand.Intn(99))
pwd := func(s string) string {
str := fmt.Sprintf("%x", md5.Sum([]byte(s)))
return str
}(use1)
data := Admin{User: use1, Password: pwd}
//db.NewRecord(data)
db.Create(&data)
}
4. 添加数据
package main
import (
"crypto/md5"
"fmt"
"github.com/jinzhu/gorm"
"math/rand"
"time"
)
import _ "github.com/jinzhu/gorm/dialects/mysql"
const (
USER = "root"
PWD = "root"
DBIP = "127.0.0.1"
DBPORT = "3306"
DBNAME = "goproject"
)
type Admin struct {
ID int64
User string
Password string
}
type Finish struct {
ID int // GORM默认会使用ID字段作为表的主键
Callid string `gorm:"size:50;not null"` // 结构体标记,指定字段属性
Subid string `gorm:"size:100"`
Aid int64 `gorm:"index"`
CreateTime time.Time
}
type Account struct {
//gorm.Model 是一个包含了ID, CreatedAt, UpdatedAt, DeletedAt四个字段的结构体
gorm.Model
Appkey string `gorm:"type:varchar(15);index:idx_appkey;not null"`
Company string `gorm:"column:cpmpany_name;size:30"`
Status int8 `gorm:"default:1"` // 指定默认值
}
// 设置表名
func (Admin) TableName() string {
return "vn_admin"
}
// 指定表名
func (Finish) TableName() string {
return "vn_finish"
}
func (Account) TableName() string {
return "vn_account"
}
// 生成随机的字符串
func gainRandomString(n int) string {
s := "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
b := make([]byte, n)
for v := range b {
b[v] = s[rand.Intn(len(s))]
}
return string(b)
}
// 生成md5字符串
func gainRandomMd5String(s string) string {
return fmt.Sprintf("%x", md5.Sum([]byte(s)))
}
func gainData(d chan Admin) {
for i := 0; i < 20; i++ {
name := gainRandomString(9)
pwd := gainRandomMd5String(name)
data := Admin{User: name, Password: pwd}
d <- data
}
close(d)
}
func main() {
info := USER + ":" + PWD + "@tcp(" + DBIP + ":" + DBPORT + ")/" + DBNAME + "?charset=utf8&parseTime=True&loc=Local&timeout=10ms"
db, err := gorm.Open("mysql", info)
defer db.Close()
defer func() {
if err := recover(); err != nil {
fmt.Println(err)
}
}()
if err != nil {
fmt.Printf("mysql connect error %v", err)
} else {
fmt.Println("mysql connect success")
}
// 对连接池的配置
// 设置最大空闲连接数
db.DB().SetMaxIdleConns(10)
// 设置最大打开连接数
db.DB().SetMaxOpenConns(100)
// 模拟业务逻辑
// 定义一个channel 缓冲为10
datas := make(chan Admin, 20)
// 生产数据
go gainData(datas)
// 插入数据
for v := range datas {
// NewRecord check if value's primary key is blank
db.NewRecord(v)
// Create insert the value into database
db.Create(&v)
}
}
5. 查询 & 修改 & 删除
package main
import (
"fmt"
"github.com/jinzhu/gorm"
"math/rand"
"time"
)
import _ "github.com/jinzhu/gorm/dialects/mysql"
const (
USER = "root"
PWD = "root"
DBIP = "127.0.0.1"
DBPORT = "3306"
DBNAME = "goproject"
)
type Admin struct {
ID int64
User string
Password string
}
type Finish struct {
ID int // GORM默认会使用ID字段作为表的主键
Callid string `gorm:"size:50;not null"` // 结构体标记,指定字段属性
Subid string `gorm:"size:100"`
Aid int64 `gorm:"index"`
CreateTime time.Time
}
type Account struct {
//gorm.Model 是一个包含了ID, CreatedAt, UpdatedAt, DeletedAt四个字段的结构体
gorm.Model
Appkey string `gorm:"type:varchar(15);index:idx_appkey;not null"`
Company string `gorm:"column:cpmpany_name;size:30"`
Status int8 `gorm:"default:1"` // 指定默认值
}
// 设置表名
func (Admin) TableName() string {
return "vn_admin"
}
// 指定表名
func (Finish) TableName() string {
return "vn_finish"
}
func (Account) TableName() string {
return "vn_account"
}
// 生成随机的字符串
func gainRandomString(n int) string {
s := "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
b := make([]byte, n)
for v := range b {
b[v] = s[rand.Intn(len(s))]
}
return string(b)
}
func main() {
info := USER + ":" + PWD + "@tcp(" + DBIP + ":" + DBPORT + ")/" + DBNAME + "?charset=utf8&parseTime=True&loc=Local&timeout=10ms"
db, err := gorm.Open("mysql", info)
defer db.Close()
defer func() {
if err := recover(); err != nil {
fmt.Println(err)
}
}()
if err != nil {
fmt.Printf("mysql connect error %v", err)
} else {
fmt.Println("mysql connect success")
}
// 对连接池的配置
// 设置最大空闲连接数
db.DB().SetMaxIdleConns(10)
// 设置最大打开连接数
db.DB().SetMaxOpenConns(100)
// 模拟业务逻辑
// 查询数据
a1 := Admin{}
// 查询单条记录 没有指定条件的时候,获取主键第一条记录
db.Select([]string{"id", "user", "password"}).Where("id = ? AND user = ?", 1, "Test59").First(&a1)
fmt.Println(a1)
// 获取主键最后一条记录
a2 := Admin{}
db.Last(&a2)
fmt.Println(a2)
// 获取所有记录
var admins []Admin
db.Where("id > 20").Find(&admins)
fmt.Println(admins)
// 更新操作
a3 := Admin{}
a3.User = "Test-demo"
a3.ID = 20
a3.Password = a1.Password
// save 更新或者保存
db.Save(&a3)
// 更新部分字段
a4 := Admin{}
a4.ID = 69
//更新字段user
db.Model(&a4).Update("user", "demo99")
// 删除操作
a5 := Admin{}
a5.ID = 2
errs := db.Delete(&a5).Error
if errs == nil{
fmt.Println("delete success")
}
}
5.1查询多条数据
package main
import (
"fmt"
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
)
const (
USER = "root"
PWD = "root"
DBIP = "127.0.0.1"
DBPORT = "3306"
DBNAME = "goproject"
)
var Supplier string
type Admin struct {
ID int
Compid string
Appkey string
Secretkey string
status int8
}
func (Admin) TableName() string {
return "vn_admin"
}
// mysql 连接测试
func mysqlConnectTest() {
info := USER + ":" + PWD + "@tcp(" + DBIP + ":" + DBPORT + ")/" + DBNAME + "?charset=utf8&parseTime=True&loc=Local&timeout=10ms"
db, err := gorm.Open("mysql", info)
defer db.Close()
defer func() {
if err := recover(); err != nil {
fmt.Println(err)
}
}()
if err != nil {
fmt.Printf("mysql connect error %v", err)
} else {
fmt.Println("mysql connect success")
}
// 对连接池的配置
// 设置最大空闲连接数
db.DB().SetMaxIdleConns(10)
// 设置最大打开连接数
db.DB().SetMaxOpenConns(100)
// 定义一个切片元素是Admin类型的数据,用于存储多条数据
var users []Admin
fields := "id,secretkey,status"
db.Select(fields).Where("id > ?",270).Find(&users)
// 遍历切片
for k,v := range users{
fmt.Println(k,v)
}
}
func main() {
mysqlConnectTest()
}
go run main.go
mysql connect success
0 {271 e966c75641752fcb8233517d40e47d15 0}
1 {272 bb677cd4b7e847c05e45ffe8c39fea88 0}
2 {273 194212f11979f204109401932d218a9b 0}
3 {274 99c41b93e3e18c896c63f4717d2834b4 0}
4 {275 7348332f6f51f1402ffeb45287a91519 0}
5 {276 0107cec0bf078522c0b8db0eda623b24 0}
6 {277 f6963e716f8b75ed52353c0efb5f7282 0}
7 {278 5410ca0466f4e68b05cf5f4dd4c8cfcb 0}
8 {280 d23f1c89e0dbf5c5159723940c0e8092 0}
