Shulou(Shulou.com)05/31 Report--

MySQL how to export CSV format data, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The sample SQL statements for exporting data in CSV format in MySQL are as follows:

Sql code

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'

Fields terminated by', 'optionally enclosed by' "'escaped by''

The sample SQL statements for importing data in CSV format in lines terminated by'\ r\ nimport; MySQL are as follows:

Sql code

Load data infile'/ tmp/test.csv'

Into table test_info

Fields terminated by', 'optionally enclosed by' "'escaped by''

Lines terminated by'\ r\ n'

Load data infile'/ tmp/test.csv'

Into table test_info

Fields terminated by', 'optionally enclosed by' "'escaped by''

Lines terminated by'\ r\ nparameters; the most important part is the format parameters.

Sql code

Fields terminated by', 'optionally enclosed by' "'escaped by''

Lines terminated by'\ r\ n'

Fields terminated by', 'optionally enclosed by' "'escaped by''

The parameter lines terminated by'\ r\ n' is set according to the RFC4180 document, whose full name is Common Format and MIME Type for Comma-Separated Values (CSV) Files, which describes the CSV format in detail, and its main points include:

(1) Fields are separated by commas, and data rows are separated by\ r\ n

(2) the string is surrounded by half-width double quotation marks, and the double quotation marks of the string itself are represented by two double quotes.

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', 'escaped 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', 'escaped 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 "

If you often want to do such import and export operations under Linux, it is best to combine them with Shell scripts. To avoid writing format parameters every time, you can save this string in a variable, as follows: (file mysql.sh)

Bash code

#! / bin/sh

# Copyright (c) 2010 codingstandards. All rights reserved.

# file: mysql.sh

# description: operate MySQL database in Bash

# license: LGPL

# author: codingstandards

# email:

# version: 1.0

# date: 2010.02.28

# Command line parameters when using CSV format when importing and exporting data in MySQL

# use: select when exporting data. From... [where...] Into outfile'/ tmp/data.csv' $MYSQL_CSV_FORMAT

# use: load data infile'/ tmp/data.csv' into table... $MYSQL_CSV_FORMAT when importing data

# CSV standard document: RFC 4180

MYSQL_CSV_FORMAT= "fields terminated by", 'optionally enclosed by'\ "'escaped by'\" 'lines terminated by'\ r\ n' "

#! / bin/sh

# Copyright (c) 2010 codingstandards. All rights reserved.

# file: mysql.sh

# description: operate MySQL database in Bash

# license: LGPL

# author: codingstandards

# email:

# version: 1.0

# date: 2010.02.28

# Command line parameters when using CSV format when importing and exporting data in MySQL

# use: select when exporting data. From... [where...] Into outfile'/ tmp/data.csv' $MYSQL_CSV_FORMAT

# use: load data infile'/ tmp/data.csv' into table... $MYSQL_CSV_FORMAT when importing data

# CSV standard document: RFC 4180

MYSQL_CSV_FORMAT= "fields terminated by", 'optionally enclosed by'\ "'escaped by'\" 'lines terminated by'\ r\ n' "

Examples of use are as follows: (file test__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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.