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.
