如何设计基础项目数据库_mysql通用表结构总结

MySQL通用表结构核心是兼顾通用性、可扩展性和维护性，采用用户权限三表分离、统一软删除与乐观并发字段、字典配置表替代硬编码、精简日志表并归档。

设计基础项目数据库时，MySQL通用表结构的核心是兼顾通用性、可扩展性和维护性。不追求“一表通吃”，而是围绕业务主干提炼出高频复用的字段和约束逻辑，让新模块能快速接入、老模

块便于统一管理。

用户与权限相关表（user / role / permission）

这是绝大多数后台系统的基础。建议采用三表分离设计，避免硬编码角色，支持动态权限分配：

• user 表：必含 id（BIGINT UNSIGNED AUTO_INCREMENT）、username（UNIQUE）、password_hash、email（UNIQUE）、status（TINYINT，默认1=启用）、created_at、updated_at；推荐加 last_login_at 和 failed_login_count 支持基础安全策略。
• role 表：含 id、name（如 'admin', 'editor'）、code（唯一标识，用于代码判断，如 'ROLE_ADMIN'）、description、is_system（TINYINT，标记是否内置角色，防误删）。
• permission 表：存具体操作权限，如 'user:read'、'order:export'；字段包括 id、code（唯一）、name、module（归类，如 'user', 'order'）；通过 role_permission 中间表关联。

内容与状态通用字段（created_at / updated_at / is_deleted / version）

所有业务表建议统一包含以下四字段，形成“软删除+乐观并发”基础能力：

• created_at DATETIME DEFAULT CURRENT_TIMESTAMP
• updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
• is_deleted TINYINT DEFAULT 0 COMMENT '0=正常,1=已删除' —— 配合查询时加 WHERE is_deleted = 0，避免真实 DELETE。
• version INT UNSIGNED DEFAULT 1 —— 更新时校验 WHERE id = ? AND version = ?，更新后 version = version + 1，防止并发覆盖。

注意：updated_at 不依赖应用层赋值，由 MySQL 自动维护更可靠；is_deleted 比 deleted_at 更轻量，适合大多数场景。

字典与配置表（dict_type / dict_item / config）

替代硬编码枚举，提升可配置性：

• dict_type：类型定义，如 'order_status'、'pay_channel'；字段含 code（唯一）、name、remark、sort。
• dict_item：具体字典项，如 'order_status:1→待支付'；字段含 type_code（外键到 dict_type.code）、value（存储值，如 '1'）、label（显示名，如 '待支付'）、sort、is_enabled。
• config：系统级配置项，如 'site_name'、'sms_switch'；字段建议有 key（唯一）、value（TEXT）、type（string/number/boolean）、remark；应用启动时可加载进内存缓存，减少 DB 查询。

日志与操作记录表（operation_log / error_log）

不为审计而堆字段，聚焦关键信息，保证写入性能：

• operation_log：记录用户关键操作，字段精简为 user_id、module（如 'user'）、action（如 'create'）、target_id（操作对象 ID，可为空）、ip、ua（可选）、created_at；避免存完整请求参数，敏感字段脱敏或转存摘要。
• error_log：捕获服务端异常，字段含 level（ERROR/WARN）、exception_class、message（简要错误描述）、stack_hash（MD5(stack_trace) 用于去重聚合）、occurred_at；建议按天分表或接入 ELK，不长期驻留主库。

这两类表建议使用 ENGINE=ARCHIVE（仅 INSERT/SELECT）或定期归档，降低主业务库压力。

通用表结构不是越全越好，而是把重复出现的模式沉淀下来。建表前多问一句：这个字段下个项目还用不用？这个约束会不会卡住未来扩展？保持克制，才能让数据库真正成为项目的稳定底座。