mysql存储过程从表中删除数据

Question

我正在尝试在 MySQL 中编写一个存储过程作为清理器，它将用于从表中删除数据(所有表的一个存储过程)

    CREATE PROCEDURE `cleaner`(
     IN table_name_in varchar(255),
     IN field_name_in varchar(255),
     IN day_in int
    )
    BEGIN
    DECLARE FoundCount INT;
    SELECT COUNT(1) INTO FoundCount
    FROM table_name_in where STR_TO_DATE(field_name_in, "%Y-%m-%d") < STR_TO_DATE(NOW() - INTERVAL day_in DAY, "%Y-%m-%d");
    IF FoundCount = 1 THEN
       SET SQL_SAFE_UPDATES = 0;
       DELETE from table_name_in where STR_TO_DATE(field_name_in, "%Y-%m-%d") < STR_TO_DATE(NOW()- INTERVAL day_in DAY, "%Y-%m-%d");
        SET SQL_SAFE_UPDATES = 1;
        select  "true" as isDeleted , "true" as isValuePresent ;
    else
        select  "false" as isDeleted , "false" as isValuePresent ;
   END IF;
   END

我称之为

   call cleaner('employee','created_on',30)

这意味着使用字段created_on从员工表中删除了30天前的所有记录

但它给了我一条错误消息

 Error Code: 1146. Table 'table_name_in' doesn't exist

其中存在员工表，但不将员工作为参数

Answer 1

这对我有用

          CREATE PROCEDURE `cleaner`(
           IN table_name_in varchar(255),
           IN field_name_in varchar(255),
           IN day_in int
          )
          BEGIN
            SET SQL_SAFE_UPDATES = 0;
            SET @sql = CONCAT('DELETE FROM ',table_name_in,' WHERE STR_TO_DATE(',field_name_in,', "%Y-%m-%d") < STR_TO_DATE(NOW() - INTERVAL ',day_in,' DAY, "%Y-%m-%d")');
            PREPARE s1 from @sql;
            EXECUTE s1;
            SET SQL_SAFE_UPDATES = 1;
         END

来源:Mysql stored procedure don't take table name as parameter