mysql - MySql 存储过程中的此 Delete 语句不起作用

Question

我有这个存储过程来将错误存储在 Elmah 表中:

CREATE DEFINER=`b4d7eb33be6399`@`%` PROCEDURE `elmah_LogError`(
    IN ErrorId CHAR(36), 
    IN Application varchar(60), 
    IN Host VARCHAR(30), 
    IN Type VARCHAR(100), 
    IN Source VARCHAR(60), 
    IN Message VARCHAR(500), 
    IN User VARCHAR(50), 
    IN AllXml TEXT, 
    IN StatusCode INT(10), 
    IN TimeUtc DATETIME
)
    MODIFIES SQL DATA
BEGIN

    INSERT INTO `elmah_error` (
        `ErrorId`, 
        `Application`, 
        `Host`, 
        `Type`, 
        `Source`, 
        `Message`, 
        `User`, 
        `AllXml`, 
        `StatusCode`, 
        `TimeUtc`
    ) VALUES (
        ErrorId, 
        Application, 
        Host, 
        Type, 
        Source, 
        Message, 
        User, 
        AllXml, 
        StatusCode, 
        TimeUtc
    );

DELETE FROM `elmah_error` WHERE ErrorId NOT IN ( 
  SELECT ErrorId 
  FROM ( 
    SELECT ErrorId 
    FROM `elmah_error`
    ORDER BY TimeUtc DESC 
    LIMIT 100
  ) x 
);

END

我添加了 DELETE 部分以仅保留 100 条最后的记录。问题是它什么都不做。但是，如果我单独使用此 DELETE 代码并在 MySql Workbench 内的查询窗口中运行它，它会正确删除我要保留的 100 条记录之后的记录。奇怪不？

Answer 1

在列名上加上反引号:

DELETE FROM `elmah_error` WHERE `ErrorId` NOT IN ( 
  SELECT `ErrorId`
  FROM ( 
    SELECT `ErrorId` 
    FROM `elmah_error`
    ORDER BY `TimeUtc` DESC 
    LIMIT 100
  ) x 
);

否则它们将被视为过程主体中的变量(因为名称相同)。