Golang操作MySql

文章目录

1. 知识储备2. 安装ORM包3. 创建表4. 添加数据5. 查询 & 修改 & 删除5.1查询多条数据

1. 知识储备

Go语言基础知识具备一般的SQL知识测试机器上安装了数据库服务端,我们默认使用MySql

2. 安装ORM包

ORM：对象关系映射（Object Relational Mapping，简称ORM）,目的是想像操作对象一样操作数据库.因为数据库不是面向对象的,所以需要编程进行映射.

Go语言能用的ORM客户端包很多,我们依旧只看最常见的ORM包,这些ORM包在github上都能找到

包名github地址参考文档gormhttps://github.com/jinzhu/gormhttp://gorm.ioxormhttps://github.com/go-xorm/xormhttp://gobook.io/read/github.com/go-xorm/manual-en-US/

这两个Golang的包都很常见,我们选择其中一个即可,此处我们选择gorm

go get -u github.com/jinzhu/gorm

gorm 这个包中已经包含了一些数据的驱动,通常来讲就不需要再下载数据库驱动,除非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}