How to select from MySQL where Table name is Variable

Question

I have a case where getting the table name should be from a set variable like:

SET @ID_1 = (SELECT ID FROM `slider` LIMIT 0,1);
SET @Cat = (SELECT Category FROM `slider` LIMIT 0,1);
select * from @Cat where ID = @ID_1

but doing that way MySQL outputs an error, so could someone show me how I can achieve that, because these are my baby steps in MySQL.

Svelte

Answer 1

You'd have to do this with a prepared statement. Something like:

SET @s = CONCAT('select * from ', @Cat, ' where ID = ', @ID_1); 

PREPARE stmt1 FROM @s; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1; 

Rachellrachelle

One more thing it show resul only when i remove DEALLOCATE PREPARE stm1; – Svelte

Brilliant solution. Helped me to solve the issue I'm having, thanks. – Wherefore

What does the DEALLOCATE PREPARE do? – Everest

@FrozenFlame, release the stmt1. If you do not release it, may you encounter the max limit of statements, enforced by max_prepared_stmt_count variable system, how you can read here. – Elver

is this still valid syntax in 2018?? – Rebatement

I think so... Don't forget, this only works for procedures, not functions. – Peanut

I know this is a old answer but there is one big problem with this is that it opens you up to SQL injections.. EXECUTE <> USING <> syntax is safe for SQL injections.. So you need to execute PREPARE table_exists FROM 'SELECT TABLE_NAME FROM information_schema.TABLE WHERE TABLE_NAME = ?'; EXECUTE table_exists USING @Cat first to check and use that to validate if there is not SQL injection vector in @Cat – Rejoin

What if I also need to store the result into a variable? SELECT x INTO @x FROM @y.... Is it possible with with CONCAT? – Manner