Go MySQL operation
Install: go get - u GitHub com/go-sql-driver/mysql
The operation database driver of GO language supports connection pool and is a standard library for concurrency security. There is no specific implementation, but lists the specific contents of some third-party libraries that need to be implemented
//Successfully connected to MySQL for the first time
package main
import (
"database/sql"
_ "github.com/go-sql-driver/mysql" // _ Want to initialize when init()
"log"
)
func main() {
// The root user name 1qa2ws3ed is the IP: Port cause library name behind the password
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err := sql.Open("mysql", dsn)
if err != nil {
panic(err)
}
// ping is an attempt to connect to the MySQL database
if err = db.Ping(); err != nil{
panic(err)
}
log.Fatalln("Mysql Database connection succeeded")
}
- Go calls MySQL and encapsulates it into a function
package main
import (
"database/sql"
"encoding/json"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
func InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = sql.Open("mysql", dsn)
CheckErr(err)
err = db.Ping()
CheckErr(err)
fmt.Println("Database connection succeeded...")
// Set the maximum number of connections to the database connection pool
db.SetConnMaxLifetime(10)
//Set the maximum number of idle connections
db.SetMaxIdleConns(5)
return
}
type data struct {
Username string `json:"username"`
Password string `json:"password"`
}
func main() {
err := InitDB()
CheckErr(err)
query, err := db.Query("select username, password from test")
CheckErr(err)
for query.Next(){
line := data{}
// The scan method must be called when querying data. If the scan connection channel is not used, the connection cannot be released
_ = query.Scan(&line.Username, &line.Password)
fmt.Println(line)
dataDic := map[string]string{
"username": line.Username,
"password": line.Password,
}
marshal, _ := json.Marshal(dataDic)
fmt.Println(string(marshal))
}
}
func CheckErr(err error) {
if err != nil {
fmt.Println(err)
panic(err)
}
}
- GO - addition, deletion, modification and query of MySQL
package main
import (
"database/sql"
"encoding/json"
"fmt"
"time"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
// InitDB database connection initialization
func InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = sql.Open("mysql", dsn)
CheckErr(err)
err = db.Ping()
CheckErr(err)
fmt.Println("Database connection succeeded...")
// Set the maximum number of connections to the database connection pool
db.SetConnMaxLifetime(10)
//Set the maximum number of idle connections
db.SetMaxIdleConns(5)
return
}
type data struct {
Username string `json:"username"`
Password string `json:"password"`
}
// SelectQuery query function
func SelectQuery() {
sqlStr := "select username, password from test where id > ?"
query, err := db.Query(sqlStr, 1)
CheckErr(err)
defer query.Close()
fmt.Printf("It's Beijing time %s , Have you made progress today?\n", time.Now().Format("2006-01-02 15:04:05"))
for query.Next() {
line := data{}
// The scan method must be called when querying data. If the scan connection channel is not used, the connection cannot be released
_ = query.Scan(&line.Username, &line.Password)
//fmt.Println(line)
dataDic := map[string]string{
"username": line.Username,
"password": line.Password,
}
marshal, _ := json.Marshal(dataDic)
fmt.Printf("The queried data is %s\n", string(marshal))
}
}
// InsertQuery insert data
func InsertQuery() {
// sql statement
sqlStr := `insert into test (username,password) values ("kuQi", "123qwe")`
result, err := db.Exec(sqlStr)
CheckErr(err)
id, err := result.LastInsertId()
CheckErr(err)
fmt.Printf("Of successfully inserted data id by %v", id)
}
// UpdateQuery update data function
func UpdateQuery(dataField string, user string) {
sqlStr := `update test set password=? where username=?`
result, err := db.Exec(sqlStr, dataField, user)
CheckErr(err)
rowsAffected, err := result.RowsAffected()
CheckErr(err)
fmt.Printf("Updated field id by%d\n", rowsAffected)
}
// DeleteQuery delete
func DeleteQuery(id int) {
sqlStr := `delete from test where id=?`
result, err := db.Exec(sqlStr, id)
CheckErr(err)
rowsAffected, err := result.RowsAffected()
CheckErr(err)
if rowsAffected == 0 {
fmt.Printf("There are no matches to delete id=%d data", id)
return
}
fmt.Printf("Delete database id by%d", id)
}
//CheckErr exception capture function
func CheckErr(err error) {
if err != nil {
fmt.Println(err)
panic(err)
}
}
// Main main function is the entry of all functions
func main() {
err := InitDB()
CheckErr(err)
//InsertQuery()
UpdateQuery("hahaGolang123", "kuQi")
SelectQuery()
DeleteQuery(5)
}
- Preprocessing of MySQL
What is preprocessing?
ordinary SQL Statement execution process:
1.Client pair SQL Statement to replace the placeholder and get the complete SQL sentence
2.Client send complete SQL Statement to MySQL Server
3.MySQL The server performs a complete SQL Statement and returns the result to the terminal
Execution process of preprocessing
1.First SQL The statement is split into two parts, SQL Statement part and parameter part
2.First SQL Statement part sent to MySQL On the server side SQL Pretreatment
3.Then the parameter part is sent to MySQL Server side, MySQL yes SQL Statement splicing
4.MySQL The server performs a complete SQL Statement returns the result
Why pretreatment?
1.In order to optimize MySQL Server repeat SQL Methods. The performance of the server can be executed. The server can compile in advance and execute multiple times at a time, so as to save the cost of subsequent repeated compilation
2.And avoid SQL injection- Go implements MySQL preprocessing
// The prepare method now sends the SQL to the MySQL server and returns a ready state for subsequent queries and commands. The return value can execute multiple queries and commands at the same time; Commands are SQL statements
// PrepareInsert preprocesses the execution of insert statements
func PrepareInsert() {
defer wg.Done()
sqlStr := `insert into test (username, password) values (?, ?)`
// -Preprocessing stmt is to pass parameters directly after the compiled sql statement
stmt, err := db.Prepare(sqlStr)
var u1 = uuid.Must(uuid.NewV4())
CheckErr(err)
defer stmt.Close()
i := rand.Int()
username := fmt.Sprintf("yonghuming%d", i)
result, err := stmt.Exec(username, u1.String()[:10])
CheckErr(err)
rowsAffected, err := result.LastInsertId()
CheckErr(err)
fmt.Printf("Successfully inserted id=%d Data bar\n", rowsAffected)
}
- MySQL transaction operation in Go language
// go language uses the following three methods to realize the transaction operation in MySQL and start the transaction
func (db *DB) Begin()(*Tx, error)
// Commit transaction is equivalent to conn.commit() in Python
func (tx *Tx) Commit() error
// Rollback transaction
func (tx *Tx) Rollback() error
package main
import (
"database/sql"
"fmt"
_ "github.com/go-sql-driver/mysql"
)
var db *sql.DB
type data struct {
Username string `json:"username"`
Password string `json:"password"`
}
// InitDB database connection initialization
func InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = sql.Open("mysql", dsn)
CheckErr(err)
err = db.Ping()
CheckErr(err)
fmt.Println("Database connection succeeded...")
// Set the maximum number of connections to the database connection pool
db.SetMaxOpenConns(100)
//Set the maximum number of idle connections
db.SetMaxIdleConns(5)
return
}
//CheckErr exception capture function
func CheckErr(err error) {
if err != nil {
fmt.Println(err)
panic(err)
}
}
// Transaction operation of MySQL transon
func TranSaCtIon() {
// Open transaction
tx, err := db.Begin()
CheckErr(err)
// Perform multiple SQL operations
sqlStr := `update test set id=id+100000 where password=?`
result, err := tx.Exec(sqlStr, "07f70f7e-4")
CheckErr(err)
id, err := result.LastInsertId()
if err != nil {
// Statement rollback
err := tx.Rollback()
fmt.Println("Transaction rollback")
CheckErr(err)
}
fmt.Printf("Modified id by%d\n", id)
}
func main() {
err := InitDB()
CheckErr(err)
TranSaCtIon()
}
- sqlx usage
The third-party library sqlx can simplify the operation and improve the development efficiency
Install go get GitHub com/jmoiron/sqlx
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
// InitDB database initialization
func InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = sqlx.Connect("mysql", dsn)
CheckErr(err)
db.SetMaxOpenConns(50)
db.SetMaxIdleConns(10)
fmt.Println("goUse Database connection succeeded")
return
}
//CheckErr exception capture function
func CheckErr(err error) {
if err != nil {
fmt.Println(err)
panic(err)
}
}
func main() {
err := InitDB()
CheckErr(err)
}
Compared with the native sql library, sqlx has the advantage that it needs the next scan callback to get the results when querying
sqlx query only needs to define a stored variable, and then automatically put the queried value into the variable
package main
import (
"encoding/json"
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
var db *sqlx.DB
type user struct {
ID int `json:"id"`
Username string `json:"username"`
Password string `json:"password"`
}
// InitDB database initialization
func InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
// Connect is the simultaneous connection of DB Ping
db, err = sqlx.Connect("mysql", dsn)
CheckErr(err)
db.SetMaxOpenConns(50)
db.SetMaxIdleConns(10)
fmt.Println("goUse Database connection succeeded")
return
}
// SelectDB method for querying single data
func SelectDB() {
sqlStr := `select * from test where id=?`
var data user
_ = db.Get(&data, sqlStr, 990)
//CheckErr(err)
fmt.Printf("%#v\n", data)
marshal, err := json.Marshal(data)
CheckErr(err)
fmt.Println(string(marshal))
}
// ManySelect query multiple data methods
func ManySelect() {
sqlStr := `select * from test where id < ?`
var dataList []user
err := db.Select(&dataList, sqlStr, 1000)
CheckErr(err)
//fmt.Println(dataList)
marshal, err := json.Marshal(dataList)
CheckErr(err)
fmt.Println(string(marshal))
}
//CheckErr exception capture function
func CheckErr(err error) {
if err != nil {
fmt.Println(err)
panic(err)
}
}
func main() {
err := InitDB()
CheckErr(err)
SelectDB()
ManySelect()
}
Go operation Redis
Install go get - u GitHub com/go-redis/redis
package main
import (
"fmt"
"github.com/go-redis/redis"
)
var redisDB *redis.Client
// InitRedisDB redis database initialization
func InitRedisDB() (err error) {
redisDB = redis.NewClient(&redis.Options{
Addr: "127.0.0.1:6379",
Password: "",
DB: 0,
})
_, err = redisDB.Ping(redisDB.Context()).Result()
CheckErr(err)
fmt.Println("redis Connection successful")
return
}
//CheckErr exception capture function
func CheckErr(err error) {
if err != nil {
fmt.Println(err)
panic(err)
}
}
func main() {
_ = InitRedisDB()
}
set(key, value): The name in the database is key of string Assign value value get(key): Returns the name in the database key of string of value getset(key, value): Give the name key of string Give the last value mget(key1, key2,..., key N): Return multiple in the library string of value setnx(key, value): add to string，Name is key，Value is value setex(key, time, value): Add to Library string，Set expiration time time mset(key N, value N): Batch setting multiple string Value of msetnx(key N, value N): If all names are key i of string None of them exist incr(key): Name is key of string Add 1 operation incrby(key, integer): Name is key of string increase integer decr(key): Name is key of string Minus 1 operation decrby(key, integer): Name is key of string reduce integer append(key, value): Name is key of string Added value of value substr(key, start, end): Return as name key of string of value Substring of
NSQ distributed message queue
NSQ is a popular distributed message queue. The following is how NSQ and GO language operate NSQ
NSQ is an open source real-time distributed memory message queue written in GO language. Its performance is very excellent. NSQ has the following advantages:
1.NSQ advocates distributed and diffused topology, without single point of failure, supports fault tolerance and high availability, and provides reliable message delivery guarantee
2.NSQ supports scale out without any centralized agents
3.NSQ is easy to configure and deploy and has a built-in management interface
Install go get - u GitHub com/nsqio/go-nsq
Context
stay Go HTTP Wrapped server In, each request corresponds to a response, and the request processing function usually starts additional processing goroutine Used to access back-end services, such as databases and rpc Service, which is used to process a request goroutine It is usually necessary to access some specific data related to the request, such as terminal authentication information and authentication token,Request and deadline. When a request is cancelled or timed out, all the data used to process the request goroutine Should exit quickly before the system can release these goroutine
How to end goroutine gracefully and release resources
// Channel version
package main
import (
"fmt"
"sync"
"time"
)
var wg sync.WaitGroup
func worker(exitChan <-chan struct{}) {
defer wg.Done()
Test:
for {
fmt.Println("worker")
time.Sleep(time.Second)
select {
case <-exitChan:
break Test
default:
}
}
}
func main() {
wg.Add(1)
c := make(chan struct{})
go worker(c)
time.Sleep(10 * time.Second)
c <- struct{}{}
close(c)
wg.Wait()
fmt.Println("Over")
}// Context version
package main
import (
"context"
"fmt"
"sync"
"time"
)
var wg sync.WaitGroup
func worker(ctx context.Context) {
defer wg.Done()
Test:
for {
fmt.Println("worker")
time.Sleep(time.Second)
select {
case <-ctx.Done():
break Test
default:
}
}
}
func main() {
wg.Add(1)
ctx, cancelFunc := context.WithCancel(context.Background())
go worker(ctx)
time.Sleep(10 * time.Second)
cancelFunc()
wg.Wait()
fmt.Println("Over")
}If goroutine opens a new goroutine, you only need to pass ctx into the new goroutine
Background() and TODO()
go has two built-in functions: Background() and TUDO(). These two functions return a background and todo that implement the context interface respectively At the beginning of our code, these two built-in context objects are used as the top-level part context to derive more sub context objects.
Background () is mainly used for main function, initialization and code testing. It is the top-level context of the tree structure of context, that is, context.
He doesn't know what he can do yet?
Precautions for using context
- It is recommended to display and pass context with parameters
- The function method with context as the parameter should take context as the first parameter
- When passing context to a function, don't nil. If you don't know what to pass, use context TODO()
- context is concurrent and safe, and can be freely passed in multiple goroutine s
log standard library
The log package defines the logger type, which provides some methods to format the output. This package also provides a predefined standard logger, which can be used by calling the functions Print series, fatal series and panic series. It is easier to use than the self created logger object.
package main
import "log"
func main() {
log.Println("This is the first work log")
v := "THIS is worker log"
log.Printf("%#v\n", v)
// After Fatal writes the value to the message, execute exit(1)
log.Fatal("After writing 10000 lines of code, I won't execute it")
// You can log Panic throws an exception after writing the log
log.Panic("test panic Log of")
}- flag option (log output content setting)
log The standard library provides the following flag Options, which are a series of defined constants.
const (
Ldate = 1 << iota
Ltime
Lmicroseconds
Llongfile
Lshortfile
LUTC
LstdFlags = Ldate | Ltime
)
package main
import "log"
func main() {
// Set default additional content
log.SetFlags(log.Llongfile | log.Ltime)
// Set log prefix
log.SetPrefix("[go_log] ")
log.Println("Test log")
}
output>>>
[go_log] 19:02:14 /Users/mac/GolandProjects/src/day02/go_log library/main.go:19: Test log
- Configure log output location
The setoutput function is used to set the output destination of the logger. The default is standard error output
package main
import (
"log"
"os"
)
func main() {
file, err := os.OpenFile("test.log", os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0644)
if err != nil {
log.Panic("File open failed")
}
// If the write file log is set, the contents will not be printed to the terminal
log.SetOutput(file)
log.SetFlags(log.Llongfile | log.Ltime)
log.SetPrefix("[go_log] ")
log.Println("Test log")
}
We can define one init The initialization function will log All configured, so it's more standardizedUse of third-party logrus
logrus is a GO structured logger, which is fully compatible with the above logger standard library
Install logrusgo get GitHub com/sirupsen/logrus
package main
import (
log "github.com/sirupsen/logrus"
)
func main() {
log.WithFields(log.Fields{
"animals": "dog",
"time": log.FieldKeyTime,
}).Info("What is this")
}- log level
Trace,debug,info,warning,error,fatal,panic
log.Trace("track?")
log.Debug("Debug?")
log.Info("information")
log.Warn("Warning?")
log.Error("Something failed but I'm not quitting.")
// After recording the log, the system will call OS Exit(1)
log.Fatal("Bye.")
// panic() will be called after logging
log.Panic("I'm bailing.")
- Logging
package main
import (
"os"
"time"
log "github.com/sirupsen/logrus"
)
func main() {
file, err := os.OpenFile("logrustest.log", os.O_APPEND|os.O_WRONLY|os.O_CREATE, 0644)
if err != nil {
log.Panicln(err)
}
log.SetOutput(file)
for i := 0; i < 100; i++ {
log.WithFields(log.Fields{
"animals": "dog",
"Countey": "China",
"City": "BeiJing",
}).Info("What is this")
time.Sleep(time.Second)
}
log.Trace("track?")
log.Info("information")
log.Warn("Warning?")
// Setting the log level will record the level above info (warn error fatal panic)
log.SetLevel(log.InfoLevel)
}
>>>result
time="2021-02-04T12:00:15+08:00" level=info msg="What is this" City=BeiJing Countey=China animals=dog
time="2021-02-04T12:00:17+08:00" level=info msg="What is this" City=BeiJing Countey=China animals=dog
time="2021-02-04T12:00:18+08:00" level=info msg="What is this" City=BeiJing Countey=China animals=dog
time="2021-02-04T12:00:19+08:00" level=info msg="What is this" City=BeiJing Countey=China animals=dog
In addition to the related logs added by withfield and withfields, there are also some log fields added by default
Timestamp of time logging msg logging information level logging level
- Log format
logrus has two kinds of log formatter built in
logrus.TextFormatter logrus.JSONFormatter
log.SetFormatter(&log.JSONFormatter{})- Tracking function
log.SetReportCaller(true)
This will record which file and which line, but it is not a special requirement. You don't need to open this because it will increase the performance overhead