How to use variables in file paths in MySQL statements? I have now a problem that I have to set paths through variables in queries like


load data infile  '/my/file/path/to/file.txt'
into  table  database1.table2
fields terminated by ''
lines terminated by '\r\n'
( id, name_en, name_de, value_en, value_de);


The part "/my/file/path/to/" should be set with a variable, but the only way I have found is to use prepared statements, which is not possible with this type of queries.

Is there any other option to keep the path in a variable?
You cannot keep the path in a variable because variables are interpreted by the server, and path names are interpreted by the mysql client.
Related question:

https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error

However, there are a couple of different work-arounds that will allow you to accomplish what you are trying to do, and they are explained in the article.

I'd like to add one more idea: 

If you call the mysql script from a Bash script, copy your file into a temp file, so the script always calls the same file name, like "work_file.csv".  

After the mysql script runs, you can delete "work_file.csv".

Your original file is kept intact, and the data from that file is now uploaded.