mysql - 触发语句写入错误

Question

我想将表的大小限制为 X 行(例如，我将使用 5 行)。当达到限制时，我想将最旧的行复制到另一个表，然后将其删除。我目前有:

CREATE TRIGGER LimitRows BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
    IF (SELECT COUNT(*) FROM MyTable) >= 5 THEN

    INSERT INTO HistoryTable
    SELECT *
    FROM MyTable A
    WHERE vhID = A.min(vhID);

    DELETE FROM MyTable
    WHERE vhID = min(vhID); 

    END IF;    
END;

目前，我收到错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 8

如何正确编写这个触发器？另外，如果表格一开始有 100 行，我该如何修改以将表格减少到 5 行？

Answer 1

您需要先更改分隔符

delimiter |
CREATE TRIGGER LimitRows BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
    IF (SELECT COUNT(*) FROM MyTable) >= 5 THEN

    INSERT INTO HistoryTable
    SELECT *
    FROM MyTable A
    WHERE vhID = A.min(vhID);

    DELETE FROM MyTable
    WHERE vhID = min(vhID); 

    END IF;    
END
|
delimiter ;

否则触发器定义将在第一个 ; 处结束，这将使其不完整。