mysql - 触发器中的动态名称表

Question

有一个触发器可以将列从一个表复制到另一个表。如何将表名分配给动态变量 @nametable？表格的名称每天都会根据当前日期更改。

USE dbo;
DROP TRIGGER IF EXISTS `update_test`;

GO

DELIMITER |

CREATE TRIGGER `update_test` AFTER INSERT ON `hello_send`

FOR EACH ROW

BEGIN

DECLARE nametable VARCHAR(128);

SET @nametable =(DATE_FORMAT(NOW(),"%d%m%Y_v"));

    INSERT INTO `dbo`.`nametable` SET
      `TIME_` =NEW.`recorded`,
      `P1` = NEW.`value1`
   ON DUPLICATE KEY UPDATE
        `TIME_` = NEW.`recorded`,
       `P1` = NEW.`value1`;

END;

出现错误 ERROR 1146: Table 'dbo.nametable' doesn't exist

Answer 1

我决定这样做作为临时解决方案:

CREATE TRIGGER `update_test` AFTER INSERT ON `hello_send`
FOR EACH ROW
BEGIN
SET @dateNameTableNOW =(DATE_FORMAT(NOW(),"%d%m%Y_v"));
SET @dateNameTableSELECT1 = '06122017_v';
SET @dateNameTableSELECT2 = '07122017_v';
IF(@dateNameTableNOW = @dateNameTableSELECT1) THEN
 INSERT INTO  dbo.06122017_v  SET TIME_=NEW.recorded, P1 = NEW.value1 ON DUPLICATE KEY UPDATE TIME_ = NEW.recorded, P1 = NEW.value1;
END IF;

IF(@dateNameTableNOW = @dateNameTableSELECT2) THEN
 INSERT INTO  dbo.07122017_v  SET TIME_=NEW.recorded, P1 = NEW.value1 ON DUPLICATE KEY UPDATE TIME_ = NEW.recorded, P1 = NEW.value1;
END IF;


END;