CODEWITHSUNDEEP
mysql
sFishman
sFishman

Reputation: 133

MySQL Prepared Statements with nested query that includes variables

To drop a bunch of tables in our db, the following works (thank you @Devart from here for help getting this far)


SET @tables = NULL;
SET @db='tech';
SET @days = 365;

SELECT GROUP_CONCAT(@db, '.`',table_name, '`') INTO @tables FROM
(select t.table_name, ifnull(t.UPDATE_TIME, t.CREATE_TIME) update_time from
 information_schema.tables t) TT;
SET @tables = CONCAT('DROP TABLE IF EXISTS ', @tables);
SELECT @tables;
PREPARE stmt1 FROM @tables;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

The problem is that I want to add a where clause with variables in the nested select statement that looks like this: CONCAT(WHERE t.table_schema=\'', @db, '\' AND IFNULL(t.UPDATE_TIME, t.CREATE_TIME) < CURDATE()-INTERVAL ', @days, 'DAY AND (TABLE_NAME NOT LIKE \'%_delete\' OR TABLE_NAME RLIKE \'[0-9].*\'));

The only way I know of to intermittently have variables in a query is to use CONCAT and then PREPARE and EXECUTE that query. The problem is I can't save the result that has multiple columns in a variable like this

SET @schema_info = CONCAT('select t.table_name, ifnull(t.UPDATE_TIME, t.CREATE_TIME) update_time 
from information_schema.tables t 
WHERE t.table_schema=\'', @db, '\' AND IFNULL(t.UPDATE_TIME, t.CREATE_TIME) < CURDATE()-INTERVAL ', @days, 'DAY AND (TABLE_NAME NOT LIKE \'%_delete\' OR TABLE_NAME RLIKE \'[0-9].*\')');
PREPARE stmt FROM @schema_info;
SET @result = EXECUTE stmt;

SELECT GROUP_CONCAT(@db, '.`',table_name, '`') INTO @tables FROM
(@result);

and I can't nest the execute statement like this

SET @schema_info = CONCAT('select t.table_name, ifnull(t.UPDATE_TIME, t.CREATE_TIME) update_time 
from information_schema.tables t
WHERE t.table_schema=\'', @db, '\' AND IFNULL(t.UPDATE_TIME, t.CREATE_TIME) < CURDATE()-INTERVAL ', @days, 'DAY AND (TABLE_NAME NOT LIKE \'%_delete\' OR TABLE_NAME RLIKE \'[0-9].*\')');
PREPARE stmt FROM @schema_info;

SELECT GROUP_CONCAT(@db, '.`',table_name, '`') INTO @tables FROM
(EXECUTE stmt);

Is there any other way to do this without using a separate stored procedure or function?

Upvotes: 1

Views: 408

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562671

You don't need to do this as a prepared statement. You can use variables in your SQL expression without concat-prepare-execute. They act like a single scalar value, so there's no risk of SQL injection.

SELECT GROUP_CONCAT(@db, '.`',table_name, '`') INTO @tables 
FROM (
  SELECT t.table_name, IFNULL(t.UPDATE_TIME, t.CREATE_TIME) update_time 
  FROM information_schema.tables t
  WHERE t.table_schema = @db
    AND IFNULL(t.UPDATE_TIME, t.CREATE_TIME) < CURDATE()-INTERVAL @days DAY
    AND (TABLE_NAME NOT LIKE '%_delete' OR TABLE_NAME RLIKE '[0-9].*')
) TT;

Upvotes: 1

Related Questions