(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)