How to extract data from MySQL database using Go language

MySQL is a very popular relational database, and the Go language is a rapidly developing programming language. If you combine the two, you can achieve convenient and efficient data extraction. This article will introduce the method of using Go language to extract MySQL data. I hope it can inspire and help everyone.

1. Install and configure the MySQL driver

Before using the Go language to extract MySQL data, we need to install the MySQL driver first. There are many choices for MySQL drivers. Here we introduce the two most commonly used ones: go-sql-driver and mysql driver.

Use go-sql-driver to install, you can use the following command:

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

Use the mysql driver to install, you can use the following command:

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

After the installation is completed, Just import the corresponding driver package in the code, for example:

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

However, it should be noted that when using the MySQL driver, you also need to configure MySQL relatedly. The specific method is as follows:

1. Set the character set of MySQL to utf8mb4

Versions before MySQL 5.7.7 use the utf8 character set by default, which only supports 3-byte unicode characters, while in 5.7. In versions 7 and later, the default character set is utf8mb4, which supports 4-byte unicode characters. You can set the character-set-server in the configuration file my.cnf or my.ini to utf8mb4 to support Chinese, etc. Special character effects.

2. Enable binlog log

In MySQL, the binlog log records all sql statements that operate on MySQL, so when extracting data, you need to enable the binlog log. for real-time data synchronization. It can be configured and enabled in my.cnf or my.ini.

2. Connect to the MySQL database

After installing the MySQL driver and configuring it accordingly, we need to connect to the MySQL database. The method to connect to the MySQL database is as follows:

db, err := sql.Open("mysql", "user:password@tcp(ip:port)/database")

Among them, user and password are the user name and password of MySQL, ip and port are the connection address and port number of MySQL, and database is the database to be accessed.

After successfully connecting to the MySQL database, we can perform corresponding data operations, such as reading data in MySQL into the Go language, or storing data in the Go language into MySQL.

3. SQL Statement Query

It is very simple to use SQL statements to query data in MySQL in Go language. We can use the sql.Query function to execute the query statement and save the results to the rows object, such as:

rows, err := db.Query("SELECT * FROM table1")

After successful execution, you can use the Next function of the rows object to traverse the results and take out one record each time. And encapsulate it into the corresponding struct object:

type Product struct {
    ID         int64  `db:"id"`
    Name       string `db:"name"`
    Price      int64  `db:"price"`
    CreateTime int64  `db:"create_time"`
}

func main() {
    rows, err := db.Query("SELECT * FROM product")  // 查询product表
    if err != nil {
        panic(err)
    }

    defer rows.Close()

    products := make([]Product, 0)

    for rows.Next() {  // 遍历结果集
        var p Product
        err = rows.Scan(&p.ID, &p.Name, &p.Price, &p.CreateTime)  // 将一条记录封装到Product对象中
        if err != nil {
            panic(err)
        }
        products = append(products, p)
    }
}

The method of using the sql.QueryRow function to query a single record is also very simple:

var p Product
err := db.QueryRow("SELECT * FROM product WHERE id=?", 1).Scan(&p.ID, &p.Name, &p.Price, &p.CreateTime)
if err != nil {
    panic(err)
}

4. Insert data

Use Inserting data into MySQL is also very simple with Go language. We can use the sql.Exec function to execute the SQL statement to achieve this:

stmt, err := db.Prepare("INSERT INTO product(name, price) VALUES(?,?)")
if err != nil {
    panic(err)
}

res, err := stmt.Exec("Product1", 100)
if err != nil {
    panic(err)
}

The Prepare function in the above code is used to prepare the SQL statement, and then use the Exec function to execute the SQL statement and return the result. If the execution is successful, the returned result contains the ID of the inserted data (if any), which can be obtained using the LastInsertId function. If you need to get the number of affected rows, you can use the RowsAffected function.

5. Update and delete data

Similar to inserting data, Go language can also use the sql.Exec function to execute SQL update statements and delete statements, as follows:

// 执行更新操作
stmt, err := db.Prepare("UPDATE product SET price=? WHERE id=?")
if err != nil {
    panic(err)
}

res, err := stmt.Exec(200, 1)
if err != nil {
    panic(err)
}

// 执行删除操作
stmt, err = db.Prepare("DELETE FROM product WHERE id=?")
if err != nil {
    panic(err)
}

res, err = stmt.Exec(1)
if err != nil {
    panic(err)
}

6. Transaction Management

When performing data operations, it is sometimes necessary to ensure the atomicity of the data, that is, all database operations either succeed or fail. At this point, we can use the transaction management mechanism to achieve this.

In the Go language, you can use db.Begin and tx.Commit to open and submit transactions, such as:

tx, err := db.Begin()
if err != nil {
    panic(err)
}

stmt, err := tx.Prepare("INSERT INTO product(name, price) VALUES(?,?)")
if err != nil {
    panic(err)
}

res, err := stmt.Exec("Product1", 100)
if err != nil {
    tx.Rollback()
    panic(err)
}

stmt, err = tx.Prepare("INSERT INTO product(name, price) VALUES(?,?)")
if err != nil {
    panic(err)
}

res, err = stmt.Exec("Product2", 200)
if err != nil {
    tx.Rollback()
    panic(err)
}

// 提交事务
err = tx.Commit()
if err != nil {
    tx.Rollback()
    panic(err)
}

In the above code, after opening the transaction, we can perform multiple operations in the transaction SQL operation. After executing all SQL operations and ensuring the atomicity of all SQL operations, you need to use the Commit() function to commit the transaction. If an error occurs in any SQL operation, you can use the Rollback() function to roll back the transaction.

7. Conclusion

This article introduces the method of using Go language to extract data from MySQL database. Through the above introduction, we can find that using Go language to perform data operations on MySQL database is very convenient and efficient. If you haven't tried using Go language for database operations, you might as well give it a try. I believe you will fall in love with it.

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