Golang database operation (sqlx) and indefinite field result query

Time:2021-9-24

1、 Mysql database

Why use a database

  • At the beginning, the user manually records the data, which cannot be saved and traced for a long time;
  • Then a file system is created, which can be saved for a long time, but it is troublesome to query, trace and update, and the data can be redundant and repeated;
  • It realizes the way of database, which can be saved for a long time, facilitate a series of operations such as query, traceability and update, and set some constraints for self-control of data.

Briefly introduce the characteristics of MySQL database: relational database, small size, fast speed, low cost, open source code, suitable for small and medium-sized websites, very suitable for beginners

2、 Golang operation MySQL

1. Existing test database table user

Golang database operation (sqlx) and indefinite field result query

2. Connect to MySQL database

2.1. Third party libraries used

Github.com/go-sql-driver/mysql (driver)

Github.com/jmoiron/sqlx (operation encapsulation of driver)

2.2. Connection

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
package main
 
import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)
 
func main() {
   db, err := sqlx.open("mysql", "username:[email protected](127.0.0.1:3306)/test?charset=utf8mb4")
   if err != nil {
      fmt.println("open mysql failed,", err)
   }
   DB. Setmaximidleconns (5) // set the maximum idle number
   DB. Setmaxopenconns (15) // set the maximum number of connections
}
 
  //db, err := sqlx.open(Database type, "User name: password @ TCP (address: port) / database name")

3. Select database query

?
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
package main
 
import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)
 
var db *sqlx.db
 
func initdb() {
   var err error
   db, err = sqlx.open("mysql", "username:[email protected](127.0.0.1:3306)/test?charset=utf8mb4&parsetime=true&loc=local")
   if err != nil {
      fmt.println("open mysql failed,", err)
   }
   db.setmaxidleconns(5)
   db.setmaxopenconns(15)
}
 
type user struct {
   id   int64  `db:"id"`
   name string `db:"name"`
   age  int64  `db:"age"`
   sex  string `db:"sex"`
}
 
func main() {
   initdb()
   defer db.close()
 
   var user []user
   sqlstr := "select * from user"
   err := db.select(&user, sqlstr)
   if err != nil {
      fmt.println(err)
   }
   fmt.println(user)
}

Get results – >

[{1 Zhang San 20 male} {2 Li Si 21 female} {3 Wang Wu 25 male}]

4. Insert database

?
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
package main
 
import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)
 
var db *sqlx.db
 
func initdb() {
   var err error
   db, err = sqlx.open("mysql", "superxon:[email protected](172.20.3.12:3306)/test?charset=utf8mb4")
   if err != nil {
      fmt.println("open mysql failed,", err)
   }
   db.setmaxidleconns(5)
   db.setmaxopenconns(15)
}
 
type user struct {
   id   int64  `db:"id"`
   name string `db:"name"`
   age  int64  `db:"age"`
   sex  string `db:"sex"`
}
 
func main() {
   initdb()
   defer db.close()
 
   var user = user{
      name: "Little six",
      age: 18,
      sex: "Female",
   }
 
   sqlstr := "insert into user(name, age, sex) values (?,?,?)"
   res, err := db.exec(sqlstr, user.name, user.age, user.sex)
   if err != nil {
      fmt.println(err)
   }
   c, _ := res.rowsaffected()
   fmt.println("How many rows were created", c)
}

Get results – >

How many rows were created 1

Golang database operation (sqlx) and indefinite field result query

5. Update database update

?
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
package main
 
import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)
 
var db *sqlx.db
 
func initdb() {
   var err error
   db, err = sqlx.open("mysql", "superxon:[email protected](172.20.3.12:3306)/test?charset=utf8mb4")
   if err != nil {
      fmt.println("open mysql failed,", err)
   }
   db.setmaxidleconns(5)
   db.setmaxopenconns(15)
}
 
type user struct {
   id   int64  `db:"id"`
   name string `db:"name"`
   age  int64  `db:"age"`
   sex  string `db:"sex"`
}
 
func main() {
   initdb()
   defer db.close()
 
   var user = user{
      id: 4,
      age: 20,
   }
 
   sqlstr := "update user set age=? where id=?"
   res, err := db.exec(sqlstr, user.age, user.id)
   if err != nil {
      fmt.println(err)
   }
   c, _ := res.rowsaffected()
   fmt.println("How many rows have been changed", c)
}

Get results – >

How many rows have been changed 1

Golang database operation (sqlx) and indefinite field result query

6. Delete database deletion

?
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
package main
 
import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)
 
var db *sqlx.db
 
func initdb() {
   var err error
   db, err = sqlx.open("mysql", "superxon:[email protected](172.20.3.12:3306)/test?charset=utf8mb4")
   if err != nil {
      fmt.println("open mysql failed,", err)
   }
   db.setmaxidleconns(5)
   db.setmaxopenconns(15)
}
 
type user struct {
   id   int64  `db:"id"`
   name string `db:"name"`
   age  int64  `db:"age"`
   sex  string `db:"sex"`
}
 
func main() {
   initdb()
   defer db.close()
 
   deleteid := 3
 
   sqlstr := "delete from user where id=?"
   res, err := db.exec(sqlstr, deleteid)
   if err != nil {
      fmt.println(err)
   }
   c, _ := res.rowsaffected()
   fmt.println("How many rows were deleted?", c)
}

Get results – >

How many rows were deleted 1

Golang database operation (sqlx) and indefinite field result query

3、 Generate dynamic field database query results

In the project, we often encounter a problem: query different tables in the same function, generate different results, and rebuild the structure every time

Idea: make the result into [] map [string] string type, so that you can fill in all the data obtained from the query.

Built in libraries are used

database/sql

?
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
package main
 
import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
)
 
var db *sql.db
 
func initdb() {
   var err error
   db, err = sql.open("mysql", "superxon:[email protected](172.20.3.12:3306)/test?charset=utf8mb4")
   if err != nil {
      fmt.println("open mysql failed,", err)
   }
   db.setmaxidleconns(5)
   db.setmaxopenconns(15)
}
 
func main() {
   initdb()
   defer db.close()
 
   sqlstr := "select * from user" //It can be replaced by other query statements, and the corresponding query results can be obtained without building the stored structure every time
   rows, err := db.query(sqlstr)
   if err != nil {
      fmt.println(err)
   }
   defer rows.close()
 
   //Lists the field names of all query results
   cols, _ := rows.columns()
 
   //Values is the value of each column, which is obtained here in byte
   values := make([][]byte, len(cols))
   //query. scan parameter. Because the length of the column queried each time is variable, len (cols) is used to determine the length of the current query
   scans := make([]interface{}, len(cols))
   //Let each row of data be filled into [] [] byte
   for i := range values {
      scans[i] = &values[i]
   }
   res := make([]map[string]string, 0)
   for rows.next() {
      _ = rows.scan(scans...)
      row := make(map[string]string)
      For K, V: = range values {// each row of data is placed in values. Now move it to row
         key := cols[k]
         row[key] = string(v)
      }
      res = append(res, row)
   }
   fmt.println(res)
}

Get results – >

[map [age: 20 ID: 1 Name: Zhang San sex: male] map [age: 21 ID: 2 Name: Li Si sex: female] map [age: 20 ID: 4 Name: Xiao Liu sex: female]]

This is the end of this article about golang database operation (sqlx) and indefinite field result query. For more information about golang database operation and indefinite field result query, please search the previous articles of developeppaper or continue to browse the relevant articles below. I hope you can support developeppaper in the future!