1. go中数据库操作涉及到的包
- database/sql：Package sql提供围绕SQL（或类SQL）数据库的通用接口。（CRUD相关）
- database/sql/driver: 驱动程序定义了由包sql使用的数据库驱动程序实现的接口（数据库抽象层相关，比如Mysql、Postgresql）
2. sql操作
- 函数
- 类型以及类型方法
- 列类型
- 连接类型：开启事务、关闭、执行上下文、ping、prepare、query、queryRow
- DB类型：打开、开始事务、关闭、连接、执行、ping、prepare、query、queryrow、设置、状态
- DB状态
- 隔离级别指定
- 字段扫描
- 行类型：扫描、关闭、列类型、错误、下一条
- 扫描器
- Stmt事务相关：执行、关闭、查询、查询行
- Tx类型：提交、执行、查询、prepare
3. Prepare 声明
prepare语句的好处有效率，安全，方便，但GO中的实现和普通sql实现有一些差别；
3.1. prepare和连接
在数据库层面，准备声明被绑定在单个数据库连接上。典型流程是客户端发送一个SQL语句声明给到数据库服务器，然后数据库服务器解析后，响应一个声明ID(statement ID)，然后客户端执行通过发送声明ID和参数；
在GO中，database/sql包没有直接将连接暴露出来，不必将prepare绑定在一个连接上，而是在DB或者一个事务Tx上面准备，并且database/sql有一些方便的行为比如重试，由于这些原因，预准备语句和连接之间的底层关联存在于驱动程序级别，对代码是隐藏的，其工作过程：
- 当在Go中声明一个prepare，它会基于连接池中的一个连接
- Stmt对象会记住选择的连接
- Stmt执行的时候，它会尝试使用之前记录的连接；如果连接不可用（比如连接关闭、繁忙等原因），它将从连接池中获取另外一个数据库连接，然后在选中的连接上面，重新prepare这个语句；
由于SQL语句声明被重复prepare准备在不同的连接上（由于原来的连接不可用），这样可能会导致Mysql服务的高并发使用，导致保存了大量的连接，用于创建预处理SQL语句。这样依赖，将导致MYSQL的高负载，甚至是Mysql服务端内部的资源限制；
3.2. 避免Prepare预处理
Go私下会创建预处理的声明，一个简单的db.Query(sql,param1,param2)，通过prepare sql，然后执行它最后关闭sql声明，一些情况preapred预处理语句不是预期想要的，因为：
- 数据库不支持预处理语句（比如连接至Sphinx或者MemSQL，它们不是采用二进制协议）
- 当SQL声明不是最长被使用到的，安全问题可以通过其他方式解决（从性能开销方面考虑）
如果不使用预处理，需要通过fmt.Sprint()或类似方式组装SQL，然后将其作为单个参数传递给db.Query()或者db.QueryRow()。
3.3. prepared 声明在事务中
Tx是独立的一个新的连接，故在Tx中创建的预处理不能够与Tx的连接分离开来；
在Tx事务外使用预处理语句，使用Tx.Stmt()，这将从事务外部准备的语句创建新的特定于事务的语句。（种行为及其实现是不可取的，在数据库/ sql源代码中甚至还有一个TODO来改进它;我们建议不要使用它）
在Tx中创建的预处理语句仅与其绑定，因此之前关于重新声明的警告注意不适用。当您对Tx对象进行操作时，您的操作将直接映射到其下的唯一连接。在DB上创建的预准备语句不能在事务中使用，因为它们将绑定到不同的连。
4. 占位符
1
2
3
4
| MySQL PostgreSQL Oracle
===== ========== ======
WHERE col = ? WHERE col = $1 WHERE col = :col
VALUES(?, ?, ?) VALUES($1, $2, $3) VALUES(:val1, :val2, :val3)
|
5. 常见的错误处理操作
所有database/sql错误类型最后返回一个错误值，你应该检测这些错误，不应该忽略它们
5.1. 结果集中迭代出现的错误
1
2
3
4
5
6
| for rows.Next() {
// ...
}
if err = rows.Err(); err != nil {
// handle the error here
}
|
rows.Err()可能是由各式各样的错误造成的结果，可以考在循环内终止，而不一定需要正常的迭代完rows，所以你总是需要检查循环是否正常终止
异常终止会自动调用rows.Close()，尽管多次调用它是无害的。
5.2. 结果集rows关闭过程的错误
如果提前退出循环，如前所述，总是应该显式关闭sql.Rows；如果循环正常退出或通过错误退出，它会自动关闭，但您可能会错误地执行此操作：
1
2
3
4
5
6
7
8
9
10
| for rows.Next() {
// ...
break; // whoops, rows is not closed! memory leak...
}
// do the usual "if err = rows.Err()" [omitted here]...
// it's always safe to [re?]close here:
if err = rows.Close(); err != nil {
// but what should we do if there's an error?
log.Println(err)
}
|
rows.Close（）返回的错误是一般规则的唯一例外，最好捕获并检查所有数据库操作中的错误；
如果rows.Close（）返回错误时候，记录下错误消息或直接panic终止程序可能是唯一明智的事情
5.3. QueryRow()过程中的错误
考虑以下查询代码：
1
2
3
4
5
6
| var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)
|
如果没有id = 1的用户怎么办？然后结果中没有行，并且.Scan（）不会将值扫描到名称中。
以上代码改进，基于无记录的sql.ErrNoRow错误常量判断：
1
2
3
4
5
6
7
8
9
10
| var name string
err = db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
if err == sql.ErrNoRows {
// there were no rows, but otherwise no error occurred
} else {
log.Fatal(err)
}
}
fmt.Println(name)
|
如果没有sql.ErrNoRow错误，QueryRow()无法识别到是否找到了行；没有这个错误，Scan()无法Scan到任何值；
5.4. 确定具体的SQL错误
5.4.1. 考虑以下代码：
1
2
3
4
5
6
| rows, err := db.Query("SELECT someval FROM sometable")
// err contains:
// ERROR 1045 (28000): Access denied for user 'foo'@'::1' (using password: NO)
if strings.Contains(err.Error(), "Access denied") {
// Handle the permission-denied error
}
|
5.4.2. 更好方式通过错误Number标识：
此处的MySQLError类型由此特定驱动程序提供，并且.Number字段可能因驱动程序而异。但是，数字的值取自MySQL的错误消息，因此是特定于数据库的，而不是特定于驱动程序的。
1
2
3
4
5
6
| // 基于硬编码，但基于SQL的驱动错误标识判断
if driverErr, ok := err.(*mysql.MySQLError); ok { // Now the error number is accessible directly
if driverErr.Number == 1045 {
// Handle the permission-denied error
}
}
|
5.4.3. 更好方式通过错误标识，而非1045魔法数字
以上代码很丑陋，1045为一个魔法数字，通常的驱动程序都提供了一些错误标识
1
2
3
4
5
| if driverErr, ok := err.(*mysql.MySQLError); ok {
if driverErr.Number == mysqlerr.ER_ACCESS_DENIED_ERROR {
// Handle the permission-denied error
}
}
|
5.5. 连接错误处理
如果与数据库的连接被删除，终止或出错，该怎么办？
无需做任何重试的逻辑，database/sql中有连接池的概念，内建了失败和错误的连接处理方式。当查询或者其他SQL声明语句中遇到错误，驱动底层将自动从连接池重新获取一个连接或者重新打开一个新的连接。重试10次。
但是，重试也可能会产生一些意想不到的后果。比如定期使用KILL取消长时间运行的查询，会导致语句被重试最多10次。
6. 数据库字段NULL值问题
6.1. 尽量在数据库层面规避
空值是导致很多丑陋代码的元凶，优先考虑在数据库设计层面避免；如果不能避免，需要使用指定的类型来处理：
1
2
3
4
5
6
7
8
9
10
| for rows.Next() {
var s sql.NullString
err := rows.Scan(&s)
// check err
if s.Valid {
// use s.String
} else {
// NULL value
}
}
|
尽量避免使用空列，因为：
- 没有
sql.NullUint64或sql.NullYourFavoriteType，你需要为此定义自己的Null类型，设计过程参见sql.NullString类型 - 空值存在很多棘手的问题，比如类型判断等
- Go语言的一个好处是为每个变量提供一个有用的默认零值。
6.2. 基于COALESCE()
如果无法避免，还有一个解决方案，通过Mysql函数COALESCE()进行字符串拼接，从而不引出无数的sql.Null*类型
1
2
3
4
5
6
7
8
9
10
11
12
| rows, err := db.Query(`
SELECT
name,
COALESCE(other_field, '') as otherField
WHERE id = ?
`, 42)
for rows.Next() {
err := rows.Scan(&name, &otherField)
// ..
// If `other_field` was NULL, `otherField` is now an empty string. This works with other data types as well.
}
|
7. Unknown列的问题 - Columns()解决
Scan()函数要求准确传递正确数量的目标变量，如果您不知道查询将返回什么，该怎么办？
通过Columns()解决：
- 通过
Columns()检查此列表的长度以查看有多少列； - 基于
Scan()，使用正确数量的值传递切片
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| cols, err := rows.Columns()
if err != nil {
// handle the error
} else {
dest := []interface{}{ // Standard MySQL columns
new(uint64), // id
new(string), // host
new(string), // user
new(string), // db
new(string), // command
new(uint32), // time
new(string), // state
new(string), // info
}
if len(cols) == 11 {
// Percona Server
} else if len(cols) > 8 {
// Handle this case
}
err = rows.Scan(dest...)
// Work with the values in dest
}
|
7.1. 如果不知道列类型，采用sql.RawBytes类型
1
2
3
4
5
6
7
8
9
10
11
| cols, err := rows.Columns() // Remember to check err afterwards
vals := make([]interface{}, len(cols))
for i, _ := range cols {
vals[i] = new(sql.RawBytes)
}
for rows.Next() {
err = rows.Scan(vals...)
// Now you can check each element of vals for nil-ness,
// and you can use type introspection and type assertions
// to fetch the column into a typed variable.
}
|
8. 连接池
database/sql包中有一个基本的连接池，相关概念：
- 连接池意味着在单个数据库上执行两个连续语句可能会打开两个连接并分别执行它们，这种行为可能引起一些代码端的行为不一致。（比如紧跟着
INSERT的锁表LOCK TABLES可能会阻塞，因为INSERT位于不持有表锁的连接上。） - 在需要时创建连接，并且池中没有空闲连接
- 默认情况下，连接数没有限制。如果您尝试同时执行大量操作，则可以创建任意数量的连接。这可能导致数据库返回错误，例如“连接太多”。
- 限制池中的空闲连接数：db.SetMaxIdleConns(N)，这并不限制池大小。
- 限制数据库的总打开连接数：db.SetMaxOpenConns(N)
- 限制连接重用的最长时：db.SetConnMaxLifetime(duration)，重用长期连接可能会导致网络问题。比如延迟关闭未使用的连接，即可以延迟关闭过期连接。
- 连接回收速度相当快。设置大量空闲连接db.SetMaxIdleConns(N)可以减少这种流失，并有助于保持连接以便重复使用。
- 长时间保持连接空闲可能会导致问题，尝试db.SetMaxIdleConns(0)连接超时是因为连接空闲时间太长。
9. 惊喜
9.1. 资源枯竭
如果没有按预期使用database/sql，可能会给自己带来麻烦，通常是消耗一些资源或阻止它们被有效重用：
- 打开和关闭数据库可能会导致资源耗尽。
- 错误的读取所有行或错误的使用rows.Close()，预定了连接池中的保留连接。
- 使用不返回行的语句
Query()将保留池中的连接。 - 未能充分利用预处理语句，可能会导致大量额外的数据库活动。
9.2. uint64大值问题
1
| _, err := db.Exec("INSERT INTO users(id) VALUES", math.MaxUint64) // Error
|
9.3. 连接状态不匹配原因
- 某些连接状态（例如您是否在事务中）应该通过Go类型来处理
- 没有考虑连接池，可能假设所有的SQL查询在单个连接上运行
不同连接：例如，当用USE语句设置当前数据库是许多人要做的典型事情。但是在Go中，它只会影响你运行它的连接。除非你在一个事务中，你认为在该连接上执行的其他语句实际上可能在从池中获取的不同连接上运行，所以它们不会看到这种变化的影响。
污染其他连接：在您更改连接后，它将返回池并可能污染其他代码的状态。这也是您不应该直接将BEGIN或COMMIT语句作为SQL命令发出的原因之一。
9.4. 特定SQL占位符
database/sqlAPI提供了一个面向行的数据库的抽象，但具体的数据库和驱动程序不同，对应的 预处理语句的占位符也不同；
9.5. 多个结果集合
Go驱动程序不以任何方式支持来自单个查询的多个结果集，这意味着，无特例外，返回多个结果集的存储过程将无法正常工作。
9.6. 存储过程调用
调用存储过程是特定于驱动程序的，在MySQL驱动程序中，它目前无法完成。
9.7. 多语句支持
- 多语句支持看服务端支持情况可能存在不同，有的数据库服务不会支持
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
| // 多语句支持看服务端
_, err := db.Exec("DELETE FROM tbl1; DELETE FROM tbl2") // Error/unpredictable result
// query在不同连接上执行
rows, err := db.Query("select * from tbl1") // Uses connection 1
for rows.Next() {
err = rows.Scan(&myvariable)
// The following line will NOT use connection 1, which is already in-use
db.Query("select * from tbl2 where id = ?", myvariable)
}
// 事务在单个连接行，Go不会阻止第二个连接，这也意味着事务中的每个语句都会导致一组单独的网络往返数据库
tx, err := db.Begin()
rows, err := tx.Query("select * from tbl1") // Uses tx's connection
for rows.Next() {
err = rows.Scan(&myvariable)
// ERROR! tx's connection is already busy!
tx.Query("select * from tbl2 where id = ?", myvariable)
}
|
10. 连接字段相关
通常，当数据库连接过多，当超出指定配置后，除了一个+1个root连接支持，其他继续访问连接基本都会得到以下错误
1
2
3
4
| $ mysql
ERROR 1040 (HY000): Too many connections
或者是：
[08004][1040] Data source rejected establishment of connection, message from server: "Too many connections"
|
10.1. db.Stats()方法
1
2
3
4
5
6
7
8
9
10
| db.Stats Key Values
---------- -----
OpenConnections 151
InUse 151
Idle 0
WaitCount 0
WaitDuration 0s
MaxIdleClosed 0
MaxLifetimeClosed 0
MaxOpenConnections 0
|
这里通过db.Stats()看到打开的数据库连接有151个，在使用的也是151个。
10.2. Mysql服务端的连接数配置
以下数据看到：
- max_connections：服务允许的最大连接数
- connect_timeout：连接超时时间
- max_connect_errors：最大连接错误
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| mysql> show variables like '%conn%';
+-----------------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------------+-----------------+
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 151 |
| max_user_connections | 0 |
| mysqlx_connect_timeout | 30 |
| mysqlx_max_connections | 100 |
| performance_schema_session_connect_attrs_size | 512 |
+-----------------------------------------------+-----------------+
|
10.3. 基于root账户查看当前状态
通过在服务端查看连接配置mysql> show status like "%conn%";，我们可以看到几个字段：
1
2
3
| Connection_errors_max_connections: 1011 //最大连接数错误
Max_used_connections_time：2018-03-18 07:19:29 //最大连接数发生的时间
Threads_connected：152 //基于配置的151+1个root连接
|
11. 示例
11.1. 数据库的初始化相关
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
| package mysql
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
"io"
"log"
"os"
"text/tabwriter"
"text/template"
)
// Mysql 连接配置
var (
dbUser = "root"
dbPass = "Secret123."
dbName = "test_db"
)
// ErrHandle Mysql错误处理
func ErrHandle(err error, pos string, v ...interface{}) {
format := "%s, %s";
if v != nil {
for range v {
format = format + ", %s"
}
log.Panicf(format+"\n", pos, err, v)
} else {
log.Panicf(format+"\n", pos, err)
}
}
// dsn string
func dsn() string {
return fmt.Sprintf("%s:%s@/%s", dbUser, dbPass, dbName)
}
// GetDb 获取DB实例
func GetDb() (db *sql.DB, err error) {
db, err = sql.Open("mysql", dsn())
if err != nil {
ErrHandle(err, "sql.open")
}
if err = db.Ping(); err != nil {
ErrHandle(err, "sql.ping")
}
return
}
// ShowStatus 打印当前连接DB状态信息
func ShowStatus(db *sql.DB) {
status := db.Stats()
stData := map[string]interface{}{
"MaxOpenConnections": status.MaxOpenConnections,
"OpenConnections": status.OpenConnections,
"InUse": status.InUse,
"Idle": status.Idle,
"WaitCount": status.WaitCount,
"WaitDuration": status.WaitDuration,
"MaxIdleClosed": status.MaxIdleClosed,
"MaxLifetimeClosed": status.MaxLifetimeClosed,
}
// text/tabwriter
textTableShow(os.Stdout, stData)
// text/template
// textTmplShow(os.Stdout, stData)
}
// 表格输出
func textTableShow(w io.Writer, data map[string]interface{}) {
const format = "%v\t%v\n"
tw := new(tabwriter.Writer).Init(w, 0, 8, 2, ' ', 0)
fmt.Fprintf(tw, format, "db.Stats Key", "Values")
fmt.Fprintf(tw, format, "----------", "-----")
for k, v := range data {
fmt.Fprintf(tw, format, k, v)
}
tw.Flush()
}
// 文本模板输出
func textTmplShow(w io.Writer, data map[string]interface{}) {
// tmpl
const tmplTxt = `DB STATUS
-----------------------{{range $k, $v := .}}
{{printf "%-18v" $k}} => {{printf "%v" .}}{{end}}
`
tmpl, err := template.New("showStatus").Parse(tmplTxt)
if err != nil {
ErrHandle(err, "template.Parse")
}
if err := tmpl.Execute(w, data); err != nil {
ErrHandle(err, "template.Execute")
}
}
|
11.2. 数据库的CRUD、Prepare以及TX事务相关
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
| // 从DB查询数据
// 使用db.Query()将查询发送到数据库。
// 推迟rows.Close ()，这非常重要（通常在query后，执行一个defer延迟关闭操作，但不要在循环中使用defer）
// 使用rows. next()遍历行（）
// 使用rows.Scan()将每一行中的列读入变量，需要创建正确类型的变量并将指针传递给它们
// 在遍历行之后检查错误。
// 只要有一个打开的结果集（由行表示），底层连接就会忙，不能用于任何其他查询;
// 意味着它在连接池中不可用
func fetchDataDemo(db *sql.DB) {
var (
id int
name string
)
rows, err := db.Query("select id, name from users where id = ?", 1)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
err := rows.Scan(&id, &name)
if err != nil {
log.Fatal(err)
}
log.Println(id, name)
}
err = rows.Err()
if err != nil {
log.Fatal(err)
}
}
// Prepare()+Query()
// 占位符Mysql使用?，PostgreSQL采用$N，SQLite采用两者都支持，Oracle采用:param形式
// 比连接字符串要好得多
func prepareQueryDemo(db *sql.DB) {
stmt, err := db.Prepare("select id, name from users where id = ?")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
rows, err := stmt.Query(1)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
// ...
}
if err = rows.Err(); err != nil {
log.Fatal(err)
}
}
// Scan()
// 遍历行并将它们扫描到目标变量时，Go会在后台执行数据类型转换。
// strconv.ParseInt()
// 只需将Scan()指针传递给整数
// QueryRow()：单行查询
// 查询中的错误将推迟到Scan
func singleRowQueryDemo(db *sql.DB) {
var name string
err := db.QueryRow("select name from users where id = ?", 1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)
}
// Prepare()+QueryRow()：预处理+单行查询
// stmt, err := db.Prepare(sql)
// err = stmt.QueryRow(bindParams,...).Scan(&name)
func queryRowPrepareDemo(db *sql.DB) {
stmt, err := db.Prepare("select name from users where id = ?")
if err != nil {
log.Fatal(err)
}
defer stmt.Close()
var name string
err = stmt.QueryRow(1).Scan(&name)
if err != nil {
log.Fatal(err)
}
fmt.Println(name)
}
// Tx Prepare Statements
// 好处：安全、效率、方便；在内部database/sql实现Prepare中有一些差异
func txPrepareDemo(db *sql.DB) {
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
defer tx.Rollback()
// tx prepare
stmt, err := tx.Prepare("INSERT INTO foo VALUES (?)")
if err != nil {
log.Fatal(err)
}
// 延迟调用close在准备好的语句执行后已经发生，这可能导致底层连接并发访问，致使连接状态不一致
defer stmt.Close() // danger!，
// tx exec
for i := 0; i < 10; i++ {
_, err = stmt.Exec(i)
if err != nil {
log.Fatal(err)
}
}
// tx commit
err = tx.Commit()
if err != nil {
log.Fatal(err)
}
// stmt.Close() runs here!
}
// 使用Exec()，最好使用预准备语句来完成INSERT，UPDATE，DELETE或其他不返回行的语句
// Exec执行该语句会生成一个sql.Result，它提供对语句元数据的访问：最后插入的ID和受影响的行数
func insertDemo(db *sql.DB) {
stmt, err := db.Prepare("INSERT INTO users(name) VALUES(?)")
if err != nil {
log.Fatal(err)
}
res, err := stmt.Exec("Dolly")
if err != nil {
log.Fatal(err)
}
lastId, err := res.LastInsertId()
if err != nil {
log.Fatal(err)
}
rowCnt, err := res.RowsAffected()
if err != nil {
log.Fatal(err)
}
log.Printf("ID = %d, affected = %d\n", lastId, rowCnt)
}
// Exec() 不关心结果怎么办，采用Exec而不是Query
// 注意：
// db.Query()保留了一个数据库连接直至被关闭。
// 由于可能存在未读数据（例如，更多数据行），因此无法使用连接。
// 在下面的示例中，db.Query()永远不会再次释放连接。
// 垃圾收集器最终将为您关闭底层net.Conn，但这可能需要很长时间。
// 此外，database/sql包会一直跟踪其池中的连接，希望您在某个时刻释放它，以便可以再次使用该连接。
// 因此，这种反模式是很容易耗尽资源，导致连接过多的错误。
//
func onlyDeleteDemo(db *sql.DB) {
_, err := db.Exec("DELETE FROM users") // OK
if err != nil {
log.Fatal(err)
}
// Query()会返回sql.Rows，db.Query()永远不会再次释放连接
_, err = db.Query("DELETE FROM users") // BAD
if err != nil {
log.Fatal(err)
}
}
// 使用事务，:
// 开启事务：db.Begin()
// Commit()、Rollback()
// 注意：
// 1. 在事务中创建的预准备语句专门绑定到特定事务上；
// 2. 不应该在SQL代码中使用与事务相关的函数（如Begin()和Commit()）与SQL语句（如BEGIN和COMMIT）混合使用，因为可能导致混乱：
// a) 数据库的状态可能与表示它的Go变量的状态不同步
// b) 你可以相信你在一个事务中的单个连接上执行查询，而实际上Go已经无形地创建了几个连接，而某些语句不是事务的一部分。
// c) Tx对象可以保持打开状态，保留池中的连接而不返回它
// 3. 所有在事务中的操作，都应该基于Tx对象，而不是基于db对象，因为db.Exec()类似操作可能在其他连接上执行
// 4. 如果需要使用多个修改连接状态的语句，也需要使用Tx对象，比如：
// a)创建仅对一个连接可见的临时表
// b)设置变量，例如MySQL的SET @var：= somevalue语法。
// c)更改连接选项，例如字符集或超时
func txDemo(db *sql.DB) {
tx, err := db.Begin()
if err != nil {
log.Fatal(err)
}
defer tx.Rollback()
// Exec Update
_, err = tx.Exec("UPDATE users SET username = ? WHERE uid=?", "clark100", 1)
if err != nil {
mysql.ErrHandle(err, "tx.Exec")
}
// Prepare Optimizing much fast for tx.Exec in for loops
stmt, err := tx.Prepare(`INSERT INTO users (username, email, passwd) VALUES (?, ?,?)`)
if err != nil {
mysql.ErrHandle(err, "tx.Prepare")
}
// Exec Insert
for i := 0; i < 1000; i++ {
name, email, pass := CreateUser()
_, err = stmt.Exec(name, email, pass)
if err != nil {
mysql.ErrHandle(err, "stmt.Exec")
}
}
//提交
tx.Commit()
}
// 用户信息
func CreateUser() (name, email, passwd string) {
rand.Seed(time.Now().UnixNano())
//User: Clark Peter
letters := [26]byte{}
for i:= 0; i< 26; i++ {
letters[i] = byte(97+i)
}
// 随机字符
randChar := func(len int) (chars []byte) {
for j:=0; j<len; j++ {
rIndex := rand.Intn(26)
chars = append(chars, letters[rIndex])
}
chars[0] -= 97-65
return chars
}
// UserName
name = fmt.Sprintf("%s %s", randChar(5-rand.Intn(3)), randChar(8-rand.Intn(4)))
email = fmt.Sprintf("%s@%s.com", randChar(4), randChar(3))
passwd = fmt.Sprintf("%x", md5.Sum([]byte(email)))
return
}
// tx test
func txConnDemo(db *sql.DB) {
tx, err := db.Begin()
if err != nil {
mysql.ErrHandle(err, "tx db.Begin")
}
defer tx.Rollback()
rows, err := tx.Query(`SELECT username, email FROM users`) // Uses tx's connection
if err != nil {
mysql.ErrHandle(err, "tx.Query")
}
defer rows.Close()
var username, email string
for rows.Next() {
if err:= rows.Err(); err != nil {
mysql.ErrHandle(err, "rows.Err()")
}
err = rows.Scan(&username, &email)
if err != nil {
mysql.ErrHandle(err, "rows.Scan")
}
// show
fmt.Println(username, email)
mysql.ShowStatus(db)
// ERROR! tx's connection is already busy!
tx.Query("select * from images where id = ?", 1)
// OK
db.Query("select * from images where id = ?", 1)
time.Sleep(200 * time.Millisecond)
}
tx.Commit()
}
|
小结
简单介绍了数据库的Prepare、CRUD以及连接池的相关概念；
代码部分主要是偏操作模拟，未做过多的最佳实践模拟，比如错误处理等，这块需要重新规划和涉及，这块可以对代码冗余度降低有较大意义。
12. 参考
- go sql接口：https://github.com/golang/go/wiki/SQLInterface
- go sql驱动：https://github.com/golang/go/wiki/SQLDrivers
- mysql驱动实现：https://github.com/go-sql-driver/mysql/
