How to use a variable value in a query

Question

As shown in the code below I want to export my tables to the location in 'secure_file_priv', as it is I just manually copy and paste it but I'd like to be able to just use it's value.

USE Library;


show VARIABLES LIKE 'secure_file_priv';

set mylocation := (VARIABLES LIKE 'secure_file_priv');

SELECT 'Id', 'Name', 'Birthplace', 'Birthday', 'Gender'
UNION ALL
SELECT Id, Name, Birthplace, Birthday, Gender
    FROM Author
    INTO OUTFILE '/var/lib/mysql-files/author.csv'
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY ''
    LINES TERMINATED BY '\n';

Instead of manually writing '/var/lib/mysql-files/author.csv' I'd like to be able to use the 'secure_file_priv' variable's value.

Possible duplicate of can I use a variable to specify OUTFILE in mysql — Rhombius Grombius
– Rhombius Grombius, Commented Jan 14, 2019 at 18:42

Ariful Islam · Accepted Answer · 2019-01-14 18:29:13Z

0

SET @mylocation = (SELECT @@secure_file_priv);

SELECT 'Id', 'Name', 'Birthplace', 'Birthday', 'Gender'
UNION ALL
SELECT Id, Name, Birthplace, Birthday, Gender
    FROM Author
    INTO OUTFILE @mylocation
    FIELDS TERMINATED BY ',' 
    OPTIONALLY ENCLOSED BY ''
    LINES TERMINATED BY '\n';

answered Jan 14, 2019 at 18:29

Ariful Islam

7,6837 gold badges38 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use a variable value in a query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related