'Prompt user to input a variable in MySQL

At school, I believe I work with Oracle SQL Developer when writing SQL. And in this I can type:

SELECT Book_Title, Auth_ID
FROM book
WHERE Auth_ID = '&Enter ID';

This will then display a little message box where the user can enter an ID number to see all the books written by an author with that ID number.

I want to know if there is a way to do this in MySQL. I have looked and the nearest thing I can find is setting a variable before hand, which is not quite what I'm looking for:

SET @EnterID := 2;
select Book_Title, Auth_ID
from book
where Auth_ID = @EnterID;

The above statement in MySQL will return all the books with author ID of 2, but only because I set it to that previously. I want the user to be able to enter the variable.

Thanks.

mysqlsqloraclevariablesoracle-sqldeveloper


Solution 1:[1]

Oracle has the concept of interactive queries, those that as you said you can run by adding the '&' before your variables names, that is a variable substitution, this concept doesn't exist in MySql, MySql is not interactive and requires the user to enter the values in the variables by using the keyword 'SET' and @ (instead of & like in Oracle).

So, no, you cannot do what you are looking for since this is not a client-side implementation either.

BTW, I just noticed this was asked so many years ago, amazing that this is still not added as a feature in mysql.

Solution 2:[2]

For a prompt, you must put the char ':' followed by the name of the variable

Example :

select * 
from YOUR_TABLE 
where YOUR_COLUMN = :your_var

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kram
Solution 2 hbpatrick81

Related Questions