Method 1: Select... into OUTFILE Mysql> select * From mytbl into OUTFILE '/tmp/mytbl.txt '; Query OK, 3 rows affected (0.00 Sec) The contents of mytbl.txt are as follows: Mysql> System CAT/tmp/mytbl.txt 1 name1 2 name2 3 \ n
Data in exported files are separated by tabs and the line break is "\ n ". 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 0000025
You can also customize separators and line breaks. Export to CSV format Mysql> select * From mytbl into OUTFILE '/tmp/mytbl2.txt' fields terminated by ', 'enabledby' "'Lines terminated by '\ r \ n '; Query OK, 3 rows affected (0.01 Sec)
Mysql> System CAT/tmp/mytbl2.txt "1", "name1" "2", "name2" "3", \ n
The exported file must not exist. (This effectively prevents MySQL from overwriting important files .) The MySQL account you log on to during export must have the file permission. The null value is processed as \ n. Disadvantage: output containing column tags cannot be generated.
Method 2: Redirect MySQLProgramOutput [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 2 name2 3 Null -- Skip-column-names remove column name rows
[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 \ n 0000027
Export to CSV format [Root @ localhost ~] # Mysql-uroot-p-e "select * From mytbl" -- skip-column-names test | sed-e "s/[\ t]/, /"-e" s/$/\ 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 \ n 3, n u 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 databases. It can write the table output as a text data file or an insert statement set used to reconstruct the table row. [Root @ localhost ~] # Mysqldump-uroot-p -- no-create-info -- tab =/tmp test mytbl Mysqldumpcreates a data file by adding a. txt suffix to the table name. Therefore, this command writes a file named/tmp/mytbl.txt.
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 Two tables mytbl and TBL are exported at the same time. If the database name is followed by multiple tables, multiple tables are exported to the corresponding file. If no table exists, all tables in the database are exported.
The null value is processed as \ n.
Conclusion: The processing requirements for null are different. You can select different export methods. If method 3 exports a fixed file name and has special requirements on the file name, it is not suitable for use. Method 3 can only export the entire table.
Method 2 can be used in combination with Linux commands, which is more flexible.
