Golang Database operation (SQLX) & indeterminate field result query

Mysql database

Why use a database

1. At the beginning, people manually record data, which can not be kept for a long time, traceability;
2. Then a file system is created, which can be saved for a long time. However, it is difficult to query and update the file system, and the data can be redundant and repeated.
3. Realized the way of database, can be saved for a long time, convenient query, trace, update and so on a series of operations, can set some constraints for data self-control and so on.

Mysql database features:Relational database, small size, fast speed, low cost, open source code, suitable for small and medium-sized websites, very suitable for beginners to learn

Mysql > select * from Golang

1. The currenttestDatabase tableuser

id	name	age	sex
1	Zhang SAN	20	male
2	Li si	21	female
3	Cathy	25	male

2. Connect to the mysql database

2.1. Third-party libraries used

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

github.com/jmoiron/sqlx (Operational encapsulation of the driver)

2.2. The connection

package main

import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)

func main(a) {
   db, err := sqlx.Open("mysql".Username: password, "@" (127.0.0.1:3306)/test? charset=utf8mb4")
   iferr ! =nil {
      fmt.Println("open mysql failed,", err)
   }
   db.SetMaxIdleConns(5) // Set the maximum free number
   db.SetMaxOpenConns(15) // Set the maximum number of connections
}
Copy the code

  / / db, err: = SQLX. Open (" database type ", "user name: password @ TCP (address: port)/database name")
Copy the code

3. SELECT database query operations

package main

import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB(a) {
   var err error
   db, err = sqlx.Open("mysql".Username: password, "@" (127.0.0.1:3306)/test? charset=utf8mb4&parseTime=true&loc=Local")
   iferr ! =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(a) {
   initDB()
   defer db.Close()

   var user []User
   sqlStr := "SELECT * FROM user"
   err := db.Select(&user, sqlStr)
   iferr ! =nil {
      fmt.Println(err)
   }
   fmt.Println(user)
}
Copy the code

Get the result ->

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

4. Insert Insert the database

package main

import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB(a) {
   var err error
   db, err = sqlx.Open("mysql"."Superxon: superxon @ (172.20.3.12:3306)/test? charset=utf8mb4")
   iferr ! =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(a) {
   initDB()
   defer db.Close()

   var user = User{
      Name: Small six "",
      Age: 18,
      Sex: "Female",
   }

   sqlStr := "INSERT INTO user(name, age, sex) VALUES (? ,? ,?) "
   res, err := db.Exec(sqlStr, user.Name, user.Age, user.Sex)
   iferr ! =nil {
      fmt.Println(err)
   }
   c, _ := res.RowsAffected()
   fmt.Println("How many rows are created?", c)
}
Copy the code

Get the result ->

How many rows are created 1

id	name	age	sex
1	Zhang SAN	20	male
2	Li si	21	female
3	Cathy	25	male
4	Small six	18	female

5. Update Updates the database

package main

import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB(a) {
   var err error
   db, err = sqlx.Open("mysql"."Superxon: superxon @ (172.20.3.12:3306)/test? charset=utf8mb4")
   iferr ! =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(a) {
   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)
   iferr ! =nil {
      fmt.Println(err)
   }
   c, _ := res.RowsAffected()
   fmt.Println("How many rows were changed?", c)
}
Copy the code

Get the result ->

How many rows are changed by 1

id	name	age	sex
1	Zhang SAN	20	male
2	Li si	21	female
3	Cathy	25	male
4	Small six	20	female

6. DELETE Deletes the database

package main

import (
   "fmt"
   _ "github.com/go-sql-driver/mysql"
   "github.com/jmoiron/sqlx"
)

var db *sqlx.DB

func initDB(a) {
   var err error
   db, err = sqlx.Open("mysql"."Superxon: superxon @ (172.20.3.12:3306)/test? charset=utf8mb4")
   iferr ! =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(a) {
   initDB()
   defer db.Close()

   deleteId := 3

   sqlStr := "DELETE FROM user WHERE id=?"
   res, err := db.Exec(sqlStr, deleteId)
   iferr ! =nil {
      fmt.Println(err)
   }
   c, _ := res.RowsAffected()
   fmt.Println(How many rows were deleted?, c)
}
Copy the code

Get the result ->

How many rows are deleted 1

id	name	age	sex
1	Zhang SAN	20	male
2	Li si	21	female
4	Small six	20	female

Third, generate dynamic field database query results

A common problem encountered in projects is:In the same function, different tables are queried, different results are generated, and the structure is rebuilt each time

[]map[string]string = []map[string]string = []map[string]

You use a built-in library

database/sql

package main

import (
   "database/sql"
   "fmt"
   _ "github.com/go-sql-driver/mysql"
)

var db *sql.DB

func initDB(a) {
   var err error
   db, err = sql.Open("mysql"."Superxon: superxon @ (172.20.3.12:3306)/test? charset=utf8mb4")
   iferr ! =nil {
      fmt.Println("open mysql failed,", err)
   }
   db.SetMaxIdleConns(5)
   db.SetMaxOpenConns(15)}func main(a) {
   initDB()
   defer db.Close()

   sqlStr := "SELECT * FROM user" // Can be replaced by other query statements, can get the corresponding query results, do not have to build the stored structure each time
   rows, err := db.Query(sqlStr)
   iferr ! =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 retrieved in byte
   values := make([] []byte.len(cols))
   Len (cols) is used to set the length of the query
   scans := make([]interface{}, len(cols))
   // fill each row with [][]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 is placed in values, now move it to row
         key := cols[k]
         row[key] = string(v)
      }
      res = append(res, row)
   }
   fmt.Println(res)
}
Copy the code

Get the result ->

[map[age:20 id:1 name: zhang SAN sex: male] [age:21 id:2 name: Li Si sex: female] [age:20 id:4 name: xiao liu sex: female]

Four, like, comment, follow

Like, comment, follow me, I give you a good tool to manage the database :)