How to create high-performance MySQL statistical operations using Go language

With the rapid development of the Internet, data statistics and analysis have become more and more important. As one of the most commonly used databases on the Internet, MySQL also plays an important role in data statistics and analysis. The Go language has become the language chosen by more and more developers because of its high concurrency and excellent performance. This article will introduce how to use Go language to create high-performance MySQL statistical operations.

Preparation work

Before starting to use Go language to operate MySQL, we need to install the go-sql-driver/mysql library first. It can be installed using the following command:

go get -u github.com/go-sql-driver/mysql

Next, we need to connect to the MySQL database. The following code can be used:

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "<dbuser>:<dbpassword>@tcp(<dbhost>:<dbport>)/<dbname>")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    err = db.Ping()
    if err != nil {
        panic(err.Error())
    }

    // 连接成功
}

In the code, we use the sql.Open() method to connect to the MySQL database, where , , , and are the user name, password, host name, port and database name of the database respectively. Next, we use the db.Ping() method to test whether the connection is successful.

Create statistical operation

Next, we will implement the following statistical operation:

Query the number of all records in the table

Query the 10th in the table Records from row to row 20

Query the average value of the salary field in the records from row 10 to row 20 in the table

Query the minimum and maximum values ​​of the salary field in the table

First, we need to define a structure to store the query results. You can use the following code:

type User struct {
    Id     int    `json:"id"`
    Name   string `json:"name"`
    Age    int    `json:"age"`
    Gender string `json:"gender"`
    Salary int    `json:"salary"`
}

Next, we implement the above four operations respectively.

Query the number of all records in the table

func countUsers(db *sql.DB) int {
    var count int

    err := db.QueryRow("SELECT COUNT(*) FROM users").Scan(&count)
    if err != nil {
        panic(err.Error())
    }

    return count
}

In the code, we use the SQL statement SELECT COUNT(*) FROM users to query the number of all records in the table. Use the db.QueryRow() method to query and store the results into the count variable, and finally return it.

Query the records from row 10 to row 20 in the table

func getUsers(db *sql.DB, offset, limit int) []User {
    rows, err := db.Query(fmt.Sprintf("SELECT * FROM users LIMIT %d,%d", offset, limit))
    if err != nil {
        panic(err.Error())
    }
    defer rows.Close()

    var users []User
    for rows.Next() {
        var user User
        err := rows.Scan(&user.Id, &user.Name, &user.Age, &user.Gender, &user.Salary)
        if err != nil {
            panic(err.Error())
        }
        users = append(users, user)
    }

    return users
}

In the code, we use the SQL statementSELECT * FROM users LIMIT <offset>,<limit> </limit></offset>Query the records from the offset 1 row to the offset limit row in the table. Use the db.Query() method to query and loop through the query results, store each record into the users array, and finally return it.

Query the average value of the salary field in the records from row 10 to row 20 in the table

func averageSalary(db *sql.DB, offset, limit int) int {
    var avgSalary int

    err := db.QueryRow(fmt.Sprintf("SELECT AVG(salary) FROM users LIMIT %d,%d", offset, limit)).Scan(&avgSalary)
    if err != nil {
        panic(err.Error())
    }

    return avgSalary
}

In the code, we use the SQL statementSELECT AVG(salary) FROM users LIMIT &lt ;offset>,<limit></limit>Query the average value of the salary field in the records from offset 1 to offset limit in the table. Use the db.QueryRow() method to query and store the results into the avgSalary variable, and finally return it.

Query the minimum and maximum values ​​of the salary field in the table

func minMaxSalary(db *sql.DB) (int, int) {
    var minSalary, maxSalary int

    err := db.QueryRow("SELECT MIN(salary),MAX(salary) FROM users").Scan(&minSalary, &maxSalary)
    if err != nil {
        panic(err.Error())
    }

    return minSalary, maxSalary
}

In the code, we use the SQL statementSELECT MIN(salary),MAX(salary) FROM users Query the minimum and maximum values ​​of the salary field in the table. Use the db.QueryRow() method to query and store the results into the minSalary and maxSalary variables, and finally return them.

Summary

This article introduces how to use Go language to create high-performance MySQL statistical operations. We first connected to the MySQL database, and then implemented the number of all records in the query table, the records from rows 10 to 20 in the query table, the average value of the salary field in the records from rows 10 to 20 in the query table, and the query Four operations on the minimum and maximum values ​​of the salary field in the table. These operations are not only simple and easy to understand, but also have excellent performance, which can help developers better complete data statistics and analysis tasks.

The above is the detailed content of How to create high-performance MySQL statistical operations using Go language. For more information, please follow other related articles on the PHP Chinese website!