mysql as 拼接变量_MySQL-CONCAT-有什么方法可以连接字符串并将其用作变量？

Low hours on mysql but starting to probe the edges. Stackoverflow a great resource - thanks everyone.

Experimenting with Concat I fell over this issue. I know there will be a way but I just can't figure it out.

My example:

set @strokes_hole_10 = 6;

set @x = 10;

set @strokes = concat('strokes_hole_',@x);

select @strokes;

I looking for @strokes to be the variable value 6 rather than the variable value "strokes_hole_10".

I find lots of information on using concat, mostly straight forward examples and I know concat is resulting in a string. I just can't figure out how to make a dynamic label work.

Am I looking at prepared statements as the way to proceed?

Thanks in advance for any help.

解决方案

If you have variable column name, you will need to use Dynamic SQL:

set @strokes_hole_10 = 6;

set @x = 10;

set @strokes = concat('@strokes_hole_',@x); -- add @ to variable string

-- generate the query string

set @query_str = CONCAT('SELECT ', @strokes);

-- prepare statement using the query string

Prepare stmt From @query_str;

-- executes the prepared statement

Execute stmt;

-- clean up after execution

Deallocate Prepare stmt;

Result

| @strokes_hole_10 |

| ---------------- |

| 6 |