mysql - 更新后触发使缓存失效

Question

我正在尝试编写一个触发器来使我的故事数据库的单词索引无效。但是，我似乎无法弄清楚如何在索引操作期间阻止触发器再次触发。我知道我需要放置一个 if 语句来停止更新，但我似乎无法弄清楚它应该是什么样子。

CREATE TRIGGER trMarkStoryForReindex BEFORE UPDATE ON Chapters
  FOR EACH ROW BEGIN

    -- any update to any chapter invalidates the index for the whole story
    -- we could just periodically flush the story index, but this way is
    -- better.
    SET New.isIndexed = FALSE;
    -- delete the index for that story
    -- It will get rebuilt in at most 15 minutes
    DELETE FROM freq WHERE storyid = NEW.StoryId;
END;

我基本上希望仅当在导致触发器的更新语句中未设置 isIndexed 时才触发触发器。

我的数据模型如下所示:

章节

• ID
• 已索引
• 故事 ID

频率

• 单词
• 故事ID

Answer 1

这是我的解决方案建议。我已经在 SQL fiddle 上对此进行了测试，它似乎有效:

-- Database setup
create table chapters (
  id int unsigned not null auto_increment primary key,
  isIndexed boolean default false,
  storyId int not null,
  index idx_storyId(storyId)
);

create table freq (
  word varchar(50),
  storyId int not null,
  index idx_storyId(storyId)
);

delimiter //
create trigger bi_chapters before update on chapters
for each row
begin
  if new.isIndexed = false then
    delete from freq where storyId = new.storyId;
  end if;
end //
delimiter ;

insert into freq(word, storyId) 
values ('one', 1), ('two', 1), ('three', 2);

insert into chapters(isIndexed, storyId) 
values (true, 1), (true, 2);

当您从freq中选择值时(在更新chapters之前)，您会得到:

select * from chapters;

| id | isIndexed | storyId |
|----|-----------|---------|
|  1 |     false |       1 |
|  2 |      true |       2 |

select * from freq;

|  word | storyId |
|-------|---------|
|   one |       1 |
|   two |       1 |
| three |       2 |

现在，对章节进行更新，并再次从freq中选择:

update chapters
  set isIndexed = false
  where storyId = 1;
select * from freq;

|  word | storyId |
|-------|---------|
| three |       2 |

我所做的唯一修改是 if block ，用于检查新行是否更新为 false。如果我正确理解你的问题，这将满足你的需要。

• SQL fiddle example