MySQL Prepared Statements with nested query that includes variables

Question

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?

Bill Karwin · Accepted Answer · 2022-09-12 16:44:10Z

1

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;

answered Sep 12, 2022 at 16:44

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL Prepared Statements with nested query that includes variables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related