Learn how to import and export CSV files in MySQL

 

A sample SQL statement for exporting CSV data in MySQL is as follows:

SQL code

1. Select * From test_info
2. Into OUTFILE '/tmp/test.csv'
3. Fields terminated by ', 'optionally enclosed by' "'escaped '"'
4. Lines terminated by '\ r \ n ';

Select * From test_info into OUTFILE '/tmp/test.csv' fields terminated by ', 'optionally enclosed by' "'escaped by '" 'Lines terminated by' \ r \ n ';

A sample SQL statement for importing CSV data in MySQL is as follows:

SQL code

1. Load data infile '/tmp/test.csv'
2. Into Table test_info
3. Fields terminated by ', 'optionally enclosed by' "'escaped '"'
4. Lines terminated by '\ r \ n ';

Load data infile '/tmp/test.csv' into Table test_info fields terminated by ', 'optionally enclosed by' "'fig by '" 'Lines terminated by' \ r \ n ';

The most important part is the format parameter.

SQL code

1. Fields terminated by ', 'optionally enclosed by' "'escaped '"'
2. Lines terminated by '\ r \ N'

Fields terminated by ', 'optionally enclosed by' "'elasticed by '" 'Lines terminated by' \ r \ N'

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.

 

File: test_csv. SQL

SQL code

1. Use test;
3. Create Table test_info (
4. Id integer not null,
5. Content varchar (64) not null,
6. Primary Key (ID)
7. );
9. Delete from test_info;
11. Insert into test_info values (2010, 'Hello, line
12. Suped
13. SEPED
14. "
15. End'
16. );
18. Select * From test_info;
20. Select * From test_info into OUTFILE '/tmp/test.csv' fields terminated by ', 'optionally enclosed by' "'escaped by '" 'Lines terminated by' \ r \ n ';
22. Delete from test_info;
24. Load data infile '/tmp/test.csv' into Table test_info fields terminated by ', 'optionally enclosed by' "'fig by '" 'Lines terminated by' \ r \ n ';
26. Select * From test_info;

Use test; Create Table test_info (ID integernot null, content varchar (64) not null, primary key (ID); Delete from test_info; insert into test_info values (2010, 'Hello, linesupedseped "end'); select * From test_info into OUTFILE '/tmp/test.csv 'fields terminated ', 'optionally enclosed by '"'elasticed by'" 'Lines terminated by '\ r \ n'; delete from test_info; load data infile '/tmp/test.csv' into Table test_info fields terminated by ', 'optionally enclosed by' "'fig by '" 'Lines terminated by' \ r \ n '; select * From test_info;

 

File: test.csv

Text code

1. 2010, "Hello, line
2. Suped
3. SEPED
4. ""
5. End"

2010, "Hello, linesupedseped" "end"

 

In Linux, if you need to perform such import and export operations, you 'd better combine them with shell scripts. To avoid writing format parameters every time, you can save the string in the variable, as follows: (File MySQL. SH)

Bash code

1. #! /Bin/sh
4. # Copyright (c) 2010 codingstandards. All rights reserved.
5. # File: mysql. Sh
6. # Description: operate MySQL Databases in bash
7. # License: lgpl
8. # Author: codingstandards
9. # Email: codingstandards@gmail.com
10. # Version: 1.0
11. # Date: 2010.02.28
14. # Command line parameters in CSV format when importing and exporting data in MySQL
15. # When exporting data, use: Select... from... [where...] into OUTFILE '/tmp/data.csv' $ mysql_csv_format;
16. # When importing data, use: load data infile '/tmp/data.csv' into table... $ mysql_csv_format;
17. # CSV standard documentation: RFC 4180
18. Mysql_csv_format = "fields terminated by ', 'optionally enclosed by' \" 'scaped by '\ "'Lines terminated by' \ r \ N '"

#! /Bin/sh # copyright (c) 2010 codingstandards. all rights reserved. # file: MySQL. sh # Description: bash in the MySQL database # license: lgpl # Author: codingstandards # Email: codingstandards@gmail.com # version: 1.0 # Date: 2010.02.28 # MySQL import export data, command line parameters when using CSV format # When exporting data, use: select... from... [Where...] into OUTFILE '/tmp/data.csv' $ mysql_csv_format; # Use: load data infile '/tmp/data.csv 'into table... $ mysql_csv_format; # CSV standard document: RFC 4180mysql_csv_format = "fields terminated ', 'optionally enclosed by '\ "'escaped by' \" 'Lines terminated by '\ r \ N '"

 

Example: (File test_mysql_csv.sh)

Bash code

1. #! /Bin/sh
3. ./Opt/shtools/commons/MySQL. Sh
5. # Mysql_csv_format = "fields terminated by ', 'optionally enclosed by' \" 'escaped by '\ "'Lines terminated by' \ r \ N '"
6. Echo "mysql_csv_format = $ mysql_csv_format"
8. Rm/tmp/test.csv
10. Mysql-p -- default-character-set = GBK-t -- verbose test <EOF
12. Use test;
14. Create Table if not exists test_info (
15. Id integer not null,
16. Content varchar (64) not null,
17. Primary Key (ID)
18. );
20. Delete from test_info;
22. Insert into test_info values (2010, 'Hello, line
23. Suped
24. SEPED
25. "
26. End'
27. );
29. Select * From test_info;
31. -- Select * From test_info into OUTFILE '/tmp/test.csv 'fields terminated by', 'optionally enclosed by' "'escaped by'" 'Lines terminated by' \ r \ n ';
32. Select * From test_info into OUTFILE '/tmp/test.csv' $ mysql_csv_format;
34. Delete from test_info;
36. -- Load data infile '/tmp/test.csv' into Table test_info fields terminated by ', 'optionally enclosed by' "'fig by '" 'Lines terminated by' \ r \ n ';
37. Load data infile '/tmp/test.csv' into Table test_info $ mysql_csv_format;
39. Select * From test_info;
42. EOF
44. Echo "==== content in/tmp/test.csv ===="
45. CAT/tmp/test.csv

#! /Bin/sh. /opt/shtools/commons/MySQL. sh # mysql_csv_format = "fields terminated ', 'optionally enclosed by '\ "'escaped by' \" 'Lines terminated by '\ r \ N' "Echo" mysql_csv_format = $ mysql_csv_format "RM/tmp/test.csv mysql-p -- default-character-set = GBK-t -- verbose test <eofuse test; create Table if not exists test_info (ID integernot null, content varchar (64) not null, primary key (ID); Delete from test_info; insert into test_info values (2010, 'Hello, linesupedseped "end'); select * From test_info; -- select * From test_info into OUTFILE '/tmp/test.csv 'fields terminated ', 'optionally enclosed by '"'elasticed by'" 'Lines terminated by '\ r \ n'; select * From test_info into outfile'/tmp/test.csv' $ mysql_csv_format; delete from test_info; -- load data infile '/tmp/test.csv 'into table test_info fields terminated ', 'optionally enclosed by '"'elasticed by'" 'Lines terminated by '\ r \ n'; load data infile'/tmp/test.csv 'into Table test_info $ mysql_csv_format; select * From test_info; eofecho "==== content in/tmp/test.csv ===" cat/tmp/test.csv