(equivalent to MyBatis in Java)
Go MySQL operation
Go language has built-in database/sql package, which ensures the universal interface of SQL or SQL like database, and does not provide specific database driver. At least one database driver must be injected to use the database/sql package.
download
Download dependency: go get -u github.com/go-sql-driver/mysql
Will be saved in the $GOPATH/src / directory
Using MySQL driver
Function signature
func Open(drivername,dataSourceName string)(*DB ,error)
A DB is a database (operation) handle that represents a connection pool with zero to more underlying connections. It can be safely used by multiple go processes at the same time.
sql package will automatically create and release connections; It also maintains a connection pool of idle connections. If the database has the concept of single link state, the state can be trusted only when the transaction is observed. Once DB.Begin is called, the returned Tx will be bound to a single connection. After the transaction Tx to Commit or RollBack, the connection used by the transaction will be returned to the idle connection pool in the DB.
Enable
package main
import{
"database/sql"
_ "github.com/go-sql-driver/mysql"//Importing but not using it directly is equivalent to using the init() method
}
func main(){
//database information
dataSourceName:="root:123456@tcp(127.0.0.1:3306)/database"//User name: password
//Connect to database
db,err:=sql.Open("mysql",dataSourceName)//The user name and password will not be verified, only the format will be verified.
if err!=nil{//Incorrect format and error report
fmt.Println("open failed,error:%v",err)
}
err=db.Ping()
if err!=nil{//Incorrect password error
fmt.Println("open failed,error:%v",err)
}
}
If it is written in the main function, the variables in main cannot be used when writing query or insert in other functions.
Therefore, variables should be defined outside main
Add, delete, modify and query
package main
import{
"database/sql"
_ "github.com/go-sql-driver/mysql"//Importing but not using it directly is equivalent to using the init() method
}
var db *sql.DB
func initDB(err error){
//database information
dataSourceName:="root:123456@tcp(127.0.0.1:3306)/database"//User name: password @ port
//Connect to database
db,err=sql.Open("mysql",dataSourceName)//Note: no colon
if err!=nil{//Incorrect format and error report
return
}
err=db.Ping()
if err!=nil{//Incorrect password error
return
}
//Set the maximum number of connections in the database connection pool. After running out, new connections will wait
db.SetMaxOpenConns(10)//It can be omitted
return
}
//structural morphology
type user struct{
ig int
name string
age int
}
func main(){
err:=initDB()
if err!=nil{
fmt.Println("Db init failed,err",err)
}
fmt.Println("Linked database successfully")
//Query single record db.QueryRow method
sqlStr:="select * from user where id=1;"
rowObj:=db.QueryRow(sqlStr)//Take a connection from the connection pool and query the singleton record in the database. The Row object is returned
//Dynamic query
//sqlStr:="select * from user where id=?;"
//rowObj:=db.QueryRow(sqlStr,1)
var u1 user
rowObj.Scan(&u1.id,&u1.name,&u1.age)//The connection will be closed automatically after use
fmt.Println("u1:%v",u1)
//Query multiple records db.Query method
sqlStr2:="select * from user where id>1;"
rows,err:=db.Query(sqlStr2)
if err!=nil{
fmt.Println("error")
}
defer rows.Close()//Important, manual shutdown required
//Read results
for rows.Next(){
var u user
err:=rows.Scan(&u1.id,&u1.name,&u1.age)
}
//Insert, update and delete data db.Exec method
sqlStr3:="insert into user(name,age) values (?,?)"
ret,err:=db.Exec(sqlStr3,"Kramer",24)
if err != nill{
fmt.Println("error")
}
theID,err:=ret.LastInsertId()//id of the newly inserted data
//n. Err: = ret.rowsaffected() / / number of rows affected
if err != nill{
fmt.Println("error")
}
fmt.Println("insert success,ID:",theID)
}
MySQL preprocessing
Normal SQL statement execution process:
1. Replace the placeholder of the SQL statement to get the complete SQL statement
2. The client sends a complete SQL statement to the MySQL server
3. The MySQL server executes the complete SQL statement and returns the result to the client
Preprocessing execution process:
1. Split the SQL statement into two parts, command part and data part
2. First, send the command part to the MySQL server for SQL preprocessing
3. Send the data part to the MySQL server, which replaces the placeholder of the SQL statement
4. The MySQL server executes the complete SQL statement and returns the result to the client
Pretreatment benefits:
Optimize the repeated SQL pairing method of MySQL server to improve server performance,
Avoid SQL injection problems
Scope of application: frequently used SQL statements
Go implements MySQL preprocessing
Syntax: func (db *DB)Prepare(query string)(*Stmt,error)
The Prepare method first sends the sql statement to the MySQL server and returns a prepared state for the query and command to stop nausea. The return value can execute multiple queries and commands at the same time.
func prepareQueryDemo(){
sqlStr;="select * from user where id>?"
stmt,err:=db.Prepare(sqlStr)
if err != nill{
fmt.Println("error")
return
}
defer stmt.Close()//important
rows,err:=stmt.Query(0)
//Add, delete and use stmt.Exec()
//_, err:=stmt.Exec(xxx,xx,xx...) execute this line several more times to complete the batch operation
if err != nill{
fmt.Println("error")
return
}
defer rows.Close()//Important, manual shutdown required
//Cycle through the data in the result set
for rows.Next(){
var u user
err:=rows.Scan(&u1.id,&u1.name,&u1.age)
if err != nill{
fmt.Println("error")
return
}
fmt.Println(u)
}
}
MySQL transaction
GO implements MySQL transactions
It depends on the following methods
//Open transaction func (db *DB)Begin()(*Tx,err) //Commit transaction func (tx *Tx)Commit()error //Rollback transaction func (tx *Tx)Rollback()error
Example:
func transactionDemo() {
tx, err := db.Begin() // Open transaction
if err != nil {
if tx != nil {
tx.Rollback() // RollBACK
}
fmt.Printf("begin trans failed, err:%v\n", err)
return
}
sqlStr1 := "Update user set age=30 where id=?"
ret1, err := tx.Exec(sqlStr1, 2)
if err != nil {
tx.Rollback() // RollBACK
fmt.Printf("exec sql1 failed, err:%v\n", err)
return
}
affRow1, err := ret1.RowsAffected()
if err != nil {
tx.Rollback() // RollBACK
fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
return
}
sqlStr2 := "Update user set age=40 where id=?"
ret2, err := tx.Exec(sqlStr2, 3)
if err != nil {
tx.Rollback() // RollBACK
fmt.Printf("exec sql2 failed, err:%v\n", err)
return
}
affRow2, err := ret2.RowsAffected()
if err != nil {
tx.Rollback() // RollBACK
fmt.Printf("exec ret1.RowsAffected() failed, err:%v\n", err)
return
}
fmt.Println(affRow1, affRow2)
if affRow1 == 1 && affRow2 == 1 {
fmt.Println("The transaction is committed...")
tx.Commit() // Commit transaction
} else {
tx.Rollback()
fmt.Println("Transaction rollback...")
}
fmt.Println("exec trans success!")
}
sqlx
Third party library to simplify operation and improve efficiency
You need to import "github.com/jmoiron/sqlx"
Note: the structure field name should correspond to the database field name
//Query single line record
func queryRowDemo(){
sqlStr:="select id,name,age from user where id =?"
var u user
err:=db.Get(&u,sqlStr,1)//The difference is that the variable u is passed in directly
if err!=nil{
fmt.Println("error:"err)
return
}
fmt.Printf("n=%v",u)
}
//Query multiline records
func queryMultiRowDemo(){
sqlStr:="select id,name,age from user where id >?"
var users []user
err:=db.Select(&users,sqlStr,0)
if err!=nil{
fmt.Println("error:"err)
return
}
fmt.Println("users:%v",users)
}
Redis
Connect to Redis
Using third-party libraries https://github.com/go-redis/resdis Connect and operate Redis database.
Installation command: go get -u github.com/go-redis/redis
//Declare a global redisdb variable
var redisdb *redis.Client
//Initialize connection
func initRedis()(err error){
redisdb=redis.NewClient(&redis.Options{
Addr:"localhost:6379",
Password:"",
DB:0,
})
_,err:=redisdb.Ping().Result()
if err!=nil{
return err
}
return nil
}
func main(){
err:=initinitRedis()
if err!=nil{
fmt.Println("connect err:",err)
}
fmt.Println("connect success ")
}
Basic use
NSQ
Distributed message queuing, advantages:
1. Distributed and decentralized topology, no single point of failure, support fault tolerance and high availability, and provide reliable information delivery guarantee
2. Supports scale out without any centralized agents
3. Easy to configure and deploy, built-in management interface
Applicable scenarios:
Asynchronous operation, application decoupling, flow peak shaving
install
use
(skip temporarily)