Statements for mysql to export data

This article introduces the knowledge of "statements for exporting data from mysql". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Method 1: SELECT...INTO OUTFILE

Mysql > select * from mytbl into outfile'/ tmp/mytbl.txt'

Query OK, 3 rows affected (0.00 sec)

View the contents of the mytbl.txt as follows:

Mysql > system cat / tmp/mytbl.txt

1 name1

2 name2

3\ N

The data in the exported file is separated by tabs, with "\ n" as the newline character

Mysql > system od-c / tmp/mytbl.txt

0000000 1\ t n a m e 1\ n 2\ t n a m e 2\ n

0000020 3\ t\ N\ n

0000025 www.2cto.com

You can also make your own delimiters and newline characters.

Export to csv format

Mysql > select * from mytbl into outfile'/ tmp/mytbl2.txt' fields terminated by', 'enclosed by' "'lines terminated by'\ r\ n'

Query OK, 3 rows affected (0.01sec)

Mysql > system cat / tmp/mytbl2.txt

"1", "name1"

"2", "name2"

"3", N

The exported file must not already exist. This effectively prevents mysql from overwriting important files. )

The mysql account logged in at the time of export requires FILE permission

The null value is processed as\ N

Disadvantage: unable to generate output containing column labels

Method 2: redirect the output of the mysql program

[root@localhost] # mysql-uroot-p-e "select * from mytbl"-- skip-column-names test > / tmp/mytbl3.txt

Enter password:

[root@localhost ~] # cat / tmp/mytbl3.txt

1 name1 www.2cto.com

2 name2

3 NULL

-- skip-column-names removes column name lines

[root@localhost] # od-c / tmp/mytbl3.txt

0000000 1\ t n a m e 1\ n 2\ t n a m e 2\ n

0000020 3\ t N U L L\ n

0000027

Export to csv format

[root@localhost ~] # mysql-uroot-p-e "select * from mytbl"-- skip-column-names test | sed-e "s / [\ t] /, /"-e "sdebase /\ r /" > / tmp/mytbl4.txt

Enter password:

[root@localhost] # od-c / tmp/mytbl4.txt

0000000 1, n a m e 1\ r\ n 2, n a m e 2

0000020\ r\ n3, N U L L\ r\ n

The null value is processed as the string "NULL"

Method 3: use mysqldump to export

The mysqldump program is used to copy or back up tables and. It can write the table output as a text data file, or a set of insert statements used to reconstruct table rows.

[root@localhost] # dump-uroot-p-- no-create-info-- tab=/tmp test mytbl

Mysqldump uses the table name with a .txt suffix to create a data file, so this command writes to a file named / tmp/mytbl.txt

Www.2cto.com

Export to csv format

[root@localhost] # mysqldump-uroot-p-- no-create-info-- tab=/tmp-- fields-enclosed-by= "\"--fields-terminated by=", "--lines-terminated-by="\ r\ n "test mytbl tbl

At the same time, two mytbl,tbl tables are exported, and the database name followed by multiple tables exports multiple tables to the corresponding file, and if there are no tables, all the tables in the database are exported.

The null value is processed as\ N

Summary: different export methods can be selected because of the different processing requirements for null. The file name derived from method 3 is fixed, and it is not suitable to use if the file name has special requirements, and method 3 can only export the whole table.

The second method can be used in conjunction with the linux command for greater flexibility.

This is the end of the content of "statements for exporting data from mysql". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!