This article provides a detailed analysis of how to import csv data into mysql. For more information, see mysql's own csv engine, you can use this engine to import csv data to a mysql database, which is much faster than a batch processing program written in php or python.
Specific implementation code example:
The code is as follows:
Load data infile '/tmp/file.csv' into table _ tablename (set character utf8)
Fields terminated ','
Enclosed '"'
Lines terminated by '\ r \ n ';
Some keywords involved in this code are explained as follows:
Fields terminated by '': specifies the Field Terminator in the csv file, that is, the delimiter between data;
Enclosed by '': specifies the envelope character;
Lines terminated by '': indicates the line terminator.
The csv format is described in detail in the csv document (RFC4180). The main points are:
(1) fields are separated by commas (,), and data rows are separated by \ n;
(2) the string is enclosed by double quotation marks, and the double quotation marks of the string itself are represented by two double quotation marks.
Through the above explanation, we should have a better understanding of the data import code.
Similarly, csv data can be imported into the mysql database, and data tables in mysql can also be exported to csv files. the exported code example is as follows:
The code is as follows:
Select * from tablename into outfile '/tmp/data.txt'
Fields terminated ','
Optionally enclosed '"'
Lines terminated by '\ n ';
After exporting data from the database to a file, you must follow the format defined in the exported file before importing the data to the database.
