MySQL 建表审计字段规范用法

我们在使用数据库的过程中几乎所有表都会有几个字段是必须存在的。它们与业务无关，但是可以帮助我们追溯记录简单的插入和更新过程，是"辅助"一般的存在。

字段	备注
id	主键，在单数据库下由数据库自增实现，用来追踪记录
creator	记录创建者，用于跟踪记录创建人
updator	记录更新更新人，用于追踪记录更新人
create_time	创建时间，用于追踪记录创建时间
update_time	更新时间，用于追踪记录更新时间
del	逻辑删除字段，用于标记记录是否已经被删除

这里我们介绍一下辅助中的特殊字段–时间相关的字段。

create_time 创建时间

这个字段一般用于追踪记录的创建时间,在插入记录时会将当前时间记录在该字段上,我们可以在建表是给它定义一个默认值。

create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

update_time 更新时间

这个字段一般用于追踪记录的更新时间，与创建时间一样它一般与业务逻辑无关，当记录发生更新操作时需要同时更新该字段，我们同样不希望这个字段的更新操作交给我们业务代码。那么就在建表是设置让它自己更新吧。

-- 表明该字段非空，默认值为当前时间戳,并且在更新操作时同时更新该字段为当前时间戳
update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

示例

-- 这里新建一张用户表，只有一个 name 的业务字段，其余都是审计字段
CREATE TABLE user(
	id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
	name varchar(255),
	create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	update_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	creator varchar(255) NOT NULL,
	updator varchar(255) NOT NULL,
	del varchar(255) NOT NULL DEFAULT 'n'
)ENGINE="InnoDB",charset="utf8mb4";

关于 del 字段的思考

之前遇到过一个场景，是有关 del 逻辑删除 和唯一索引冲突的问题，我有记录是通过逻辑删除删掉了，但是这条记录实际上还是存在的。当我想要重新插入这条数据的时候由于原来被删除的数据还在导致新的数据被唯一索引限制住了插不进去。当然我们这里可以使用 REPLACE INTO 的方式将原来的数据删除后插入 或者INSERT FOR UPDATE 插入时条件更新。

但是如果我既想要留住原来已经被逻辑删除的数据，又想插入新的记录使两者不发生冲突怎么办？

解决方式是唯一索引中加入del审计字段，使得唯一的判定范围扩大。

比如上面那张表，我希望在 name字段 上创建唯一索引。并插入记录。

-- 在 user 表的 name 字段上创建索引
CREATE UNIQUE INDEX uk_name ON user(name);
-- 插入一条记录
INSERT INTO user(name,creator,updator) values("张三","ghimi","ghimi");
-- 逻辑删除该记录
UPDATE user set del='y' WHERE id=1;
-- 重新插入该记录时会报错
INSERT INTO user(name,creator,updator) values("张三","ghimi","ghimi");
-- 为了保证唯一索引和逻辑删除字段不冲突，我们需要在该表的所有唯一索引中加入 del 审计字段
DROP INDEX IF EXISTS uk_name on user;
CREATE UNIQUE INDEX uk_name_del ON user(name,del);
-- 同时逻辑删除修改为
UPDATE user set del=concat('y-',now()) WHERE id=1;
-- 这样再次插入相同的记录的时候就不会报唯一索引错误了

参考资料