Home >Database >Mysql Tutorial >How to import data into MySQL database using Go language

How to import data into MySQL database using Go language

WBOY
WBOYOriginal
2023-06-17 12:49:401936browse

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()  // 注意关闭数据库连接
  1. 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')"
  1. 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: 将读取到的数据进行插入操作
}
  1. 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)
}
  1. 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!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn