Performing Interactive queries in MySql (mainly from the GUI)

Question

I mainly use the MySQL GUI tools. This allows me to easily see the results in a table as well as to quick edits and bookmark frequently run queries. This suits my needs far better than the command line.

I remember when I used to do this on Oracle DBs years ago I could put variables in the query itself, so that when running the query I got prompted for the variable.

e.g.

select email from users where login = [VAR]

And when you run the query the system prompts you for VAR and you can type in john_smith14 and it executes the query. This is really useful for adhoc queries which you run a lot.

Yes I know using shell scripts and the command line this could be done more easily, but for several reasons aside from this, shell scripts are not a good solution for me.

Answer 1

I'm not sure if there's a way to get the GUI tools to prompt you for a value, but you can certainly use variables in MySQL.

SET @myVar='john_smith14';
SELECT email FROM users WHERE login = @myVar;

That might even suit you better, since you don't have to keep typing in the variable value each time..?

Answer 2

Ok, a different solution, since it appears Bill is right (read the comments on my other answer).

In the Params tab in the bottom right, you can right click the "Local Params" folder and add a new parameter. Give it a name, eg: "myTest". Initially it is given a value of NULL. Double click on NULL and type in a new value.

Now you can access it in your query like this:

SELECT email FROM users WHERE login = :myTest;

To make this persist between sessions (opening and closing the query browser), just make it a global parameter instead of a local parameter. This works even if you restart the MySQL server.

Answer 3

Using prepared statements might be useful for you in this case.

PREPARE query1 FROM select email from users where login = ?

then execute it with your variable:

SET @a = 'john';
EXECUTE query1 USING @a;

This statement will be there during your whole session, and dropped when you disconnect.

This might seem like much overhead, but is useful when using the same query over and over again, with slightly different values.

http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html