TiDB 和 Golang 的简单 CRUD 应用程序

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

注意

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

第 1 步：启动你的 TiDB 集群

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

• TiDB Cloud
• 本地集群
• Gitpod

创建 Serverless Tier 集群。

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

• 部署本地测试 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

3. import (
4. "gorm.io/gorm"
5. )

7. // 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.
8. func TiDBGormBegin(db *gorm.DB, pessimistic bool, fc func(tx *gorm.DB) error) (err error) {
9. session := db.Session(&gorm.Session{})
10. if session.Error != nil {
11. return session.Error
12. }

14. if pessimistic {
15. session = session.Exec("set @@tidb_txn_mode=pessimistic")
16. } else {
17. session = session.Exec("set @@tidb_txn_mode=optimistic")
18. }

20. if session.Error != nil {
21. return session.Error
22. }
23. return session.Transaction(fc)
24. }

进入目录 gorm ：

1. cd gorm

目录结构如下所示：

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

其中，gorm.go 是 gorm 这个示例程序的主体。使用 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"` 的注解，用来指示映射关系。

2. package main

4. import (
5. "fmt"
6. "math/rand"

8. "github.com/google/uuid"
9. "github.com/pingcap-inc/tidb-example-golang/util"

11. "gorm.io/driver/mysql"
12. "gorm.io/gorm"
13. "gorm.io/gorm/clause"
14. "gorm.io/gorm/logger"
15. )

17. type Player struct {
18. ID    string `gorm:"primaryKey;type:VARCHAR(36);column:id"`
19. Coins int    `gorm:"column:coins"`
20. Goods int    `gorm:"column:goods"`
21. }

23. func (*Player) TableName() string {
24. return "player"
25. }

27. func main() {
28. // 1. Configure the example database connection.
29. db := createDB()

31. // AutoMigrate for player table
32. db.AutoMigrate(&Player{})

34. // 2. Run some simple examples.
35. simpleExample(db)

37. // 3. Explore more.
38. tradeExample(db)
39. }

41. func tradeExample(db *gorm.DB) {
42. // Player 1: id is "1", has only 100 coins.
43. // Player 2: id is "2", has 114514 coins, and 20 goods.
44. player1 := &Player{ID: "1", Coins: 100}
45. player2 := &Player{ID: "2", Coins: 114514, Goods: 20}

47. // Create two players "by hand", using the INSERT statement on the backend.
48. db.Clauses(clause.OnConflict{UpdateAll: true}).Create(player1)
49. db.Clauses(clause.OnConflict{UpdateAll: true}).Create(player2)

51. // Player 1 wants to buy 10 goods from player 2.
52. // It will cost 500 coins, but player 1 cannot afford it.
53. fmt.Println("\nbuyGoods:\n    => this trade will fail")
54. if err := buyGoods(db, player2.ID, player1.ID, 10, 500); err == nil {
55. panic("there shouldn't be success")
56. }

58. // So player 1 has to reduce the incoming quantity to two.
59. fmt.Println("\nbuyGoods:\n    => this trade will success")
60. if err := buyGoods(db, player2.ID, player1.ID, 2, 100); err != nil {
61. panic(err)
62. }
63. }

65. func simpleExample(db *gorm.DB) {
66. // Create a player, who has a coin and a goods..
67. if err := db.Clauses(clause.OnConflict{UpdateAll: true}).
68. Create(&Player{ID: "test", Coins: 1, Goods: 1}).Error; err != nil {
69. panic(err)
70. }

72. // Get a player.
73. var testPlayer Player
74. db.Find(&testPlayer, "id = ?", "test")
75. fmt.Printf("getPlayer: %+v\n", testPlayer)

77. // Create players with bulk inserts. Insert 1919 players totally, with 114 players per batch.
78. bulkInsertPlayers := make([]Player, 1919, 1919)
79. total, batch := 1919, 114
80. for i := 0; i < total; i++ {
81. bulkInsertPlayers[i] = Player{
82. ID:    uuid.New().String(),
83. Coins: rand.Intn(10000),
84. Goods: rand.Intn(10000),
85. }
86. }

88. if err := db.Session(&gorm.Session{Logger: db.Logger.LogMode(logger.Error)}).
89. CreateInBatches(bulkInsertPlayers, batch).Error; err != nil {
90. panic(err)
91. }

93. // Count players amount.
94. playersCount := int64(0)
95. db.Model(&Player{}).Count(&playersCount)
96. fmt.Printf("countPlayers: %d\n", playersCount)

98. // Print 3 players.
99. threePlayers := make([]Player, 3, 3)
100. db.Limit(3).Find(&threePlayers)
101. for index, player := range threePlayers {
102. fmt.Printf("print %d player: %+v\n", index+1, player)
103. }
104. }

106. func createDB() *gorm.DB {
107. dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8mb4"
108. db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{
109. Logger: logger.Default.LogMode(logger.Info),
110. })
111. if err != nil {
112. panic(err)
113. }

115. return db
116. }

118. func buyGoods(db *gorm.DB, sellID, buyID string, amount, price int) error {
119. return util.TiDBGormBegin(db, true, func(tx *gorm.DB) error {
120. var sellPlayer, buyPlayer Player
121. if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
122. Find(&sellPlayer, "id = ?", sellID).Error; err != nil {
123. return err
124. }

126. if sellPlayer.ID != sellID || sellPlayer.Goods < amount {
127. return fmt.Errorf("sell player %s goods not enough", sellID)
128. }

130. if err := tx.Clauses(clause.Locking{Strength: "UPDATE"}).
131. Find(&buyPlayer, "id = ?", buyID).Error; err != nil {
132. return err
133. }

135. if buyPlayer.ID != buyID || buyPlayer.Coins < price {
136. return fmt.Errorf("buy player %s coins not enough", buyID)
137. }

139. updateSQL := "UPDATE player set goods = goods + ?, coins = coins + ? WHERE id = ?"
140. if err := tx.Exec(updateSQL, -amount, price, sellID).Error; err != nil {
141. return err
142. }

144. if err := tx.Exec(updateSQL, amount, -price, buyID).Error; err != nil {
145. return err
146. }

148. fmt.Println("\n[buyGoods]:\n    'trade success'")
149. return nil
150. })
151. }

使用 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;

4. CREATE TABLE player (
5. `id` VARCHAR(36),
6. `coins` INTEGER,
7. `goods` INTEGER,
8. PRIMARY KEY (`id`)
9. );

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

1. package main

3. import (
4. "database/sql"
5. "fmt"

7. _ "github.com/go-sql-driver/mysql"
8. )

10. func main() {
11. // 1. Configure the example database connection.
12. dsn := "root:@tcp(127.0.0.1:4000)/test?charset=utf8mb4"
13. openDB("mysql", dsn, func(db *sql.DB) {
14. // 2. Run some simple examples.
15. simpleExample(db)

17. // 3. Explore more.
18. tradeExample(db)
19. })
20. }

22. func simpleExample(db *sql.DB) {
23. // Create a player, who has a coin and a goods.
24. err := createPlayer(db, Player{ID: "test", Coins: 1, Goods: 1})
25. if err != nil {
26. panic(err)
27. }

29. // Get a player.
30. testPlayer, err := getPlayer(db, "test")
31. if err != nil {
32. panic(err)
33. }
34. fmt.Printf("getPlayer: %+v\n", testPlayer)

36. // Create players with bulk inserts. Insert 1919 players totally, with 114 players per batch.

38. err = bulkInsertPlayers(db, randomPlayers(1919), 114)
39. if err != nil {
40. panic(err)
41. }

43. // Count players amount.
44. playersCount, err := getCount(db)
45. if err != nil {
46. panic(err)
47. }
48. fmt.Printf("countPlayers: %d\n", playersCount)

50. // Print 3 players.
51. threePlayers, err := getPlayerByLimit(db, 3)
52. if err != nil {
53. panic(err)
54. }
55. for index, player := range threePlayers {
56. fmt.Printf("print %d player: %+v\n", index+1, player)
57. }
58. }

60. func tradeExample(db *sql.DB) {
61. // Player 1: id is "1", has only 100 coins.
62. // Player 2: id is "2", has 114514 coins, and 20 goods.
63. player1 := Player{ID: "1", Coins: 100}
64. player2 := Player{ID: "2", Coins: 114514, Goods: 20}

66. // Create two players "by hand", using the INSERT statement on the backend.
67. if err := createPlayer(db, player1); err != nil {
68. panic(err)
69. }
70. if err := createPlayer(db, player2); err != nil {
71. panic(err)
72. }

74. // Player 1 wants to buy 10 goods from player 2.
75. // It will cost 500 coins, but player 1 cannot afford it.
76. fmt.Println("\nbuyGoods:\n    => this trade will fail")
77. if err := buyGoods(db, player2.ID, player1.ID, 10, 500); err == nil {
78. panic("there shouldn't be success")
79. }

81. // So player 1 has to reduce the incoming quantity to two.
82. fmt.Println("\nbuyGoods:\n    => this trade will success")
83. if err := buyGoods(db, player2.ID, player1.ID, 2, 100); err != nil {
84. panic(err)
85. }
86. }

88. func openDB(driverName, dataSourceName string, runnable func(db *sql.DB)) {
89. db, err := sql.Open(driverName, dataSourceName)
90. if err != nil {
91. panic(err)
92. }
93. defer db.Close()

95. runnable(db)
96. }

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

1. package util

3. import (
4. "context"
5. "database/sql"
6. )

8. type TiDBSqlTx struct {
9. *sql.Tx
10. conn        *sql.Conn
11. pessimistic bool
12. }

14. func TiDBSqlBegin(db *sql.DB, pessimistic bool) (*TiDBSqlTx, error) {
15. ctx := context.Background()
16. conn, err := db.Conn(ctx)
17. if err != nil {
18. return nil, err
19. }
20. if pessimistic {
21. _, err = conn.ExecContext(ctx, "set @@tidb_txn_mode=?", "pessimistic")
22. } else {
23. _, err = conn.ExecContext(ctx, "set @@tidb_txn_mode=?", "optimistic")
24. }
25. if err != nil {
26. return nil, err
27. }
28. tx, err := conn.BeginTx(ctx, nil)
29. if err != nil {
30. return nil, err
31. }
32. return &TiDBSqlTx{
33. conn:        conn,
34. Tx:          tx,
35. pessimistic: pessimistic,
36. }, nil
37. }

39. func (tx *TiDBSqlTx) Commit() error {
40. defer tx.conn.Close()
41. return tx.Tx.Commit()
42. }

44. func (tx *TiDBSqlTx) Rollback() error {
45. defer tx.conn.Close()
46. return tx.Tx.Rollback()
47. }

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

1. package main

3. import (
4. "database/sql"
5. "fmt"
6. "math/rand"
7. "strings"

9. "github.com/google/uuid"
10. "github.com/pingcap-inc/tidb-example-golang/util"
11. )

13. type Player struct {
14. ID    string
15. Coins int
16. Goods int
17. }

19. // createPlayer create a player
20. func createPlayer(db *sql.DB, player Player) error {
21. _, err := db.Exec(CreatePlayerSQL, player.ID, player.Coins, player.Goods)
22. return err
23. }

25. // getPlayer get a player
26. func getPlayer(db *sql.DB, id string) (Player, error) {
27. var player Player

29. rows, err := db.Query(GetPlayerSQL, id)
30. if err != nil {
31. return player, err
32. }
33. defer rows.Close()

35. if rows.Next() {
36. err = rows.Scan(&player.ID, &player.Coins, &player.Goods)
37. if err == nil {
38. return player, nil
39. } else {
40. return player, err
41. }
42. }

44. return player, fmt.Errorf("can not found player")
45. }

47. // getPlayerByLimit get players by limit
48. func getPlayerByLimit(db *sql.DB, limit int) ([]Player, error) {
49. var players []Player

51. rows, err := db.Query(GetPlayerByLimitSQL, limit)
52. if err != nil {
53. return players, err
54. }
55. defer rows.Close()

57. for rows.Next() {
58. player := Player{}
59. err = rows.Scan(&player.ID, &player.Coins, &player.Goods)
60. if err == nil {
61. players = append(players, player)
62. } else {
63. return players, err
64. }
65. }

67. return players, nil
68. }

70. // bulk-insert players
71. func bulkInsertPlayers(db *sql.DB, players []Player, batchSize int) error {
72. tx, err := util.TiDBSqlBegin(db, true)
73. if err != nil {
74. return err
75. }

77. stmt, err := tx.Prepare(buildBulkInsertSQL(batchSize))
78. if err != nil {
79. return err
80. }

82. defer stmt.Close()

84. for len(players) > batchSize {
85. if _, err := stmt.Exec(playerToArgs(players[:batchSize])...); err != nil {
86. tx.Rollback()
87. return err
88. }

90. players = players[batchSize:]
91. }

93. if len(players) != 0 {
94. if _, err := tx.Exec(buildBulkInsertSQL(len(players)), playerToArgs(players)...); err != nil {
95. tx.Rollback()
96. return err
97. }
98. }

100. if err := tx.Commit(); err != nil {
101. tx.Rollback()
102. return err
103. }

105. return nil
106. }

108. func getCount(db *sql.DB) (int, error) {
109. count := 0

111. rows, err := db.Query(GetCountSQL)
112. if err != nil {
113. return count, err
114. }

116. defer rows.Close()

118. if rows.Next() {
119. if err := rows.Scan(&count); err != nil {
120. return count, err
121. }
122. }

124. return count, nil
125. }

127. func buyGoods(db *sql.DB, sellID, buyID string, amount, price int) error {
128. var sellPlayer, buyPlayer Player

130. tx, err := util.TiDBSqlBegin(db, true)
131. if err != nil {
132. return err
133. }

135. buyExec := func() error {
136. stmt, err := tx.Prepare(GetPlayerWithLockSQL)
137. if err != nil {
138. return err
139. }
140. defer stmt.Close()

142. sellRows, err := stmt.Query(sellID)
143. if err != nil {
144. return err
145. }
146. defer sellRows.Close()

148. if sellRows.Next() {
149. if err := sellRows.Scan(&sellPlayer.ID, &sellPlayer.Coins, &sellPlayer.Goods); err != nil {
150. return err
151. }
152. }
153. sellRows.Close()

155. if sellPlayer.ID != sellID || sellPlayer.Goods < amount {
156. return fmt.Errorf("sell player %s goods not enough", sellID)
157. }

159. buyRows, err := stmt.Query(buyID)
160. if err != nil {
161. return err
162. }
163. defer buyRows.Close()

165. if buyRows.Next() {
166. if err := buyRows.Scan(&buyPlayer.ID, &buyPlayer.Coins, &buyPlayer.Goods); err != nil {
167. return err
168. }
169. }
170. buyRows.Close()

172. if buyPlayer.ID != buyID || buyPlayer.Coins < price {
173. return fmt.Errorf("buy player %s coins not enough", buyID)
174. }

176. updateStmt, err := tx.Prepare(UpdatePlayerSQL)
177. if err != nil {
178. return err
179. }
180. defer updateStmt.Close()

182. if _, err := updateStmt.Exec(-amount, price, sellID); err != nil {
183. return err
184. }

186. if _, err := updateStmt.Exec(amount, -price, buyID); err != nil {
187. return err
188. }

190. return nil
191. }

193. err = buyExec()
194. if err == nil {
195. fmt.Println("\n[buyGoods]:\n    'trade success'")
196. tx.Commit()
197. } else {
198. tx.Rollback()
199. }

201. return err
202. }

204. func playerToArgs(players []Player) []interface{} {
205. var args []interface{}
206. for _, player := range players {
207. args = append(args, player.ID, player.Coins, player.Goods)
208. }
209. return args
210. }

212. func buildBulkInsertSQL(amount int) string {
213. return CreatePlayerSQL + strings.Repeat(",(?,?,?)", amount-1)
214. }

216. func randomPlayers(amount int) []Player {
217. players := make([]Player, amount, amount)
218. for i := 0; i < amount; i++ {
219. players[i] = Player{
220. ID:    uuid.New().String(),
221. Coins: rand.Intn(10000),
222. Goods: rand.Intn(10000),
223. }
224. }

226. return players
227. }

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

1. package main

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

第 3 步：运行代码

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

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

• 使用 GORM（推荐）
• 使用 go-sql-driver/mysql

注意

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

无需手动初始化表。

注意

在 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 Cloud Serverless Tier 集群，更改 gorm.go 内 dsn 参数值：

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

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

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

那么此处应将 mysql.RegisterTLSConfig 和 dsn 更改为：

1. mysql.RegisterTLSConfig("register-tidb-tls", &tls.Config {
2. MinVersion: tls.VersionTLS12,
3. ServerName: "xxx.tidbcloud.com",
4. })

6. dsn := "2aEp24QWEDLqRFs.root:123456@tcp(xxx.tidbcloud.com:4000)/test?charset=utf8mb4&tls=register-tidb-tls"

若你使用 TiDB Cloud Serverless Tier 集群，更改 sqldriver.go 内 dsn 参数的值：

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

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

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

那么此处应将 mysql.RegisterTLSConfig 和 dsn 更改为：

1. mysql.RegisterTLSConfig("register-tidb-tls", &tls.Config {
2. MinVersion: tls.VersionTLS12,
3. ServerName: "xxx.tidbcloud.com",
4. })

6. 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 预期输出