MySQL command line import and export data

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

SQL code

1. SELECT * FROM test_info
2. into outfile '/tmp/test.csv '
3. Fields terminated by ', ' optionally enclosed by ' "' escaped by '" '
4. Lines terminated by ' \ r \ n ';

[SQL]View Plaincopy print?

1. SELECT * FROM test_info
2. into outfile '/tmp/test.csv '
3. Fields terminated by ', ' optionally enclosed by ' "' escaped by '" '
4. Lines terminated by ' \ r \ n ';

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

SQL code

1. Load data infile '/tmp/test.csv '
2. into table Test_info
3. Fields terminated by ', ' optionally enclosed by ' "' escaped by '" ' /c5>
4. Lines terminated by ' \ r \ n ';

[SQL]View Plaincopy print?

1. Load data infile '/tmp/test.csv '
2. into table Test_info
3. Fields terminated by ', ' optionally enclosed by ' "' escaped by '" ' /c5>
4. Lines terminated by ' \ r \ n ';

Load data infile '/tmp/test.csv ' into table test_info fields  terminated by ', '  optionally enclosed by ' "' escape D by ' "' lines terminated by ' \ r \ n ';

The most critical part of this is the format parameter.

SQL code

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

[SQL]View Plaincopy print?

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

Fields terminated by ', ' optionally enclosed by ' "' escaped by '" ' lines terminated by ' \ r \ n '

This parameter is set according to the RFC4180 document, which is the full name common format and MIME Type for comma-separated Values (CSV) Files, which detail the CSV format, and the key points include:

(1) The fields are separated by commas, and the data rows are separated by \ r \ n;

(2) The string is surrounded by a half-width double quotation mark, and the string itself is represented by a double quotation mark in two double quotes.

Files: Test_csv.sql

SQL code

2. Use test;
6. Create table Test_info (
8. ID integer not null,
10. Content varchar (+) is not null,
12. primary key (ID)
14. );
18. Delete from test_info;
22. Insert INTO test_info values ("Hello, line
24. suped
26. seped
28. "
30. End
32. );
36. SELECT * from test_info;
40. SELECT * from test_info to outfile '/tmp/test.csv ' fields terminated by ', ' optionally enclosed   By ', ' escaped by ', ' lines terminated by ' \ r \ n ';
44. Delete from test_info;
48. Load data infile '/tmp/test.csv ' into table test_info fields terminated by ', ' optionally enclosed '  C6>by ' "' escaped by '" ' Lines terminated by ' \ r \ n ';
52. SELECT * from test_info;

[SQL]View Plaincopy print?

2. Use test;
6. Create table Test_info (
8. ID integer not null,
10. Content varchar (+) is not null,
12. primary key (ID)
14. );
18. Delete from test_info;
22. Insert INTO test_info values ("Hello, line
24. suped
26. seped
28. "
30. End
32. );
36. SELECT * from test_info;
40. SELECT * from test_info to outfile '/tmp/test.csv ' fields terminated by ', ' optionally enclosed   By ', ' escaped by ', ' lines terminated by ' \ r \ n ';
44. Delete from test_info;
48. Load data infile '/tmp/test.csv ' into table test_info fields terminated by ', ' optionally enclosed '  C6>by ' "' escaped by '" ' Lines terminated by ' \ r \ n ';
52. SELECT * from test_info;

Use Test;create table Test_info (ID integernot null,content varchar ($) Not null,primary key (ID));d elete from Test_info;i Nsert into test_info values (' Hello, linesupedseped ' end '); select * FROM Test_info;select * from Test_info to Outfi Le '/tmp/test.csv ' terminated by ', ' optionally enclosed by ' "' escaped by '" ' lines terminated by ' \ r \ n ';d elete fr Om test_info;load data infile '/tmp/test.csv ' into table test_info fields  terminated by ', '  

Files: test.csv

Text Code

1. , "Hello, line
2. suped
3. seped
4. ""
5. End

[Text]View Plaincopy print?

1. , "Hello, line
2. suped
3. seped
4. ""
5. End

"Hello, linesupedseped" "End"

Under Linux If you often want to do such import and export operations, of course, it is best to combine with shell script, in order to avoid each time to write the format parameters, you can save this string in the variable, as follows: (File mysql.sh)

Bash code

2. #!/bin/sh
8. # Copyright (c) codingstandards.  All rights reserved.
10. # file:mysql.sh
12. # operating MySQL database in Description:bash
14. # LICENSE:LGPL
16. # Author:codingstandards
18. # email: [Email protected]
20. # Version: 1.0
22. # Date: 2010.02. -
28. # When importing and exporting data in MySQL, command-line arguments when using CSV format
30. # When exporting data using: Select ... from ... [Where ...]  into outfile '/tmp/data.csv ' $MYSQL _csv_format;
32. # used when importing data: Load data infile '/tmp/data.csv ' into table ... $MYSQL _csv_format;
34. # CSV standard document: RFC 4180
36. Mysql_csv_format="Fields terminated by ', ' optionally enclosed by ' \" ' escaped by ' \ "' lines terminated by ' \ r \ n '"

#!/bin/sh# Copyright (c) codingstandards. All rights reserved.# file:mysql.sh# Description:bash operation MySQL Database # license:lgpl# author:codingstandards# email: [Email protected]# version:1.0# date:2010.02.28# When importing and exporting data in MySQL, the command line arguments # when using CSV format are used when exporting data: Select ... [Where ...] into outfile '/tmp/data.csv ' $MYSQL _csv_format;# used when importing data: Load data infile '/tmp/data.csv ' into table ... $MYSQL _csv_form at;# CSV Standard document: RFC 4180mysql_csv_format= "Fields terminated by ', ' optionally enclosed by ' \" ' escaped by ' \ "' Lines Terminat Ed by ' \ r \ n ' "

Examples of use are as follows: (File test_mysql_csv.sh)

Bash code

2. #!/bin/sh
6. . /opt/shtools/commons/mysql.sh
10. # mysql_csv_format="Fields terminated by ', ' optionally enclosed by ' \" ' escaped by ' \ "' lines terminated by ' \ r \ n '"
12. echo "mysql_csv_format= $MYSQL _csv_format"
16. Rm/tmp/test.csv
20. Mysql-p--default-character-set=gbk-t--verbose Test <<eof
24. Use test;
28. CREATE table if not exists test_info (
30. ID integer NOT NULL,
32. Content varchar (+) is not NULL,
34. Primary KEY (ID)
36. );
40. Delete from Test_info;
44. INSERT into test_info values ("Hello, Line
46. suped
48. seped
50. "
52. End
54. );
58. SELECT * from Test_info;
62. --select * from Test_info to outfile '/tmp/test.csv ' fields terminated by ', ' optionally enclosed by ' "' ES  Caped by ' "' lines terminated by ' \ r \ n ';
64. SELECT * from Test_info to outfile '/tmp/test.csv ' $MYSQL _csv_format;
68. Delete from Test_info;
72. --Load data infile '/tmp/test.csv ' into table test_info fields terminated by ', ' optionally enclosed by ' '  Escaped by ' "' lines terminated by ' \ r \ n ';
74. Load data infile '/tmp/test.csv ' into table Test_info $MYSQL _csv_format;
78. SELECT * from Test_info;
84. Eof
88. echo "===== content in/tmp/test.csv ====="
90. Cat/tmp/test.csv

Transferred from: http://blog.csdn.net/sara_yhl/article/details/6850107

MySQL command line import and export data