在事务内部使用match/for时,它似乎不会从临时未提交的数据中进行查询:
start transaction;
insert into feed_full_text (feed_id, full_text) values (5000008, "lorem ipsum");
select feed_id, full_text
from feed_full_text
where feed_id = 5000008 and match(full_text) against("lorem" in boolean mode)
order by feed_id desc
limit 1;
commit
但不返回结果:
start transaction;
insert into feed_full_text (feed_id, full_text) values (5000008, "lorem ipsum");
select feed_id, full_text
from feed_full_text
where feed_id = 5000008
order by feed_id desc
limit 1;
commit
返回刚刚插入的行,并:
insert into feed_full_text (feed_id, full_text) values (5000008, "lorem ipsum");
select feed_id, full_text
from feed_full_text
where feed_id = 5000008 and match(full_text) against("lorem" in boolean mode)
order by feed_id desc
limit 1;
也返回行。这是虫子还是我遗漏了什么?我使用的是支持innodb全文索引的5.7.11。
最佳答案
这是预期的行为。documentation表示:
InnoDB全文索引事务处理
innodb全文索引由于其缓存和批处理行为,具有特殊的事务处理特性。具体来说,全文索引上的更新和插入是在事务提交时处理的,这意味着全文搜索只能看到提交的数据。