How to Execute Dynamic Queries in MySQL?

Executing Dynamic Queries in MySQL

Executing dynamic queries, where the SQL statement itself is constructed at runtime, is commonly performed in MSSQL using the EXEC stored procedure. However, MySQL lacks a direct equivalent.

Overcoming the Challenge

To execute dynamic queries in MySQL, one approach is to construct the query string as a variable and then use the PREPARE and EXECUTE statements.

Implementation

The following code demonstrates how to execute a dynamic query in MySQL:

SET @queryString = (
SELECT CONCAT('INSERT INTO user_group (`group_id`,`user_id`) VALUES ', www.vals) as res FROM (
    SELECT GROUP_CONCAT(qwe.asd SEPARATOR ',') as vals FROM ( 
           SELECT CONCAT('(59,', user_id, ')') as asd FROM access WHERE residency = 9 
    ) as qwe 
) as www
);

PREPARE stmt FROM @queryString;
EXECUTE stmt;
DEALLOCATE PREPARE stmt; 
SET @asd = NULL;

1. Construct the query string as a variable (@queryString) using dynamic string concatenation and subqueries.
2. Prepare the statement using the PREPARE statement with the constructed query string as input.
3. Execute the prepared statement using the EXECUTE statement.
4. Deallocate the prepared statement using the DEALLOCATE PREPARE statement.

By following this approach, MySQL can execute dynamic queries even though the query itself is not known in advance.

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