OUTFILE option in mysql

Posted on 2006-05-24
Last Modified: 2012-06-27
Hi ,

Is there a batch process there for downloading data from mysql db to a file  like there is LOAD DATA INFILE for uploading. There is an option where you can redirect the result set from a select query onto a  file using OUTFILE. But i am looking for a more faster process.

Thanks in advance
Question by:harisundhar
    LVL 11

    Accepted Solution

    You don't need to use OUTFILE, use mysql

    try something like
    mysql -h {host-name} -u {user-name} -p{passwd} -e {'query'} {dbname} > {outfile-name}

    note query should be in single quotes
    LVL 1

    Author Comment

    Is there an option using the mysql command to set a delimiter and line sperator and enclosing each value by qoutes . Basically to change the format the values are written to the file.
    LVL 30

    Assisted Solution


     The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. As of MySQL 5.0.19, the character_set_filesystem system variable controls the interpretation of the filename.

    The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

    SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE; the syntax for the export_options part of the statement consists of the same FIELDS and LINES clauses that are used with the LOAD DATA INFILE statement. See Section 13.2.5, “LOAD DATA INFILE Syntax”.

    FIELDS ESCAPED BY controls how to write special characters. If the FIELDS ESCAPED BY character is not empty, it is used as a prefix that precedes following characters on output:


          The FIELDS ESCAPED BY character

          The FIELDS [OPTIONALLY] ENCLOSED BY character

          The first character of the FIELDS TERMINATED BY and LINES TERMINATED BY values

          ASCII NUL (the zero-valued byte; what is actually written following the escape character is ASCII ‘0’, not a zero-valued byte)

    The FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters must be escaped so that you can read the file back in reliably. ASCII NUL is escaped to make it easier to view with some pagers.

    The resulting file does not have to conform to SQL syntax, so nothing else need be escaped.

    If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

    Here is an example that produces a file in the comma-separated values (CSV) format used by many programs:

    SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
      FROM test_table;

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now