Shulou(Shulou.com)06/03 Report--

Editor to share with you about the Golang database operation and indeterminate field results query example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

I. Mysql database

Why use a database?

At the beginning, people record the data manually, which can not be saved for a long time.

Then a file system is created, which can be saved for a long time, but the query is troublesome to update retroactively, and the data can be redundant and duplicated.

To achieve the way of the database, can be long-term preservation, convenient query, traceability, update and other operations, can set some constraints for data self-control and so on.

This paper briefly introduces the characteristics of Mysql database: relational database, small size, high speed, low cost, open source code, suitable for small and medium-sized websites, very suitable for beginners.

2. Golang operates Mysql

1. Existing test database table user

two。 Connect to mysql database

2.1. Third-party libraries used

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

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

2.2. Connect

Package mainimport ("fmt" _ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx") func main () {db, err: = sqlx.Open ("mysql", "username:password@ (127.0.0.1 github.com/go-sql-driver/mysql 3306) / test?charset=utf8mb4") if err! = nil {fmt.Println ("open mysql failed," Err)} db.SetMaxIdleConns (5) / / set the maximum number of idle 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 operation

Package mainimport ("fmt" _ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx") var db * sqlx.DBfunc initDB () {var err error db, err = sqlx.Open ("mysql", "username:password@ (127.0.0.1 github.com/go-sql-driver/mysql 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 the result->

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

4. Insert database insert operation

Package mainimport ("fmt" _ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx") var db * sqlx.DBfunc initDB () {var err error db, err = sqlx.Open ("mysql", "superxon:superxon@ (172.20.3.12 github.com/go-sql-driver/mysql 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: "Primary 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 are created", c)}

Get the result->

How many rows have been created 1

5. Update database update operation

Package mainimport ("fmt" _ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx") var db * sqlx.DBfunc initDB () {var err error db, err = sqlx.Open ("mysql", "superxon:superxon@ (172.20.3.12 github.com/go-sql-driver/mysql 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 are changed", c)}

Get the result->

How many rows have been changed 1

6. Delete DELETE database

Package mainimport ("fmt" _ "github.com/go-sql-driver/mysql"github.com/jmoiron/sqlx") var db * sqlx.DBfunc initDB () {var err error db, err = sqlx.Open ("mysql", "superxon:superxon@ (172.20.3.12 github.com/go-sql-driver/mysql 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 are deleted", c)}

Get the result->

How many rows have been deleted 1

Third, generate dynamic field database query results

A problem is often encountered in a project: in the same function, different tables are queried, different results are generated, and the structure is rebuilt each time.

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

Using built-in libraries

Database/sql

Package mainimport ("database/sql"fmt" _ "github.com/go-sql-driver/mysql") var db * sql.DBfunc initDB () {var err error db, err = sql.Open ("mysql", "superxon:superxon@ (172.20.3.12 github.com/go-sql-driver/mysql) / 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" / / can be replaced with other query statements to get the corresponding query results without having to build the stored structure rows every time Err: = db.Query (sqlStr) if err! = nil {fmt.Println (err)} defer rows.Close () / / list the field name of all query results cols, _: = rows.Columns () / / values is the value of each column The parameter values: = make ([] [] byte, len (cols)) / / query.Scan in byte is obtained here, because the columns from each query are of variable length. Use len (cols) to fix the length of the current query scans: = make ([] interface {}, len (cols)) / / fill each row of data 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 line of data is put in values, now move it to row key: = cols [k] row [key] = string (v)} res = append (res, row)} fmt.Println (res)}

Get the result->

[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: primary six sex: female]]

The above is all the contents of the article "sample Analysis of Golang Database Operations and indeterminate Field results query". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.