Mysql export data statement

Method 1: SELECT... INTO OUTFILEmysql> select * from mytbl into outfile '/tmp/mytbl.txt'; Query OK, 3 rows affected (0.00 secure1_mytbl.txt contains the following content: mysql> system cat/tmp/mytbl.txt 1 name12 name23 \ N the data in the exported file is separated by tabs, take "\ n" as the linefeed mysql> system od-c/tmp/mytbl.txt 0000000 1 \ t n a m e 1 \ n 2 \ t n a m e 2 \ n0000020 3 \ t \ N \ n0000025 www.2cto.com can also customize separators and line breaks to export them into 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 Exported Files must not already exist. (This effectively prevents mysql from overwriting important files .) When exporting, the mysql account that logs on must have the FILE Permission. The null value is processed as \ N. disadvantage: cannot generate output containing column tags. Method 2: Redirect mysql program output [root @ localhost ~] # Mysql-uroot-p-e "select * from mytbl" -- skip-column-names test>/tmp/mytbl3.txtEnter password: [root @ localhost ~] # Cat/tmp/mytbl3.txt 1 name1 www.2cto.com 2 name23 NULL -- skip-column-names remove the column name row [root @ localhost ~] # Od-c/tmp/mytbl3.txt0000000 1 \ t n a m e 1 \ n 2 \ t n a m e 2 \ n0000020 3 \ t n u l \ n0000027 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.txtEnter password: [root @ localhost ~] # Od-c/tmp/mytbl4.txt 0000000 1, n a m e 1 \ r \ n 2, n a m e 20000020 \ r \ n 3, n u l \ r \ n null value is processed as 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 mytblmysqldumpadd A. txt suffix to the table name to create a data file, therefore, this command writes a file www.2cto.com named/tmp/mytbl.txt and exports it to the 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 both mytbl and tbl tables are exported, after 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. 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. Author stublue