Search code examples
mysqlsqlinto-outfile

MySQL SELECT INTO OUTFILE and User Defined Variables

Any idea why this fails in MySQL 5.1:

SET @LOAD_TIME = UNIX_TIMESTAMP();
SET @OUTFILE = CONCAT(CONCAT('/tmp/outfile_', @LOAD_TIME), '.sql');

SELECT *
FROM `tableA`
INTO OUTFILE @OUTFILE;

Is it a limitation of MySQL's SELECT or am I missing something here?

Solution

  • you can't use a variable for the filename. like LIMIT, it must be a literal value, not a formula or a variable.

    for example, what filename is the file going to be written to here?

    SET @LOAD_TIME = UNIX_TIMESTAMP();
SET @OUTFILE = CONCAT(CONCAT('/tmp/outfile_', @LOAD_TIME), '.sql');

SELECT @OUTFILE = columnA
, columnB
FROM `tableA`
INTO OUTFILE @OUTFILE;

    this is a similar restriction to the one on LIMIT. if expressions were allowed in LIMIT clauses, you could come up with queries that have unpredictable behavior. for example, say you have a table with 10 rows and column a has the values of 1 through 10. what's the output of this query?

    SELECT *
  FROM myTable
ORDER
    BY a
 LIMIT 10 - a

    the basis for this limitation is: if you can calculate the limit, then you should explicitly calculate the limit and then build the query with the literal value. allowing expressions like 10 - a to be evaluated makes the query execution plan potentially MUCH more complicated.