从零开始搭建go项目(gin框架)(五) – 支持Mysql

本节内容，我们将mysql引入我们的框架 (本地mysql部署可参考 Mysql集群部署(主从复制)。

第一步 引入mysql相关服务

go get -u gorm.io/gorm
go get -u gorm.io/driver/mysql
go get -u github.com/go-sql-driver/mysql

第二步 创建测试库表

create database fusheng_admin;
create table `user` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
	`username` varchar(100) NOT NULL DEFAULT ""  COMMENT '名称',
	`password` varchar(100) NOT NULL  DEFAULT ""  COMMENT '密码',
	`email` varchar(100) NOT NULL  DEFAULT ""  COMMENT '邮箱',
	`update_at` int(10) NOT NULL DEFAULT 0 COMMENT '更新时间',
	`create_at` int(10) NOT NULL DEFAULT 0 COMMENT '创建时间',
	PRIMARY KEY ( `id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

第三部 对mysql进行封装

mkdir ./library/database

创建./library/database/mysql.go :

package database

import (
	"fmt"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"time"
)

type MysqlConf struct {
	Service     string        `yaml:"service"`
	DataBase     string        `yaml:"database"`
	Addr         string        `yaml:"addr"`
	User         string        `yaml:"user"`
	Password     string        `yaml:"password"`
	MaxIdleConns int `yaml:"maxIdleConns"`
	MaxOpenConns int `yaml:"maxOpenConns"`
	ConnTimeout      time.Duration `yaml:"connTimeout"`
	ReadTimeout  time.Duration `yaml:"readTimeout"`
	WriteTimeout time.Duration `yaml:"writeTimeout"`
	ConnMaxLifeTime time.Duration `yaml:"ConnMaxLifeTime"`
}

var MysqlAllClients map[string]*gorm.DB

func InitMysqlClient(conf *MysqlConf) (client *gorm.DB, err error) {
	dsn := fmt.Sprintf("%s:%s@tcp(%s)/%s?timeout=%s&readTimeout=%s&writeTimeout=%s&parseTime=True&loc=Asia%%2FShanghai",
		conf.User,
		conf.Password,
		conf.Addr,
		conf.DataBase,
		conf.ConnTimeout,
		conf.ReadTimeout,
		conf.WriteTimeout)

	client, err = gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		return client, err
	}

	db, err := client.DB()
	if err != nil {
		return client, err
	}

	db.SetMaxOpenConns(conf.MaxOpenConns)
	db.SetMaxIdleConns(conf.MaxIdleConns)
	db.SetConnMaxLifetime(conf.ConnMaxLifeTime)

	return client, nil
}

type DB struct {
	Name string
	Ctx *gin.Context
}

func (d *DB) GetConn() *gorm.DB {
	return MysqlAllClients[d.Name]
}

func NewDb(ctx *gin.Context, name string) DB {
	return DB{
		Name: name,
		Ctx: ctx,
	}
}

第四步 增加mysql配置与读取

./conf/config.yaml, 新增

mysql:
  fusheng_admin:
    database: fusheng_admin
    addr: 10.252.187.163:3306
    user: root
    password: 123456
    connTimeout: 300ms
    readTimeOut: 500ms
    writeTimeOut: 500ms
    MaxIdleConns: 10
    MaxOpenConns: 10
    ConnMaxLifeTime: 3m

./conf/config.go:

package conf

import (
	"fusheng-admin/library/cache"
	"fusheng-admin/library/database"
	"gopkg.in/yaml.v3"
	"io/ioutil"
)

var BaseConf Config

var ConfigFile = "./conf/config.yaml"

type LogConf struct {
	Dir string `yaml:"dir"`
}

type Config struct {
	Log   LogConf                        `yaml:"log"`
	Redis *cache.RedisConf               `yaml:"redis"`
	Mysql map[string]*database.MysqlConf `yaml:"mysql"`
}

func InitConf() {
	if yamlFile, err := ioutil.ReadFile(ConfigFile); err != nil {
		panic("read conf error: " + err.Error())
	} else if err = yaml.Unmarshal(yamlFile, &BaseConf); err != nil {
		panic("conf file unmarshal error: " + err.Error())
	}
}

第五步 mysql初始化

创建文件./helpers/mysql.go:

package helpers

import (
	"fusheng-admin/conf"
	"fusheng-admin/library/database"
	"gorm.io/gorm"
)

func InitMysql() {
	database.MysqlAllClients = make(map[string]*gorm.DB)
	for service, dbConf := range conf.BaseConf.Mysql {
		if client, err := database.InitMysqlClient(dbConf); err != nil {
			panic("mysql connect " + service + " error :%s" + err.Error())
		} else {
			database.MysqlAllClients[service] = client
		}
	}
}

第六步 main函数中调用

./main.go :

package main

import (
	"fusheng-admin/conf"
	"fusheng-admin/helpers"
	"fusheng-admin/library/middleware"
	"fusheng-admin/router"
	"github.com/gin-gonic/gin"
)

func main()  {
	// 1.创建路由
	r := gin.Default()
	r.Use(middleware.LoggerToFile())

	// 初始化配置
	conf.InitConf()
	helpers.InitMysql()
	helpers.InitRedis()

	// 2.绑定路由规则，执行的函数
	// gin.Context，封装了request和response
	router.Http(r)

	// 3.监听端口，默认在8080
	// Run("里面不指定端口号默认为8088")
	r.Run(":8088")
}

至此，我们项目就已经支持mysql使用啦~，下一章我们再介绍mysql在项目中的具体使用

参考链接

https://gorm.io/