Clever Geek Handbook

MySQL SELECT INTO OUTFILE and user 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 this a limitation of MySQL SELECT or am I missing something here?

+2


source to share


1 answer


you cannot use a variable for the filename. like LIMIT, it must be a literal value, not a formula or variable.

for example, what is the filename to be written 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 limitation on the LIMIT value. if expressions were allowed in LIMIT clauses, you might find queries with unpredictable behavior. for example, let's say you have a table with 10 rows and column a has values ​​from 1 to 10. What is the result of this query?

SELECT *
  FROM myTable
ORDER
    BY a
 LIMIT 10 - a

the basis for this constraint is: if you can compute the limit, then you must compute the limit explicitly and then build the query with a literal value. allowing type expressions to be evaluated 10 - a

makes the query execution plan potentially more complex.

+5


source






More articles:


All Articles