MENU


Internal Commands

SELECT … INTO @var1, @var2…
This stores the column values of a single row into variables.
SELECT * FROM tbl  LIMIT 1 INTO @a , @b;

SELECT … INTO OUTFILE filename …
This stores multiple rows to a file using a specified format.
SELECT * FROM tbl INTO OUTFILE 'a.txt'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '^'
LINES TERMINATED BY '\r\n';
1,"abc"
2,"def"
3,"ghi"
4,"jkl"
5,"mno"
6,"pqr"
9,"yz"
10,"^""
Special characters should be escaped so that the file can be read back in reliably.

SELECT … INTO DUMPFILE filename
This stores a single row to a file without any formatting.
SELECT * FROM tbl LIMIT 1 INTO DUMPFILE 'aa.txt';
1abc

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE tbl_name
[CHARACTER SET charset_name]
[{FIELDS | COLUMNS}
[TERMINATED BY 'string']
[[OPTIONALLY] ENCLOSED BY 'char']
[ESCAPED BY 'char']
]
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
This reads rows from a text file into a table at a very high speed.
LOW_PRIORITY causes the loading to be delayed until no other clients are reading the table. CONCURRENT allows data to be read from the table during the loading.
If LOCAL is specified, the file is read on the client host and sent to the server.
REPLACE replaces existing rows with the same value for a primary key or unique index. IGNORE ignores the additions of such rows. Without both REPLACE and IGNORE, the rest of the text file is ignored if LOCAL is not specified.
IGNORE number LINE ignores number lines at the start of the file.
If the field starts with the ENCLOSED BY character, instances of that character are recognized as terminating a field value only if followed by the field or line TERMINATED BY sequence. If the ENCLOSED BY character is “”,
“Hello “”WORLD”” !”
is interpreted as
Hello “WORLD”!
LOAD DATA INFILE 'data.txt' INTO TABLE tbl;
LOAD DATA INFILE 'data.txt'
INTO TABLE tbl(column1, @var) SET column2 = @var*100;

LOAD XML [LOW_PRIORITY | CONCURRENT]
[LOCAL] INFILE 'file_name'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[PARTITION (partition_name,...)]
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number {LINES | ROWS}]
[(column_or_user_var,...)]
[SET col_name = expr,...]
This reads data from an XML file into a table.