在触发器中提取动态表名

Question

TB_SITES_21

我在制造一个触发器

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT ON `tb_tickets` 
       FOR EACH ROW UPDATE tb_sites_21 
       SET color_status = NEW.status 
       WHERE site_id = NEW.site_id;

它是工作良好的唯一东西我需要的是tb_sites_21，我想要这个21是从tb_tickets的program_id中挑选出来的，为它做了新的条目。

类似这样的事情：

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT ON `tb_tickets` 
       FOR EACH ROW UPDATE tb_sites_NEW.program_id 
       SET color_status = NEW.status 
       WHERE site_id = NEW.site_id;

或

 CREATE DEFINER=root@localhost 
         TRIGGER color_changed AFTER INSERT ON tb_tickets FOR EACH ROW 
         SET @table_name := (SELECT CONCAT("tb_sites_" , program_id) 
         FROM tb_tickets 
         WHERE ticket_id = NEW.ticket_id); 
         UPDATE table_name set 
         color_status = NEW.status WHERE site_id = NEW.site_id

我如何实现这一点？

Answer 1

按program_id划分表是一个棘手的设计：选择*FROM sales+@yymm

您不能在触发器内部动态SQL/Prepared语句，因此唯一可行的选项是使用if语句，但您需要事先了解表名。

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT
BEGIN
 IF NEW.program_id = 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ELSEIF NEW.program_id = 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ELSEIF ... THEN
   -- ...
 ELSE
   -- ...
 END
END;

Answer 2

表名不能使用变量。无论您走到哪里，这都是关于SQL的一个事实，查询以及查询中引用的所有表和列都必须在解析查询时固定。对于触发器，这意味着表名必须在创建触发器时固定。

另一种方法是使用带有PREPARE和Execute的动态SQL。这允许您从字符串表达式构建查询，并使MySQL在运行时解析并执行它。

它可能看起来像这样：

SELECT CONCAT("tb_sites_" , program_id) INTO @table_name
     FROM tb_tickets WHERE ticket_id = NEW.ticket_id; 
SET @sql = CONCAT('UPDATE ', @table_name, 
    ' SET color_status = ? WHERE site_id = ?');
SET @color_status = NEW.status, @site_id = NEW.site_id;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @color_status, @site_id;
DEALLOCATE PREPARE stmt;

https://dev.mysql.com/doc/refman/8.0/en/sql-syntax-prepared-statements.html表示：

已准备语句的SQL语法可以在存储过程中使用，但不能在存储函数或触发器中使用。

来自P.Salmon和Lukasz Szozda的其他评论和回答一直试图解释这一点，但你似乎没有在听。

有三种选择：

1.对每个案例进行硬编码（卢卡斯·索兹达的回答）

我会使用case语句，而不是一个IF/then/else IF块链，但逻辑是相同的。你需要

CREATE DEFINER=`root`@`localhost` 
       TRIGGER `color_changed` 
       AFTER INSERT
BEGIN
 CASE NEW.program_id
 WHEN 1 THEN
   UPDATE tb_sites_1 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 2 THEN
   UPDATE tb_sites_2 
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 WHEN 3 THEN
   UPDATE tb_sites_3
   SET color_status = NEW.status 
   WHERE site_id = NEW.site_id;
 ...etc...
 END
END

这有一个缺点，即如果有许多表要更新，它可能会变得很长，并且每次添加新表时都需要重新定义触发器。

2.使用存储过程而不是触发器（Rick James的答案）

这不使用触发器。相反，它运行两个语句，一个INSERT后跟一个相应站点表的UPDATE。您可以使用Prepare/Execute语法在存储过程中完成此操作。

CREATE PROCEDURE InsertTicket(
  IN in_ticket_id INT,
  IN in_program_id INT, 
  IN in_color_status VARCHAR(10),
  IN in_site_id INT)
BEGIN
  DECLARE table_name VARCHAR(64);

  -- First insert into the tickets table
  INSERT INTO tb_tickets 
    SET ticket_id = in_ticket_id,
        program_id = in_program_id, 
        color_status = in_color_status,
        site_id = in_site_id;

  -- Second, do a dynamic update into the respective site table
  SET table_name = CONCAT('tb_sites_', in_program_id);
  SET @sql = CONCAT('UPDATE ', table_name, 
    ' SET color_status = ? WHERE site_id = ?');
  SET @color_status = in_color_status, @site_id = in_site_id;
  PREPARE stmt FROM @sql;
  EXECUTE stmt USING @color_status, @site_id;
  DEALLOCATE PREPARE stmt;
END

您还可以在任何应用程序编码语言中执行一对等价语句。您不需要执行存储过程。

您可以使用一个事务来包装这两个语句，以便它们同时提交，或者都回滚。

此替代方案将要求您更改应用程序插入票据的方式。因此必须更改应用程序代码。如果您没有更改应用程序代码的权限，这不是一个好的替代方案。

3.将sites表重构为一个表（Paul Spiegel的评论）

有几个人这样建议，但你说你没有更改桌子设计的权限。这很不幸，但很常见。更改应用程序中的表设计是昂贵的，因为可能有大量的应用程序代码取决于当前的表设计。需要重构所有代码以支持对表的更改。