如何将数据从CSV导入MySQL

Question

I have CSV x gb and want to insert into mysql, I would use Go for this, but I am not hitting the right way to do it, has anyone done that?

my proect: https://github.com/DevJoseWeb/AMCOM/tree/master/amcom-systems-go

Answer 1

Regardless of the language, there's two basic approaches. First is to read and parse the CSV file yourself and insert one row at a time. This is inefficient.

The other is to use MySQL's load data local infile to load a CSV file into a table letting MySQL do all the work. The local part means you'll be sending MySQL the CSV file.

Unlike other SQL statements, this requires special client support to read and send the CSV file. Fortunately the go-sql-driver you're using has this support and notes a few caveats.

Files must be whitelisted by registering them with mysql.RegisterLocalFile(filepath) (recommended) or the Whitelist check must be deactivated by using the DSN parameter allowAllFiles=true (Might be insecure!).

To use a io.Reader a handler function must be registered with mysql.RegisterReaderHandler(name, handler) which returns a io.Reader or io.ReadCloser. The Reader is available with the filepath Reader:: then. Choose different names for different handlers and DeregisterReaderHandler when you don't need it anymore.

See the godoc of Go-MySQL-Driver for details.

Go-MySQL-Driver has examples. With RegisterLocalFile...

filePath := "/home/gopher/data.csv"
mysql.RegisterLocalFile(filePath)
err := db.Exec("LOAD DATA LOCAL INFILE '" + filePath + "' INTO TABLE foo")
if err != nil {
...

And with RegisterReaderHandler.

mysql.RegisterReaderHandler("data", func() io.Reader {
    var csvReader io.Reader // Some Reader that returns CSV data
    ... // Open Reader here
    return csvReader
})
err := db.Exec("LOAD DATA LOCAL INFILE 'Reader::data' INTO TABLE foo")
if err != nil {
...