MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
Pre-General Availability Draft: 2017-07-17
The SELECT ...
INTO form of SELECT
enables a query result to be stored in variables or written to a
file:
SELECT ... INTOselects column values and stores them into variables.var_listSELECT ... INTO OUTFILEwrites the selected rows to a file. Column and line terminators can be specified to produce a specific output format.SELECT ... INTO DUMPFILEwrites a single row to a file without any formatting.
The SELECT syntax description
(see Section 13.2.10, “SELECT Syntax”) shows the INTO
clause near the end of the statement. It is also possible to use
INTO immediately following the
select_expr list.
An INTO clause should not be used in a nested
SELECT because such a
SELECT must return its result to
the outer context.
The INTO clause can name a list of one or
more variables, which can be user-defined variables, stored
procedure or function parameters, or stored program local
variables. (Within a prepared SELECT ... INTO
OUTFILE statement, only user-defined variables are
permitted;see Section 13.6.4.2, “Local Variable Scope and Resolution”.)
The selected values are assigned to the variables. The number of
variables must match the number of columns. The query should
return a single row. If the query returns no rows, a warning
with error code 1329 occurs (No data), and
the variable values remain unchanged. If the query returns
multiple rows, error 1172 occurs (Result consisted of
more than one row). If it is possible that the
statement may retrieve multiple rows, you can use LIMIT
1 to limit the result set to a single row.
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;User variable names are not case sensitive. See Section 9.4, “User-Defined Variables”.
The SELECT ... INTO
OUTFILE ' form of
file_name'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. The
character_set_filesystem system
variable controls the interpretation of the file name.
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 other host than
the server host, you normally cannot use
SELECT ... INTO
OUTFILE since there is no way to write a path to the
file relative to the server host's file system.
However, if the MySQL client software is installed on the remote
machine, you can instead use a client command such as
mysql -e "SELECT ..." >
to generate the
file on the client host.
file_name
It is also possible to create the resulting file on a different host other than the server host, if the location of the file on the remote host can be accessed using a network-mapped path on the server's file system. In this case, the presence of mysql (or some other MySQL client program) is not required on the target host.
SELECT ... INTO
OUTFILE is the complement of
LOAD DATA
INFILE. Column values are written converted to the
character set specified in the CHARACTER SET
clause. If no such clause is present, values are dumped using
the binary character set. In effect, there is
no character set conversion. If a result set contains columns in
several character sets, the output data file will as well and
you may not be able to reload the file correctly.
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.7, “LOAD DATA INFILE Syntax”, for
information about the FIELDS and
LINES clauses, including their default values
and permissible values.
FIELDS ESCAPED BY controls how to write
special characters. If the FIELDS ESCAPED BY
character is not empty, it is used when necessary to avoid
ambiguity as a prefix that precedes following characters on
output:
The
FIELDS ESCAPED BYcharacterThe
FIELDS [OPTIONALLY] ENCLOSED BYcharacterThe first character of the
FIELDS TERMINATED BYandLINES TERMINATED BYvaluesASCII
NUL(the zero-valued byte; what is actually written following the escape character is ASCII0, 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'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
If you use INTO DUMPFILE instead of
INTO OUTFILE, MySQL writes only one row into
the file, without any column or line termination and without
performing any escape processing. This is useful if you want to
store a BLOB value in a file.
Note
Any file created by INTO OUTFILE or
INTO DUMPFILE is writable by all users on
the server host. The reason for this is that the MySQL server
cannot create a file that is owned by anyone other than the
user under whose account it is running. (You should
never run mysqld as
root for this and other reasons.) The file
thus must be world-writable so that you can manipulate its
contents.
If the secure_file_priv
system variable is set to a nonempty directory name, the file
to be written must be located in that directory.
In the context of
SELECT ...
INTO statements that occur as part of events executed
by the Event Scheduler, diagnostics messages (not only errors,
but also warnings) are written to the error log, and, on
Windows, to the application event log. For additional
information, see Section 23.4.5, “Event Scheduler Status”.
select * from control_pagos
into outfile 'c:\\data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
para aplicar este procedimiento el usuario debe de tener privilegios de GRANT / FILE.
Este ejemplo le agregamos los encabezados (with headers)
select 'idPago','fecha','lead','idAlumno','idTipoPago','idGpo',
'idTaller','idDocente','pagoImporte','NoFactura','facturaImporte',
'mes','formaPago','observaciones' union all
(select id_control_pagos, fecha, lead, id_alumno, id_concepto_pago, id_Gpo,id_Taller,
id_docente, Pagoimporte, NoFactura, FacturaImporte, Mensualidad_No, FormaPago,
Observaciones from control_pagos
into outfile 'c:\\data.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n');
SELECT id, data INTO x, y FROM test.t1 LIMIT 1;
instead of
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
-- create procedure to export entire table to CSV with header for Excel pivot tables
-- - input: schema and table for export, file path for CSV file
--------------------------------------------------------------------------------
delimiter $$
drop procedure if exists test.export_csv_proc
$$
create procedure test.export_csv_proc(IN vSchema varchar(200), IN vTable varchar(200), IN vFilePath varchar(2000))
begin
--------------------------------------------------------------------------------
-- declare variables
declare vColumn varchar(200); -- field name
declare vDataType char(20); -- field data type
declare vOrdinalPosition smallint unsigned default 0; -- counter based on information_schema data
declare vMaxFields smallint unsigned default 0; -- number of fields in export table
declare vHeaderSQL text default ''; -- hold text for header export SQL
declare vDataSQL text default ''; -- hold text for data export SQL
declare vOutputSQL text default ''; -- combine all SQL into final code for exporting table
--------------------------------------------------------------------------------
-- create cursor to get list of columns for export
declare cur_header cursor for
select distinct
c.column_name,
c.data_type,
c.ordinal_position
from information_schema.columns as c
where c.table_schema = vSchema
and c.table_name = vTable
order by
c.ordinal_position
;
--------------------------------------------------------------------------------
-- how many columns are in the export table
set @pSQL = concat("
select @vFields:= max(c.ordinal_position)
from information_schema.columns as c
where c.table_schema = '", vSchema,
"' and c.table_name = '", vTable, "'"
);
prepare stmt from @pSQL;
execute stmt;
deallocate prepare stmt;
set vMaxFields = @vFields;
--------------------------------------------------------------------------------
-- create SQL export text
-- - add double quote enclosure only for character fields
-- - format date fields for Excel import (DD/MM/YYYY)
-- - add comma after all but last field
open cur_header;
repeat
fetch cur_header into vColumn, vDataType, vOrdinalPosition;
set vHeaderSQL = concat(vHeaderSQL, '\'"\',', '\'', vColumn, '\'', ',\'"\'');
case
when vDataType in ('char','varchar') then set vDataSQL = concat(vDataSQL, '\'"\',ifnull(`', vColumn, '`,\'\'),\'"\'');
when vDataType in ('date','datetime','timestamp') then set vDataSQL = concat(vDataSQL, 'ifnull(date_format(`', vColumn, '`,\'%d/%m/%Y %T\'),\'\')');
else set vDataSQL = concat(vDataSQL, 'ifnull(`', vColumn, '`,\'\')');
end case;
if vOrdinalPosition < vMaxFields
then set vHeaderSQL = concat(vHeaderSQL, ',', '",", '),
vDataSQL = concat(vDataSQL, ',', '",", ')
;
end if;
until vOrdinalPosition = vMaxFields
end repeat;
close cur_header;
--------------------------------------------------------------------------------
-- create CSV SQL text
set vOutputSQL = concat(
'select concat (',
vHeaderSQL,
') UNION select concat (',
vDataSQL,
') from ', vSchema, '.', vTable,
' into outfile "', vFilePath,
'" lines terminated by "\\r\\n"'
);
--------------------------------------------------------------------------------
-- execute created statement
set @pSQL = vOutputSQL;
prepare stmt from @pSQL;
execute stmt;
deallocate prepare stmt;
--------------------------------------------------------------------------------
end
$$
delimiter ;
--------------------------------------------------------------------------------
--call test.export_csv_proc('test_schema', 'test_table', 'c:/temp/test.csv');
First, if you use backslash, you must double them, as you would in writing a literal string in C or C-related languages:
SELECT whatever INTO OUTFILE 'D:\\Temp\somefile'...
Because MySQL doesn't overwrite that file, you may be persuaded to change the filename each time around, by using a variable (perhaps a timestamp) for the file name:
SET @filepath = CONCAT('D:\\Temp\somefile\\TheFile_', @variablepart);
SELECT whatever INTO OUTFILE @filepath...
However, this isn't legal syntax because a variable isn't allowed at that location. So instead you might move to preparing the SELECT .. INTO statement. That works, but then the filepath string goes through two steps of unescaping, so you'll need to double escape the path separators (ie: four backslashes).
SET @filepath = CONCAT('D:\\\\Temp\somefile\\\\TheFile_', @variablepart);
Probably a better solution is to use a single forward slash instead, which MySQL apparently converts to the platform-appropriate separator when calling the OS API.
SET @filepath = CONCAT('D:/Temp/somefile/TheFile_', @variablepart);