mysql - 多行插入时触发器不工作/触发

Question

数据库中有两个表 KistStatus和 LastKistStatus .后者将保存 KistStatus 的所有“最新”值。 .

KistStatus有大约 174.000 条记录，LastKistStatus应该有大约 9.500 条记录。

如果我手动插入记录(手动执行一条一条插入)，LastKistStatus将被正确填写。但是如果我执行sql导出文件，触发器似乎坏了。

触发器:

DROP TRIGGER IF EXISTS `KistStatusTrigger`;
DELIMITER //
CREATE TRIGGER `KistStatusTrigger` AFTER INSERT ON `KistStatus`
 FOR EACH ROW begin
       DECLARE id_exists Boolean;
       DECLARE time_exists Timestamp;

       -- Check LastKistStatus table
       SELECT 1, `Timestamp`
         INTO @id_exists, @time_exists
       FROM LastKistStatus
       WHERE LastKistStatus.idKist = NEW.idKist;

       IF @id_exists > 0
       THEN
            IF @time_exists <= NEW.Timestamp
            THEN
                UPDATE LastKistStatus SET `Timestamp` = NEW.Timestamp, `idPartij` = NEW.idPartij, `Actie` = NEW.Actie, `idTaak` = NEW.idTaak, `idBewaarplaats` = NEW.idBewaarplaats, `Rij` = NEW.Rij, `Stapel` = NEW.Stapel, `Hoogte`= NEW.Hoogte, `KnolAantal` = NEW.KnolAantal, `Gewicht` = NEW.Gewicht, `idGebruiker` = NEW.idGebruiker, `Laatste` = NEW.Laatste WHERE `idKist` = NEW.idKist;
            END IF;
        ELSE
            INSERT INTO LastKistStatus (`idKistStatus`, `idKist`, `Timestamp`, `idPartij`, `Actie`, `idTaak`, `idBewaarplaats`, `Rij`, `Stapel`, `Hoogte`, `KnolAantal`, `Gewicht`, `idGebruiker`, `Laatste` ) VALUES (NEW.idKistStatus, NEW.idKist, NEW.Timestamp, NEW.idPartij, NEW.Actie, NEW.idTaak, NEW.idBewaarplaats, NEW.Rij, NEW.Stapel, NEW.Hoogte, NEW.KnolAantal, NEW.Gewicht, NEW.idGebruiker, NEW.Laatste);
        END IF;
END
//
DELIMITER ;

插入命令(从命令行完成) mysql -u *** -p TTTDB < KistStatus.sql

还有 KistStatus.sql有很多这样的行:

INSERT INTO `KistStatus` (`idKistStatus`, `idKist`, `Timestamp`, `idPartij`, `Actie`, `idTaak`, `idBewaarplaats`, `Rij`, `Stapel`, `Hoogte`, `KnolAantal`, `Gewicht`, `idGebruiker`, `Laatste`) VALUES
(1, 227862, '2015-09-04 14:15:29', 40, '3', 0, 260, 2060, 33980, 1, NULL, 0, 40, 1),
(21, 229522, '2015-09-04 14:15:29', 40, '3', 0, 260, 2060, 33980, 2, NULL, 0, 40, 1),
(1083, 51102, '2015-09-05 07:33:37', 80, '3', 0, 40, 440, 5060, 1, NULL, 0, 100, 1),
(1103, 51102, '2015-09-05 07:33:44', 80, '3', 0, 40, 440, 5060, 1, NULL, 0, 100, 2),
(1123, 51102, '2015-09-05 07:33:44', 80, '3', 0, 40, 440, 5060, 1, NULL, 0, 100, 1),
(1143, 22202, '2015-09-05 07:37:33', 80, '3', 0, 40, 420, 4820, 1, NULL, 0, 100, 1);

我也测试过它并按记录插入，但这并没有改变任何东西(除了永远插入......)

我做错了什么？我的触发器有问题吗？从命令行插入时不会正确触发吗？我现在没主意了

Answer 1

你的问题

MySQL 有两种不同类型的变量:

• 局部变量(不以 @ 为前缀)，它们是强类型的，并且作用域限定在它们所在的存储程序 block 中已用 DECLARE 声明陈述;和

• user variables (以 @ 为前缀)，它们是松散类型的并且限定在 session 中。请注意，它们既不需要也不能声明——它们只是直接使用。

您声明了名为id_exists 和time_exists 的局部变量，但此后使用名为@ 的用户变量 id_exists 和 @time_exists。由于后者在 session 范围内，它们保留它们的值在同一 session 中触发器的后续调用之间——因此，一旦 @id_exists 被设置为 1 不会插入更多记录。

在这种情况下，显而易见的解决方案是不使用用户变量，而是坚持使用本地变量:即删除整个触发器中的 @ 前缀。

更好的方法

1. 减少数据重复

   除非您真的需要，否则不要将 KistStatus 中的每一列的副本存储在 LastKistStatus 中——您可以简单地存储 (idKist, idKistStatus) 并在需要时获取基础记录。

   ALTER TABLE LastKistStatus
     DROP COLUMN Timestamp,
     DROP COLUMN idPartij,
     DROP COLUMN Actie,
     DROP COLUMN idTaak,
     DROP COLUMN idBewaarplaats,
     DROP COLUMN Rij,
     DROP COLUMN Stapel,
     DROP COLUMN Hoogte,
     DROP COLUMN KnolAantal,
     DROP COLUMN Gewicht,
     DROP COLUMN idGebruiker,
     DROP COLUMN Laatste;
   

   然后，每当您之前引用 LastKistStatus 时，您现在只需在其位置引用 LastKistStatus NATURAL JOIN KistStatus 即可获得相同的结果。

2. 了解 INSERT ... ON DUPLICATE KEY UPDATE .

   鉴于触发器中的逻辑，LastKistStatus 似乎只会包含具有任何给定 idKist 的单个记录。通过在数据库中强制执行此约束，您可以做一些聪明的事情:

   ALTER TABLE LastKistStatus ADD UNIQUE KEY (idKist);
   

   我在这里向表格添加了一个UNIQUE KEY。但是，这很可能(并且应该)是表的 PRIMARY KEY，它具有相同的行为(但速度更快，因为它的特殊状态)。

   现在，您可以将操作合并为一个，而不是测试记录是否已经存在然后相应地插入或更新:

   INSERT INTO LastKistStatus
     (idKist, idKistStatus)
   VALUES
     (NEW.idKist, NEW.idKistStatus)
   ON DUPLICATE KEY UPDATE
     idKistStatus = NEW.idKistStatus;
   

3. 观察到 LastKistStatus 实际上只是 Kist

   的一个属性

   我假设您有一张表 Kist。您可以简单地将 idKistStatus 从 LastKistStatus 表移动到那个表中，然后完全删除 LastKistStatus 表。

   ALTER TABLE Kist
     ADD COLUMN idKistStatus BIGINT UNSIGNED NULL DEFAULT NULL,
     ADD FOREIGN KEY (idKistStatus) REFERENCES KistStatus (idKistStatus);
   
   UPDATE Kist JOIN LastKistStatus USING (idKist)
   SET    Kist.idKistStatus = LastKistStatus.idKistStatus;
   

4. 完全取消触发器和缓存

   通过查找 groupwise maximum从 KistStatus 表中，您可以找到最新的状态，而无需维护 LastKistStatus:

   SELECT * FROM KistStatus NATURAL JOIN (
     SELECT idKist, MAX(Timestamp) AS Timestamp FROM KistStatus GROUP BY idKist
   ) t;
   

   这可以通过 (idKist, Timestamp) 上的索引进行搜索:

   ALTER TABLE KistStatus ADD INDEX (idKist, Timestamp);
   

   但是，请注意，它会返回每个组中具有最大时间戳的所有记录——如果您特别喜欢某个记录，则需要定义用于识别哪个记录的逻辑。

   您甚至可以创建一个 View 来产生与您从 LastKistStatus 获得的结果相同的结果:

   CREATE VIEW LastKistStatus AS
   SELECT * FROM KistStatus NATURAL JOIN (
     SELECT idKist, MAX(Timestamp) AS Timestamp FROM KistStatus GROUP BY idKist
   ) t;
   

   然后像以前一样使用它:

   SELECT * FROM LastKistStatus WHERE ...