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.

Dennis Y. Parygin · Accepted Answer · 2015-11-12 12:19:09Z

2

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

answered Nov 12, 2015 at 12:19

Dennis Y. Parygin

4235 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

O. Jones · Accepted Answer · 2015-11-12 11:58:08Z

0

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.

answered Nov 12, 2015 at 11:58

O. Jones

110k17 gold badges135 silver badges188 bronze badges

2 Comments

Silwerclaw Over a year ago

Prepared statements forbidden in functions

O. Jones Over a year ago

Oh, true enough. If you must do this, you need a stored PROCEDURE. dba.stackexchange.com/questions/48262/…

Collectives™ on Stack Overflow

Dynamic variable names in MySQL

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related