How to Execute SQL Queries with Dynamically Generated Table Names in MySQL?

Using Dynamic Table Names in MySQL Queries

In MySQL, executing SQL queries with dynamically generated table names can lead to errors. To overcome this, we can utilize prepared statements, which offer a secure and efficient way to handle such scenarios.

In the provided code:

SET @id := '47';
SET @table := @id+'_2013_2014_voucher';
SELECT * FROM @table;
Delete FROM @table where>

The error occurs because MySQL doesn't recognize "@table" as a valid table name directly in the query.

To resolve this, we can use a prepared statement to dynamically construct the query based on the table name. The following code demonstrates how to achieve this:

SET @id := '47';
SET @table := concat(@id,'_2013_2014_voucher');
set @qry1:= concat('select * from ',@table);
prepare stmt from @qry1 ;
execute stmt ;

In MySQL, the "concat" function is used for string concatenation. We use it to construct the table name within the prepared statement "@qry1".

Once the prepared statement is created, we can then execute it using the "execute stmt" command. This approach enables us to dynamically generate table names and execute queries without encountering syntax errors.

The above is the detailed content of How to Execute SQL Queries with Dynamically Generated Table Names in MySQL?. For more information, please follow other related articles on the PHP Chinese website!