对具有自动增量和外键的表执行插入/更新操作

Question

我有一张这样的表格：

id entity_id  first_year  last_year sessions_attended age

1   2020       1996         2008         3            34.7
2   2024       1993         2005         2            45.1
3    ...       ...          ...

id为自增主键，entity_id为外键，表内唯一。

我有一个计算第一年和最后一年出席率的查询，我希望每次运行时都能用新数据更新这个表，只更新第一年和去年的列：

这是我对“第一年”的插入/更新：

    insert into my_table (entity_id, first_year)
( select contact_id, @sd:= year(start_date) 
from 
( select  contact_id, event_id, start_date from participations
    join events on participations.event_id = events.id where events.event_type_id = 7
    group by contact_id order by event_id ASC) as starter) 
    ON DUPLICATE KEY UPDATE first_year_85 = @sd;

我有一个类似的“去年”，除了目标列和order by之外都是一样的。

查询本身返回所需的值，但是我在使用插入/更新查询时遇到了问题。当我运行它们时，我最终得到了两个字段的相同值(正确的first_year值)。

有什么突出的原因导致了这种情况吗？

轶事注:这似乎可以在MySQL 5.5.54上运行，但当在我本地的MariaDB上运行时，它只显示出上述行为……

更新：

这不是我设计的餐桌。这是一个客户关系管理，允许自定义字段由最终用户定义，我通过外部查询填充数据。

participations表保存所有entity_ids的所有事件注册，但开始日期保存在单独的events表中，因此称为join。

之所以有这个变量，是因为ON DUPLICATE UPDATE不会接受对没有它的列的引用。

年龄实际上稍微更复杂一些:它是某种类型的下一个活动事件开始日期之前的年龄。

字段正被“硬”更新，因为此表中的值被CRM中的报告和搜索拉取，它们需要存在，不能动态计算。

Answer 1

既然你有一个“天然”的PK (entity_id)，为什么还要用id

age？您是否需要每天更改该列，或者至少每月更改一次？这不是一个好的设计。最好在表中包含常量birth_date，然后在SELECT中计算年龄。

“计算出勤的第一年和最后一年”--这意味着您有一个列出所有出勤年份的表(yoa)？如果是这样的话，MAX(yoa)和MIN(yoa)可能是计算事物的更好方法。

在查询中很少需要@variables。

仔细阅读我的评论；在提供了新的查询、SHOW CREATE TABLE、EXPLAIN和一些示例数据之后，请回来寻求更多的想法。