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 SQLCode:

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, which is the full name of common format and
Mime Type for comma-separated values (CSV)
Files, which describes the CSV format 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
'"'Scaped '"'
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.