Go language operation MySQL example (additions and deletions)

This is a creation in Article, where the information may have evolved or changed.

http://blog.csdn.net/jesseyoung/article/details/40398321

Introduction to the Go language connection MySQL
Go official only offers two packages under the database Package,database package sql,sql/driver. The two packages are used to define the interface of the operational database, which ensures that regardless of which database is used, they operate the same way.
But go official does not provide the connection database driver, if you want to operate the database, but also need a third-party driver package, the most commonly used are:
Https://github.com/Go-SQL-Driver/MySQL Support Database/sql, all with Go write.
Https://github.com/ziutek/mymysql supports Database/sql, and also supports custom interfaces, all with go writing.
It is recommended to use the former, because the former is more efficient, the comparison between the two efficiency can refer to benchmark test results: Https://github.com/go-sql-driver/sql-benchmark

Go to connect to other mainstream database drive introduction can refer to:

https://code.google.com/p/go-wiki/wiki/SQLDrivers

2 Test environment preparation
Operating system: Red Hat Enterprise Linux Server Release 6.4
MySQL version: mysql-5.5.28
Install Git client (easy to get MySQL driver from GitHub)

[Plain] View plain copy

1. [root@localhost/]# yum install git

Get MySQL Driver

[Plain] View plain copy

1. [Root@localhost/]# Go get github.com/go-sql-driver/mysql
2. [Root@localhost/]# ls
3. Pkg src

You can see more than two folders in the current directory pkg and SRC, copy the src folder to the Go Program installation directory or go to work environment can be

[Plain] View plain copy

1. [Root@localhost/]# cp-r src/usr/local/go/

3 Programming examples

[Plain] View plain copy

2. Package Main
6. Import (
8. "Database/sql"
10. "FMT"
12. _ "Github.com/go-sql-driver/mysql"
14. "Reflect"
16. )
20. Func Main () {
22. /*DSN Data Source Name
24. [username[:p assword]@] [Protocol[(address)]]/dbname[?param1=value1¶mn=valuen]
26. User@unix (/path/to/socket)/dbname
28. User:password@tcp (localhost:5555)/dbname?charset=utf8&autocommit=true
30. USER:PASSWORD@TCP ([de:ad:be:ef::ca:fe]:80)/dbname?charset=utf8mb4,utf8
32. User:password@/dbname
34. No database: user:password@/
36. */
38. DB, err: = SQL. Open ("MySQL", "jesse:jesse@tcp (127.0.0.1:3306)/?charset=utf8")//The first parameter is the driver name
40. Checkerr (ERR)
42. Db. Query ("drop database if exists tmpdb")
44. Db. Query ("CREATE Database Tmpdb")
46. Db. Query ("Use tmpdb")
48. Db. Query ("CREATE TABLE Tmpdb.tmptab (c1 int, C2 varchar (), C3 varchar (20))")
50. Db. Query ("INSERT into Tmpdb.tmptab values (101, ' name 1 ', ' Address1 '), (102, ' Name 2 ', ' Address2 '), (103, ' name 3 ', ' ADDRESS3 '), (104, ' Name 4 ', ' Address4 ') ")
52. Checkerr (ERR)
54. Query, Err: = db. Query ("SELECT * from Tmpdb.tmptab")
58. Checkerr (ERR)
60. V: = reflect. ValueOf (query)
62. Fmt. Println (v)
64. Fmt. Println ("--increase data test--")
66. Printresult (query)
68. Db. Query ("Delete from Tmpdb.tmptab where C1 = 101")
70. Checkerr (ERR)
72. Query2, _: = db. Query ("SELECT * from Tmpdb.tmptab")
74. Fmt. Println ("--delete data test--")
76. Printresult (Query2)
78. Db. Query ("Update Tmpdb.tmptab set c3 = ' Address4 ' WHERE C1 = 103")
80. Checkerr (ERR)
82. Query3, _: = db. Query ("SELECT * from Tmpdb.tmptab")
84. Fmt. Println ("--Update data test--")
86. Printresult (Query3)
88. Db. Query ("Delete from Tmpdb.tmptab")
90. Checkerr (ERR)
92. Query4, _: = db. Query ("SELECT * from Tmpdb.tmptab")
94. Fmt. Println ("--empty data test--")
96. Printresult (Query4)
98. Db. Query ("drop table Tmpdb.tmptab")
100. Db. Query ("drop Database Tmpdb")
102. stmt, err: = db. Prepare ("CREATE Database Tmpdb")
104. Db. Close ()
106. }
110. Func Checkerr (errmasg error) {
112. If ERRMASG! = Nil {
114. Panic (ERRMASG)
116. }
118. }
122. Func printresult (Query *sql. Rows) {
124. Column, _: = Query. Columns ()//read out the queried column field name
126. Values: = Make ([][]byte, Len (column))//values is the value of each column, obtained in byte
128. Scans: = Make ([]interface{}, Len (column))//The length of the query is fixed with Len (column), because each query comes out of an indeterminate column
130. For I: = range values {//Let each row of data populate [][]byte]
132. Scans[i] = &values[i]
134. }
136. Results: = make (map[int]map[string]string)//Last obtained map
138. I: = 0
140. For query. Next () {//loop, let the cursor Move Down
142. If err: = Query. Scan (Scans ...); Err! = Nil {//query. The indefinite Long value of the scan query is placed in scans[i] = &values[i], i.e. each line is placed in the values
144. Fmt. PRINTLN (ERR)
146. Return
148. }
150. Row: = Make (map[string]string)/per row of data
152. For k, V: = range values {//each row of data is placed in values, now move it to row
154. Key: = Column[k]
156. Row[key] = string (v)
158. }
160. Results[i] = row//load in result set
162. i++
164. }
166. For k, V: = Range Results {//Query out array
168. Fmt. Println (k, v)
170. }
172. }

4 Running the program

4.1 Compiling and running

[Plain] View plain copy < param name= "allowfullscreen" value= "false" >< param name= "wmode" value= "Transparent" >

1. [Root@localhost/]# Go Build mysqlgotest.go
2. [Root@localhost/]# ls
3. Mysqlgotest.go Mysqlgotest
4. [Root@localhost/]#./mysqlgotest

4.2 Direct operation

[Plain] View plain copy

2. [Root@localhost/]# Go Run mysqlgotest.go
4. <*sql. Rows value>
6. --Increase data testing--
8. 0 map[c1:101 C2: Name 1 C3:address1]
10. 1 map[c1:102 C2: Name 2 c3:address2]
12. 2 map[c1:103 C2: Name 3 C3:ADDRESS3]
14. 3 map[c1:104 C2: Name 4 C3:ADDRESS4]
16. --Delete data test--
18. 0 map[c1:102 C2: Name 2 c3:address2]
20. 1 map[c1:103 C2: Name 3 C3:ADDRESS3]
22. 2 map[c1:104 C2: Name 4 C3:ADDRESS4]
24. --Update data test--
26. 0 map[c1:102 C2: Name 2 c3:address2]
28. 1 map[c1:103 C2: Name 3 C3:ADDRESS4]
30. 2 map[c1:104 C2: Name 4 C3:ADDRESS4]
32. --Empty data test--

5 Supplemental Knowledge

5.1 Avoid Chinese garbled characters

To ensure that the program is written to the database and read out from the database does not appear garbled, you need to do the following configuration: Go client program level: Go program File Settings encoding UTF8, such as

[Plain] View plain copy

1. DB, err: = SQL. Open ("MySQL", "jesse:jesse@tcp (127.0.0.1:3306)/?charset=utf8")

MySQL database level:
Set MySQL database client and server configuration to UTF8
For example:
Configuring in the MY.CNF configuration file

[Plain] View plain copy < param name= "allowfullscreen" value= "false" >< param name= "wmode" value= "Transparent" >

1. [MySQL]
2. Default_character_set=utf8
3. [Mysqld]
4. Character-set-server=utf8
5. Collation-server=utf8_bin

5.2 Go language reflection mechanism

Purpose: Can get Object data type

Many languages have a reflection mechanism. Through reflection, we can know an unknown property of the image, the method.

When writing a function, sometimes you need a different object or some property of the class, but the program does not recognize the object or class that is needed, and then it needs to be manipulated by reflection. By reflection, you become very convenient to load, detect, and use objects or classes that are completely unknown during compilation.

The so-called reflection, which is the equivalent of physical reflection, you can see your own touch through the mirror, the function through reflection, you can get the information you want. In Golang reflector reflect, the reflection type type () and value () can change the value of the reflected back variable. For example, gets the type of the variable value, which can be reflect by the function. ValueOf () to operate.

[Plain] View plain copy

1. var value interface {} = &user{1, "Tom", "Nan"}
2. V: = reflect. ValueOf (value)
3. Fmt. Println (v)

 
****************************************************************************************
    Original address: http://blog.csdn.net/jesseyoung/article/details/40398321
    Blog home: http://blog.csdn.net/ Jesseyoung
****************************************************************************************