Mysql

[!TIP] This document is machine-translated by Google. If you find grammatical and semantic errors, and the document description is not clear, please PR

go-zero provides easier operation of mysql API.

[!TIP] But stores/mysql positioning is not an orm framework. If you need to generate model layer code through sql/scheme -> model/struct reverse engineering, developers can use goctl model, this is an excellent feature.

Features

  • Provides a more developer-friendly API compared to native
  • Complete the automatic assignment of queryField -> struct
  • Insert “bulkinserter” in batches
  • Comes with fuse
  • API has been continuously tested by several services
  • Provide partial assignment feature, do not force strict assignment of struct

Connection

Let’s use an example to briefly explain how to create a mysql connected model:

  1. // 1. Quickly connect to a mysql
  2. // datasource: mysql dsn
  3. heraMysql := sqlx.NewMysql(datasource)
  5. // 2. Call in the `servicecontext`, understand the logic layer call of the model upper layer
  6. model.NewMysqlModel(heraMysql, tablename),
  8. // 3. model layer mysql operation
  9. func NewMysqlModel(conn sqlx.SqlConn, table string) *MysqlModel {
  10.     defer func() {
  11.         recover()
  12.     }()
  13.     // 4. Create a batch insert [mysql executor]
  14.     // conn: mysql connection; insertsql: mysql insert sql
  15.     bulkInserter , err := sqlx.NewBulkInserter(conn, insertsql)
  16.     if err != nil {
  17.         logx.Error("Init bulkInsert Faild")
  18.         panic("Init bulkInsert Faild")
  19.         return nil
  20.     }
  21.     return &MysqlModel{conn: conn, table: table, Bulk: bulkInserter}
  22. }

CRUD

Prepare an User model

  1. var userBuilderQueryRows = strings.Join(builderx.FieldNames(&User{}), ",")
  3. type User struct {
  4.     Avatar string `db:"avatar"`             
  5.     UserName string `db:"user_name"`         
  6.     Sex int `db:"sex"`                         
  7.     MobilePhone string `db:"mobile_phone"`     
  8. }

Among them, userBuilderQueryRows: go-zero provides struct -> [field...] conversion. Developers can use this as a template directly.

insert

  1. // An actual insert model layer operation
  2. func (um *UserModel) Insert(user *User) (int64, error) {
  3.     const insertsql = `insert into `+um.table+` (`+userBuilderQueryRows+`) values(?, ?, ?)`
  4.     // insert op
  5.     res, err := um.conn.Exec(insertsql, user.Avatar, user.UserName, user.Sex, user.MobilePhone)
  6.     if err != nil {
  7.         logx.Errorf("insert User Position Model Model err, err=%v", err)
  8.         return -1, err
  9.     }
  10.     id, err := res.LastInsertId()
  11.     if err != nil {
  12.         logx.Errorf("insert User Model to Id  parse id err,err=%v", err)
  13.         return -1, err
  14.     }
  15.     return id, nil
  16. }
  • Splicing insertsql
  • Pass in insertsql and the struct field corresponding to the placeholder -> con.Exex(insertsql, field...)

[!WARNING] conn.Exec(sql, args...): args... needs to correspond to the placeholder in sql. Otherwise, there will be problems with assignment exceptions.

go-zero unified and abstracted operations involving mysql modification as Exec(). So the insert/update/delete operations are essentially the same. For the remaining two operations, the developer can try the above insert process.

query

You only need to pass in the querysql and model structure, and you can get the assigned model. No need for developers to manually assign values.

  1. func (um *UserModel) FindOne(uid int64) (*User, error) {
  2.     var user User
  3.     const querysql = `select `+userBuilderQueryRows+` from `+um.table+` where id=? limit 1`
  4.     err := um.conn.QueryRow(&user, querysql, uid)
  5.     if err != nil {
  6.         logx.Errorf("userId.findOne error, id=%d, err=%s", uid, err.Error())
  7.         if err == sqlx.ErrNotFound {
  8.             return nil, ErrNotFound
  9.         }
  10.         return nil, err
  11.     }
  12.     return &user, nil
  13. }
  • Declare model struct, splicing querysql
  • conn.QueryRow(&model, querysql, args...): args... corresponds to the placeholder in querysql.

[!WARNING] The first parameter in QueryRow() needs to be passed in Ptr “The bottom layer needs to be reflected to assign a value to struct“

The above is to query one record, if you need to query multiple records, you can use conn.QueryRows()

  1. func (um *UserModel) FindOne(sex int) ([]*User, error) {
  2.     users := make([]*User, 0)
  3.     const querysql = `select `+userBuilderQueryRows+` from `+um.table+` where sex=?`
  4.     err := um.conn.QueryRows(&users, querysql, sex)
  5.     if err != nil {
  6.         logx.Errorf("usersSex.findOne error, sex=%d, err=%s", uid, err.Error())
  7.         if err == sqlx.ErrNotFound {
  8.             return nil, ErrNotFound
  9.         }
  10.         return nil, err
  11.     }
  12.     return users, nil
  13. }

The difference from QueryRow() is: model needs to be set to Slice, because it is to query multiple rows, and multiple models need to be assigned. But at the same time you need to pay attention to ️: the first parameter needs to be passed in Ptr

querypartial

In terms of use, it is no different from the above-mentioned QueryRow(), “this reflects the highly abstract design of go-zero.”

the difference:

  • QueryRow(): len(querysql fields) == len(struct), and one-to-one correspondence
  • QueryRowPartial() ：len(querysql fields) <= len(struct)

numA: Number of database fields; numB: the number of defined struct attributes. If numA <numB, but you just need to unify multiple queries, “multiple struct is defined to return different purposes, and all of them can use the same querysql“, you can use QueryRowPartial()

Transaction

To perform a series of operations in a transaction, the general process is as follows:

  1. var insertsql = `insert into User(uid, username, mobilephone) values (?, ?, ?)`
  2. err := usermodel.conn.Transact(func(session sqlx.Session) error {
  3.     stmt, err := session.Prepare(insertsql)
  4.     if err != nil {
  5.         return err
  6.     }
  7.     defer stmt.Close()
  9.     // Any error returned will roll back the transaction
  10.     if _, err := stmt.Exec(uid, username, mobilephone); err != nil {
  11.         logx.Errorf("insert userinfo stmt exec: %s", err)
  12.         return err
  13.     }
  15.     // You can also continue to perform insert/update/delete related operations
  16.     return nil
  17. })

As in the above example, developers only need to wrap all operations in transaction in a function func(session sqlx.Session) error {}, if the operation in the transaction returns any error, Transact( ) will automatically roll back the transaction.