如何配置SQL表的PublishYear字段自动提取日期年份存入？

根据需求场景的不同，有两种实现方式，以下是主流数据库的具体写法：

场景1：自动填充当前系统日期的年份 #

直接给PublishYear字段设置默认值，利用数据库内置的年份提取函数即可：

MySQL/MariaDB #

CREATE TABLE BOOK(
    BOOKId INT PRIMARY KEY,
    `Book title` VARCHAR(255),
    Author VARCHAR(255),
    PublishYear INT DEFAULT YEAR(CURDATE())
);

PostgreSQL #

CREATE TABLE BOOK(
    BOOKId INT PRIMARY KEY,
    "Book title" VARCHAR(255),
    Author VARCHAR(255),
    PublishYear INT DEFAULT EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER
);

SQL Server #

CREATE TABLE BOOK(
    BOOKId INT PRIMARY KEY,
    [Book title] VARCHAR(255),
    Author VARCHAR(255),
    PublishYear INT DEFAULT YEAR(GETDATE())
);

场景2：基于表中另一个日期字段（如PublishDate）的年份自动填充 #

如果需要根据插入的PublishDate字段自动同步年份，必须通过触发器实现：

MySQL/MariaDB #

-- 先添加日期字段（如果表中还没有）
ALTER TABLE BOOK ADD COLUMN PublishDate DATE;

-- 创建触发器
DELIMITER //
CREATE TRIGGER trg_sync_publish_year
BEFORE INSERT ON BOOK
FOR EACH ROW
BEGIN
    SET NEW.PublishYear = YEAR(NEW.PublishDate);
END //
DELIMITER ;

PostgreSQL #

-- 添加日期字段（如果表中还没有）
ALTER TABLE BOOK ADD COLUMN PublishDate DATE;

-- 创建触发函数
CREATE OR REPLACE FUNCTION sync_publish_year()
RETURNS TRIGGER AS $$
BEGIN
    NEW.PublishYear = EXTRACT(YEAR FROM NEW.PublishDate)::INTEGER;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trg_sync_publish_year
BEFORE INSERT ON BOOK
FOR EACH ROW
EXECUTE FUNCTION sync_publish_year();

SQL Server #

-- 添加日期字段（如果表中还没有）
ALTER TABLE BOOK ADD COLUMN PublishDate DATE;

-- 创建触发器
CREATE TRIGGER trg_sync_publish_year
ON BOOK
INSTEAD OF INSERT
AS
BEGIN
    INSERT INTO BOOK (BOOKId, [Book title], Author, PublishDate, PublishYear)
    SELECT BOOKId, [Book title], Author, PublishDate, YEAR(PublishDate)
    FROM inserted;
END;

注意事项 #

• 若只需自动填充当前年份，默认值方案足够简洁高效；若需关联其他日期字段，触发器是唯一可行的方式
• 确保你的数据库版本支持上述函数与触发器语法

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