How to include data from a variable in an interactive MySQL command?

Question

I'm trying to write a shell script that dumps all tables with a user specified table prefix. The following is part of the script:

variable=vardata
mysql -u user -p -N information_schema -e "SELECT table_name FROM tables WHERE table_schema = 'dbname' AND table_name LIKE 'test_$variable%'"

Accessing the variable can't work like this, because of the hard quotes in the SQL statement, but how else can I do it? I tried MySQL variables @var, but that also doesn't seem to work with the LIKE operator.

Possible duplicate of [Bash: Variable in single quote](http://stackoverflow.com/questions/8084389/bash-variable-in-single-quote) — neuhaus, Jan 28 '16 at 14:45

vmachan · Accepted Answer · 2016-01-28T14:44:02.887

0

You can try something like

variable=vardata
sql_stmt="SELECT table_name FROM tables WHERE table_schema = 'dbname' AND table_name LIKE 'test_"$variable"%'"
mysql -u user -p -N information_schema -e "$sql_stmt"

Hope this works

UPDATED: Per comment from J.Doe

edited Jan 28 '16 at 14:44

answered Jan 28 '16 at 14:37

vmachan

1,672
1
10
10

mysql -u user -p -N information_schema -e "$sql_stmt" <- This works. Thanks a lot! – J. Doe Jan 28 '16 at 14:43

How to include data from a variable in an interactive MySQL command?

1 Answers1