How to use Go language to import and export data from MySQL database

With the advent of the Internet and big data era, data processing has become an essential skill. As the most popular relational database management system in the world, MySQL has been widely used in the field of data processing. MySQL has the advantages of high performance, ease of use, and flexibility, but there may be duplicate or invalid data during the data import and export process. Therefore, this article will introduce how to use the Go language to filter data import and export of the MySQL database.

1. Environment setup

1. Installing MySQL database

Before starting, you first need to install a MySQL database. The installation steps of MySQL will not be repeated here. You can install it through the official website or install it in your own system source. After the installation is complete, start the MySQL service and create a database.

2. Install Go and MySQL drivers

Before writing Go language programs, you need to install Go and MySQL drivers. Go language is relatively easy to install and can be installed through the official website. The MySQL driver can be installed through the go get command.

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

2. Import data

The following will introduce how to use Go language to import through a sample program MySQL database data and perform data filtering to ensure that there is no duplicate data in the database.

1. Writing a program

The following is a simple Go language program for importing data into a MySQL database.

package main

import (
    "database/sql"
    "fmt"

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

func main() {
    // 打开MySQL连接
    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/database")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 插入数据
    stmt, err := db.Prepare("INSERT INTO users(name, age) VALUES(?, ?)")
    if err != nil {
        panic(err)
    }
    defer stmt.Close()

    _, err = stmt.Exec("Tom", 20)
    if err != nil {
        panic(err)
    }
    fmt.Println("Insert data success!")
}

In the above code, we first use the sql.Open() function to open a MySQL connection. Among them, "username" and "password" are the MySQL username and password respectively, "127.0.0.1:3306" is the address and port number of the MySQL database, and "database" is the name of the database to be operated. Next, we use the db.Prepare() function to prepare a SQL statement for inserting data into the database. Then use the stmt.Exec() function to execute the statement and insert the data into the database.

2. Data filtering

For the data imported into the database, we need to ensure that there is no duplicate data in the database. Below is a sample program that adds data and performs duplicate data filtering.

package main

import (
    "database/sql"
    "fmt"

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

func main() {
    // 打开MySQL连接
    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/database")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 插入数据并进行重复数据过滤
    err = insert(db, "Tom", 20)
    if err != nil {
        panic(err)
    }
    fmt.Println("Insert data success!")
}

// 将数据插入到数据库中，并进行重复数据过滤
func insert(db *sql.DB, name string, age int) error {
    // 判断数据是否已经存在
    rows, err := db.Query("SELECT COUNT(*) FROM users WHERE name = ? AND age = ?", name, age)
    if err != nil {
        return err
    }
    rows.Next()
    var count int
    err = rows.Scan(&count)
    if err != nil {
        return err
    }
    if count > 0 {
        fmt.Printf("Data[%s, %d] already exists
", name, age)
        return nil
    }

    // 插入数据
    stmt, err := db.Prepare("INSERT INTO users(name, age) VALUES(?, ?)")
    if err != nil {
        return err
    }
    defer stmt.Close()

    _, err = stmt.Exec(name, age)
    if err != nil {
        return err
    }
    return nil
}

In the above code, we define an insert() function to implement the data import and filtering functions. In the insert() function, first use the SELECT statement to query whether the data to be inserted exists. If it exists, no more insertion operations will be performed. Otherwise, use the INSERT statement to insert the data into the database.

3. Export data

The export of data usually means outputting the data in the database to a file in a specific format for manual analysis or other system processing. Go language provides fmt package and text/template package, which can easily output data into text format or HTML format.

The following is a sample program for exporting and outputting data in a MySQL database into HTML format.

package main

import (
    "database/sql"
    "fmt"
    "html/template"
    "os"

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

type User struct {
    Name string
    Age  int
}

func main() {
    // 打开MySQL连接
    db, err := sql.Open("mysql", "username:password@tcp(127.0.0.1:3306)/database")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // 导出数据并输出为HTML格式
    err = export(db)
    if err != nil {
        panic(err)
    }
}

// 从数据库中导出数据并输出为HTML格式
func export(db *sql.DB) error {
    // 查询数据
    rows, err := db.Query("SELECT * FROM users")
    if err != nil {
        return err
    }
    defer rows.Close()

    var userList []User
    for rows.Next() {
        var user User
        err = rows.Scan(&user.Name, &user.Age)
        if err != nil {
            return err
        }
        userList = append(userList, user)
    }

    // 输出为HTML格式
    tmpl, err := template.New("userlist").Parse(`
        <!DOCTYPE html>
        <html lang="en">
        <head>
            <meta charset="UTF-8">
            <title>User list</title>
        </head>
        <body>
            <h1>User list</h1>
            <table>
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Age</th>
                    </tr>
                </thead>
                <tbody>
                    {{range .}}
                    <tr>
                        <td>{{.Name}}</td>
                        <td>{{.Age}}</td>
                    </tr>
                    {{end}}
                </tbody>
            </table>
        </body>
        </html>
    `)
    if err != nil {
        return err
    }

    file, err := os.Create("userlist.html")
    if err != nil {
        return err
    }
    defer file.Close()

    return tmpl.Execute(file, userList)
}

In the above code, we define an export() function to implement the data export and formatted output functions. In the export() function, first use the SELECT statement to query the data and store the query results into a slice of type User. Then use the template package to output the data into HTML format and write it to a file.

Conclusion

This article introduces the method of using Go language to perform data import and export filtering for MySQL database. Through a sample program, it explains in detail how to use Go language to write a data import and export filter program for MySQL database. And analyzed and explained. It is worth pointing out that the sample program in this article is only a basic method, and readers can adjust and improve it according to their own needs to achieve better results.

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