Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”

This article has participated in the “Digitalstar Project” and won a creative gift package to challenge the creative incentive money

In the last article we did data manipulation, all in the form of placeholders

We can actually use mysql preprocessing to do this

So let’s see what is processed?

What is pre-processing?

To understand what preprocessing is, we can compare the normal SQL statement execution process with the preprocessing execution process

Common SQL statement execution process:

  • The client performs placeholder substitution on the SQL statement to obtain the complete SQL statement
  • The client sends a complete SQL statement to the mysql server
  • The mysql server executes the complete SQL statement and returns the results to the client

Pre-processing execution process:

  • Split the SQL statement into two parts
    • The command part
    • Data section
  • The command part is first sent to the mysql server, which preprocesses the SQL
  • The data portion is then sent to the mysql server, which performs placeholder replacements for SQL statements
  • The mysql server executes the complete SQL statement and returns the results to the client

With the steps and flow, we know that preprocessing is definitely faster than normal SQL

So what are the benefits of preprocessing?

  • By optimizing the mysql server to execute SQL repeatedly, the server performance can be improved and the server can be compiled in advance. In this way, the cost of subsequent compilation can be saved
  • Avoid SQL injection problems
// Preprocess the insert data operation
func prepareInfo(db *sql.DB) {
	sqlInfo := "insert into user (name,age)values(? ,?) "

	stmt, err := db.Prepare(sqlInfo)
	iferr ! =nil {
		fmt.Println("Exec err : ", err)
		return
	}

	ret, err := stmt.Exec("Flower pig 2".28)
	iferr ! =nil {
		fmt.Println("stmt Exec err : ", err)
		return
	}
	ret, err = stmt.Exec("Flower pig 3".28)
	iferr ! =nil {
		fmt.Println("stmt Exec err : ", err)
		return
	}

	rows, err := ret.RowsAffected()
	iferr ! =nil {
		fmt.Println("stmt Exec err : ", err)
		return
	}
	fmt.Println("rows = ", rows)

}
Copy the code

Go implements MySQL transactions

  • If transaction processing is enabled, there will be a rollback mechanism. If all the transactions are successfully committed, the transaction is successfully processed

  • Func (db * db) Begin() (*Tx, error) The transaction starts

  • Func (tx * tx) Commit() Error transaction Commit

  • Rollback() Error Transaction Rollback

    func trasaction(db *sql.DB) {

	// Start a transaction

	tx, err := db.Begin()
	iferr ! =nil {
		iftx ! =nil {
			tx.Rollback()
		}
		fmt.Printf("Begin err :%v", err)
		return
	}

	sqlStr := "update user set name='xxx' where id=?"
	_, err = tx.Exec(sqlStr, 9)
	iferr ! =nil {
		iftx ! =nil {
			tx.Rollback()
		}
		fmt.Printf("Exec err :%v", err)
		return
	}

	sqlStr = "update user set name='xxx' where id=?"
	_, err = tx.Exec(sqlStr, 6)
	iferr ! =nil {
		iftx ! =nil {
			tx.Rollback()
		}
		fmt.Printf("Exec err :%v", err)
		return
	}

    // Commit the transaction
	err = tx.Commit()
	iferr ! =nil {
		iftx ! =nil {
			tx.Rollback()
		}
		fmt.Printf("Commit err :%v", err)
		return
	}

	fmt.Println("commit success ")}Copy the code

SQLX use

We can also use the third-party library SQLX

  • To install the third-party library SQLX, go get github.com/jmoiron/sqlx

  • Third-party libraries can deliver high development efficiency and simplify development operations

    package main

import (
	"fmt"
	"github.com/jmoiron/sqlx"
	_ "github.com/go-sql-driver/mysql" // Call the initialization function after the exception is commented out
)

var db *sqlx.DB

func insertInfo(a) {

	sqlStr := "insert into user(name,age)values(? ,?) "
	res, err := db.Exec(sqlStr, "xxx".2)
	iferr ! =nil {
		fmt.Printf("Exec err : %v", err)
		return
	}
	id, err := res.LastInsertId()
	iferr ! =nil {
		fmt.Printf("LastInsertId err : %v", err)
		return
	}
	fmt.Printf("id == %d", id)

	rows, err := res.RowsAffected()
	iferr ! =nil {
		fmt.Printf("RowsAffected err : %v", rows)
		return
	}
	fmt.Printf("rows == %d", rows)
	return

}

func main(a) {

	var err error
	dsn := "Root: 123456 @ TCP/go_test (127.0.0.1:3306)? charset=utf8mb4"
	db, err = sqlx.Connect("mysql", dsn)
	iferr ! =nil {
		fmt.Printf("Connect err : %v\n", err)
		return
	}
	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)

	// Insert data
	insertInfo()
}

Copy the code

gin + mysql + rest full api

Of course, we do not need to use the HTTP package before the method implementation, we can also give the framework, really can achieve efficient implementation of business, but at the same time, I still want to go to the specific implementation principle research

Practice the following steps:

  • Import github.com/gin-gonic/gin library

  • Create table USERS, id, name, telephone

    CREATE TABLE `users` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT '',
`telephone` VARCHAR(20) DEFAULT  '',
PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT 
CHARSET=utf8mb4;
Copy the code

    The figure above shows the data generated in practice

  • Package to add, delete, change and check the database

  • Write route operations

Need source code for reference comparison of XDM, can comment or private letter, thank you can have feedback

Welcome to like, follow and favorites

Friends, your support and encouragement, I insist on sharing, improve the quality of the power

All right, that’s it for this time

Technology is open, our mentality, should be more open. Embrace change, live in the sun, and strive to move forward.

I am Nezha, welcome to like, see you next time ~