如何为MySQL的companies表自动添加插入与更新时间字段？

场景说明 #

现有companies表，初始结构如下：

create table companies(
    name varchar(20),
    city char(10),
    numberofemployees int(10),
    averagesalary double
);

已执行以下语句新增字段：

alter table companies add inserttime datetime, add updatedtime datetime, add id serial;

需求：执行插入操作时无需指定inserttime和updatedtime字段，仅通过INSERT INTO companies VALUES ("company","bglr",30,400.00)就能让这两个字段自动填充为当前时间。

解决步骤 #

1. 配置inserttime自动填充

修改inserttime字段，设置默认值为当前时间，插入数据时自动生效：

ALTER TABLE companies MODIFY COLUMN inserttime DATETIME DEFAULT CURRENT_TIMESTAMP;

2. 配置updatedtime自动填充

针对updatedtime，利用MySQL自带的ON UPDATE特性，实现插入和更新时自动同步当前时间（支持MySQL 5.6及以上版本）：

ALTER TABLE companies MODIFY COLUMN updatedtime DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

若使用MySQL 5.5及以下版本，可通过触发器实现：

-- 插入时自动设置updatedtime
DELIMITER //
CREATE TRIGGER trg_companies_insert
BEFORE INSERT ON companies
FOR EACH ROW
BEGIN
    SET NEW.updatedtime = CURRENT_TIMESTAMP;
END //
DELIMITER ;

-- 更新时自动更新updatedtime
DELIMITER //
CREATE TRIGGER trg_companies_update
BEFORE UPDATE ON companies
FOR EACH ROW
BEGIN
    SET NEW.updatedtime = CURRENT_TIMESTAMP;
END //
DELIMITER ;

3. 测试插入操作

执行插入语句（推荐指定列名避免字段顺序问题，若坚持原语句需确保表字段顺序与插入值匹配）：

-- 推荐写法（指定列名，不受表结构顺序影响）
INSERT INTO companies (name, city, numberofemployees, averagesalary) VALUES ("company","bglr",30,400.00);

-- 原语句写法（需保证表字段顺序为name,city,numberofemployees,averagesalary,inserttime,updatedtime,id）
INSERT INTO companies VALUES ("company","bglr",30,400.00, DEFAULT, DEFAULT, DEFAULT);

执行后，inserttime和updatedtime会自动填充为当前时间，id字段自动生成自增值。

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