고기맛을 알아버린 스님

SELECT
   [ALL | DISTINCT | DISTINCTROW ]
     [HIGH_PRIORITY]
     [STRAIGHT_JOIN]
     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
     [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
   select_expr, ...
   [INTO OUTFILE 'file_name' export_options
     | INTO DUMPFILE 'file_name']
   [FROM table_references
     [WHERE where_definition]
     [GROUP BY {col_name | expr | position}
       [ASC | DESC], ... [WITH ROLLUP]]
     [HAVING where_definition]
     [ORDER BY {col_name | expr | position}
       [ASC | DESC] , ...]
     [LIMIT {[offset,] row_count | row_count OFFSET offset}]
     [PROCEDURE procedure_name(argument_list)]
     [FOR UPDATE | LOCK IN SHARE MODE]]

...........

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. The file cannot already exist, which among other things prevents files such as `/etc/passwd' and database tables from being destroyed.

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

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 14.1.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 to prefix the 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 0 (what is actually written following the escape character is ASCII `0', not a zero-valued byte)

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.

The reason for the above is that you must escape any FIELDS TERMINATED BY, ENCLOSED BY, ESCAPED BY, or LINES TERMINATED BY characters to reliably be able to read the file back.

ASCII NUL is escaped to make it easier to view with some pagers. The resulting file doesn't have to conform to SQL syntax, so nothing else need be escaped. Here is an example that produces a file in the comma-separated values format used by many programs:

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

ex)

mysql> select * into outfile 'dump.sql' from test_tbl;

File_priv => 'Y' .... check !!!

or

$ mysql -uxxx -p db_name -e "select * from test_tbl" > dump.sql

http://mysql.com