Go to operate MySQL
Install:go get -u /go-sql-driver/mysql
The driver of the GO language operation database natively supports connection pooling, and is concurrently secure. The standard library has no specific implementation, but only lists the specific contents of the required third-party library implementation.
//The first time I connected to MySQL successfullypackage main
import (
"database/sql"
_ "/go-sql-driver/mysql" // _Want to be initialized init() "log"
)
func main() {
// root username 1qa2ws3ed is the password The following book ip:port gouse library name dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err := ("mysql", dsn)
if err != nil {
panic(err)
}
// ping is trying to connect to MySQL database
if err = (); err != nil{
panic(err)
}
("Mysql database connection is successful")
}
Go calls MySQL to encapsulate it into a function
package main
import (
"database/sql"
"encoding/json"
"fmt"
_ "/go-sql-driver/mysql"
)
var db *
func InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = ("mysql", dsn)
CheckErr(err)
err = ()
CheckErr(err)
("The database connection is successful...")
// Set the maximum number of connections in the database connection pool (10)
//Set the maximum number of idle connections (5)
return
}
type data struct {
Username string `json:"username"`
Password string `json:"password"`
}
func main() {
err := InitDB()
CheckErr(err)
query, err := ("select username, password from test")
CheckErr(err)
for (){
line := data{}
// When querying data, you must call the scan method. If the scan connection channel is not used, the connection cannot be released. _ = (&, &)
(line)
dataDic := map[string]string{
"username": ,
"password": ,
}
marshal, _ := (dataDic)
(string(marshal))
}
}
func CheckErr(err error) {
if err != nil {
(err)
panic(err)
}
}
GO—MySQL addition, deletion, modification and search
package main
import (
"database/sql"
"encoding/json"
"fmt"
"time"
_ "/go-sql-driver/mysql"
)
var db *
// InitDB database connection initializationfunc InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = ("mysql", dsn)
CheckErr(err)
err = ()
CheckErr(err)
("The database connection is successful...")
// Set the maximum number of connections in the database connection pool (10)
//Set the maximum number of idle connections (5)
return
}
type data struct {
Username string `json:"username"`
Password string `json:"password"`
}
// SelectQuery query functionfunc SelectQuery() {
sqlStr := "select username, password from test where id > ?"
query, err := (sqlStr, 1)
CheckErr(err)
defer ()
("It's Beijing time %s, have you made progress today?\n", ().Format("2006-01-02 15:04:05"))
for () {
line := data{}
// When querying data, you must call the scan method. If the scan connection channel is not used, the connection cannot be released. _ = (&, &)
//(line)
dataDic := map[string]string{
"username": ,
"password": ,
}
marshal, _ := (dataDic)
("The query data is %s\n", string(marshal))
}
}
// InsertQuery Insert datafunc InsertQuery() {
// sql statement sqlStr := `insert into test (username,password) values ("kuQi", "123qwe")`
result, err := (sqlStr)
CheckErr(err)
id, err := ()
CheckErr(err)
("The id of the data successfully inserted is %v", id)
}
// UpdateQuery update data functionfunc UpdateQuery(dataField string, user string) {
sqlStr := `update test set password=? where username=?`
result, err := (sqlStr, dataField, user)
CheckErr(err)
rowsAffected, err := ()
CheckErr(err)
("The id of the updated field is %d\n", rowsAffected)
}
// DeleteQueryfunc DeleteQuery(id int) {
sqlStr := `delete from test where id=?`
result, err := (sqlStr, id)
CheckErr(err)
rowsAffected, err := ()
CheckErr(err)
if rowsAffected == 0 {
("No match to deleteid=%ddata", id)
return
}
("Delete the id of the database is %d", id)
}
//CheckErr Exception capture functionfunc CheckErr(err error) {
if err != nil {
(err)
panic(err)
}
}
// main main function The entrance to all functionsfunc main() {
err := InitDB()
CheckErr(err)
//InsertQuery()
UpdateQuery("hahaGolang123", "kuQi")
SelectQuery()
DeleteQuery(5)
}
MySQL preprocessing
What is preprocessing?
Normal SQL statement execution process:
1. The client replaces the SQL statement with placeholder and obtains the complete SQL statement
2. The client sends complete SQL statements to the MySQL server
The server executes the complete SQL statement and returns the result to the terminal
Preprocessing execution process
1. First split the SQL statement into two parts, the SQL statement part and the parameter part
2. First send the SQL statement part to the MySQL server for SQL preprocessing
3. Then the parameter part is sent to the MySQL server, and MySQL splices the SQL statements
The server executes a complete SQL statement and returns the result
Why preprocessing?
1. In order to optimize the method of repeating SQL execution by MySQL server. Can execute the performance of the server, make the server compile in advance, compile multiple times at a time, saving the cost of subsequent repeated compilation
2. And avoid SQL injection
Go implements MySQL preprocessing
// prepare method now sends SQL to the MySQL server, returning a prepared state for subsequent queries and commands. The return value can execute multiple queries and commands at the same time; commands are SQL statements// PrepareInsert preprocessing to execute insert statementfunc PrepareInsert() {
defer ()
sqlStr := `insert into test (username, password) values (?, ?)`
// - Preprocessing stmt is to compiled SQL statements and then pass the parameters directly stmt, err := (sqlStr)
var u1 = (uuid.NewV4())
CheckErr(err)
defer ()
i := ()
username := ("yonghuming%d", i)
result, err := (username, ()[:10])
CheckErr(err)
rowsAffected, err := ()
CheckErr(err)
("Successfully insertedid=%dData\n", rowsAffected)
}
Go language implements MySQL to implement transaction operations
// Use the following three methods in the go language to implement transaction operations in MySQL and start transactionsfunc (db *DB) Begin()(*Tx, error)
// Submit transactions is equivalent to () in Pythonfunc (tx *Tx) Commit() error
// Roll back the transactionfunc (tx *Tx) Rollback() error
package main
import (
"database/sql"
"fmt"
_ "/go-sql-driver/mysql"
)
var db *
type data struct {
Username string `json:"username"`
Password string `json:"password"`
}
// InitDB database connection initializationfunc InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = ("mysql", dsn)
CheckErr(err)
err = ()
CheckErr(err)
("The database connection is successful...")
// Set the maximum number of connections in the database connection pool (100)
//Set the maximum number of idle connections (5)
return
}
//CheckErr Exception capture functionfunc CheckErr(err error) {
if err != nil {
(err)
panic(err)
}
}
//TranSaCtIon MySQL transaction operationfunc TranSaCtIon() {
// Start transactions tx, err := ()
CheckErr(err)
// Perform multiple SQL operations sqlStr := `update test set id=id+100000 where password=?`
result, err := (sqlStr, "07f70f7e-4")
CheckErr(err)
id, err := ()
if err != nil {
// Statement rollback err := ()
("Transaction Rollback")
CheckErr(err)
}
("The modified id is %d\n", id)
}
func main() {
err := InitDB()
CheckErr(err)
TranSaCtIon()
}
Use sqlx
The third-party library SQLX can simplify operations and improve development efficiency
Installgo get /jmoiron/sqlx
package main
import (
"fmt"
_ "/go-sql-driver/mysql"
"/jmoiron/sqlx"
)
var db *
// InitDB database initializationfunc InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
db, err = ("mysql", dsn)
CheckErr(err)
(50)
(10)
("goUse database connection is successful")
return
}
//CheckErr Exception capture functionfunc CheckErr(err error) {
if err != nil {
(err)
panic(err)
}
}
func main() {
err := InitDB()
CheckErr(err)
}
The advantage of sqlx compared to native sql libraries is that sql native needs to obtain the result when querying next scan callback
SQLX query only needs to define a stored variable and then automatically put the query's value into the variable
package main
import (
"encoding/json"
"fmt"
_ "/go-sql-driver/mysql"
"/jmoiron/sqlx"
)
var db *
type user struct {
ID int `json:"id"`
Username string `json:"username"`
Password string `json:"password"`
}
// InitDB database initializationfunc InitDB() (err error) {
dsn := "root:1qa2ws3ed@tcp(127.0.0.1:3306)/gouse"
// Connect is to connect () db, err = ("mysql", dsn)
CheckErr(err)
(50)
(10)
("goUse database connection is successful")
return
}
// SelectDB method to query a single piece of datafunc SelectDB() {
sqlStr := `select * from test where id=?`
var data user
_ = (&data, sqlStr, 990)
//CheckErr(err)
("%#v\n", data)
marshal, err := (data)
CheckErr(err)
(string(marshal))
}
// ManySelect query multiple data methodsfunc ManySelect() {
sqlStr := `select * from test where id < ?`
var dataList []user
err := (&dataList, sqlStr, 1000)
CheckErr(err)
//(dataList)
marshal, err := (dataList)
CheckErr(err)
(string(marshal))
}
//CheckErr Exception capture functionfunc CheckErr(err error) {
if err != nil {
(err)
panic(err)
}
}
func main() {
err := InitDB()
CheckErr(err)
SelectDB()
ManySelect()
}
Go operation Redis
Installgo get -u /go-redis/redis
package main
import (
"fmt"
"/go-redis/redis"
)
var redisDB *
// InitRedisDB redis database initializationfunc InitRedisDB() (err error) {
redisDB = (&{
Addr: "127.0.0.1:6379",
Password: "",
DB: 0,
})
_, err = (()).Result()
CheckErr(err)
("Redis connection succeeded")
return
}
//CheckErr Exception capture functionfunc CheckErr(err error) {
if err != nil {
(err)
panic(err)
}
}
func main() {
_ = InitRedisDB()
}
- set(key, value): Assign a value to a string with the name key in the database to a value
- get(key): Returns the value of the string named key in the database
- getset(key, value): Assign the last value to the string named key
- mget(key1, key2,…, key N): Returns the value of multiple strings in the library
- setnx(key, value): add string, name is key, value is value
- setex(key, time, value): add string to the library and set the expiration time
- mset(key N, value N): set the values of multiple strings in batches
- msetnx(key N, value N): If all strings with the name key i do not exist
- incr(key): string with name key increment operation 1
- incrby(key, integer): add integer with string name as key
- decr(key): string minus 1 operation with name key
- decrby(key, integer): string with name key reduces integer
- append(key, value): append value of string with name key
- substr(key, start, end): Returns the substring of the value of the string named key
NSQ distributed message queue
NSQ is a popular distributed message queue. The following are mainly how to operate NSQ and GO languages.
NSQ is an open source real-time distributed memory message queue written in GO language. Its performance is very excellent. The advantages of NSQ are:
Advocate distributed and diffusion topology, no single point of failure, supports fault tolerance and high availability, and provides reliable message delivery guarantees
Supports scale-out without any centralized proxy
Easy to configure and deploy, and has a built-in management interface
Install go get -u /nsqio/go-nsq
Context
In the server of the Go HTTP package, each request corresponds to a response. The request handling function usually starts an additional goroutine to access the backend services, such as database and rpc services. The goroutine used to handle a request usually needs to access some request-specific data, such as the terminal's identity authentication information, verification-related tokens, requests and deadlines. When a request is cancelled or timed out, all goroutines used to process the request should be exited quickly before the system can release these goroutines
How to end goroutine elegantly to release resources
// Channel versionpackage main
import (
"fmt"
"sync"
"time"
)
var wg
func worker(exitChan <-chan struct{}) {
defer ()
Test:
for {
("worker")
()
select {
case <-exitChan:
break Test
default:
}
}
}
func main() {
(1)
c := make(chan struct{})
go worker(c)
(10 * )
c <- struct{}{}
close(c)
()
("Over")
}
// Context versionpackage main
import (
"context"
"fmt"
"sync"
"time"
)
var wg
func worker(ctx ) {
defer ()
Test:
for {
("worker")
()
select {
case <-():
break Test
default:
}
}
}
func main() {
(1)
ctx, cancelFunc := (())
go worker(ctx)
(10 * )
cancelFunc()
()
("Over")
}
If the goroutine is enabled, you only need to pass ctx into the new goroutine
Background() and TODO()
There are two built-in functions: Background() and TUDO(), which return a background and todo that implement the context interface respectively. At the beginning of our code, we use these two built-in context objects as the top-level parent context to derive more subcontext objects.
backgroud() is mainly used for main functions, initialization and code testing. It is the top-level context of the context tree structure, that is, context.
todo(), he doesn't know what he can do yet?
Things to note when using context
- Recommended to pass context in parameter display
- Function method with context as parameter should use context as first parameter
- When passing context to a function, don't nil. If you don't know what to pass, use()
- context is concurrently safe and can be passed in multiple goroutines at will.
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 that can be used by calling the functions Print series, fatal series and panic series, which is easier to use than self-created logger objects.
package main
import "log"
func main() {
("This is the first work log")
v := "THIS is worker log"
("%#v\n", v)
// After Fatal writes the value to the information, execute exit(1) ("I will not execute it after that.")
// Can raise an exception by throwing a log to throw an exception after writing it ("Test the panic log")
}
flag option (log output content settings)
The log 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 attached content ( | )
// Set log prefix ("[go_log] ")
("Test Log")
}
output>>>
[go_log] 19:02:14 /Users/mac/GolandProjects/src/day02/go_logLibrary/:19: Test log
Configure log output location
The setoutput function is used to set the output destination of the logger, and the default is the standard error output.
package main
import (
"log"
"os"
)
func main() {
file, err := ("", os.O_CREATE|os.O_WRONLY|os.O_APPEND, 0644)
if err != nil {
("File opening failed")
}
// Set the write file and the log content will not be printed to the terminal (file)
( | )
("[go_log] ")
("Test Log")
}
We can define an init initialization function and configure all the logs so that it is more standardized
Use of third-party log library logrus
logrus is a GO structured logger that is fully compatible with the logger standard library above.
Install logrusgo get /sirupsen/logrus
package main
import (
log "/sirupsen/logrus"
)
func main() {
({
"animals": "dog",
"time": ,
}).Info("What is this")
}
Log Level
Trace、debug、info、warning、error、fatal、panic
("track?")
("Debug?")
("information")
("warn?")
("Something failed but I'm not quitting.")
// After recording the log, it will be called (1) ("Bye.")
// Panic() will be called after logging ("I'm bailing.")
Logging
package main
import (
"os"
"time"
log "/sirupsen/logrus"
)
func main() {
file, err := ("", os.O_APPEND|os.O_WRONLY|os.O_CREATE, 0644)
if err != nil {
(err)
}
(file)
for i := 0; i < 100; i++ {
({
"animals": "dog",
"Countey": "China",
"City": "BeiJing",
}).Info("What is this")
()
}
("track?")
("information")
("warn?")
// Set the log level and record the info-above level (warn error fatal panic) ()
}
>>>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 using withfield and withfields, log fields are also added by default.
time log timestamp msg log information level log level
Log formatting
Logrus has two log formatters built in
(&{})
Tracking functions
(true)
In this way, all the file and line will be recorded. However, it is not a special requirement and there is no need to open this because it will increase the performance
This is the article about the example code of Go language operation database and its regular operations. For more relevant Go language operation database content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!