Export Data into file using MySQL

  • Avinash
  • 2
  • Sep 07, 2011
  • Database

With MySQL we can easily export our selected rows to the files. MySQL has several syntax which can export the retrieved rows into the file.

Yes we can import these rows back to database tables also.

Lets see how you can export rows to the file.

Export data to File

SELECT * FROM table_name INTO OUTFILE "rows.txt"
  SELECT * FROM table_name INTO OUTFILE "rows.txt"

So after executing above query you can see the rows.txt on the server folder where database data files are stored.

Now import the exported files into database tables.

Import data from File

LOAD DATA INFILE 'rows.txt' INTO TABLE test; // We can have other options like below LOAD DATA INFILE 'rows.txt' INTO TABLE test FIELDS TERMINATED BY ',' LINES STARTING BY 'xx';
  LOAD DATA INFILE 'rows.txt' INTO TABLE test;

// We can have other options like below
 
  LOAD DATA INFILE 'rows.txt' INTO TABLE test
  FIELDS TERMINATED BY ','  LINES STARTING BY 'xx';

Note: FIELDS TERMINATED BY and LINES STARTING BY are used for the formatting purspose.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by:

  • Tjorriemorrie

    Is this still appropriate for extremely large databases (with millions of rows)? At our company we can’t afford the table getting locked for very long. Any suggestions?

    • http://www.xpertdeveloper.com Avinash

      Hi Tjorriemorrie ,

      I have tried with table with 13983816 rows. This table is about 1 GB in size.