Dynamic variable names in MySQL

Question

I need to create some stored function in MySQL that will work exactly like getter/setter and return or set for me variable by it's name.

For example get_variable('my_special') will return me value of @my_special variable, set_variable('my_special', 23) will set @my_special := 23.

Question is: how can I, having variable name as a string set or get it in MySQL? Smth like SET @{'my_special'} = 23 or $$var = 23 (as in PHP)

Update: According to my task I found that it's impossible to do in mysql. The purpose I wanted this was a chain of events:

• I wanted to store query with variables as a view in DB. MySQL rejects storing view with variables but allows with using functions.
• I've decided I'll create functions which will return/set my variables. But I had about 4 variables inside query - so it's not efficient to create 4 pairs of functions to get/set variables. So I've wanted to create universal getters/setters.
• Only way to get/set variable by name is to run dynamic query which are forbidden inside functions(only in procedures which are not very comfortable to use inside select statements).
• So as a result of this question - it's impossible.

Answer 1

create procedure `eval`( in param text ) 
begin 
 set @sql = param; 
 prepare stmt from @sql; 
 execute stmt; 
 deallocate prepare stmt; 
end

Call the procedure call tests.eval('set @ABC = 120') and in the current session, you can access the variable @ABC

call tests.eval('set @ABC = 120');
select @ABC;

Source

Answer 2

If you must do this in SQL you're going to need the MySQL feature called Prepared Statements. Despite its name, this is not the same as the client-side prepare() feature offered by JDBC, mysqli, PDO, and other apis supporting client side languages.

Why? SQL doesn't allow the names of database objects (tables, columns, &c.) to be handled as bind variables.

• Silwerclaw: Prepared statements forbidden in functions
• O. Jones: Oh, true enough. If you must do this, you need a stored PROCEDURE. dba.stackexchange.com/questions/48262/…