Learn Go language to operate MySQL preprocessing

                                                                                               

Friendly reminder: This article takes about 6 minutes and 41 seconds to read. Please give me any advice on any shortcomings. Thank you for reading. Subscribe to this site

Preprocessing

Preprocessing is a technology used by MySQL to prevent frequent client requests. It performs the same processing on the same statement. Pre-loaded in MySQL, the operating variable data is replaced with placeholders, reducing frequent requests to MySQL and making the server run efficiently.

Here the client is not the C/S architecture between the front and backend, but the C/S architecture in which the background program operates the database server. In this way, you can briefly understand the background program as the Client to MySQL. Server requests and processes the results.

Related learning recommendations: GO language tutorial

Ordinary SQL execution processing process:

1. Prepare the SQL statement on the client;
2. Send the SQL statement to the MySQL server;
3. Execute the SQL statement on the MySQL server;
4. The server returns the execution result to the client .

Preprocessing execution process:

1. Split SQL into structure part and data part;
2. When executing the SQL statement, first send the same command and structure part to the MySQL server, and let the MySQL server perform preprocessing in advance (the SQL statement is not actually executed at this time);
3. In order to ensure the structural integrity of the SQL statement, when the SQL statement is sent for the first time, the variable data parts are represented by a data placeholder;
4. Then the data part is sent to the MySQL server , the MySQL server replaces the placeholders of the SQL statement;
5. The MySQL server executes the complete SQL statement and returns the result to the client.

Advantages of preprocessing

• Preprocessing statements greatly reduce analysis time, and only made one query (although the statement Multiple executions);
• Bind parametersReduces server bandwidth, only the parameters of the query are sent instead of the entire statement;
• Preprocessed statements are targeted at **SQL Injection** is very useful because different protocols are used after the parameter value is sent, ensuring the legality of the data.

Go language implementation

In the Go language, use the db.Prepare() method to implement preprocessing:

func (db *DB) Prepare(query string) (*Stmt, error)

Prepare executes the preprocessed SQL statement and returns the Stmt structure pointer for data binding operations.

Query operations use the db.Prepare() method to declare preprocessing SQL, and use stmt.Query() to replace the data with placeholders for query, update, and insertion. , use stmt.Exec() to perform deletion operations.

Preprocessing query example

// 预处理查询数据func prepareQuery() {
    sqlStr := "SELECT id,name,age FROM user WHERE id > ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare sql failed, err:%v\n", err)
        return
    }
    rows, err := stmt.Query(1)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    defer rows.Close()

    for rows.Next() {
        var u user
        err := rows.Scan(&u.id, &u.name, &u.age)
        if err != nil {
            fmt.Printf("scan data failed, err:%v\n", err)
            return
        }
        fmt.Printf("id:%d, name:%s, age:%d\n", u.id, u.name, u.age)
    }}

Preprocessing update example

// 预处理更新数据func prepareUpdate() {
    sqlStr := "UPDATE user SET age = ? WHERE id = ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare sql failed, err:%v\n", err)
        return
    }
    _, err = stmt.Exec(18, 2)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    fmt.Printf("prepare update data success")}

Preprocessing insert example

// 预处理更新数据func prepareUpdate() {
    sqlStr := "UPDATE user SET age = ? WHERE id = ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare sql failed, err:%v\n", err)
        return
    }
    _, err = stmt.Exec(18, 2)
    if err != nil {
        fmt.Printf("exec failed, err:%v\n", err)
        return
    }
    fmt.Printf("prepare update data success")}

Preprocessing deletion example

// 预处理删除数据func prepareDelete() {
    sqlStr := "DELETE FROM user WHERE id = ?"
    stmt, err := db.Prepare(sqlStr)
    if err != nil {
        fmt.Printf("prepare sql failed, err:%v\n", err)
        return
    }
    result, err := stmt.Exec(3)
    n, err := result.RowsAffected()
    if err != nil {
        fmt.Printf("delete rows failed, err:%v\n", err)
        return
    }
    if n > 0 {
        fmt.Printf("delete data success")
    } else {
        fmt.Printf("delete data error")
    }

The above is the detailed content of Learn Go language to operate MySQL preprocessing. For more information, please follow other related articles on the PHP Chinese website!