How to Construct Dynamic Queries with Variable Table Names in MySQL?

Constructing Dynamic Queries with Variable Table Names in MySQL

When executing MySQL queries that require variable table names, users may encounter errors such as the one reported in the question. This article aims to explore the usage of prepared statements and provide a solution to this specific issue.

Understanding the Error

The error mentioned, "[Err] 1064 - You have an error in your SQL syntax," indicates that the MySQL server is unable to interpret the query syntax correctly. The use of the variable "@table" directly in the query statement could be the culprit.

Utilizing Prepared Statements

Prepared statements offer a reliable approach for handling dynamic queries involving variable table names. They allow you to define a query template and dynamically substitute values later on. In this case, we can define a query template using the CONCAT function to build the variable table name.

Consider the following steps:

SET @id := '47';
SET @table := CONCAT(@id, '_2013_2014_voucher');
SET @qry1 := CONCAT('SELECT * FROM ', @table);

This sets up the dynamic query template in the variable @qry1.

Next, prepare the statement using the PREPARE command:

PREPARE stmt FROM @qry1;

Now, you can execute the prepared statement multiple times, substituting different table names as needed:

EXECUTE stmt;

Handling Delete Queries

The same approach can be applied to delete queries. Simply construct a dynamic query template using CONCAT and prepare it as shown above:

SET @qry2 := CONCAT('DELETE FROM ', @table, ' WHERE id = @id');

Conclusion

Using prepared statements with dynamic table names in MySQL allows you to execute queries with variable table names efficiently and elegantly. Remember to utilize the CONCAT function for string concatenation and to embrace the power of prepared statements for enhanced query flexibility and performance.

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