TiDB 和 Golang 的简单 CRUD 应用程序

本文档将展示如何使用 TiDB 和 Golang 来构造一个简单的 CRUD 应用程序。

Golang - 图1

注意

推荐使用 Golang 1.16 以上版本进行 TiDB 的应用程序的编写。

第 1 步:启动你的 TiDB 集群

本节将介绍 TiDB 集群的启动方法。

  • TiDB Cloud
  • 本地集群
  • Gitpod

创建 TiDB Serverless 集群

你可以部署一个本地测试的 TiDB 集群或正式的 TiDB 集群。详细步骤,请参考:

基于 Git 的预配置的开发环境:现在就试试

该环境会自动克隆代码,并通过 TiUP 部署测试集群。

第 2 步:获取代码

  1. git clone https://github.com/pingcap-inc/tidb-example-golang.git
  • 使用 GORM(推荐)
  • 使用 go-sql-driver/mysql

当前开源比较流行的 Golang ORM 为 GORM,此处将以 v1.23.5 版本进行说明。

封装一个用于适配 TiDB 事务的工具包 util,编写以下代码备用:

  1. package util
  2. import (
  3. "gorm.io/gorm"
  4. )
  5. // TiDBGormBegin start a TiDB and Gorm transaction as a block. If no error is returned, the transaction will be committed. Otherwise, the transaction will be rolled back.
  6. func TiDBGormBegin(db *gorm.DB, pessimistic bool, fc func(tx *gorm.DB) error) (err error) {
  7. session := db.Session(&gorm.Session{})
  8. if session.Error != nil {
  9. return session.Error
  10. }
  11. if pessimistic {
  12. session = session.Exec("set @@tidb_txn_mode=pessimistic")
  13. } else {
  14. session = session.Exec("set @@tidb_txn_mode=optimistic")
  15. }
  16. if session.Error != nil {
  17. return session.Error
  18. }
  19. return session.Transaction(fc)
  20. }

进入目录 gorm

  1. cd gorm

目录结构如下所示:

  1. .
  2. ├── Makefile
  3. ├── go.mod
  4. ├── go.sum
  5. └── gorm.go

其中,gorm.gogorm 这个示例程序的主体。使用 gorm 时,相较于 go-sql-driver/mysql,gorm 屏蔽了创建数据库连接时,不同数据库差异的细节,其还封装了大量的操作,如 AutoMigrate、基本对象的 CRUD 等,极大的简化了代码量。

Player 是数据结构体,为数据库表在程序内的映射。Player 的每个属性都对应着 player 表的一个字段。相较于 go-sql-driver/mysql,gorm 的 Player 数据结构体为了给 gorm 提供更多的信息,加入了形如 `gorm:"primaryKey;type:VARCHAR(36);column:id"` 的注解,用来指示映射关系。

  1. package main
  2. import (
  3. "fmt"
  4. "math/rand"
  5. "github.com/google/uuid"
  6. "github.com/pingcap-inc/tidb-example-golang/util"
  7. "gorm.io/driver/mysql"
  8. "gorm.io/gorm"
  9. "gorm.io/gorm/clause"
  10. "gorm.io/gorm/logger"
  11. )
  12. type Player struct {
  13. ID string `gorm:"primaryKey;type:VARCHAR(36);column:id"`
  14. Coins int `gorm:"column:coins"`
  15. Goods int `gorm:"column:goods"`
  16. }
  17. func (*Player) TableName() string {
  18. return "player"
  19. }
  20. func main() {
  21. // 1. Configure the example database connection.
  22. db := createDB()
  23. // AutoMigrate for player table
  24. db.AutoMigrate(&Player{})
  25. // 2. Run some simple examples.
  26. simpleExample(db)
  27. // 3. Explore more.
  28. tradeExample(db)
  29. }
  30. func tradeExample(db *gorm.DB) {
  31. // Player 1: id is "1", has only 100 coins.
  32. // Player 2: id is "2", has 114514 coins, and 20 goods.
  33. player1 := &Player{ID: "1", Coins: 100}
  34. player2 := &Player{ID: "2", Coins: 114514, Goods: 20}
  35. // Create two players "by hand", using the INSERT statement on the backend.
  36. db.Clauses(clause.OnConflict{UpdateAll: true}).Create(player1)
  37. db.Clauses(clause.OnConflict{UpdateAll: true}).Create(player2)
  38. // Player 1 wants to buy 10 goods from player 2.
  39. // It will cost 500 coins, but player 1 cannot afford it.
  40. fmt.Println("\nbuyGoods:\n => this trade will fail")
  41. if err := buyGoods(db, player2.ID, player1.ID, 10, 500); err == nil {
  42. panic("there shouldn't be success")
  43. }
  44. // So player 1 has to reduce the incoming quantity to two.
  45. fmt.Println("\nbuyGoods:\n => this trade will success")
  46. if err := buyGoods(db, player2.ID, player1.ID, 2, 100); err != nil {
  47. panic(err)
  48. }
  49. }
  50. func simpleExample(db *gorm.DB) {
  51. // Create a player, who has a coin and a goods..
  52. if err := db.Clauses(clause.OnConflict{UpdateAll: true}).
  53. Create(&Player{ID: "test", Coins: 1, Goods: 1}).Error; err != nil {
  54. panic(err)
  55. }
  56. // Get a player.
  57. var testPlayer Player
  58. db.Find(&testPlayer, "id = ?", "test")
  59. fmt.Printf("getPlayer: %+v\n", testPlayer)
  60. // Create players with bulk inserts. Insert 1919 players totally, with 114 players per batch.
  61. bulkInsertPlayers := make([]Player, 1919, 1919)
  62. total, batch := 1919, 114
  63. for i := 0; i < total; i++ {
  64. bulkInsertPlayers[i] = Player{
  65. ID: uuid.New().String(),
  66. Coins: rand.Intn(10000),
  67. Goods: rand.Intn(10000),
  68. }
  69. }
  70. if err := db.Session(&gorm.Session{Logger: db.Logger.LogMode(logger.Error)}).
  71. CreateInBatches(bulkInsertPlayers, batch).Error; err != nil {
  72. panic(err)
  73. }
  74. // Count players amount.
  75. playersCount := int64(0)
  76. db.Model(&Player{}).Count(&playersCount)
  77. fmt.Printf("countPlayers: %d\n", playersCount)
  78. // Print 3 players.
  79. threePlayers := make([]Player, 3, 3)
  80. db.Limit(3).Find(&threePlayers)
  81. for index, player := range threePlayers {
  82. fmt.Printf("print %d player: %+v\n", index+1, player)
  83. }
  84. }
  85. func createDB() *gorm.DB {
  86. dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8mb4"
  87. db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
  88. Logger: logger.Default.LogMode(logger.Info),
  89. })
  90. if err != nil {
  91. panic(err)
  92. }
  93. return db
  94. }
  95. func buyGoods(db *gorm.DB, sellID, buyID string, amount, price int) error {
  96. return util.TiDBGormBegin(db, true, func(tx *gorm.DB) error {
  97. var sellPlayer, buyPlayer Player
  98. if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
  99. Find(&sellPlayer, "id = ?", sellID).Error; err != nil {
  100. return err
  101. }
  102. if sellPlayer.ID != sellID || sellPlayer.Goods < amount {
  103. return fmt.Errorf("sell player %s goods not enough", sellID)
  104. }
  105. if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
  106. Find(&buyPlayer, "id = ?", buyID).Error; err != nil {
  107. return err
  108. }
  109. if buyPlayer.ID != buyID || buyPlayer.Coins < price {
  110. return fmt.Errorf("buy player %s coins not enough", buyID)
  111. }
  112. updateSQL := "UPDATE player set goods = goods + ?, coins = coins + ? WHERE id = ?"
  113. if err := tx.Exec(updateSQL, -amount, price, sellID).Error; err != nil {
  114. return err
  115. }
  116. if err := tx.Exec(updateSQL, amount, -price, buyID).Error; err != nil {
  117. return err
  118. }
  119. fmt.Println("\n[buyGoods]:\n 'trade success'")
  120. return nil
  121. })
  122. }

使用 go-sql-driver/mysql 时,首先进入目录 sqldriver

  1. cd sqldriver

目录结构如下所示:

  1. .
  2. ├── Makefile
  3. ├── dao.go
  4. ├── go.mod
  5. ├── go.sum
  6. ├── sql
  7. └── dbinit.sql
  8. ├── sql.go
  9. └── sqldriver.go

其中,dbinit.sql 为数据表初始化语句:

  1. USE test;
  2. DROP TABLE IF EXISTS player;
  3. CREATE TABLE player (
  4. `id` VARCHAR(36),
  5. `coins` INTEGER,
  6. `goods` INTEGER,
  7. PRIMARY KEY (`id`)
  8. );

sqldriver.gosqldriver 这个示例程序的主体。因为 TiDB 与 MySQL 协议兼容,因此,需要初始化一个 MySQL 协议的数据源 db, err := sql.Open("mysql", dsn),以此连接到 TiDB。并在其后,调用 dao.go 中的一系列方法,用来管理数据对象,进行增删改查等操作。

  1. package main
  2. import (
  3. "database/sql"
  4. "fmt"
  5. _ "github.com/go-sql-driver/mysql"
  6. )
  7. func main() {
  8. // 1. Configure the example database connection.
  9. dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8mb4"
  10. openDB("mysql", dsn, func(db *sql.DB) {
  11. // 2. Run some simple examples.
  12. simpleExample(db)
  13. // 3. Explore more.
  14. tradeExample(db)
  15. })
  16. }
  17. func simpleExample(db *sql.DB) {
  18. // Create a player, who has a coin and a goods.
  19. err := createPlayer(db, Player{ID: "test", Coins: 1, Goods: 1})
  20. if err != nil {
  21. panic(err)
  22. }
  23. // Get a player.
  24. testPlayer, err := getPlayer(db, "test")
  25. if err != nil {
  26. panic(err)
  27. }
  28. fmt.Printf("getPlayer: %+v\n", testPlayer)
  29. // Create players with bulk inserts. Insert 1919 players totally, with 114 players per batch.
  30. err = bulkInsertPlayers(db, randomPlayers(1919), 114)
  31. if err != nil {
  32. panic(err)
  33. }
  34. // Count players amount.
  35. playersCount, err := getCount(db)
  36. if err != nil {
  37. panic(err)
  38. }
  39. fmt.Printf("countPlayers: %d\n", playersCount)
  40. // Print 3 players.
  41. threePlayers, err := getPlayerByLimit(db, 3)
  42. if err != nil {
  43. panic(err)
  44. }
  45. for index, player := range threePlayers {
  46. fmt.Printf("print %d player: %+v\n", index+1, player)
  47. }
  48. }
  49. func tradeExample(db *sql.DB) {
  50. // Player 1: id is "1", has only 100 coins.
  51. // Player 2: id is "2", has 114514 coins, and 20 goods.
  52. player1 := Player{ID: "1", Coins: 100}
  53. player2 := Player{ID: "2", Coins: 114514, Goods: 20}
  54. // Create two players "by hand", using the INSERT statement on the backend.
  55. if err := createPlayer(db, player1); err != nil {
  56. panic(err)
  57. }
  58. if err := createPlayer(db, player2); err != nil {
  59. panic(err)
  60. }
  61. // Player 1 wants to buy 10 goods from player 2.
  62. // It will cost 500 coins, but player 1 cannot afford it.
  63. fmt.Println("\nbuyGoods:\n => this trade will fail")
  64. if err := buyGoods(db, player2.ID, player1.ID, 10, 500); err == nil {
  65. panic("there shouldn't be success")
  66. }
  67. // So player 1 has to reduce the incoming quantity to two.
  68. fmt.Println("\nbuyGoods:\n => this trade will success")
  69. if err := buyGoods(db, player2.ID, player1.ID, 2, 100); err != nil {
  70. panic(err)
  71. }
  72. }
  73. func openDB(driverName, dataSourceName string, runnable func(db *sql.DB)) {
  74. db, err := sql.Open(driverName, dataSourceName)
  75. if err != nil {
  76. panic(err)
  77. }
  78. defer db.Close()
  79. runnable(db)
  80. }

随后,封装一个用于适配 TiDB 事务的工具包 util,编写以下代码备用:

  1. package util
  2. import (
  3. "context"
  4. "database/sql"
  5. )
  6. type TiDBSqlTx struct {
  7. *sql.Tx
  8. conn *sql.Conn
  9. pessimistic bool
  10. }
  11. func TiDBSqlBegin(db *sql.DB, pessimistic bool) (*TiDBSqlTx, error) {
  12. ctx := context.Background()
  13. conn, err := db.Conn(ctx)
  14. if err != nil {
  15. return nil, err
  16. }
  17. if pessimistic {
  18. _, err = conn.ExecContext(ctx, "set @@tidb_txn_mode=?", "pessimistic")
  19. } else {
  20. _, err = conn.ExecContext(ctx, "set @@tidb_txn_mode=?", "optimistic")
  21. }
  22. if err != nil {
  23. return nil, err
  24. }
  25. tx, err := conn.BeginTx(ctx, nil)
  26. if err != nil {
  27. return nil, err
  28. }
  29. return &TiDBSqlTx{
  30. conn: conn,
  31. Tx: tx,
  32. pessimistic: pessimistic,
  33. }, nil
  34. }
  35. func (tx *TiDBSqlTx) Commit() error {
  36. defer tx.conn.Close()
  37. return tx.Tx.Commit()
  38. }
  39. func (tx *TiDBSqlTx) Rollback() error {
  40. defer tx.conn.Close()
  41. return tx.Tx.Rollback()
  42. }

dao.go 中定义一系列数据的操作方法,用来对提供数据的写入能力。这也是本例子中和核心部分。

  1. package main
  2. import (
  3. "database/sql"
  4. "fmt"
  5. "math/rand"
  6. "strings"
  7. "github.com/google/uuid"
  8. "github.com/pingcap-inc/tidb-example-golang/util"
  9. )
  10. type Player struct {
  11. ID string
  12. Coins int
  13. Goods int
  14. }
  15. // createPlayer create a player
  16. func createPlayer(db *sql.DB, player Player) error {
  17. _, err := db.Exec(CreatePlayerSQL, player.ID, player.Coins, player.Goods)
  18. return err
  19. }
  20. // getPlayer get a player
  21. func getPlayer(db *sql.DB, id string) (Player, error) {
  22. var player Player
  23. rows, err := db.Query(GetPlayerSQL, id)
  24. if err != nil {
  25. return player, err
  26. }
  27. defer rows.Close()
  28. if rows.Next() {
  29. err = rows.Scan(&player.ID, &player.Coins, &player.Goods)
  30. if err == nil {
  31. return player, nil
  32. } else {
  33. return player, err
  34. }
  35. }
  36. return player, fmt.Errorf("can not found player")
  37. }
  38. // getPlayerByLimit get players by limit
  39. func getPlayerByLimit(db *sql.DB, limit int) ([]Player, error) {
  40. var players []Player
  41. rows, err := db.Query(GetPlayerByLimitSQL, limit)
  42. if err != nil {
  43. return players, err
  44. }
  45. defer rows.Close()
  46. for rows.Next() {
  47. player := Player{}
  48. err = rows.Scan(&player.ID, &player.Coins, &player.Goods)
  49. if err == nil {
  50. players = append(players, player)
  51. } else {
  52. return players, err
  53. }
  54. }
  55. return players, nil
  56. }
  57. // bulk-insert players
  58. func bulkInsertPlayers(db *sql.DB, players []Player, batchSize int) error {
  59. tx, err := util.TiDBSqlBegin(db, true)
  60. if err != nil {
  61. return err
  62. }
  63. stmt, err := tx.Prepare(buildBulkInsertSQL(batchSize))
  64. if err != nil {
  65. return err
  66. }
  67. defer stmt.Close()
  68. for len(players) > batchSize {
  69. if _, err := stmt.Exec(playerToArgs(players[:batchSize])...); err != nil {
  70. tx.Rollback()
  71. return err
  72. }
  73. players = players[batchSize:]
  74. }
  75. if len(players) != 0 {
  76. if _, err := tx.Exec(buildBulkInsertSQL(len(players)), playerToArgs(players)...); err != nil {
  77. tx.Rollback()
  78. return err
  79. }
  80. }
  81. if err := tx.Commit(); err != nil {
  82. tx.Rollback()
  83. return err
  84. }
  85. return nil
  86. }
  87. func getCount(db *sql.DB) (int, error) {
  88. count := 0
  89. rows, err := db.Query(GetCountSQL)
  90. if err != nil {
  91. return count, err
  92. }
  93. defer rows.Close()
  94. if rows.Next() {
  95. if err := rows.Scan(&count); err != nil {
  96. return count, err
  97. }
  98. }
  99. return count, nil
  100. }
  101. func buyGoods(db *sql.DB, sellID, buyID string, amount, price int) error {
  102. var sellPlayer, buyPlayer Player
  103. tx, err := util.TiDBSqlBegin(db, true)
  104. if err != nil {
  105. return err
  106. }
  107. buyExec := func() error {
  108. stmt, err := tx.Prepare(GetPlayerWithLockSQL)
  109. if err != nil {
  110. return err
  111. }
  112. defer stmt.Close()
  113. sellRows, err := stmt.Query(sellID)
  114. if err != nil {
  115. return err
  116. }
  117. defer sellRows.Close()
  118. if sellRows.Next() {
  119. if err := sellRows.Scan(&sellPlayer.ID, &sellPlayer.Coins, &sellPlayer.Goods); err != nil {
  120. return err
  121. }
  122. }
  123. sellRows.Close()
  124. if sellPlayer.ID != sellID || sellPlayer.Goods < amount {
  125. return fmt.Errorf("sell player %s goods not enough", sellID)
  126. }
  127. buyRows, err := stmt.Query(buyID)
  128. if err != nil {
  129. return err
  130. }
  131. defer buyRows.Close()
  132. if buyRows.Next() {
  133. if err := buyRows.Scan(&buyPlayer.ID, &buyPlayer.Coins, &buyPlayer.Goods); err != nil {
  134. return err
  135. }
  136. }
  137. buyRows.Close()
  138. if buyPlayer.ID != buyID || buyPlayer.Coins < price {
  139. return fmt.Errorf("buy player %s coins not enough", buyID)
  140. }
  141. updateStmt, err := tx.Prepare(UpdatePlayerSQL)
  142. if err != nil {
  143. return err
  144. }
  145. defer updateStmt.Close()
  146. if _, err := updateStmt.Exec(-amount, price, sellID); err != nil {
  147. return err
  148. }
  149. if _, err := updateStmt.Exec(amount, -price, buyID); err != nil {
  150. return err
  151. }
  152. return nil
  153. }
  154. err = buyExec()
  155. if err == nil {
  156. fmt.Println("\n[buyGoods]:\n 'trade success'")
  157. tx.Commit()
  158. } else {
  159. tx.Rollback()
  160. }
  161. return err
  162. }
  163. func playerToArgs(players []Player) []interface{} {
  164. var args []interface{}
  165. for _, player := range players {
  166. args = append(args, player.ID, player.Coins, player.Goods)
  167. }
  168. return args
  169. }
  170. func buildBulkInsertSQL(amount int) string {
  171. return CreatePlayerSQL + strings.Repeat(",(?,?,?)", amount-1)
  172. }
  173. func randomPlayers(amount int) []Player {
  174. players := make([]Player, amount, amount)
  175. for i := 0; i < amount; i++ {
  176. players[i] = Player{
  177. ID: uuid.New().String(),
  178. Coins: rand.Intn(10000),
  179. Goods: rand.Intn(10000),
  180. }
  181. }
  182. return players
  183. }

sql.go 中存放了 SQL 语句的常量。

  1. package main
  2. const (
  3. CreatePlayerSQL = "INSERT INTO player (id, coins, goods) VALUES (?, ?, ?)"
  4. GetPlayerSQL = "SELECT id, coins, goods FROM player WHERE id = ?"
  5. GetCountSQL = "SELECT count(*) FROM player"
  6. GetPlayerWithLockSQL = GetPlayerSQL + " FOR UPDATE"
  7. UpdatePlayerSQL = "UPDATE player set goods = goods + ?, coins = coins + ? WHERE id = ?"
  8. GetPlayerByLimitSQL = "SELECT id, coins, goods FROM player LIMIT ?"
  9. )

第 3 步:运行代码

本节将逐步介绍代码的运行方法。

第 3 步第 1 部分:go-sql-driver/mysql 表初始化

  • 使用 GORM(推荐)
  • 使用 go-sql-driver/mysql

Golang - 图2

注意

在 Gitpod Playground 中尝试 GORM: 现在就试试

无需手动初始化表。

Golang - 图3

注意

在 Gitpod Playground 中尝试 go-sql-driver/mysql: 现在就试试

使用 go-sql-driver/mysql 时,需手动初始化数据库表,若你本地已经安装了 mysql-client,且使用本地集群,可直接在 sqldriver 目录下运行:

  1. make mysql

或直接执行:

  1. mysql --host 127.0.0.1 --port 4000 -u root<sql/dbinit.sql

若你不使用本地集群,或未安装 mysql-client,请直接登录你的集群,并运行 sql/dbinit.sql 文件内的 SQL 语句。

第 3 步第 2 部分:TiDB Cloud 更改参数

  • 使用 GORM(推荐)
  • 使用 go-sql-driver/mysql

若你使用 TiDB Serverless 集群,更改 gorm.godsn 参数值:

  1. dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8mb4"

若你设定的密码为 123456,而且从 TiDB Serverless 集群面板中得到的连接信息为:

  • Endpoint: xxx.tidbcloud.com
  • Port: 4000
  • User: 2aEp24QWEDLqRFs.root

那么此处应将 mysql.RegisterTLSConfigdsn 更改为:

  1. mysql.RegisterTLSConfig("register-tidb-tls", &tls.Config {
  2. MinVersion: tls.VersionTLS12,
  3. ServerName: "xxx.tidbcloud.com",
  4. })
  5. dsn := "2aEp24QWEDLqRFs.root:123456@tcp(xxx.tidbcloud.com:4000)/test?charset=utf8mb4&tls=register-tidb-tls"

若你使用 TiDB Serverless 集群,更改 sqldriver.godsn 参数的值:

  1. dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8mb4"

若你设定的密码为 123456,而且从 TiDB Serverless 集群面板中得到的连接信息为:

  • Endpoint: xxx.tidbcloud.com
  • Port: 4000
  • User: 2aEp24QWEDLqRFs.root

那么此处应将 mysql.RegisterTLSConfigdsn 更改为:

  1. mysql.RegisterTLSConfig("register-tidb-tls", &tls.Config {
  2. MinVersion: tls.VersionTLS12,
  3. ServerName: "xxx.tidbcloud.com",
  4. })
  5. dsn := "2aEp24QWEDLqRFs.root:123456@tcp(xxx.tidbcloud.com:4000)/test?charset=utf8mb4&tls=register-tidb-tls"

第 3 步第 3 部分:运行

  • 使用 GORM(推荐)
  • 使用 go-sql-driver/mysql

运行 make all,这是以下两个操作的组合:

  • 构建二进制 (make build):go build -o bin/gorm-example
  • 运行 (make run):./bin/gorm-example

你也可以单独运行这两个 make 命令或原生命令。

运行 make all,这是以下三个操作的组合:

  • 创建表 (make mysql):mysql --host 127.0.0.1 --port 4000 -u root<sql/dbinit.sql
  • 构建二进制 (make build): go build -o bin/sql-driver-example
  • 运行 (make run): ./bin/sql-driver-example

你也可以单独运行这三个 make 命令或原生命令。

第 4 步:预期输出

  • 使用 GORM(推荐)
  • 使用 go-sql-driver/mysql

GORM 预期输出

go-sql-driver/mysql 预期输出