Go-zero学习 第四章 数据库操作（Mysql） 1 目录结构说明2 相关命令2.1 生成sqlx代码命令2.2 生成sqlc代码命令 3 sqlx3.1 sqlx代码讲解3.2 新增
本节内容是在 go-zero学习 第三章 微服务基础上进一步学习总结，使用的数据库是mysql数据库。
本节内容的代码都放在/rpc/database/下，目录结构如下：
├─gORM ├─sql │ └─user├─sqlc└─sqlxgorm：gorm相关代码；sql：主要是sql文件，下面可以进一步分组；sqlc：带缓存的数据库操作代码；sqlx：无缓存的数据库操作代码；goctl model mysql 指令用于生成基于 MySQL 的 model 代码，支持生成带缓存和不带缓存的代码。MySQL 代码生成支持从 sql 文件，数据库连接两个来源生成代码。注意：虽然go-zero的goctl model mysql 指令支持从 sql 文件，数据库连接两个来源生成代码，两者生成的代码是完全一样的。但是我个人比较推荐根据sql文件生成，因为可以记录sql文件的变化。
注意：最后的参数-style=go_zero是指定生成文件名称的格式，这里是蛇形命名，不喜欢的可以去除这个参数。
sql 文件生成sqlx代码的命令：【推荐】单表：
goctl model mysql ddl -src="./rpc/database/sql/user/zero_users.sql" -dir="./rpc/database/sqlx/usermodel" -style=go_zero多表：
goctl model mysql ddl -src="./rpc/database/sql/user/zero_*.sql" -dir="./rpc/database/sqlx/usermodel" -style=go_zero-src：sql文件目录；
-dir：sqlx代码目录；
数据库连接生成sqlx代码的命令：goctl model mysql datasource -url="root:root@tcp(127.0.0.1:3357)/go-zero-micro" -table="zero_users" -dir="./rpc/database/sqlx/usermodel"-url：数据库连接；
-table：数据表；
-dir：sqlx代码目录；
同 2.1 生成sqlx代码的命令类似，只是后面需要再加一个 -cache即可。
sql 文件生成sqlc代码的命令：【推荐】单表：
goctl model mysql ddl -src="./rpc/database/sql/user/zero_users.sql" -dir="./rpc/database/sqlc/usermodel" -style=go_zero -cache多表：
goctl model mysql ddl -src="./rpc/database/sql/user/zero_*.sql" -dir="./rpc/database/sqlc/usermodel" -style=go_zero -cache-src：sql文件目录；
-dir：sqlc代码目录；
数据库连接生成sqlc代码的命令：goctl model mysql datasource -url="root:root@tcp(127.0.0.1:3357)/go-zero-micro" -table="zero_users" -dir="./rpc/database/sqlc/usermodel" -cache-url：数据库连接；
-table：数据表；
-dir：sqlc代码目录；
通过 2.1的命令生成的sqlx代码有三个文件：
vars.go：zerousersmodel_gen.gozerousersmodel.gomodel，可以在这里新增所需要的数据库操作接口及其实现。主要代码都在 zerousersmodel.go，这里使用了反射对拼接的sql语句进行了优化：
注意：其实自定义的操作接口应该都加入context参数，便于链路追踪，这一点已在该分支最新提交的代码中补上。
package usermodelimport ("context""database/sql""fmt""GitHub.com/zeromicro/go-zero/core/stores/sqlc""github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/common/utils""reflect""strings""time")var _ ZeroUsersModel = (*customZeroUsersModel)(nil)type (// ZeroUsersModel is an interface to be customized, add more methods here,// and implement the added methods in customZeroUsersModel.ZeroUsersModel interface {zeroUsersModelTrans(ctx context.Context, fn func(context context.Context, session sqlx.Session) error) errorCount(data *ZeroUsers, beginTime, endTime string) (int64, error)FindPageListByParam(data *ZeroUsers, beginTime, endTime string, current, pageSize int64) ([]*ZeroUsers, error)FindAllByParam(data *ZeroUsers) ([]*ZeroUsers, error)FindOneByParam(data *ZeroUsers) (*ZeroUsers, error)Save(ctx context.Context, data *ZeroUsers) (sql.Result, error)Edit(ctx context.Context, data *ZeroUsers) (sql.Result, error)DeleteData(ctx context.Context, data *ZeroUsers) error}customZeroUsersModel struct {*defaultZeroUsersModel})func (c customZeroUsersModel) Trans(ctx context.Context, fn func(context context.Context, session sqlx.Session) error) error {return c.conn.TransactCtx(ctx, func(ctx context.Context, session sqlx.Session) error {return fn(ctx, session)})}func userSqlJoins(queryModel *ZeroUsers) string {typ := reflect.TypeOf(queryModel).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(queryModel).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()sql := ""for i := 0; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()colName := typ.Field(i).Tag.Get("db")if colType == "int64" {if Field.Int() > 0 {sql += fmt.Sprintf(" AND %s=%d", colName, Field.Int())}} else if colType == "string" {if Field.String() != "" {sql += fmt.Sprintf(" AND %s LIKE %s", colName, "'%"+Field.String()+"%'")}} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {sql += fmt.Sprintf(" AND %s='%s'", colName, Field.String())}}}return sql}func (c customZeroUsersModel) Count(data *ZeroUsers, beginTime, endTime string) (int64, error) {sql := fmt.Sprintf("SELECT count(*) as count FROM %s WHERE deleted_flag = %d", c.table, utils.DelNo)joinSql := userSqlJoins(data)beginTimeSql := ""if beginTime != "" {beginTimeSql = fmt.Sprintf(" AND created_at >= %s", "'"+beginTime+"'")}endTimeSql := ""if endTime != "" {endTimeSql = fmt.Sprintf(" AND created_at <= %s", "'"+endTime+"'")}sql = sql + joinSql + beginTimeSql + endTimeSqlvar count int64err := c.conn.QueryRow(&count, sql)switch err {case nil:return count, nilcase sqlc.ErrNotFound:return 0, ErrNotFounddefault:return 0, err}}func (c customZeroUsersModel) FindPageListByParam(data *ZeroUsers, beginTime, endTime string, current, pageSize int64) ([]*ZeroUsers, error) {sql := fmt.Sprintf("SELECT %s FROM %s WHERE deleted_flag = %d", zeroUsersRows, c.table, utils.DelNo)joinSql := userSqlJoins(data)beginTimeSql := ""if beginTime != "" {beginTimeSql = fmt.Sprintf(" AND created_at >= %s", "'"+beginTime+"'")}endTimeSql := ""if endTime != "" {endTimeSql = fmt.Sprintf(" AND created_at <= %s", "'"+endTime+"'")}orderSql := " ORDER BY created_at DESC"limitSql := fmt.Sprintf(" LIMIT %d,%d", (current-1)*pageSize, pageSize)sql = sql + joinSql + beginTimeSql + endTimeSql + orderSql + limitSqlvar result []*ZeroUserserr := c.conn.QueryRows(&result, sql)switch err {case nil:return result, nilcase sqlc.ErrNotFound:return nil, ErrNotFounddefault:return nil, err}}func (c customZeroUsersModel) FindAllByParam(data *ZeroUsers) ([]*ZeroUsers, error) {sql := fmt.Sprintf("SELECT %s FROM %s WHERE deleted_flag = %d", zeroUsersRows, c.table, utils.DelNo)joinSql := userSqlJoins(data)orderSql := " ORDER BY created_at DESC"sql = sql + joinSql + orderSqlvar result []*ZeroUserserr := c.conn.QueryRows(&result, sql)switch err {case nil:return result, nilcase sqlc.ErrNotFound:return nil, ErrNotFounddefault:return nil, err}}func (c customZeroUsersModel) FindOneByParam(data *ZeroUsers) (*ZeroUsers, error) {sql := fmt.Sprintf("SELECT %s FROM %s WHERE deleted_flag = %d", zeroUsersRows, c.table, utils.DelNo)joinSql := userSqlJoins(data)orderSql := " ORDER BY created_at DESC"sql = sql + joinSql + orderSqlvar result ZeroUserserr := c.conn.QueryRow(&result, sql)switch err {case nil:return &result, nilcase sqlc.ErrNotFound:return nil, ErrNotFounddefault:return nil, err}}func (c customZeroUsersModel) Save(ctx context.Context, data *ZeroUsers) (sql.Result, error) {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""values := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {if Field.Int() > 0 {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("%d,", Field.Int())}} else if colType == "string" {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", value.Format(utils.DateTimeFormat))}}}names = strings.TrimRight(names, ",")values = strings.TrimRight(values, ",")saveSql := fmt.Sprintf("INSERT INTO %s(%s) VALUE(%s)", c.table, names, values)result, err := c.conn.ExecCtx(ctx, saveSql)return result, err}func (c customZeroUsersModel) Edit(ctx context.Context, data *ZeroUsers) (sql.Result, error) {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {if Field.Int() > 0 {names += fmt.Sprintf("`%s`=%d,", typ.Field(i).Tag.Get("db"), Field.Int())}} else if colType == "string" {names += fmt.Sprintf("`%s`='%s',", typ.Field(i).Tag.Get("db"), Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`='%s',", typ.Field(i).Tag.Get("db"), value.Format(utils.DateTimeFormat))}}}names = strings.TrimRight(names, ",")sql := fmt.Sprintf("UPDATE %s SET deleted_flag = %d, %s WHERE id = %d", c.table, utils.DelNo, names, data.Id)result, err := c.conn.ExecCtx(ctx, sql)return result, err}func (c customZeroUsersModel) DeleteData(ctx context.Context, data *ZeroUsers) error {UpdateTime := data.UpdatedAt.Format(utils.DateTimeFormat)sql := fmt.Sprintf("UPDATE %s SET deleted_flag = %d,deleted_at= %s WHERE id = %d", c.table, utils.DelYes, "'"+UpdateTime+"'", data.Id)_, err := c.conn.ExecCtx(ctx, sql)return err}// NewZeroUsersModel returns a model for the database table.func NewZeroUsersModel(conn sqlx.SqlConn) ZeroUsersModel {return &customZeroUsersModel{defaultZeroUsersModel: newZeroUsersModel(conn),}}RPC服务yaml配置加入MySQL连接配置：MySQL: #本地数据库 DataSource: root:root@tcp(127.0.0.1:3357)/go-zero-micro?charset=utf8mb4&parseTime=true&loc=Asia%2FShanghaiRPC服务yaml配置映射类internal/config/config.go加入MySQL连接配置：package configimport "github.com/zeromicro/go-zero/zrpc"type Config struct {zrpc.RpcServerConfJwt struct {AccessSecret stringAccessExpire int64}MySQL struct {DataSource string}UploadFile UploadFile}type UploadFile struct {MaxFileNum int64MaxFileSize int64SavePath string}internal/svc/servicecontext.go创建操作数据库的连接package svcimport ("github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/rpc/code/ucenter/internal/config""go-zero-micro/rpc/database/sqlx/usermodel")type ServiceContext struct {Config config.ConfigUsersModel usermodel.ZeroUsersModel}func NewServiceContext(c config.Config) *ServiceContext {mysqlConn := sqlx.NewMysql(c.MySQL.DataSource)return &ServiceContext{Config: c,UsersModel: usermodel.NewZeroUsersModel(mysqlConn),}}internal/logic/ucentersqlx/loginuserlogic.go使用具体的操作接口package ucentersqlxlogicimport ("context""errors""fmt""go-zero-micro/common/utils""go-zero-micro/rpc/database/sqlx/usermodel""time""go-zero-micro/rpc/code/ucenter/internal/svc""go-zero-micro/rpc/code/ucenter/ucenter""github.com/jinzhu/copier""github.com/zeromicro/go-zero/core/logx")type LoginUserLogic struct {ctx context.ContextsvcCtx *svc.ServiceContextlogx.Logger}func NewLoginUserLogic(ctx context.Context, svcCtx *svc.ServiceContext) *LoginUserLogic {return &LoginUserLogic{ctx: ctx,svcCtx: svcCtx,Logger: logx.WithContext(ctx),}}// LoginUser 用户登录func (l *LoginUserLogic) LoginUser(in *ucenter.User) (*ucenter.UserLoginResp, error) {param := &usermodel.ZeroUsers{Account: in.Account,}dbRes, err := l.svcCtx.UsersModel.FindOneByParam(param)if err != nil {logx.Error(err)errInfo := fmt.Sprintf("LoginUser:FindOneByParam:db err:%v , in : %+v", err, in)return nil, errors.New(errInfo)}if utils.ComparePassWord(in.Password, dbRes.Password) {copier.Copy(in, dbRes)return l.LoginSuccess(in)} else {errInfo := fmt.Sprintf("LoginUser:user password error:in : %+v", in)return nil, errors.New(errInfo)}}func (l *LoginUserLogic) LoginSuccess(in *ucenter.User) (*ucenter.UserLoginResp, error) {AccessSecret := l.svcCtx.Config.JWT.AccessSecretAccessExpire := l.svcCtx.Config.JWT.AccessExpirenow := time.Now().Unix()jwtToken, err := utils.GenerateJwtToken(AccessSecret, now, AccessExpire, in.Id)if err != nil {return nil, err}resp := &ucenter.UserLoginResp{}copier.Copy(resp, in)resp.AccessToken = jwtTokenresp.AccessExpire = now + AccessExpireresp.RefreshAfter = now + AccessExpire/2return resp, nil}注意：
context参数，便于链路追踪，这一点已在该分支最新提交的代码中补上。gorm。xxxmodel.go 中加入调用事务的接口，新增含有session的数据库操作接口（1）调用事务的接口及其实现
//接口TransCtx(ctx context.Context, fn func(context context.Context, session sqlx.Session) error) error//实现func (c customZeroUsersModel) TransCtx(ctx context.Context, fn func(context context.Context, session sqlx.Session) error) error {return c.conn.TransactCtx(ctx, func(ctx context.Context, session sqlx.Session) error {return fn(ctx, session)})}（2）含有session的数据库操作接口（以添加用户为例）
可以发现，与没有事务特性的插入相比只是更改了操作的调用者为session。
//接口，有session参数TransSaveCtx(ctx context.Context, session sqlx.Session, data *ZeroUsers) (sql.Result, error)//实现func (c customZeroUsersModel) TransSaveCtx(ctx context.Context, session sqlx.Session, data *ZeroUsers) (sql.Result, error) {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""values := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {if Field.Int() > 0 {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("%d,", Field.Int())}} else if colType == "string" {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", value.Format(utils.DateTimeFormat))}}}names = strings.TrimRight(names, ",")values = strings.TrimRight(values, ",")saveSql := fmt.Sprintf("INSERT INTO %s(%s) VALUE(%s)", c.table, names, values)//result, err := c.conn.ExecCtx(ctx, saveSql)//return result, errresult, err := session.ExecCtx(ctx, saveSql)return result, err}xxxlogic.go中使用事务（1）在xxxlogic.go中将对主子表的操作全部放到同一个事务中，每一步操作有错误就返回错误，事务遇到返回的错误会回滚，没有错误最后就返回nil；
（2）注意在同一事务里的每步操作（往主子表插入数据时），需要使用同一个session，否则事务特性不生效；
代码示例在 internal/logic/ucentersqlx/adduserlogic.go中：
package ucentersqlxlogicimport ("context""github.com/jinzhu/copier""github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/common/errorx""go-zero-micro/common/utils""go-zero-micro/rpc/database/sqlx/usermodel""time""go-zero-micro/rpc/code/ucenter/internal/svc""go-zero-micro/rpc/code/ucenter/ucenter""github.com/zeromicro/go-zero/core/logx")type AddUserLogic struct {ctx context.ContextsvcCtx *svc.ServiceContextlogx.Logger}func NewAddUserLogic(ctx context.Context, svcCtx *svc.ServiceContext) *AddUserLogic {return &AddUserLogic{ctx: ctx,svcCtx: svcCtx,Logger: logx.WithContext(ctx),}}// AddUser 添加用户func (l *AddUserLogic) AddUser(in *ucenter.User) (*ucenter.BaseResp, error) {userId := utils.GetUidFromCtxInt64(l.ctx, "userId")currentTime := time.Now()var InsertUserId int64//将对主子表的操作全部放到同一个事务中，每一步操作有错误就返回错误，没有错误最后就返回nil，事务遇到错误会回滚；if err := l.svcCtx.UsersModel.TransCtx(l.ctx, func(context context.Context, session sqlx.Session) error {userParam := &usermodel.ZeroUsers{}copier.Copy(userParam, in)userParam.Password = utils.GeneratePassword(l.svcCtx.Config.DefaultConfig.DefaultPassword)userParam.CreatedBy = userIduserParam.CreatedAt = currentTimedbUserRes, err := l.svcCtx.UsersModel.TransSaveCtx(l.ctx, session, userParam)if err != nil {return err}uid, err := dbUserRes.LastInsertId()if err != nil {return err}userInfoParam := &usermodel.ZeroUserInfos{}copier.Copy(userInfoParam, in)userInfoParam.UserId = uiduserInfoParam.CreatedBy = userIduserInfoParam.CreatedAt = currentTime_, err = l.svcCtx.UserInfosModel.TransSaveCtx(l.ctx, session, userInfoParam)if err != nil {return err}InsertUserId = uidreturn nil}); err != nil {return nil, errorx.NewDefaultError(errorx.DbAddErrorCode)}return &ucenter.BaseResp{Id: InsertUserId,}, nil}注意：其实自定义的操作接口应该都加入context参数，便于链路追踪，这一点已在该分支最新提交的代码中补上。
前面使用反射特性减少了操作每个数据表（即xxxmodel.go）中查询、添加、修改接口的代码量，减少了手动拼接sql。
本节使用golang新增的泛型特性，对查询、新增、修改这三种类型的代码进一步优化，从而实现使用同一个接口操作多个数据表的目的。
使用泛型优化查询、新增、修改的拼接sql，具体代码在common/utils/database.go：
// QuerySqlJoins 根据查询条件拼接sql，使用泛型更加通用func QuerySqlJoins[T any](data *T) string {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()sql := ""for i := 0; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()colName := typ.Field(i).Tag.Get("db")if colType == "int64" {if Field.Int() > 0 {sql += fmt.Sprintf(" AND %s=%d", colName, Field.Int())}} else if colType == "string" {if Field.String() != "" {sql += fmt.Sprintf(" AND %s LIKE %s", colName, "'%"+Field.String()+"%'")}} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {sql += fmt.Sprintf(" AND %s='%s'", colName, Field.String())}}}return sql}// SaveSqlJoins 根据实际参数拼接sql，使用泛型更加通用func SaveSqlJoins[T any](data *T, table string) string {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""values := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {//if Field.Int() > 0 {//names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))//values += fmt.Sprintf("%d,", Field.Int())//}names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("%d,", Field.Int())} else if colType == "string" {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", value.Format(DateTimeFormat))}}}names = strings.TrimRight(names, ",")values = strings.TrimRight(values, ",")sql := fmt.Sprintf("INSERT INTO %s(%s) VALUE(%s)", table, names, values)return sql}// EditSqlJoins 根据实际参数拼接sql，使用泛型更加通用func EditSqlJoins[T any](data *T, table string, Id int64) string {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {if Field.Int() > 0 {names += fmt.Sprintf("`%s`=%d,", typ.Field(i).Tag.Get("db"), Field.Int())}} else if colType == "string" {names += fmt.Sprintf("`%s`='%s',", typ.Field(i).Tag.Get("db"), Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`='%s',", typ.Field(i).Tag.Get("db"), value.Format(DateTimeFormat))}}}names = strings.TrimRight(names, ",")sql := fmt.Sprintf("UPDATE %s SET deleted_flag = %d, %s WHERE id = %d", table, DelNo, names, Id)return sql}xxx_model.go中的相应代码，例如：（1）查询：
原调用的查询拼接sql：
joinSql := userSqlJoins(data)新调用的查询拼接sql：
joinSql := utils.QuerySqlJoins(data)（2）新增：
原sql：
func (c customZeroUsersModel) SaveCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error) {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""values := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {if Field.Int() > 0 {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("%d,", Field.Int())}} else if colType == "string" {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`,", typ.Field(i).Tag.Get("db"))values += fmt.Sprintf("'%s',", value.Format(utils.DateTimeFormat))}}}names = strings.TrimRight(names, ",")values = strings.TrimRight(values, ",")saveSql := fmt.Sprintf("INSERT INTO %s(%s) VALUE(%s)", c.table, names, values)saveSql := utils.SaveSqlJoins(data, c.table)result, err := c.conn.ExecCtx(ctx, saveSql)return result, err}新sql：
func (c customZeroUsersModel) SaveCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error) {saveSql := utils.SaveSqlJoins(data, c.table)result, err := c.conn.ExecCtx(ctx, saveSql)return result, err}（3）修改：
原sql：
func (c customZeroUsersModel) EditCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error) {typ := reflect.TypeOf(data).Elem() //指针类型需要加 Elem()val := reflect.ValueOf(data).Elem() //指针类型需要加 Elem()fieldNum := val.NumField()names := ""for i := 1; i < fieldNum; i++ {Field := val.Field(i)colType := Field.Type().String()if colType == "int64" {if Field.Int() > 0 {names += fmt.Sprintf("`%s`=%d,", typ.Field(i).Tag.Get("db"), Field.Int())}} else if colType == "string" {names += fmt.Sprintf("`%s`='%s',", typ.Field(i).Tag.Get("db"), Field.String())} else if colType == "time.Time" {value := Field.Interface().(time.Time)if !value.IsZero() {names += fmt.Sprintf("`%s`='%s',", typ.Field(i).Tag.Get("db"), value.Format(utils.DateTimeFormat))}}}names = strings.TrimRight(names, ",")sql := fmt.Sprintf("UPDATE %s SET deleted_flag = %d, %s WHERE id = %d", c.table, utils.DelNo, names, data.Id)result, err := c.conn.ExecCtx(ctx, sql)editSql := utils.EditSqlJoins(data, c.table, data.Id)result, err := c.conn.ExecCtx(ctx, editSql)return result, err}新sql：
func (c customZeroUsersModel) EditCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error) {editSql := utils.EditSqlJoins(data, c.table, data.Id)result, err := c.conn.ExecCtx(ctx, editSql)return result, err}sqlc相比sqlx，主要是加入了缓存（Redis），可以避免频繁访问数据库。sqlc，只需要把生成sqlx的命令中再加入 -cache，同时加入缓存相关的配置即可。※默认的缓存接口主要针对的是单条数据，因为设置到Redis里的key只细化到了id，因此针对单条数据的 增以zero_users数据表为例：
2.2 生成sqlc代码命令。database/sqlc/usermodel/zero_users_model.go自定义其他查询接口及具体实现。注意：这里只有FindOneByParamCtx、EditCtx、DeleteDataCtx使用了缓存。
package usermodelimport ("database/sql""fmt""github.com/zeromicro/go-zero/core/stores/cache""github.com/zeromicro/go-zero/core/stores/sqlc""github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/common/utils""golang.org/x/net/context")var _ ZeroUsersModel = (*customZeroUsersModel)(nil)type (// ZeroUsersModel is an interface to be customized, add more methods here,// and implement the added methods in customZeroUsersModel.ZeroUsersModel interface {zeroUsersModelTransCtx(ctx context.Context, fn func(context context.Context, session sqlx.Session) error) errorCountCtx(ctx context.Context, data *ZeroUsers, beginTime, endTime string) (int64, error)FindPageListByParamCtx(ctx context.Context, data *ZeroUsers, beginTime, endTime string, current, pageSize int64) ([]*ZeroUsers, error)FindAllByParamCtx(ctx context.Context, data *ZeroUsers) ([]*ZeroUsers, error)FindOneByParamCtx(ctx context.Context, data *ZeroUsers) (*ZeroUsers, error)SaveCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error)EditCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error)DeleteDataCtx(ctx context.Context, data *ZeroUsers) errorTransSaveCtx(ctx context.Context, session sqlx.Session, data *ZeroUsers) (sql.Result, error)}customZeroUsersModel struct {*defaultZeroUsersModel})func (c customZeroUsersModel) TransCtx(ctx context.Context, fn func(context context.Context, session sqlx.Session) error) error {return c.TransactCtx(ctx, func(ctx context.Context, session sqlx.Session) error {return fn(ctx, session)})}func (c customZeroUsersModel) CountCtx(ctx context.Context, data *ZeroUsers, beginTime, endTime string) (int64, error) {querySql := fmt.Sprintf("SELECT count(*) as count FROM %s WHERE deleted_flag = %d", c.table, utils.DelNo)joinSql := utils.QuerySqlJoins(data)beginTimeSql := ""if beginTime != "" {beginTimeSql = fmt.Sprintf(" AND created_at >= %s", "'"+beginTime+"'")}endTimeSql := ""if endTime != "" {endTimeSql = fmt.Sprintf(" AND created_at <= %s", "'"+endTime+"'")}querySql = querySql + joinSql + beginTimeSql + endTimeSqlvar count int64err := c.QueryRowNoCacheCtx(ctx, &count, querySql)switch err {case nil:return count, nilcase sqlc.ErrNotFound:return 0, ErrNotFounddefault:return 0, err}}func (c customZeroUsersModel) FindPageListByParamCtx(ctx context.Context, data *ZeroUsers, beginTime, endTime string, current, pageSize int64) ([]*ZeroUsers, error) {querySql := fmt.Sprintf("SELECT %s FROM %s WHERE deleted_flag = %d", zeroUsersRows, c.table, utils.DelNo)joinSql := utils.QuerySqlJoins(data)beginTimeSql := ""if beginTime != "" {beginTimeSql = fmt.Sprintf(" AND created_at >= %s", "'"+beginTime+"'")}endTimeSql := ""if endTime != "" {endTimeSql = fmt.Sprintf(" AND created_at <= %s", "'"+endTime+"'")}orderSql := " ORDER BY created_at DESC"limitSql := fmt.Sprintf(" LIMIT %d,%d", (current-1)*pageSize, pageSize)querySql = querySql + joinSql + beginTimeSql + endTimeSql + orderSql + limitSqlvar result []*ZeroUserserr := c.QueryRowsNoCacheCtx(ctx, &result, querySql)switch err {case nil:return result, nilcase sqlc.ErrNotFound:return nil, ErrNotFounddefault:return nil, err}}func (c customZeroUsersModel) FindAllByParamCtx(ctx context.Context, data *ZeroUsers) ([]*ZeroUsers, error) {querySql := fmt.Sprintf("SELECT %s FROM %s WHERE deleted_flag = %d", zeroUsersRows, c.table, utils.DelNo)joinSql := utils.QuerySqlJoins(data)orderSql := " ORDER BY created_at DESC"querySql = querySql + joinSql + orderSqlvar result []*ZeroUserserr := c.QueryRowsNoCacheCtx(ctx, &result, querySql)switch err {case nil:return result, nilcase sqlc.ErrNotFound:return nil, ErrNotFounddefault:return nil, err}}func (c customZeroUsersModel) FindOneByParamCtx(ctx context.Context, data *ZeroUsers) (*ZeroUsers, error) {querySql := fmt.Sprintf("SELECT %s FROM %s WHERE deleted_flag = %d", zeroUsersRows, c.table, utils.DelNo)joinSql := utils.QuerySqlJoins(data)orderSql := " ORDER BY created_at DESC"querySql = querySql + joinSql + orderSqlvar result ZeroUsersvar err errorif data.Id > 0 {zeroUsersIdKey := fmt.Sprintf("%s%v", cacheZeroUsersIdPrefix, data.Id)err = c.QueryRowCtx(ctx, &result, zeroUsersIdKey, func(ctx context.Context, conn sqlx.SqlConn, v any) error {return conn.QueryRowCtx(ctx, v, querySql)})} else {err = c.QueryRowNoCacheCtx(ctx, &result, querySql)}switch err {case nil:return &result, nilcase sqlc.ErrNotFound:return nil, ErrNotFounddefault:return nil, err}}func (c customZeroUsersModel) SaveCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error) {saveSql := utils.SaveSqlJoins(data, c.table)//zeroUsersIdKey := fmt.Sprintf("%s%v", cacheZeroUsersIdPrefix, data.Id)//result, err := c.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (result sql.Result, err error) {//return conn.ExecCtx(ctx, saveSql)//}, zeroUsersIdKey)result, err := c.ExecNoCacheCtx(ctx, saveSql)return result, err}func (c customZeroUsersModel) EditCtx(ctx context.Context, data *ZeroUsers) (sql.Result, error) {editSql := utils.EditSqlJoins(data, c.table, data.Id)zeroUsersIdKey := fmt.Sprintf("%s%v", cacheZeroUsersIdPrefix, data.Id)result, err := c.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (result sql.Result, err error) {return conn.ExecCtx(ctx, editSql)}, zeroUsersIdKey)return result, err}func (c customZeroUsersModel) DeleteDataCtx(ctx context.Context, data *ZeroUsers) error {UpdateTime := data.UpdatedAt.Format(utils.DateTimeFormat)deleteSql := fmt.Sprintf("UPDATE %s SET deleted_flag = %d,deleted_at= %s WHERE id = %d", c.table, utils.DelYes, "'"+UpdateTime+"'", data.Id)zeroUsersIdKey := fmt.Sprintf("%s%v", cacheZeroUsersIdPrefix, data.Id)_, err := c.ExecCtx(ctx, func(ctx context.Context, conn sqlx.SqlConn) (result sql.Result, err error) {return conn.ExecCtx(ctx, deleteSql)}, zeroUsersIdKey)return err}func (c customZeroUsersModel) TransSaveCtx(ctx context.Context, session sqlx.Session, data *ZeroUsers) (sql.Result, error) {saveSql := utils.SaveSqlJoins(data, c.table)//result, err := c.conn.ExecCtx(ctx, saveSql)//return result, errresult, err := session.ExecCtx(ctx, saveSql)return result, err}// NewZeroUsersModel returns a model for the database table.func NewZeroUsersModel(conn sqlx.SqlConn, c cache.CacheConf, opts ...cache.Option) ZeroUsersModel {return &customZeroUsersModel{defaultZeroUsersModel: newZeroUsersModel(conn, c, opts...),}}Cache相关配置（1）RPC服务的yaml中新增缓存的配置，缓存用的是Redis。
CacheRedis: - Host: 127.0.0.1:6379 Type: node Pass: ""（2）internal/config/config.go加入缓存配置的映射。
package configimport ("github.com/zeromicro/go-zero/core/stores/cache""github.com/zeromicro/go-zero/zrpc")type Config struct {zrpc.RpcServerConfJWT struct {AccessSecret stringAccessExpire int64}MySQL struct {DataSource string}CacheRedis cache.CacheConfDefaultConfig DefaultConfigUploadFile UploadFile}type UploadFile struct {MaxFileNum int64MaxFileSize int64SavePath string}// DefaultConfig 默认配置type DefaultConfig struct {//默认密码DefaultPassword string}internal/svc/servicecontext.go加入sqlc数据库查询依赖。package svcimport ("github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/rpc/code/ucenter/internal/config"sqlc_usermodel "go-zero-micro/rpc/database/sqlc/usermodel"sqlx_usermodel "go-zero-micro/rpc/database/sqlx/usermodel")type ServiceContext struct {Config config.ConfigSqlxUsersModel sqlx_usermodel.ZeroUsersModelSqlxUserInfosModel sqlx_usermodel.ZeroUserInfosModelSqlcUsersModel sqlc_usermodel.ZeroUsersModelSqlcUserInfosModel sqlc_usermodel.ZeroUserInfosModel}func NewServiceContext(c config.Config) *ServiceContext {mysqlConn := sqlx.NewMysql(c.MySQL.DataSource)return &ServiceContext{Config: c,SqlxUsersModel: sqlx_usermodel.NewZeroUsersModel(mysqlConn),SqlxUserInfosModel: sqlx_usermodel.NewZeroUserInfosModel(mysqlConn),SqlcUsersModel: sqlc_usermodel.NewZeroUsersModel(mysqlConn, c.CacheRedis),SqlcUserInfosModel: sqlc_usermodel.NewZeroUserInfosModel(mysqlConn, c.CacheRedis),}}xxxlogic.go处理逻辑中的sqlx代码替换为sqlc代码（1）internal/logic/ucentersqlx/loginuserlogic.go
注意：在loginuserlogic.go中查询参数加入了Id，这样就可以测试缓存是否生效了。
package ucentersqlxlogicimport ("context""errors""fmt""go-zero-micro/common/utils"sqlc_usermodel "go-zero-micro/rpc/database/sqlc/usermodel""time""go-zero-micro/rpc/code/ucenter/internal/svc""go-zero-micro/rpc/code/ucenter/ucenter""github.com/jinzhu/copier""github.com/zeromicro/go-zero/core/logx")type LoginUserLogic struct {ctx context.ContextsvcCtx *svc.ServiceContextlogx.Logger}func NewLoginUserLogic(ctx context.Context, svcCtx *svc.ServiceContext) *LoginUserLogic {return &LoginUserLogic{ctx: ctx,svcCtx: svcCtx,Logger: logx.WithContext(ctx),}}// LoginUser 用户登录func (l *LoginUserLogic) LoginUser(in *ucenter.User) (*ucenter.UserLoginResp, error) {param := &sqlc_usermodel.ZeroUsers{Id: 1, //测试缓存Account: in.Account,}dbRes, err := l.svcCtx.SqlcUsersModel.FindOneByParamCtx(l.ctx, param)if err != nil {logx.Error(err)errInfo := fmt.Sprintf("LoginUser:FindOneByParam:db err:%v , in : %+v", err, in)return nil, errors.New(errInfo)}if utils.ComparePassword(in.Password, dbRes.Password) {copier.Copy(in, dbRes)return l.LoginSuccess(in)} else {errInfo := fmt.Sprintf("LoginUser:user password error:in : %+v", in)return nil, errors.New(errInfo)}}func (l *LoginUserLogic) LoginSuccess(in *ucenter.User) (*ucenter.UserLoginResp, error) {AccessSecret := l.svcCtx.Config.JWT.AccessSecretAccessExpire := l.svcCtx.Config.JWT.AccessExpirenow := time.Now().Unix()jwtToken, err := utils.GenerateJwtToken(AccessSecret, now, AccessExpire, in.Id)if err != nil {return nil, err}resp := &ucenter.UserLoginResp{}copier.Copy(resp, in)resp.AccessToken = jwtTokenresp.AccessExpire = now + AccessExpireresp.RefreshAfter = now + AccessExpire/2return resp, nil}（2）internal/logic/ucentersqlx/adduserlogic.go
package ucentersqlxlogicimport ("context""github.com/jinzhu/copier""github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/common/errorx""go-zero-micro/common/utils"sqlc_usermodel "go-zero-micro/rpc/database/sqlc/usermodel""time""go-zero-micro/rpc/code/ucenter/internal/svc""go-zero-micro/rpc/code/ucenter/ucenter""github.com/zeromicro/go-zero/core/logx")type AddUserLogic struct {ctx context.ContextsvcCtx *svc.ServiceContextlogx.Logger}func NewAddUserLogic(ctx context.Context, svcCtx *svc.ServiceContext) *AddUserLogic {return &AddUserLogic{ctx: ctx,svcCtx: svcCtx,Logger: logx.WithContext(ctx),}}// AddUser 添加用户func (l *AddUserLogic) AddUser(in *ucenter.User) (*ucenter.BaseResp, error) {userId := utils.GetUidFromCtxInt64(l.ctx, "userId")currentTime := time.Now()var InsertUserId int64//将对主子表的操作全部放到同一个事务中，每一步操作有错误就返回错误，没有错误最后就返回nil，事务遇到错误会回滚；if err := l.svcCtx.SqlcUsersModel.TransCtx(l.ctx, func(context context.Context, session sqlx.Session) error {userParam := &sqlc_usermodel.ZeroUsers{}copier.Copy(userParam, in)userParam.Password = utils.GeneratePassword(l.svcCtx.Config.DefaultConfig.DefaultPassword)userParam.CreatedBy = userIduserParam.CreatedAt = currentTimedbUserRes, err := l.svcCtx.SqlcUsersModel.TransSaveCtx(l.ctx, session, userParam)if err != nil {return err}uid, err := dbUserRes.LastInsertId()if err != nil {return err}userInfoParam := &sqlc_usermodel.ZeroUserInfos{}copier.Copy(userInfoParam, in)userInfoParam.UserId = uiduserInfoParam.CreatedBy = userIduserInfoParam.CreatedAt = currentTime_, err = l.svcCtx.SqlcUserInfosModel.TransSaveCtx(l.ctx, session, userInfoParam)if err != nil {return err}InsertUserId = uidreturn nil}); err != nil {return nil, errorx.NewDefaultError(errorx.DbAddErrorCode)}return &ucenter.BaseResp{Id: InsertUserId,}, nil}注意：cache.CacheConf只是用于数据缓存，比redis.Redis操作的Redis数据类型少。
查询单条数据时的过程：
core/stores/sqlc/cachedsql.go// QueryRowCtx unmarshals into v with given key and query func.func (cc CachedConn) QueryRowCtx(ctx context.Context, v any, key string, query QueryCtxFn) error {return cc.cache.TakeCtx(ctx, v, key, func(v any) error {return query(ctx, cc.db, v)})}core/stores/cache/cachenode.gofunc (c cacheNode) TakeCtx(ctx context.Context, val any, key string,query func(val any) error) error {return c.doTake(ctx, val, key, query, func(v any) error {return c.SetCtx(ctx, key, v)})}core/stores/cache/cachenode.go重点分析doTake方法，看里面的注释即可。func (c cacheNode) doTake(ctx context.Context, v any, key string,query func(v any) error, cacheVal func(v any) error) error {logger := logx.WithContext(ctx)val, fresh, err := c.barrier.DoEx(key, func() (any, error) {//先从Redis缓存中取指定key的数据：if err := c.doGetCache(ctx, key, v); err != nil {//1、如果返回的错误类型是 errPlaceholder，说明Redis缓存中指定key的值是*，因为一开始从数据库中查询不到指定数据时会在Redis缓存中将指定key的值设置为*，时间是1分钟（防止雪崩击穿）；if err == errPlaceholder {return nil, c.errNotFound//2、如果返回的错误类型不等于errNotFound，则说明可能是Redis出现了故障；} else if err != c.errNotFound {// why we just return the error instead of query from db,// because we don't allow the disaster pass to the dbs.// fail fast, in case we bring down the dbs.return nil, err}//3、走到这里说明错误类型是errNotFound，则说明Redis中没有指定key的数据，这时则需要根据传入的参数方法从数据库中查询指定数据if err = query(v); err == c.errNotFound {//4、如果从数据库中查询指定数据也还是空，则在Redis缓存中将指定key的值设置为*，时间是1分钟（防止雪崩击穿）if err = c.setCacheWithNotFound(ctx, key); err != nil {logger.Error(err)}return nil, c.errNotFound} else if err != nil {c.stat.IncrementDbFails()return nil, err}//5、如果有数据，则缓存到Redis中if err = cacheVal(v); err != nil {logger.Error(err)}}//6、返回查询结果return JSONx.Marshal(v)})if err != nil {return err}if fresh {return nil}// got the result from previous ongoing query.// why not call IncrementTotal at the beginning of this function?// because a shared error is returned, and we don't want to count.// for example, if the db is down, the query will be failed, we count// the shared errors with one db failure.c.stat.IncrementTotal()c.stat.IncrementHit()return jsonx.Unmarshal(val.([]byte), v)}修改、删除单条数据时的过程：
core/stores/sqlc/cachedsql.go// ExecCtx runs given exec on given keys, and returns execution result.func (cc CachedConn) ExecCtx(ctx context.Context, exec ExecCtxFn, keys ...string) (sql.Result, error) {res, err := exec(ctx, cc.db)if err != nil {return nil, err}if err := cc.DelCacheCtx(ctx, keys...); err != nil {return nil, err}return res, nil}core/stores/sqlc/cachedsql.gokey的缓存数据删除了。// DelCacheCtx deletes cache with keys.func (cc CachedConn) DelCacheCtx(ctx context.Context, keys ...string) error {return cc.cache.DelCtx(ctx, keys...)}总结：通过简单分析源码可以得出缓存的具体使用过程，其中设置指定key的数据为*的目的是防止雪崩击穿。
key的数据是*，则说明前面已经查询过数据库了，结果是数据库中没有数据，所以暂时不用马上再次查询数据库；Redis节点故障；key的数据，则需要查询一次数据库；Redis缓存中将指定key的数据设置为*，时长自定义。Redis缓存中将指定key的数据设置为数据库查到的数据，时长自定义，同时将查询到的结果返回。本次示例代码
RPC服务：
database/gorm/usermodel/gorm_zero_models.go添加数据表结构对应的结构体package usermodelimport ("database/sql""time")type (ZeroUsers struct {Id int64 // idAccount string // 账号Username string // 用户名Password string // 密码Gender int64 // 性别 1：未设置；2：男性；3：女性UpdatedBy int64 // 更新人UpdatedAt time.Time // 更新时间CreatedBy int64 // 创建人CreatedAt time.Time // 创建时间DeletedAt sql.NullTime // 删除时间DeletedFlag int64 // 是否删除 1：正常 2：已删除}ZeroUserInfos struct {Id int64 // idUserId int64 // 用户idEmail string // 邮箱Phone string // 手机号UpdatedBy int64 // 更新人UpdatedAt time.Time // 更新时间CreatedBy int64 // 创建人CreatedAt time.Time // 创建时间DeletedAt sql.NullTime // 删除时间DeletedFlag int64 // 是否删除 1：正常 2：已删除})internal/svc/servicecontext.go中创建gorm连接package svcimport ("fmt""github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/rpc/code/ucenter/internal/config"sqlc_usermodel "go-zero-micro/rpc/database/sqlc/usermodel"sqlx_usermodel "go-zero-micro/rpc/database/sqlx/usermodel""gorm.io/driver/mysql""gorm.io/gorm""gorm.io/gorm/schema")type ServiceContext struct {Config config.ConfigSqlxUsersModel sqlx_usermodel.ZeroUsersModelSqlxUserInfosModel sqlx_usermodel.ZeroUserInfosModelSqlcUsersModel sqlc_usermodel.ZeroUsersModelSqlcUserInfosModel sqlc_usermodel.ZeroUserInfosModelGormDb *gorm.DB}func NewServiceContext(c config.Config) *ServiceContext {mysqlConn := sqlx.NewMysql(c.MySQL.DataSource)gormDb, err := gorm.Open(mysql.Open(c.MySQL.DataSource), &gorm.Config{NamingStrategy: schema.NamingStrategy{//TablePrefix: "tech_", // 表名前缀，`User` 的表名应该是 `t_users`//SingularTable: true, // 使用单数表名，启用该选项，此时，`User` 的表名应该是 `t_user`},})if err != nil {errInfo := fmt.Sprintf("Gorm connect database err:%v", err)panic(errInfo)}//自动同步更新表结构,不要建表了O(∩_∩)O哈哈~//db.AutoMigrate(&models.User{})return &ServiceContext{Config: c,SqlxUsersModel: sqlx_usermodel.NewZeroUsersModel(mysqlConn),SqlxUserInfosModel: sqlx_usermodel.NewZeroUserInfosModel(mysqlConn),SqlcUsersModel: sqlc_usermodel.NewZeroUsersModel(mysqlConn, c.CacheRedis),SqlcUserInfosModel: sqlc_usermodel.NewZeroUserInfosModel(mysqlConn, c.CacheRedis),GormDb: gormDb,}}internal/logic/ucentergorm/loginuserlogic.go中使用package ucentergormlogicimport ("context""errors""fmt""github.com/jinzhu/copier""go-zero-micro/common/utils"gorm_usermodel "go-zero-micro/rpc/database/gorm/usermodel""time""go-zero-micro/rpc/code/ucenter/internal/svc""go-zero-micro/rpc/code/ucenter/ucenter""github.com/zeromicro/go-zero/core/logx")type LoginUserLogic struct {ctx context.ContextsvcCtx *svc.ServiceContextlogx.Logger}func NewLoginUserLogic(ctx context.Context, svcCtx *svc.ServiceContext) *LoginUserLogic {return &LoginUserLogic{ctx: ctx,svcCtx: svcCtx,Logger: logx.WithContext(ctx),}}// LoginUser 用户登录func (l *LoginUserLogic) LoginUser(in *ucenter.User) (*ucenter.UserLoginResp, error) {param := &gorm_usermodel.ZeroUsers{Id: in.Id,Account: in.Account,}dbRes := &gorm_usermodel.ZeroUsers{}l.svcCtx.GormDb.Where(param).First(dbRes)if utils.ComparePassword(in.Password, dbRes.Password) {copier.Copy(in, dbRes)return l.LoginSuccess(in)} else {errInfo := fmt.Sprintf("LoginUser:user password error:in : %+v", in)return nil, errors.New(errInfo)}}func (l *LoginUserLogic) LoginSuccess(in *ucenter.User) (*ucenter.UserLoginResp, error) {AccessSecret := l.svcCtx.Config.JWT.AccessSecretAccessExpire := l.svcCtx.Config.JWT.AccessExpirenow := time.Now().Unix()jwtToken, err := utils.GenerateJwtToken(AccessSecret, now, AccessExpire, in.Id)if err != nil {return nil, err}resp := &ucenter.UserLoginResp{}copier.Copy(resp, in)resp.AccessToken = jwtTokenresp.AccessExpire = now + AccessExpireresp.RefreshAfter = now + AccessExpire/2return resp, nil}API服务：
internal/logic/login/loginbypasswordlogic.go中将UcenterSqlxRpc替换为UcenterGormRpc即可。gorm本身不支持缓存，如果想使用缓存的话，可以参考sqlc中是如何使用缓存的。
sqlx切换成gorm的流程：
（1）sqlx 切成gorm，同时结合sqlc；这种骚操作怎么改的，看到网上有同学这样干。
（2）只需要把带缓存生成的model中，sqlx执行db部分换成gorm即可。
（3）替换后不影响go-zero 中封装的数据库分布式事务，因为dtm支持gorm，可以看dtm官网。
前面是只把Redis作为缓存使用，而且缓存提供的方法也并不多，所以需要另外的Redis连接，提供更多样的操作方法。
参考1：Redis 连接
注意：Redis中DB 选择，go-zero仅支持 db0, 不支持 db 的选择。 如果存在通过 db 区分不同的业务场景，建议使用多个 redis 实例进行管理。
RPC的yaml配置文件里加入Redis配置# 核心配置Redis: Host: 127.0.0.1:6379 Type: node Pass: "" Key: rpc-ucenterRPC的config.go映射类中映射zrpc.RpcServerConf中已经封装好了，这里就不需要再进行配置了。// A RpcServerConf is a rpc server config.RpcServerConf struct {service.ServiceConfListenOn stringEtcd discov.EtcdConf `json:",optional,inherit"`Auth bool `json:",optional"`Redis redis.RedisKeyConf `json:",optional"`StrictControl bool `json:",optional"`// setting 0 means no timeoutTimeout int64 `json:",default=2000"`CpuThreshold int64 `json:",default=900,range=[0:1000]"`// grpc health check switchHealth bool `json:",default=true"`Middlewares ServerMiddlewaresConf}internal/svc/servicecontext.go中创建Redis连接package svcimport ("fmt""github.com/zeromicro/go-zero/core/stores/redis""github.com/zeromicro/go-zero/core/stores/sqlx""go-zero-micro/rpc/code/ucenter/internal/config"sqlc_usermodel "go-zero-micro/rpc/database/sqlc/usermodel"sqlx_usermodel "go-zero-micro/rpc/database/sqlx/usermodel""gorm.io/driver/mysql""gorm.io/gorm""gorm.io/gorm/schema")type ServiceContext struct {Config config.ConfigRedisClient *redis.RedisSqlxUsersModel sqlx_usermodel.ZeroUsersModelSqlxUserInfosModel sqlx_usermodel.ZeroUserInfosModelSqlcUsersModel sqlc_usermodel.ZeroUsersModelSqlcUserInfosModel sqlc_usermodel.ZeroUserInfosModelGormDb *gorm.DB}func NewServiceContext(c config.Config) *ServiceContext {mysqlConn := sqlx.NewMysql(c.MySQL.DataSource)gormDb, err := gorm.Open(mysql.Open(c.MySQL.DataSource), &gorm.Config{NamingStrategy: schema.NamingStrategy{//TablePrefix: "tech_", // 表名前缀，`User` 的表名应该是 `t_users`//SingularTable: true, // 使用单数表名，启用该选项，此时，`User` 的表名应该是 `t_user`},})if err != nil {errInfo := fmt.Sprintf("Gorm connect database err:%v", err)panic(errInfo)}//自动同步更新表结构,不要建表了O(∩_∩)O哈哈~//db.AutoMigrate(&models.User{})redisConn := redis.New(c.Redis.Host, func(r *redis.Redis) {r.Type = c.Redis.Typer.Pass = c.Redis.Pass})return &ServiceContext{Config: c,RedisClient: redisConn,SqlxUsersModel: sqlx_usermodel.NewZeroUsersModel(mysqlConn),SqlxUserInfosModel: sqlx_usermodel.NewZeroUserInfosModel(mysqlConn),SqlcUsersModel: sqlc_usermodel.NewZeroUsersModel(mysqlConn, c.CacheRedis),SqlcUserInfosModel: sqlc_usermodel.NewZeroUserInfosModel(mysqlConn, c.CacheRedis),GormDb: gormDb,}}xxxlogic.go中使用来源地址：https://blog.csdn.net/Mr_XiMu/article/details/131658247
--结束END--
本文标题: go-zero学习 第四章 数据库操作（MySQL）
本文链接: https://lsjlt.com/news/440564.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
2024-10-23
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
2024-10-22
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
官方手机版
微信公众号
商务合作
0