如何在SQL中创建自动计算管理者任职年限的生成列

问题分析 #

你现有的建表语句无法实现需求，原因有2个：

• DEFAULT 约束仅支持常量、确定性系统函数，不能引用同表的其他字段做动态运算
• years_passed 字段类型选择错误：YEAR 类型是用于存储4位年份值（如2020、2024），而任职年限是差值数值，应该用INT类型存储

注意：如果选择持久化存储任职年限（stored计算列、触发器写入），数据不会随跨年自动更新，仅在行数据被修改时才会重算；如果需要任职年限始终和当前日期同步，建议使用非持久化计算列或者查询时动态计算。

可行实现方案 #

• 方案1：使用计算列（最优方案，主流数据库MySQL8.0+、SQL Server、PostgreSQL均支持）
  计算列会自动根据定义的规则生成值，无需手动维护，以下是MySQL环境的示例代码：

  create table manager(
    id int primary key not null,
    working_comp int,
    -- 如果只需要存入职年份可保留YEAR类型，需要精确到年月日则改为DATE类型
    started_working date not null,
    -- 自动计算任职年限，加stored表示持久化存储，不加则查询时实时计算
    years_passed int as (TIMESTAMPDIFF(YEAR, started_working, CURDATE())) stored not null,
    foreign key (working_comp) references company(company_AFM)
  );
  

  不同数据库的时间计算函数有差异：

  • SQL Server：年份差用DATEDIFF(YEAR, started_working, GETDATE())
  • PostgreSQL：年份差用EXTRACT(YEAR FROM AGE(CURRENT_DATE, started_working))

• 方案2：使用触发器（适配不支持计算列的低版本数据库）
  可以创建插入、更新双触发器，在数据写入/修改时自动计算任职年限：

  -- 插入数据触发计算
  CREATE TRIGGER calc_years_before_insert
  BEFORE INSERT ON manager
  FOR EACH ROW
  SET NEW.years_passed = TIMESTAMPDIFF(YEAR, NEW.started_working, CURDATE());
  
  -- 更新入职日期触发重算
  CREATE TRIGGER calc_years_before_update
  BEFORE UPDATE ON manager
  FOR EACH ROW
  SET NEW.years_passed = TIMESTAMPDIFF(YEAR, NEW.started_working, CURDATE());
  

• 方案3：查询时动态计算
  不需要在表中创建years_passed字段，每次查询时实时生成结果，适合数据量不大的场景：

  SELECT 
    id,
    working_comp,
    started_working,
    TIMESTAMPDIFF(YEAR, started_working, CURDATE()) AS years_passed
  FROM manager;
  

内容的提问来源于stack exchange，提问作者BrainlessPOMO