mysql 代码生成

MySQL 用法## 概述

mysql 代码生成支持从 sql 文件和数据库链接生成, 且支持生成带缓存逻辑代码。

mysql 生成的代码内容有数据表对应的 golang 结构体、CURD 操作方法,缓存逻辑等信息,更多详细的数据库代码生成可参考指南 goctl model

任务目标

  1. 熟悉 goctl 生成 mysql 代码的命令使用,了解目前支持的指令和功能
  2. 初步了解 goctl 生成 mysql 代码的格式
  3. 初步掌握从 sql 文件编写到 mysql 代码的生成流程

准备条件

  1. 完成 golang 安装
  2. 完成 goctl 安装

代码生成

  1. 首先执行如下指令将示例 sql 文件存储到本地的 user.sql 文件中。

    1. CREATE TABLE user (
    2. id bigint AUTO_INCREMENT,
    3. name varchar(255) NULL COMMENT 'The username',
    4. password varchar(255) NOT NULL DEFAULT '' COMMENT 'The user password',
    5. mobile varchar(255) NOT NULL DEFAULT '' COMMENT 'The mobile phone number',
    6. gender char(10) NOT NULL DEFAULT 'male' COMMENT 'gender,male|female|unknown',
    7. nickname varchar(255) NULL DEFAULT '' COMMENT 'The nickname',
    8. type tinyint(1) NULL DEFAULT 0 COMMENT 'The user type, 0:normal,1:vip, for test golang keyword',
    9. create_at timestamp NULL,
    10. update_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    11. UNIQUE mobile_index (mobile),
    12. UNIQUE name_index (name),
    13. PRIMARY KEY (id)
    14. ) ENGINE = InnoDB COLLATE utf8mb4_general_ci COMMENT 'user table';
  2. 新建工作空间和目录工程

    1. $ mkdir -p ~/workspace/model/mysql
  3. 将上文中存储的 user.sql 文件移动到 ~/workspace/model/mysql 目录下

  4. 生成 model 代码

    1. $ cd ~/workspace/model/mysql
    2. $ goctl model mysql ddl --src user.sql --dir .
    3. Done.

    当你看到 Done. 输出则代表生成成功了,接下来我们来看一下生成的代码内容:

    1. # 列出当前目录下的文件
    2. $ ls
    3. user.sql usermodel.go usermodel_gen.go vars.go
    4. # 查看目录树
    5. $ tree
    6. .
    7. ├── user.sql
    8. ├── usermodel.go
    9. ├── usermodel_gen.go
    10. └── vars.go
    11. 0 directories, 4 files
  5. 代码查看

    • user.sql
    • usermodel.go
    • usermodel_gen.go
    • vars.go
    1. CREATE TABLE user (
    2. id bigint AUTO_INCREMENT,
    3. name varchar(255) NULL COMMENT 'The username',
    4. password varchar(255) NOT NULL DEFAULT '' COMMENT 'The user password',
    5. mobile varchar(255) NOT NULL DEFAULT '' COMMENT 'The mobile phone number',
    6. gender char(10) NOT NULL DEFAULT 'male' COMMENT 'gender,male|female|unknown',
    7. nickname varchar(255) NULL DEFAULT '' COMMENT 'The nickname',
    8. type tinyint(1) NULL DEFAULT 0 COMMENT 'The user type, 0:normal,1:vip, for test golang keyword',
    9. create_at timestamp NULL,
    10. update_at timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    11. UNIQUE mobile_index (mobile),
    12. UNIQUE name_index (name),
    13. PRIMARY KEY (id)
    14. ) ENGINE = InnoDB COLLATE utf8mb4_general_ci COMMENT 'user table';
    1. package mysql
    2. import "github.com/zeromicro/go-zero/core/stores/sqlx"
    3. var _ UserModel = (*customUserModel)(nil)
    4. type (
    5. // UserModel is an interface to be customized, add more methods here,
    6. // and implement the added methods in customUserModel.
    7. UserModel interface {
    8. userModel
    9. }
    10. customUserModel struct {
    11. *defaultUserModel
    12. }
    13. )
    14. // NewUserModel returns a model for the database table.
    15. func NewUserModel(conn sqlx.SqlConn) UserModel {
    16. return &customUserModel{
    17. defaultUserModel: newUserModel(conn),
    18. }
    19. }
    1. // Code generated by goctl. DO NOT EDIT.
    2. package mysql
    3. import (
    4. "context"
    5. "database/sql"
    6. "fmt"
    7. "strings"
    8. "time"
    9. "github.com/zeromicro/go-zero/core/stores/builder"
    10. "github.com/zeromicro/go-zero/core/stores/sqlc"
    11. "github.com/zeromicro/go-zero/core/stores/sqlx"
    12. "github.com/zeromicro/go-zero/core/stringx"
    13. )
    14. var (
    15. userFieldNames = builder.RawFieldNames(&User{})
    16. userRows = strings.Join(userFieldNames, ",")
    17. userRowsExpectAutoSet = strings.Join(stringx.Remove(userFieldNames, "`id`", "`update_time`", "`create_at`", "`created_at`", "`create_time`", "`update_at`", "`updated_at`"), ",")
    18. userRowsWithPlaceHolder = strings.Join(stringx.Remove(userFieldNames, "`id`", "`update_time`", "`create_at`", "`created_at`", "`create_time`", "`update_at`", "`updated_at`"), "=?,") + "=?"
    19. )
    20. type (
    21. userModel interface {
    22. Insert(ctx context.Context, data *User) (sql.Result, error)
    23. FindOne(ctx context.Context, id int64) (*User, error)
    24. FindOneByMobile(ctx context.Context, mobile string) (*User, error)
    25. FindOneByName(ctx context.Context, name sql.NullString) (*User, error)
    26. Update(ctx context.Context, data *User) error
    27. Delete(ctx context.Context, id int64) error
    28. }
    29. defaultUserModel struct {
    30. conn sqlx.SqlConn
    31. table string
    32. }
    33. User struct {
    34. Id int64 `db:"id"`
    35. Name sql.NullString `db:"name"` // The username
    36. Password string `db:"password"` // The user password
    37. Mobile string `db:"mobile"` // The mobile phone number
    38. Gender string `db:"gender"` // gender,male|female|unknown
    39. Nickname string `db:"nickname"` // The nickname
    40. Type int64 `db:"type"` // The user type, 0:normal,1:vip, for test golang keyword
    41. CreateAt sql.NullTime `db:"create_at"`
    42. UpdateAt time.Time `db:"update_at"`
    43. }
    44. )
    45. func newUserModel(conn sqlx.SqlConn) *defaultUserModel {
    46. return &defaultUserModel{
    47. conn: conn,
    48. table: "`user`",
    49. }
    50. }
    51. func (m *defaultUserModel) Delete(ctx context.Context, id int64) error {
    52. query := fmt.Sprintf("delete from %s where `id` = ?", m.table)
    53. _, err := m.conn.ExecCtx(ctx, query, id)
    54. return err
    55. }
    56. func (m *defaultUserModel) FindOne(ctx context.Context, id int64) (*User, error) {
    57. query := fmt.Sprintf("select %s from %s where `id` = ? limit 1", userRows, m.table)
    58. var resp User
    59. err := m.conn.QueryRowCtx(ctx, &resp, query, id)
    60. switch err {
    61. case nil:
    62. return &resp, nil
    63. case sqlc.ErrNotFound:
    64. return nil, ErrNotFound
    65. default:
    66. return nil, err
    67. }
    68. }
    69. func (m *defaultUserModel) FindOneByMobile(ctx context.Context, mobile string) (*User, error) {
    70. var resp User
    71. query := fmt.Sprintf("select %s from %s where `mobile` = ? limit 1", userRows, m.table)
    72. err := m.conn.QueryRowCtx(ctx, &resp, query, mobile)
    73. switch err {
    74. case nil:
    75. return &resp, nil
    76. case sqlc.ErrNotFound:
    77. return nil, ErrNotFound
    78. default:
    79. return nil, err
    80. }
    81. }
    82. func (m *defaultUserModel) FindOneByName(ctx context.Context, name sql.NullString) (*User, error) {
    83. var resp User
    84. query := fmt.Sprintf("select %s from %s where `name` = ? limit 1", userRows, m.table)
    85. err := m.conn.QueryRowCtx(ctx, &resp, query, name)
    86. switch err {
    87. case nil:
    88. return &resp, nil
    89. case sqlc.ErrNotFound:
    90. return nil, ErrNotFound
    91. default:
    92. return nil, err
    93. }
    94. }
    95. func (m *defaultUserModel) Insert(ctx context.Context, data *User) (sql.Result, error) {
    96. query := fmt.Sprintf("insert into %s (%s) values (?, ?, ?, ?, ?, ?)", m.table, userRowsExpectAutoSet)
    97. ret, err := m.conn.ExecCtx(ctx, query, data.Name, data.Password, data.Mobile, data.Gender, data.Nickname, data.Type)
    98. return ret, err
    99. }
    100. func (m *defaultUserModel) Update(ctx context.Context, newData *User) error {
    101. query := fmt.Sprintf("update %s set %s where `id` = ?", m.table, userRowsWithPlaceHolder)
    102. _, err := m.conn.ExecCtx(ctx, query, newData.Name, newData.Password, newData.Mobile, newData.Gender, newData.Nickname, newData.Type, newData.Id)
    103. return err
    104. }
    105. func (m *defaultUserModel) tableName() string {
    106. return m.table
    107. }
    1. package mysql
    2. import "github.com/zeromicro/go-zero/core/stores/sqlx"
    3. var ErrNotFound = sqlx.ErrNotFound