SQL statement for exporting CSV data in MySQL

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

SQL code
Select * from test_info
Into outfile '/tmp/test.csv'
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '\ r \ n ';

Select * from test_info
Into outfile '/tmp/test.csv'
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '\ r \ n'; the SQL statement sample for importing CSV data in MySQL is as follows:

SQL code
Load data infile '/tmp/test.csv'
Into table test_info
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '\ r \ n ';

Load data infile '/tmp/test.csv'
Into table test_info
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '\ r \ n'; the most important part is the format parameter.

SQL code
Fields terminated by ', 'optionally enclosed by' "'escaped '"'
Lines terminated by '\ r \ N'

Fields terminated by ', 'optionally enclosed by' "'escaped '"'
The lines terminated by '\ r \ n' parameter is set according to the RFC4180 document. 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
Use test;
 
Create table test_info (
Id integer not null,
Content varchar (64) not null,
Primary key (id)
);
 
Delete from test_info;
 
Insert into test_info values (2010, 'Hello, line
Suped
Seped
"
End'
);
 
Select * from test_info;
 
Select * from test_info into outfile '/tmp/test.csv' fields terminated by ', 'optionally enclosed by' "'escaped 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;
 

Use test;

Create table test_info (
Id integer not null,
Content varchar (64) not null,
Primary key (id)
);

Delete from test_info;

Insert into test_info values (2010, 'Hello, line
Suped
Seped
"
End'
);

Select * from test_info;

Select * from test_info into outfile '/tmp/test.csv' fields terminated by ', 'optionally enclosed by' "'escaped 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
2010, "hello, line
Suped
Seped
""
End"

2010, "hello, line
Suped
Seped
""
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
#! /Bin/sh
 
 
# Copyright (c) 2010 codingstandards. All rights reserved.
# File: mysql. sh
# Description: operate MySQL Databases in Bash
# License: LGPL
# Author: codingstandards
# Email: codingstandards@gmail.com
# Version: 1.0
# Date: 2010.02.28
 
 
# Command line parameters in CSV format when importing and exporting data in MySQL
# When exporting data, use: select... from... [where...] into outfile '/tmp/data.csv' $ MYSQL_CSV_FORMAT;
# When importing data, use: load data infile '/tmp/data.csv' into table... $ MYSQL_CSV_FORMAT;
# CSV standard documentation: RFC 4180
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: operate MySQL Databases in Bash
# License: LGPL
# Author: codingstandards
# Email: codingstandards@gmail.com
# Version: 1.0
# Date: 2010.02.28

# Command line parameters in CSV format when importing and exporting data in MySQL
# When exporting data, use: select... from... [where...] into outfile '/tmp/data.csv' $ MYSQL_CSV_FORMAT;
# When importing data, use: load data infile '/tmp/data.csv' into table... $ MYSQL_CSV_FORMAT;
# CSV standard documentation: RFC 4180
MYSQL_CSV_FORMAT = "fields terminated by ', 'optionally enclosed by' \" 'scaped by '\ "'Lines terminated by' \ r \ N '"

 
Example: (File test_mysql_csv.sh)

Bash code
#! /Bin/sh
 
./Opt/shtools/commons/mysql. sh
 
# MYSQL_CSV_FORMAT = "fields terminated by ', '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 <EOF
 
Use test;
 
Create table if not exists test_info (
Id integer not null,
Content varchar (64) not null,
Primary key (id)
);
 
Delete from test_info;
 
Insert into test_info values (2010, 'Hello, line
Suped
Seped
"
End'
);
 
Select * from test_info;
 
-- Select * from test_info into outfile '/tmp/test.csv 'fields terminated by', 'optionally enclosed by' "'escaped 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 by ', 'optionally enclosed by' "'fig by '" 'Lines terminated by' \ r \ n ';
Load data infile '/tmp/test.csv' into table test_info $ MYSQL_CSV_FORMAT;
 
Select * from test_info;
 
 
EOF
 
Echo "==== content in/tmp/test.csv ===="
Cat/tmp/test.csv

#! /Bin/sh

./Opt/shtools/commons/mysql. sh

# MYSQL_CSV_FORMAT = "fields terminated by ', '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 <EOF

Use test;

Create table if not exists test_info (
Id integer not null,
Content varchar (64) not null,
Primary key (id)
);

Delete from test_info;

Insert into test_info values (2010, 'Hello, line
Suped
Seped
"
End'
);

Select * from test_info;

-- Select * from test_info into outfile '/tmp/test.csv 'fields terminated by', 'optionally enclosed by' "'escaped 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 by ', 'optionally enclosed by' "'fig by '" 'Lines terminated by' \ r \ n ';
Load data infile '/tmp/test.csv' into table test_info $ MYSQL_CSV_FORMAT;

Select * from test_info;

EOF

Echo "==== content in/tmp/test.csv ===="
Cat/tmp/test.csv

Author: "wangqiaowqo"