TheRiver | blog

You have reached the world's edge, none but devils play past here

0%

golang连接mysql

Posted on Edited on
Symbols count in article: 4.5k Reading time ≈ 4 mins.

参考

http://go-database-sql.org/

code

1
2
3
4
5
6
7
8
9
 CREATE TABLE `tt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `field` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`),
  KEY `index_d` (`d`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1
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
package main

import (
	"database/sql"
	"fmt"
	"log"
	"strings"

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

//go get github.com/go-sql-driver/mysql
func mysql_open() (*sql.DB, error) {
	db, err := sql.Open("mysql",
		"root:123456@tcp(127.0.0.1:3306)/mysql")
	if err != nil {
		log.Fatal(err)
	}

	return db, err
}

/*
MySQL [mysql]> select * from tt;
+----+------+------+-------+
| id | c    | d    | field |
+----+------+------+-------+
|  1 |    1 |  777 | hello |
|  2 |    2 |  200 |       |
|  3 |    3 |  999 |       |
|  9 |    5 |    0 |       |
| 20 |    4 |    0 |       |
| 25 |    6 |  600 |       |
+----+------+------+-------+
6 rows in set (0.00 sec)
*/
type tt struct {
	id    int
	c     int
	d     int
	field string
}

func queryAll(db *sql.DB) []*tt {
	var ret []*tt
	//query all
	rows, err := db.Query("select id, c, d, field from tt where 1 = ?", 1)
	defer rows.Close()
	if err != nil {
		log.Fatal(err)
	}

	fmt.Println("id\tc\td\tfield")
	for rows.Next() {
		data := &tt{}
		err = rows.Scan(&data.id,
			&data.c,
			&data.d,
			&data.field)
		if err != nil {
			log.Fatal(err)
		}
		ret = append(ret, data)
		//log.Println(data.id, "\t", data.c, "\t", data.d, "\t", data.field)
		fmt.Println(data.id, "\t", data.c, "\t", data.d, "\t", data.field)
	}

	err = rows.Err()
	if err != nil {
		log.Fatal(err)
	}

	return ret
}

func querySingle(db *sql.DB) *tt {
	var data tt
	//query single-row
	err := db.QueryRow("select id, c, d, field from tt where id = ?", 1).Scan(&data.id,
		&data.c,
		&data.d,
		&data.field)
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println()
	fmt.Println("id\tc\td\tfield")
	fmt.Println(data.id, "\t", data.c, "\t", data.d, "\t", data.field)
	return &data
}

func modifybyTx(db *sql.DB, data []*tt) {
	tx, err := db.Begin()
	if err != nil {
		fmt.Println("begin tx err!")
		log.Fatal(err)
	}

	defer func() {
		if err == nil {
			_ = tx.Commit()
		} else {
			_ = tx.Rollback()
		}
	}()

	param := make([]interface{}, 0)
	for _, row := range data {
		param = append(param, row.id)
	}

	sql := strings.Builder{}
	sql.WriteString(fmt.Sprintf("update tt set field = unix_timestamp(NOW()) where id in (?%s)",
		strings.Repeat(", ?", len(data)-1)))

	_, err = tx.Exec(sql.String(), param...)
	if err != nil {
		log.Fatal(err)
	}
}

func main() {
	db, err := mysql_open()
	if err != nil {
		fmt.Println("open mysql error!")
		return
	}

	var data []*tt
	data = queryAll(db)
	fmt.Println("-------------------------")
	querySingle(db)
	fmt.Println("-------------------------")
	modifybyTx(db, data)
	queryAll(db)
	fmt.Println("-------------------------")

	defer func() {
		db.Close()
	}()
}


error

next循环异常结束

1
2
3
4
5
6
for rows.Next() {
	// ...
}
if err = rows.Err(); err != nil {
	// handle the error here
}

The error from rows.Err() could be the result of a variety of errors in the rows.Next() loop. The loop might exit for some reason other than finishing the loop normally, so you always need to check whether the loop terminated normally or not. An abnormal termination automatically calls rows.Close(), although it’s harmless to call it multiple times.

查询结果为空

1
2
3
4
5
6
7
8
9
10
11
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)

未知列

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
func Unknown(db *sql.DB) {
	//query all
	rows, err := db.Query("select * from tt")
	defer rows.Close()
	if err != nil {
		log.Fatal(err)
	}

	cols, err := rows.Columns() // Remember to check err afterwards
	if err != nil {
		log.Fatal(err)
	}
	fmt.Println(len(cols))
	// if len(cols) == 11 {
	// 	// Percona Server
	// } else if len(cols) > 8 {
	// 	// Handle this case
	// }

	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.
		fmt.Println(vals)
	}

}

ending

----------- ending -----------