How to Select Data from a Table with a Variable Name in MySQL?

Selecting Data from a Variable Table Name in MySQL

In MySQL, you may encounter scenarios where you need to retrieve data from a table whose name is stored in a variable. This can be tricky to achieve with SQL alone, as accessing a table by its name dynamically can lead to errors.

Problem:

Consider the following scenario:

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;

Running this query will result in an error, as MySQL cannot interpret the variable @Cat as a valid table name.

Solution:

To overcome this limitation, you can use a prepared statement:

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

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

Breakdown:

• SET @s: Concatenates the strings to form the dynamic SQL query.
• PREPARE stmt1 FROM @s: Prepares the statement stmt1 using the dynamic SQL query stored in the variable @s.
• EXECUTE stmt1: Executes the prepared statement.
• DEALLOCATE PREPARE stmt1: Deallocates the prepared statement to release resources.

This approach allows you to execute a SQL query dynamically, where the table name is determined at runtime from the values stored in the variables @Cat and @ID_1.

The above is the detailed content of How to Select Data from a Table with a Variable Name in MySQL?. For more information, please follow other related articles on the PHP Chinese website!