How to import data into MySQL database using Go language

How to use Go language to import data into MySQL database

MySQL is a very popular relational database at present. More and more developers choose to use Go language to interact with MySQL for data. This article will introduce How to use Go language to import data into MySQL database.

1. Determine the imported data structure
   Before importing data, we first need to determine the imported data structure, that is, the table structure, such as table fields, types, primary keys, etc., to ensure that it is consistent with the MySQL database The table structure remains consistent.

2. Preparing data sources
   In Go language, files in CSV, JSON, XML and other formats can be used as data sources for import. The following uses a CSV file as an example:

   id,name,age,gender
   1,张三,18,男
   2,李四,20,男
   3,王五,22,女

   As you can see from the above example, each line of data is separated by commas. The first line is the field name, and the following lines are specific data records.

3. Connecting to MySQL database
   In Go language, you can use a third-party library to connect to the MySQL database and execute SQL statements to complete data import. Specifically, you can use the go-sql-driver/mysql library and the Open() function to connect to the MySQL database.

Sample code:

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

db, err := sql.Open("mysql", "user:password@tcp(your-mysql-ip:port)/your-database-name")
if err != nil {
    panic(err.Error())
}
defer db.Close()  // 注意关闭数据库连接

4. Prepare SQL statements
   After connecting to the MySQL database, we need to prepare SQL statements for inserting data. Here we can use Golang's fmt.Sprintf() function to dynamically generate SQL statements. Note that SQL injection attacks need to be prevented.

Sample code:

sqlStr := "INSERT INTO your-table-name(id, name, age, gender) VALUES (%d, '%s', %d, '%s')"

5. Reading CSV files
   Using the csv package built into the Go language, you can easily read CSV files The data. Use the NewReader() function to create a CSV file object, and use the Read() function to read each line of data.

Sample code:

import (
    "bufio"
    "encoding/csv"
    "os"
)

csvFile, err := os.Open("your-csv-file.csv")
if err != nil {
    panic(err)
}
defer csvFile.Close()
reader := csv.NewReader(bufio.NewReader(csvFile))
for {
    line, err := reader.Read()
    if err == io.EOF {
        break
    } else if err != nil {
        panic(err)
    }
    // TODO: 将读取到的数据进行插入操作
}

6. Perform the insertion operation
   After reading each row of data in the CSV file, we need to insert the data into the MySQL database . Use the Exec() function to execute the SQL statement and complete the data insertion operation.

Sample code:

query := fmt.Sprintf(sqlStr, id, name, age, gender)
_, err = db.Exec(query)
if err != nil {
    panic(err)
}

7. Full code
   The following is the complete code written through the above steps:

   import (
    "bufio"
    "database/sql"
    "encoding/csv"
    "fmt"
    "io"
    "os"
    _ "github.com/go-sql-driver/mysql"
   )
   
   func main() {
    db, err := sql.Open("mysql", "user:password@tcp(your-mysql-ip:port)/your-database-name")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()
   
    sqlStr := "INSERT INTO your-table-name(id, name, age, gender) VALUES (%d, '%s', %d, '%s')"
    csvFile, err := os.Open("your-csv-file.csv")
    if err != nil {
        panic(err)
    }
    defer csvFile.Close()
   
    reader := csv.NewReader(bufio.NewReader(csvFile))
    for {
        line, err := reader.Read()
        if err == io.EOF {
            break
        } else if err != nil {
            panic(err)
        }
        id := line[0]
        name := line[1]
        age := line[2]
        gender := line[3]
   
        query := fmt.Sprintf(sqlStr, id, name, age, gender)
        _, err = db.Exec(query)
        if err != nil {
            panic(err)
        }
    }
   }

Use the above code to import data from CSV files into the MySQL database. It should be noted that when using the Exec() function to execute a SQL statement, the inserted data needs to be converted to data type and error handling performed to avoid data type errors causing data insertion failure. At the same time, attention must be paid to preventing SQL injection attacks, and escaping is required when using dynamically generated SQL statements.

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