MySql database export csv
MySql database export csv file command:
Mysql> select first_name, last_name, email from account into outfile 'E: // output1.csv 'Fields terminated by', 'optionally enclosed by ''' lines terminated by '/N ';
Csv file effect:
| Sunny |
Grigoryan |
Lovechoosesun@gmail.com |
|
|
|
|
|
| Jon |
Siegal |
Sun@riliantech.net |
|
|
|
|
|
| Joe |
Siegal |
Zhao@gmail.com |
|
|
|
|
|
| Alejandro |
Medina |
Wei@gmail.com |
|
|
|
|
|
Run the following command to import a cvs file to the MySql database:
Mysql> load data local infile 'E: // input1.csv 'into table test1 fields termin
Ated by ', 'lines terminated by'/N' (first_name, last_name, email );
Query OK, 1 row affected, 1 warning (0.00 sec)
Records: 69 Deleted: 0 Skipped: 68 Warnings: 0
Mysql> select * from test1;
+ ---- + ------------ + ----------- + -------------------------- +
| Id | first_name | last_name | email |
+ ---- + ------------ + ----------- + -------------------------- +
| 0 | sunny | Grigoryan | lovechoosesun@gmail.com
+ ---- + ------------ + ----------- + -------------------------- +
Fields terminated by ---- Field Terminator
Optionally enclosed by ---- envelope character
Lines terminated by ---- line terminator
Connect to the server through the mysql client shell, select the database to use, and enter the SQL code:
Select * from test_info
Into outfile '/tmp/test.csv'
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '/r/N ';
The most important part is the format parameter.
This parameter is set according to RFC4180. The full name of this document is Common Format and MIME Type for Comma-Separated Values (CSV) Files. the CSV Format is described in detail. the key points include:
(1) fields are separated by commas, and data rows are separated by/r/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.
By executing the above code, you can export the required data to the executed file in csv format.
In addition, the SQL code for importing CSV data in MySQL is as follows:
Load data infile '/tmp/test.csv'
Into table test_info
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '/r/N ';
When the number of columns in each row in the csv file is smaller than that in the database table, the following statements are useful:
Load data infile "/tmp/appleAppV2_search_day_20110525.csv" into table apple_search_log fields terminated by '; 'Lines terminated by'/N' (apptypeId, keyword, searchTime, model );
The Blue part is the highlight. This statement is useful if the data table contains an auto-increment field, such as an auto-increment ID.