How to make reliable MySQL database connection using Go language?

With the large amount of data that needs to be stored and processed, MySQL has become one of the most commonly used relational databases in application development. The Go language is becoming more and more popular among developers due to its efficient concurrency processing and concise syntax. This article will lead readers to implement reliable MySQL database connection through Go language, allowing developers to query and store data more efficiently.

1. Several ways for Go language to connect to MySQL database

There are usually three ways to connect to MySQL database in Go language, which are:

1. Third-party library

There are many third-party libraries in the Go language that can connect to the MySQL database, such as go-sql-driver/mysql, mysql-go and golang-mysql-driver. Among them, the go-sql-driver/mysql library is the officially recommended MySQL driver library, which can easily connect and operate the MySQL database.

2. Use the database/sql standard library

database/sql is an interface provided by the standard library in the Go language to interact with relational databases. Through this interface, you can easily connect and operate multiple databases, including MySQL database. To use the database/sql standard library to connect to a MySQL database, you need to import the MySQL driver first, and then create a *sql.DB instance to connect and operate.

3. Use the ORM library

The ORM (Object Relational Mapping) library can map objects in the Go language to tables in the MySQL database, and can automatically complete the mapping between objects and tables in the database. Convert. Currently, the more popular ORM libraries include gorm, xorm, etc.

2. Use the go-sql-driver/mysql library to connect to the MySQL database

Here we take the go-sql-driver/mysql library as an example to introduce how to use this library to connect to the MySQL database.

1. Install the go-sql-driver/mysql library

Use the go command line to install:

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

2. Connect to the MySQL database

In Go In the language, connecting to the MySQL database requires specifying the driver name of the database and its corresponding data source format. The driver defined by the go-sql-driver/mysql library is named mysql, and the corresponding data source format is:

mysql://用户名:密码@协议(默认tcp)://主机名:端口号/默认使用的数据库?参数名1=参数值1&参数名2=参数值2...

Therefore, the core code for using the go-sql-driver/mysql library to connect to the MySQL database is as follows:

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

func main() {
    // 准备连接MySQL数据库的信息
    db, err := sql.Open("mysql", "<username>:<password>@tcp(<host>:<port>)/<database>?charset=utf8mb4")
    if err != nil {
        fmt.Println("连接数据库失败：", err)
        return
    }
    // 测试连接
    err = db.Ping()
    if err != nil {
        fmt.Println("连接数据库失败：", err)
        return
    }
    fmt.Println("连接MySQL数据库成功！")
}

Among them, d6025a37ea8687b5422f951f7288bdc5, cb1ebc435675187bdcfb539b370c2e37, f7e6dec31ab1a0471d06c55afaca8d77, 298c9bd6ad6e8c821dc63aa0473d6209, 8bc586dd4295b97d3f41371ae861e7c1 need to be replaced according to specific circumstances.

3. Query data

After connecting to the MySQL database, you can use the sql.DB instance to perform a series of SQL operations. The following takes querying data as an example to introduce how to use the sql.DB instance to query data in the MySQL database.

// 定义一个结构体用于存储查询结果
type User struct {
    ID    int
    Name  string
    Email string
}

// 查询所有用户
func getAllUsers(db *sql.DB) ([]User, error) {
    // 准备查询语句
    query := "SELECT * FROM users"
    // 执行查询
    rows, err := db.Query(query)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    // 解析查询结果
    var users []User
    for rows.Next() {
        var user User
        err := rows.Scan(&user.ID, &user.Name, &user.Email)
        if err != nil {
            return nil, err
        }
        users = append(users, user)
    }
    return users, nil
}

func main() {
    // 连接数据库
    db, err := sql.Open("mysql", "<username>:<password>@tcp(<host>:<port>)/<database>?charset=utf8mb4")
    if err != nil {
        fmt.Println("连接数据库失败：", err)
        return
    }
    defer db.Close()
    // 查询所有用户的数据
    users, err := getAllUsers(db)
    if err != nil {
        fmt.Println("查询用户数据失败：", err)
        return
    }
    fmt.Println(users)
}

In the above code, we define a User structure to store query results. In the function of querying data, the query statement is first prepared, and then the db.Query() method is called to execute the query, parse the query results and return them.

Through the above code examples, we learned how to use the go-sql-driver/mysql library to connect to the MySQL database and query the data in the MySQL database.

3. Common problems and solutions for connecting to MySQL database

1. Connection timeout problem

When connecting to the MySQL database, a connection timeout may occur. This is generally caused by unstable network or untimely response of the MySQL server. The solution to this problem is to increase the timeout for connecting to the MySQL database.

db.SetConnMaxLifetime(time.Second * 10)

Among them, time.Second * 10 means that the connection timeout time is 10 seconds.

2.Mysql: Error 1040 Too many connections problem

This problem usually occurs when the network load of the server is high. The MySQL server cannot handle all connection requests at the same time, resulting in a connection exceeded Server load conditions. The solution is to appropriately increase the network connection limit parameters of the MySQL server and system, and optimize the SQL query statement.

3. Garbled code problem

When the query results contain Chinese, garbled codes may occur. The solution is to specify the character encoding when connecting to the MySQL database:

db, err := sql.Open("mysql", "<username>:<password>@tcp(<host>:<port>)/<database>?charset=utf8mb4")

Among them, charset=utf8mb4 means using UTF-8 encoding for communication.

4. Summary

Connecting to the MySQL database is a necessary step in application development. The Go language provides multiple ways to connect to the MySQL database through the third-party library go-sql-driver/mysql, the standard library database/sql and the ORM library. Before connecting to the MySQL database, you need to prepare database-related information, such as database user name, password, host name, port number, etc. At the same time, you also need to pay attention to some common connection problems, such as connection timeout, connection overflow, Chinese garbled characters, etc., to ensure the reliability of the connection and use of the MySQL database.

The above is the detailed content of How to make reliable MySQL database connection using Go language?. For more information, please follow other related articles on the PHP Chinese website!