mysql 视图的数据刷新存在延迟

Question

问题描述

请问一下各位大佬，我在一个请求中更新了一个mysql表的数据，请求结束以后立马在另一个请求中查询包含这个表的视图，会查到修改之前的旧数据，在请求一次才能查到新的数据了，而且不是每次都会出现这种情况。

select h.*,i.staff_name person_name from (SELECT

a.ec_id AS ec_id,
a.region_id AS region_id,
e.region_name AS region_name,
a.production_process_id as production_process_id,
a.production_process_name as production_process_name,
a.production_process_type as production_process_type,
g.entry_name as production_process_type_name,
a.entity_id AS entity_id,
a.create_time as create_time,
a.create_id as create_id,
a.status as status,
max(IF ((d.tag = 'BeginTime'),b.attr_value,NULL)) AS begin_time,
max(IF ((d.tag = 'EndTime'),b.attr_value,NULL)) AS end_time,
max(IF ((d.tag = 'Person'),b.attr_value,NULL)) AS person,
max(IF ((d.tag = 'Event'),b.attr_value,NULL)) AS event
FROM erp_production_process a

LEFT JOIN (select * from entity_attr_value where inure_time <= now() and expire_time >now()) b on b.instance_id = a.production_process_id
left join entity_attr_cfg c on c.attr_id = b.attr_id and c.entity_id = a.entity_id
left join entity_attr_lib d on d.attr_id = b.attr_id
left join ec_produce_region e on e.region_id = a.region_id,
dict_def g
WHERE g.dict_type = 2003
and g.dict_class = 1003
and g.entry_id = a.production_process_type
GROUP BY

a.ec_id       ,
a.production_process_id   ,
a.production_process_name ,
a.production_process_type ,
g.entry_name  ,
a.entity_id   ,
a.create_time ,
a.create_id   ,
a.status      )h
left join ec_staff i on i.staff_id = h.person
;

问题出现的环境背景及自己尝试过哪些方法

我的项目是用spring-boot+mybatis开发的

Answer 1

原来是我的视图查询语句有问题，因为mysql默认存储时间字段和now()函数只存到秒，后面的毫秒是四舍五入的，所以就导致了一瞬间查到的是旧的过期数据，解决方案是把存储日期的DateTime(3)字段精度配成3，now(3)函数精度也改成3就没问题了