gin: 用gorm连接到mysql数据库
一，安装用到的第三方库
官网地址:
https://gorm.io/安装gorm:
$ go get -u gorm.io/gorm
go: downloading gorm.io/gorm v1.30.5
go: downloading golang.org/x/text v0.29.0
go: added github.com/jinzhu/inflection v1.0.0
go: added github.com/jinzhu/now v1.1.5
go: upgraded golang.org/x/text v0.28.0 => v0.29.0
go: added gorm.io/gorm v1.30.5安装数据库驱动:
$ go get -u gorm.io/driver/mysql
go: downloading gorm.io/driver/mysql v1.6.0
go: downloading github.com/go-sql-driver/mysql v1.9.3
go: added filippo.io/edwards25519 v1.1.0
go: added github.com/go-sql-driver/mysql v1.9.3
go: added gorm.io/driver/mysql v1.6.0二，编写代码
1，自定义日志类:
package global
import (
"bytes"
"context"
"errors"
"fmt"
"github.com/gin-gonic/gin"
gormlogger "gorm.io/gorm/logger"
"gorm.io/gorm/utils"
"log"
"os"
"runtime"
"strings"
"time"
)
const (
Reset = "\033[0m"
Red = "\033[31m"
Green = "\033[32m"
Yellow = "\033[33m"
Blue = "\033[34m"
Magenta = "\033[35m"
Cyan = "\033[36m"
White = "\033[37m"
BlueBold = "\033[34;1m"
MagentaBold = "\033[35;1m"
RedBold = "\033[31;1m"
YellowBold = "\033[33;1m"
)
const (
// Silent silent log level
Silent gormlogger.LogLevel = iota + 1
// Error error log level
Error
// Warn warn log level
Warn
// Info info log level
Info
)
//gorm原日志的配置
type FileLogConfig struct {
gormlogger.Config
}
//定义日志类
type StdFileLogger struct {
FileLogConfig
infoStr, warnStr, errStr string
traceStr, traceErrStr, traceWarnStr string
path,name string
}
//初始化，区分有颜色和无颜色
func NewStdFileLogger(config FileLogConfig,path,name string) *StdFileLogger {
var (
infoStr = "%s\n[info] "
warnStr = "%s\n[warn] "
errStr = "%s\n[error] "
traceStr = "%s\n[%.3fms] [rows:%v] %s"
traceWarnStr = "%s %s\n[%.3fms] [rows:%v] %s"
traceErrStr = "%s %s\n[%.3fms] [rows:%v] %s"
)
if config.Colorful {
infoStr = Green + "%s\n" + Reset + Green + "[info] " + Reset
warnStr = BlueBold + "%s\n" + Reset + Magenta + "[warn] " + Reset
errStr = Magenta + "%s\n" + Reset + Red + "[error] " + Reset
traceStr = Green + "%s\n" + Reset + Yellow + "[%.3fms] " + BlueBold + "[rows:%v]" + Reset + " %s"
traceWarnStr = Green + "%s " + Yellow + "%s\n" + Reset + RedBold + "[%.3fms] " + Yellow + "[rows:%v]" + Magenta + " %s" + Reset
traceErrStr = RedBold + "%s " + MagentaBold + "%s\n" + Reset + Yellow + "[%.3fms] " + BlueBold + "[rows:%v]" + Reset + " %s"
}
return &StdFileLogger{
FileLogConfig: config,
path:path,
name:name,
//Loggers: loggers,
infoStr: infoStr,
warnStr: warnStr,
errStr: errStr,
traceStr: traceStr,
traceWarnStr: traceWarnStr,
traceErrStr: traceErrStr,
}
}
func (logger *StdFileLogger) printf(msg string, data ...interface{}) {
//得到要打印的内容
content:=fmt.Sprintf(msg, data...)
//打印到控制台
log.Printf(content)
//得到文件名:
now := time.Now()
dateStr:=now.Format("2006-01-02")
filePath := logger.path+"/"+logger.name+"_"+dateStr+".log"
// 替换掉彩色打印符号
content = strings.ReplaceAll(content, Reset, "")
content = strings.ReplaceAll(content, Red, "")
content = strings.ReplaceAll(content, Green, "")
content = strings.ReplaceAll(content, Yellow, "")
content = strings.ReplaceAll(content, Blue, "")
content = strings.ReplaceAll(content, Magenta, "")
content = strings.ReplaceAll(content, Cyan, "")
content = strings.ReplaceAll(content, White, "")
content = strings.ReplaceAll(content, BlueBold, "")
content = strings.ReplaceAll(content, MagentaBold, "")
content = strings.ReplaceAll(content, RedBold, "")
content = strings.ReplaceAll(content, YellowBold, "")
// 格式化时间
formatted := now.Format("2006-01-02 15:04:05")
content = formatted+" "+content
//保存到文件
logger.LogToFile(filePath,content+"\n")
}
//日志的级别
func (logger *StdFileLogger) LogMode(lv gormlogger.LogLevel) gormlogger.Interface {
logger.LogLevel = lv
return logger
}
//info
func (logger *StdFileLogger) Info(ctx context.Context, msg string, data ...interface{}) {
if logger.LogLevel >= Info {
logger.printf(logger.infoStr+msg, append([]interface{}{utils.FileWithLineNum()}, data...)...)
}
}
//warn
func (logger *StdFileLogger) Warn(ctx context.Context, msg string, data ...interface{}) {
if logger.LogLevel >= Warn {
logger.printf(logger.infoStr+msg, append([]interface{}{utils.FileWithLineNum()}, data...)...)
}
}
//error
func (logger *StdFileLogger) Error(ctx context.Context, msg string, data ...interface{}) {
if logger.LogLevel >= Error {
//fmt.Println("当前是错误日志:")
logger.printf("当前是错误日志:\n")
logger.printf(logger.infoStr+msg, append([]interface{}{utils.FileWithLineNum()}, data...)...)
}
}
// Trace 打印sql语句
func (logger *StdFileLogger) Trace(ctx context.Context, begin time.Time, fc func() (sql string, rowsAffected int64), err error) {
if logger.LogLevel <= Silent {
return
}
fmt.Println("gorm trace===============")
fmt.Println(ctx)
//fmt.Println("==================create:create======================")
c, ok := ctx.(*gin.Context)
if ok {
fmt.Println("转换成功")
envStr:=GetHttpEnv(c)
fmt.Println("envstr:")
fmt.Println(envStr)
logger.printf(envStr)
} else {
fmt.Println("转换出错")
}
elapsed := time.Since(begin)
switch {
case err != nil && logger.LogLevel >= Error && (!errors.Is(err, gormlogger.ErrRecordNotFound) || !logger.IgnoreRecordNotFoundError):
sql, rows := fc()
//得到stack
stack:= logger.PrintStackTrace(err)
if rows == -1 {
logger.printf(logger.traceErrStr+"\n"+stack, utils.FileWithLineNum(), err, float64(elapsed.Nanoseconds())/1e6, "-", sql)
} else {
logger.printf(logger.traceErrStr+"\n"+stack, utils.FileWithLineNum(), err, float64(elapsed.Nanoseconds())/1e6, rows, sql)
}
case elapsed > logger.SlowThreshold && logger.SlowThreshold != 0 && logger.LogLevel >= Warn:
sql, rows := fc()
slowLog := fmt.Sprintf("SLOW SQL >= %v", logger.SlowThreshold)
if rows == -1 {
logger.printf(logger.traceWarnStr, utils.FileWithLineNum(), slowLog, float64(elapsed.Nanoseconds())/1e6, "-", sql)
} else {
logger.printf(logger.traceWarnStr, utils.FileWithLineNum(), slowLog, float64(elapsed.Nanoseconds())/1e6, rows, sql)
}
case logger.LogLevel == Info:
sql, rows := fc()
if rows == -1 {
logger.printf(logger.traceStr, utils.FileWithLineNum(), float64(elapsed.Nanoseconds())/1e6, "-", sql)
} else {
logger.printf(logger.traceStr, utils.FileWithLineNum(), float64(elapsed.Nanoseconds())/1e6, rows, sql)
}
}
}
//出错时打印错误的堆栈信息
func (logger *StdFileLogger) PrintStackTrace(err error) string {
// 创建一个缓冲区用于存储堆栈信息
buf := bytes.NewBuffer(nil)
// 获取当前goroutine的堆栈信息
for i := 0; ; i++ {
pc, file, line, ok := runtime.Caller(i)
if !ok {
break
}
fmt.Fprintf(buf, "%d: %s:%d (0x%x)\n", i, file, line, pc)
}
// 打印堆栈信息
//fmt.Println(buf.String())
return buf.String()
}
//写内容到文件
func (logger *StdFileLogger) LogToFile(filename,msg string) {
// 输出到文件
file, err := os.OpenFile(filename, os.O_APPEND|os.O_CREATE|os.O_WRONLY, 0644)
if err != nil {
fmt.Println("日志文件的打开错误 :", err)
}
defer file.Close()
if _, err := file.WriteString(msg); err != nil {
fmt.Println("写入日志文件错误 :", err)
}
}2, 建立连接:
package global
import (
"fmt"
"gorm.io/driver/mysql"
"gorm.io/gorm"
"gorm.io/gorm/logger"
"mediabank/config"
"time"
)
var (
DBLink *gorm.DB
)
func SetupDBLink() (error) {
var err error
//创建日志类
path:="./logs"
name:="gormlog"
mylogger := NewStdFileLogger(FileLogConfig{
Config: logger.Config{ // gorm日志原始配置项
SlowThreshold: 200 * time.Millisecond,
IgnoreRecordNotFoundError: true,
Colorful: true,
},
},path,name).LogMode(Info)
dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?charset=%s&parseTime=%t&loc=Local",
config.DatabaseSetting.UserName,
config.DatabaseSetting.Password,
config.DatabaseSetting.Host,
config.DatabaseSetting.DBName,
config.DatabaseSetting.Charset,
true, )
//DBLink, err = gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: logger.Default.LogMode(logger.Info),})
DBLink, err = gorm.Open(mysql.Open(dsn), &gorm.Config{Logger: mylogger,})
if err != nil {
return err
}
sqlDB, _ := DBLink.DB()
// SetMaxIdleConns 设置空闲连接池中连接的最大数量
sqlDB.SetMaxIdleConns(10)
// SetMaxOpenConns 设置打开数据库连接的最大数量
sqlDB.SetMaxOpenConns(30)
// SetConnMaxLifetime 设置了连接可复用的最大时间
sqlDB.SetConnMaxLifetime(time.Hour)
//"query:before"
return nil
}3，初始化:
package main
import (
"embed"
"fmt"
"log"
"mediabank/config"
"mediabank/global"
"mediabank/routes"
)
// 嵌入文件只能为源码文件同级目录和子目录下的文件
//go:embed static/* templates/*
var embedFs embed.FS
func init() {
//读取配置
err := config.ReadSettings()
if err != nil {
log.Fatalf("init.setupSetting err: %v", err)
}
//创建到数据库的连接
errDb := global.SetupDBLink()
if errDb != nil {
fmt.Println("db link error")
}
//创建访问日志
errALog := global.SetupAccessLogger()
if errALog != nil {
fmt.Println("access log error")
}
}
// 入口函数
func main() {
//引入路由
r := routes.Routes(embedFs)
//run
err := r.Run(":"+config.AppSetting.Port)
if err != nil {
return
}
}4, model
package model
type LinkModel struct {
Id uint `gorm:"column:id;primaryKey" json:"id"`
//定义链接的struct
Cname string `gorm:"column:cname;" json:"cname"`
Name string `gorm:"column:name;" json:"name"`
Imageurl string `gorm:"column:imageurl;" json:"imageurl"`
Linkurl string `gorm:"column:linkurl;" json:"linkurl"`
DispImageurl string `gorm:"-;default:''" json:"dispimageurl"`
}
func (LinkModel) TableName() string {
return "zr_link"
}5, dao
package dao
import (
"database/sql"
"fmt"
"github.com/gin-gonic/gin"
"mediabank/global"
"mediabank/model"
)
//按id查询 link
func GetHomeSlides(c *gin.Context) ([]*model.LinkModel, error) {
fields := []string{"id", "cname", "name","imageurl","linkurl"}
var rows *sql.Rows
var err error
rows,err = global.DBLink.WithContext(c).Select(fields).Table(model.LinkModel{}.TableName()).Where("cid=72 and status=1").Order("orderid desc,id desc").Offset(0).Limit(5).Rows()
if err != nil {
fmt.Println(err)
}
defer rows.Close()
var links []*model.LinkModel
for rows.Next() {
r := &model.LinkModel{}
if err := rows.Scan(&r.Id, &r.Cname, &r.Name, &r.Imageurl, &r.Linkurl); err != nil {
fmt.Println(err)
} else {
r.DispImageurl = ""
}
links = append(links, r)
}
return links, nil
}6, 调用:
//得到列表
func (ic *MediaController) List(c *gin.Context) {
//linkDao := dao.NewLinkDao(c)
//_,err:=articleService.InsertOneImage(imageOne)
slides,err := dao.GetHomeSlides(c)
if err != nil {
global.NewResult(c).ErrorCode(500,"数据查询出错",nil)
return
}
fmt.Print("slides:")
fmt.Print(slides)
for index, value := range slides {
fmt.Printf("Index: %d, Value id: %d\n", index, value.Id)
}
var data = gin.H{
"Title": "Gin 列表示例",
"Message": "欢迎来到Gin 列表！",
}
c.HTML(200, "list.html", data)
}三，测试效果:
正常获取到数据后的结果:
2025/09/09 17:45:14 /data/goapp/mediabank/dao/linkDao.go:20
[31.745ms] [rows:-] SELECT id,cname,name,imageurl,linkurl FROM `zr_link` WHERE cid=72 and status=1 ORDER BY orderid desc,id desc LIMIT 5
slides:[0xc0001316e0 0xc000131740 0xc0001317a0 0xc000131800 0xc000131860]
Index: 0, Value id: 564
Index: 1, Value id: 563
Index: 2, Value id: 562
Index: 3, Value id: 561
Index: 4, Value id: 472遇到报错时的日志:
2025-09-09 17:39:17 192.168.219.1 [2025-09-09 17:39:17] GET /media/list?a=1&b=2 HTTP/1.1 192.168.219.3:8080 Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/139.0.0.0 Safari/537.36
GET: key:a,value:1
key:b,value:2
2025-09-09 17:39:17 /data/goapp/mediabank/dao/linkDao.go:20 Error 1054 (42S22): Unknown column 'cdid' in 'where clause'
[1.742ms] [rows:-] SELECT id,cname,name,imageurl,linkurl FROM `zr_link` WHERE cdid=72 and status=1 ORDER BY orderid desc,id desc LIMIT 5
0: /data/goapp/mediabank/global/gormlogger.go:208 (0xa98824)
1: /data/goapp/mediabank/global/gormlogger.go:177 (0xa98377)
2: /data/gopath/pkg/mod/gorm.io/gorm@v1.30.5/callbacks.go:140 (0x8ca3d0)
3: /data/gopath/pkg/mod/gorm.io/gorm@v1.30.5/finisher_api.go:518 (0x8d4ab1)
4: /data/goapp/mediabank/dao/linkDao.go:20 (0xa993ed)
5: /data/goapp/mediabank/controller/MediaController.go:52 (0xa99949)
6: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/context.go:185 (0xa7e98a)
7: /data/goapp/mediabank/middleware/accesslog.go:54 (0xa9c4fe)
8: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/context.go:185 (0xa9b27b)
9: /data/goapp/mediabank/routes/routes.go:128 (0xa9b269)
10: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/context.go:185 (0xa8c74e)
11: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/recovery.go:102 (0xa8c73b)
12: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/context.go:185 (0xa8b824)
13: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/logger.go:249 (0xa8b80b)
14: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/context.go:185 (0xa8acaa)
15: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/gin.go:644 (0xa8a667)
16: /data/gopath/pkg/mod/github.com/gin-gonic/gin@v1.10.1/gin.go:600 (0xa8a18c)
17: /usr/local/soft/go/src/net/http/server.go:3340 (0x7c59ad)
18: /usr/local/soft/go/src/net/http/server.go:2109 (0x7b6544)
19: /usr/local/soft/go/src/runtime/asm_amd64.s:1693 (0x484f80)
浙公网安备 33010602011771号