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.
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.
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() // 注意关闭数据库连接
Sample code:
sqlStr := "INSERT INTO your-table-name(id, name, age, gender) VALUES (%d, '%s', %d, '%s')"
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: 将读取到的数据进行插入操作 }
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) }
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!