在数据库设计中，创建时间和修改时间是常见的通用字段，用于记录数据行的创建和最后修改时间。这些字段对于跟踪数据的历史变化、审计日志以及数据分析非常有用。以下是一些关于如何在数据库表中设计和使用这些通用字段的最佳实践：

1. 字段定义
通常，你会在每个表中添加两个字段来记录创建时间和修改时间。常见的命名约定包括 created_at 和 updated_at。

SQL 示例
Sql
深色版本
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
2. 默认值
创建时间 (created_at): 通常设置为 CURRENT_TIMESTAMP 或 NOW()（取决于数据库系统），这样在插入新记录时会自动填充当前时间。
修改时间 (updated_at): 也可以设置为 CURRENT_TIMESTAMP，但为了确保每次更新记录时都能更新这个字段，需要额外的配置。
PostgreSQL 示例
Sql
深色版本
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
注意：PostgreSQL 并不直接支持 ON UPDATE CURRENT_TIMESTAMP 语法。你需要使用触发器来实现这一功能。

3. 使用触发器自动更新 updated_at
在 PostgreSQL 中，你可以使用触发器来自动更新 updated_at 字段。

创建触发器函数
Sql
深色版本
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
创建触发器
Sql
深色版本
CREATE TRIGGER update_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
4. 在应用程序代码中处理
如果你使用的是 ORM（如 Entity Framework, Hibernate 等），通常可以通过配置或注解来自动管理这些字段。

Entity Framework Core 示例
Csharp
深色版本
public class User
{
    public int Id { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }

    // 创建时间
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;

    // 修改时间
    public DateTime UpdatedAt { get; set; }

    // 配置实体
    public void Configure(EntityTypeBuilder<User> builder)
    {
        builder.Property(u => u.CreatedAt).HasDefaultValueSql("GETUTCDATE()").ValueGeneratedOnAdd();
        builder.Property(u => u.UpdatedAt).HasDefaultValueSql("GETUTCDATE()").ValueGeneratedOnAddOrUpdate();
    }
}
5. 其他通用字段
除了 created_at 和 updated_at 之外，还有一些其他常见的通用字段：

created_by: 记录创建该记录的用户ID。
updated_by: 记录最后一次更新该记录的用户ID。
is_deleted: 逻辑删除标记，用于软删除（逻辑删除）而不是物理删除。
SQL 示例
Sql
深色版本
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by INT,
    updated_by INT,
    is_deleted BOOLEAN DEFAULT FALSE
);
6. 数据库迁移
如果你使用的是 ORM，并且需要进行数据库迁移，确保在迁移脚本中包含这些字段及其默认值和触发器。

7. 审计日志
对于更详细的审计需求，可以考虑使用专门的审计日志表来记录所有更改操作，包括操作时间、操作者和变更内容。

通过上述方法，你可以有效地在数据库设计中加入创建时间和修改时间等通用字段，并确保它们在插入和更新时自动更新。这将有助于提高数据的可追溯性和完整性。