Go语言入门心法(八): mysql驱动安装报错onnection failed
Go语言入门心法(十):Go语言操作MYSQL(CRUD)|事务处理
一: ORM映射框架认知
安装依赖:
S D:\program_file\go_workspace> go get gorm.io/gorm
PS D:\program_file\go_workspace> go get gorm.io/driver/sqlite
PS D:\program_file\go_workspace> go get gorm.io/driver/mysqlPS D:\program_file\go_workspace> go get github.com/jinzhu/gorm
二: 检查数据连接情况
package main
import (
"github.com/jinzhu/gorm"
_ "github.com/jinzhu/gorm/dialects/mysql"
"log"
)
func main() {
db, err := gorm.Open("mysql", "root:123456@tcp(127.0.0.1:3306)/tope-pay-user?charset=utf8&parseTime=True&loc=Local")
//延时关闭数据库连接
defer db.Close()
// 设置最大连接数
db.DB().SetMaxOpenConns(100)
// 设置最大闲置数据
db.DB().SetMaxIdleConns(10)
gormDatabaseLinkCheckErr(err)
// 通过ping检查数量是否成功
err = db.DB().Ping()
gormDatabaseLinkCheckErr(err)
log.Println("数据库连接成功! ")
}
// 定义结构体
type gormUser struct {
ID uint `json:"id"`
Phone string `json:"phone"`
Name string `json:"name"`
Password string `json:"password"`
}
func gormDatabaseLinkCheckErr(err error) {
if err != nil {
log.Fatal("系统异常: ", err.Error())
}
}
运行效果:
GOROOT=D:\program_file_worker\go1.20 #gosetup
GOPATH=D:\program_file_worker\go1.20\bin;C:\Users\Administrator\go #gosetup
D:\program_file_worker\go1.20\bin\go.exe build -o C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_OOPGormToDatabaseGrammar_go.exe D:\program_file\go_workspace\org.jd.data\gorm\OOPGormToDatabaseGrammar.go #gosetup
C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_OOPGormToDatabaseGrammar_go.exe
2023/10/22 14:15:46 数据库连接成功!Process finished with the exit code 0
三: GORM使用结构体创建表
package main
/**
gorm对象关系映射建表:sys_gorm_user
*/
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"log"
"time"
)
func main() {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:123456@tcp(127.0.0.1:3306)/tope-pay-user?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: true, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: true, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false,
}), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: true,
})
sqlDB, err := db.DB()
//延时关闭数据库连接
defer sqlDB.Close()
// 设置最大连接数
// 设置最大闲置数据
sqlDB.SetMaxIdleConns(10)
sqlDB.SetConnMaxLifetime(time.Hour)
gormDatabaseCreateTableCheckErr(err)
// 通过ping检查数量是否成功
err = sqlDB.Ping()
gormDatabaseCreateTableCheckErr(err)
log.Println("数据库连接成功! ")
tx := db.Begin()
// 如果表存在,则删除
result := db.Migrator().HasTable(&GormUser{})
if result {
// 表存在先删除
err = db.Migrator().DropTable(&GormUser{})
if err != nil {
log.Println("删除表异常: ", err.Error())
} else {
log.Println("\t表删除成功")
}
}
// 创建表:gorm_users
err = db.Set("gorm:table_options", "ENGINE=InnoDB").AutoMigrate(&GormUser{})
if err != nil {
// 事务回滚
tx.Rollback()
fmt.Println("事务回滚:", err.Error())
}
// 重命名列
db.Migrator().RenameColumn(&GormUser{}, "id", "deptId")
db.Migrator().RenameColumn(&GormUser{}, "CreatedAt", "createTime")
db.Migrator().RenameColumn(&GormUser{}, "UpdatedAt", "updateTime")
db.Migrator().RenameColumn(&GormUser{}, "DeletedAt", "deleted")
db = tx.Commit()
err = db.Error
if err != nil {
log.Println("系统异常:", err.Error())
return
}
log.Println("\t执行建表成功")
}
// GormUser 定义结构体
type GormUser struct { // 约定主键为ID才会自动创建,数据库的主键为id,二不是解析的deptId,但可用在创建之后通过重命名的方式修改列名
gorm.Model
ID int64 `json:"deptId" gorm:"primaryKey;autoIncrement:true,unique"`
Phone string `json:"phone" gorm:"size:11,unique,index:idx_phone"`
Name string `json:"name" gorm:"size:50,index:idx_name"`
Password string `json:"password" gorm:"size:100"`
}
func gormDatabaseCreateTableCheckErr(err error) {
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
}
运行效果:
GOROOT=D:\program_file_worker\go1.20 #gosetup
GOPATH=D:\program_file_worker\go1.20\bin;C:\Users\Administrator\go #gosetup
D:\program_file_worker\go1.20\bin\go.exe build -o C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_org_jd_data_org_jd_data_gorm__1_.exe D:\program_file\go_workspace\org.jd.data\gorm\OOPGormToDatabaseGrammarCreateTable.go #gosetup
C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_org_jd_data_org_jd_data_gorm__1_.exe
2023/10/22 21:16:13 数据库连接成功!
2023/10/22 21:16:13 表删除成功
2023/10/22 21:16:13 执行建表成功Process finished with the exit code 0
四: Gorm对象关系映射插入数据
package main
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
"log"
"time"
)
/*
插入单条数据
*/
func main() {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:123456@tcp(127.0.0.1:3306)/tope-pay-user?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: false, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: false, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false,
}), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: false,
})
sqlDB, err := db.DB()
//延时关闭数据库连接
defer sqlDB.Close()
// 设置最大连接数
// 设置最大闲置数据
sqlDB.SetMaxIdleConns(10)
// 最大连接数
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
gormDatabaseInsertCheckErr(err)
// 通过ping检查数量是否成功
err = sqlDB.Ping()
gormDatabaseInsertCheckErr(err)
log.Println("数据库连接成功! ")
println()
tx := db.Begin() // 开启事务
user := &GormUser{Name: "张三", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()}
result := db.Create(&user)
err = result.Error
if err != nil {
tx.Rollback() // 回滚事务
println("插入数据异常,已回滚事务.......")
}
result = tx.Commit() // 提交事务
err = result.Error
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
println("============================插入数据成功=======================================")
}
type GormUser struct {
deptId int64 `json:"deptId"` //
Phone string `json:"phone"` //
Name string `json:"name"` //
Password string `json:"password"` //
CreateTime time.Time `json:"createTime" gorm:"column:createTime"` // gorm 的column:映射数据库中对应列的字段为createTime
UpdateTime time.Time `json:"updateTime" gorm:"column:updateTime"` //
Deleted time.Time `json:"deleted" gorm:"column:deleted"` //
}
func gormDatabaseInsertCheckErr(err error) {
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
}
运行效果:
GOROOT=D:\program_file_worker\go1.20 #gosetup
GOPATH=D:\program_file_worker\go1.20\bin;C:\Users\Administrator\go #gosetup
D:\program_file_worker\go1.20\bin\go.exe build -o C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_OOPGormToDatabaseGrammarInsert_go.exe D:\program_file\go_workspace\org.jd.data\gorm\insert\OOPGormToDatabaseGrammarInsert.go #gosetup
C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_OOPGormToDatabaseGrammarInsert_go.exe
2023/10/23 12:25:10 数据库连接成功!============================插入数据成功=======================================
五: Gorm批量插入数据
package main
import (
"gorm.io/driver/mysql"
"gorm.io/gorm"
"log"
"time"
)
func main() {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:123456@tcp(127.0.0.1:3306)/tope-pay-user?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: false, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: false, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false,
}), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: false,
})
sqlDB, err := db.DB()
//延时关闭数据库连接
defer sqlDB.Close()
// 设置最大连接数
// 设置最大闲置数据
sqlDB.SetMaxIdleConns(10)
// 最大连接数
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
gormDatabaseBatchInsertCheckErr(err)
// 通过ping检查数量是否成功
err = sqlDB.Ping()
gormDatabaseBatchInsertCheckErr(err)
log.Println("数据库连接成功! ")
println()
tx := db.Begin() // 开启事务
var users = []GormUser{{Name: "张三", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()},
{Name: "张三", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()},
{Name: "李四", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()},
{Name: "王五", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()},
{Name: "赵四", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()},
{Name: "王二麻子", Phone: "15910771022", Password: "123456", CreateTime: time.Now(), UpdateTime: time.Now(), Deleted: time.Now()}}
result := db.CreateInBatches(users, 100)
err = result.Error
if err != nil {
tx.Rollback() // 回滚事务
println("插入数据异常,已回滚事务.......")
}
result = tx.Commit() // 提交事务
err = result.Error
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
println("============================批量插入数据成功=======================================")
}
type GormUser struct {
deptId int64 `json:"deptId"` //
Phone string `json:"phone"` //
Name string `json:"name"` //
Password string `json:"password"` //
CreateTime time.Time `json:"createTime" gorm:"column:createTime"` // gorm 的column:映射数据库中对应列的字段为createTime
UpdateTime time.Time `json:"updateTime" gorm:"column:updateTime"` //
Deleted time.Time `json:"deleted" gorm:"column:deleted"` //
}
func gormDatabaseBatchInsertCheckErr(err error) {
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
}
运行效果:
rogram_file_worker\go1.20 #gosetup
GOPATH=D:\program_file_worker\go1.20\bin;C:\Users\Administrator\go #gosetup
D:\program_file_worker\go1.20\bin\go.exe build -o C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_org_jd_data_org_jd_data_gorm_batch.exe org.jd.data/org.jd.data/gorm/batch #gosetup
C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_org_jd_data_org_jd_data_gorm_batch.exe
2023/10/23 12:45:15 数据库连接成功!============================批量插入数据成功=======================================
Process finished with the exit code 0
六: Gorm查询数据
package main
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"log"
"time"
)
func main() {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:123456@tcp(127.0.0.1:3306)/tope-pay-user?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: false, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: false, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false,
}), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: false,
})
sqlDB, err := db.DB()
//延时关闭数据库连接
defer sqlDB.Close()
// 设置最大连接数
// 设置最大闲置数据
sqlDB.SetMaxIdleConns(10)
// 最大连接数
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
gormDatabaseInsertCheckErr(err)
// 通过ping检查数量是否成功
err = sqlDB.Ping()
gormDatabaseInsertCheckErr(err)
log.Println("数据库连接成功! ")
println()
var users []GormUser
// Get all records SELECT * FROM gorm_users ;
doResult := db.Find(&users)
err = doResult.Error // returns error
gormDatabaseInsertCheckErr(err)
rowCount := doResult.RowsAffected // returns found records count, equals `len(gorm_users)`
fmt.Printf("获取多少 %d 条数据\n", rowCount)
println("=============================条件查询====================================")
// IN
rowCountNum := db.Where("name IN ?", []string{"张三", "李四", "王五"}).Find(&users)
// SELECT * FROM gorm_users WHERE name IN ('张三','李四', "王五");
fmt.Printf("查找到 %d 条数据\n", rowCountNum.RowsAffected)
println("=============================模糊查询====================================")
// LIKE
rowCountNum = db.Where("name LIKE ?", "%李%").Find(&users)
// SELECT * FROM gorm_users WHERE name LIKE '%李%';
fmt.Printf("查找到 %d 条数据\n", rowCountNum.RowsAffected)
}
type GormUser struct {
deptId int64 `json:"deptId"` //
Phone string `json:"phone"` //
Name string `json:"name"` //
Password string `json:"password"` //
CreateTime time.Time `json:"createTime" gorm:"column:createTime"` // gorm 的column:映射数据库中对应列的字段为createTime
UpdateTime time.Time `json:"updateTime" gorm:"column:updateTime"` //
Deleted time.Time `json:"deleted" gorm:"column:deleted"` //
}
func gormDatabaseInsertCheckErr(err error) {
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
}
运行效果:
GOROOT=D:\program_file_worker\go1.20 #gosetup
GOPATH=D:\program_file_worker\go1.20\bin;C:\Users\Administrator\go #gosetup
D:\program_file_worker\go1.20\bin\go.exe build -o C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_org_jd_data_org_jd_data_gorm_query.exe org.jd.data/org.jd.data/gorm/query #gosetup
C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_org_jd_data_org_jd_data_gorm_query.exe
2023/10/23 13:29:18 数据库连接成功!获取多少 39 条数据
=============================条件查询====================================
查找到 27 条数据
=============================模糊查询====================================
查找到 6 条数据Process finished with the exit code 0
七: Gorm更新数据
package main
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"log"
"time"
)
func main() {
db, err := gorm.Open(mysql.New(mysql.Config{
DSN: "root:123456@tcp(127.0.0.1:3306)/tope-pay-user?charset=utf8&parseTime=True&loc=Local", // data source name
DefaultStringSize: 256, // default size for string fields
DisableDatetimePrecision: true, // disable datetime precision, which not supported before MySQL 5.6
DontSupportRenameIndex: false, // drop & create when rename index, rename index not supported before MySQL 5.7, MariaDB
DontSupportRenameColumn: false, // `change` when rename column, rename column not supported before MySQL 8, MariaDB
SkipInitializeWithVersion: false,
}), &gorm.Config{
DisableForeignKeyConstraintWhenMigrating: false,
})
sqlDB, err := db.DB()
//延时关闭数据库连接
defer sqlDB.Close()
// 设置最大连接数
// 设置最大闲置数据
sqlDB.SetMaxIdleConns(10)
// 最大连接数
sqlDB.SetMaxOpenConns(100)
sqlDB.SetConnMaxLifetime(time.Hour)
gormDatabaseInsertCheckErr(err)
// 通过ping检查数量是否成功
err = sqlDB.Ping()
gormDatabaseInsertCheckErr(err)
log.Println("数据库连接成功! ")
println()
println("============================================开始更新数据===============================")
tx := db.Begin() // 开启事务
// Update with conditions 更新单列数据
result := db.Model(&GormUser{}).Where("name = ?", "李四").Update("name", "老高")
rowCount := result.RowsAffected
fmt.Printf("受影响的行数 %d 条\n", rowCount)
err = result.Error
if err != nil {
tx.Rollback() // 回滚事务
println("更新数据异常,已回滚事务.......")
}
result = tx.Commit() // 提交事务
err = result.Error
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
println("=======================================数据更新成功=======================================")
}
type GormUser struct {
deptId int64 `json:"deptId"` //
Phone string `json:"phone"` //
Name string `json:"name"` //
Password string `json:"password"` //
CreateTime time.Time `json:"createTime" gorm:"column:createTime"` // gorm 的column:映射数据库中对应列的字段为createTime
UpdateTime time.Time `json:"updateTime" gorm:"column:updateTime"` //
Deleted time.Time `json:"deleted" gorm:"column:deleted"` //
}
func gormDatabaseInsertCheckErr(err error) {
if err != nil {
log.Fatal("系统异常: ", err.Error())
return
}
}
运行效果:
GOROOT=D:\program_file_worker\go1.20 #gosetup
GOPATH=D:\program_file_worker\go1.20\bin;C:\Users\Administrator\go #gosetup
D:\program_file_worker\go1.20\bin\go.exe build -o C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_OOPGormToDatabaseGrammarUpdate_go.exe D:\program_file\go_workspace\org.jd.data\gorm\update\OOPGormToDatabaseGrammarUpdate.go #gosetup
C:\Users\Administrator\AppData\Local\Temp\GoLand\___go_build_OOPGormToDatabaseGrammarUpdate_go.exe
2023/10/23 13:42:11 数据库连接成功!============================================开始更新数据===============================
受影响的行数 6 条
=======================================数据更新成功=======================================Process finished with the exit code 0
更多详细操作查看官方文档: