molong/vibe_coding

Fork 0

Files

molong 130de0fd5d 初始化

2026-03-05 21:27:11 +08:00

29 KiB

Raw Permalink Blame History

Data Model — MySQL 数据建模

定义数据库表结构、关系和设计约定。AI Agent 在生成 Migration 和 Model 时参考本文档。

1. 设计原则

主键统一使用 BIGINT UNSIGNED AUTO_INCREMENT
字符集 utf8mb4，排序规则 utf8mb4_unicode_ci
所有表包含 created_at, updated_at, deleted_at (软删除)
外键字段命名 <table_singular>_id，必须有索引
金额字段使用 DECIMAL(10,2)
状态字段使用 VARCHAR(20) 或 TINYINT（避免 ENUM）
时间字段使用 TIMESTAMP
排序字段使用 INT UNSIGNED DEFAULT 0

2. 命名规范

2.1 表命名规则

表名必须以所属模块名作为前缀（snake_case），格式：<module>_<entity_plural>。

模块	前缀	说明
用户与权限	`auth_`	用户、角色、菜单、OAuth 等
案例核心	`case_`	案例主表、图库、文件、分类等
设计师	`designer_`	设计师档案、奖项、标签关联等
运营内容	`operation_`	轮播图、专题、标签字典等
用户互动	`interaction_`	收藏、点赞、评论、关注、订阅等
日志	`log_`	登录日志、操作日志、下载日志等
安全风控	`security_`	黑名单、风控事件、会话等
系统配置	`system_`	系统参数配置等

规则说明：

同一业务域下的所有表使用相同前缀，避免不同模块表名冲突

多对多关联表同样需要加模块前缀：<module>_<a>_belongs_<b>

跨模块关联的中间表，以主体模块的前缀命名

2.2 通用命名约定

类型	规范	示例
表名	`<module>_` 前缀 + snake_case 复数	`case_cases`, `auth_users`
字段名	snake_case	`total_building_area`
主键	`id`	`BIGINT UNSIGNED`
外键	`<singular>_id`	`case_id`, `designer_id`
索引	`idx_<table>_<cols>`	`idx_case_cases_status`
唯一索引	`uk_<table>_<cols>`	`uk_case_cases_code`
多对多关联表	`<module>_<a>_belongs_<b>`	`case_case_belongs_tag`
布尔字段	`is_<adj>`	`is_featured`, `is_active`
计数缓存	`<noun>_count`	`view_count`, `like_count`

2.3 各模块表名对照

当前表名	所属模块	规范表名
`users`	用户与权限	`auth_users`
`oauth_bindings`	用户与权限	`auth_oauth_bindings`
`roles`	用户与权限	`auth_roles`
`menus`	用户与权限	`auth_menus`
`user_belongs_role`	用户与权限	`auth_user_belongs_role`
`role_belongs_menu`	用户与权限	`auth_role_belongs_menu`
`categories`	案例核心	`case_categories`
`tags`	运营内容	`operation_tags`
`cases`	案例核心	`case_cases`
`case_images`	案例核心	`case_images`（已有前缀）
`case_files`	案例核心	`case_files`（已有前缀）
`case_belongs_tag`	案例核心	`case_belongs_tag`（已有前缀）
`case_team_members`	案例核心	`case_team_members`（已有前缀）
`designers`	设计师	`designer_profiles`
`designer_awards`	设计师	`designer_awards`（已有前缀）
`designer_belongs_tag`	设计师	`designer_belongs_tag`（已有前缀）
`banners`	运营内容	`operation_banners`
`topics`	运营内容	`operation_topics`
`topic_belongs_case`	运营内容	`operation_topic_belongs_case`
`favorites`	用户互动	`interaction_favorites`
`likes`	用户互动	`interaction_likes`
`comments`	用户互动	`interaction_comments`
`follows`	用户互动	`interaction_follows`
`newsletter_subscriptions`	用户互动	`interaction_newsletter_subscriptions`
`user_login_logs`	日志	`log_user_logins`
`admin_operation_logs`	日志	`log_admin_operations`
`download_logs`	日志	`log_downloads`
`user_sessions`	安全风控	`security_user_sessions`
`security_risk_events`	安全风控	`security_risk_events`（已有前缀）
`security_blacklists`	安全风控	`security_blacklists`（已有前缀）
`system_configs`	系统配置	`system_configs`（已有前缀）

3. ER 关系总览

用户与权限
─────────
users ←→ user_belongs_role ←→ roles
  │                              │
  └→ oauth_bindings        role_belongs_menu ←→ menus

案例核心
────────
categories ←── cases ──→ case_images
                │   └──→ case_files
                │   └──→ case_belongs_tag ←── tags
                │   └──→ case_team_members ──→ designers
                │                               │
                │                               └→ designer_awards
                │                               └→ designer_belongs_tag ←── tags
                │
                └──→ favorites ←── users
                └──→ likes ←── users
                └──→ comments ←── users

运营内容
────────
banners (独立)
topics ←→ topic_belongs_case ←→ cases

会话与安全
──────────
users ──→ user_sessions
users ──→ security_risk_events
security_blacklists (独立)

日志与系统
──────────
user_login_logs
admin_operation_logs
download_logs
system_configs
newsletter_subscriptions

4. 核心数据表

4.1 用户与权限域

`users` — 统一用户表

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK, AUTO_INCREMENT
`user_type`	VARCHAR(20)	NOT NULL, DEFAULT 'user'	user / admin
`username`	VARCHAR(50)	NULL, UNIQUE	登录账号
`phone`	VARCHAR(20)	NULL, UNIQUE	手机号
`password`	VARCHAR(255)	NULL	bcrypt 哈希
`enterprise_user_id`	VARCHAR(100)	NULL, UNIQUE	企业平台用户唯一标识
`source`	VARCHAR(20)	NOT NULL, DEFAULT 'local'	local / enterprise / wechat / qq
`is_enterprise_linked`	TINYINT	NOT NULL, DEFAULT 0	是否已绑定企业账号
`nickname`	VARCHAR(50)	NULL	显示昵称
`avatar`	VARCHAR(500)	NULL	头像 URL
`role_label`	VARCHAR(50)	NULL	展示角色标签（如 Architect）
`previous_password_hash`	VARCHAR(255)	NULL	上次密码哈希（防重复使用）
`agree_at`	TIMESTAMP	NULL	用户同意协议时间
`agreement_version`	VARCHAR(30)	NULL	同意时协议版本
`status`	TINYINT	NOT NULL, DEFAULT 1	1=启用, 0=禁用
`deactivation_requested_at`	TIMESTAMP	NULL	注销申请时间（冷静期起始）
`scheduled_deletion_at`	TIMESTAMP	NULL	计划删除时间（冷静期结束）
`last_login_at`	TIMESTAMP	NULL
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP
`deleted_at`	TIMESTAMP	NULL

索引：uk_users_username, uk_users_phone, uk_users_enterprise_user_id, idx_users_user_type, idx_users_status

`oauth_bindings` — 第三方登录绑定

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`provider`	VARCHAR(20)	NOT NULL	wechat / qq / enterprise
`provider_user_id`	VARCHAR(100)	NOT NULL	第三方平台用户 ID
`access_token`	VARCHAR(500)	NULL
`refresh_token`	VARCHAR(500)	NULL
`expires_at`	TIMESTAMP	NULL
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：uk_oauth_provider_uid (provider, provider_user_id), idx_oauth_user_id

`roles` — 角色表

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`name`	VARCHAR(50)	NOT NULL, UNIQUE	角色标识：super_admin / content_ops / reviewer
`display_name`	VARCHAR(50)	NOT NULL	超级管理员 / 内容运营 / 审核员
`description`	VARCHAR(200)	NULL
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

`menus` — 菜单/权限表

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`parent_id`	BIGINT UNSIGNED	DEFAULT 0	树形结构
`name`	VARCHAR(50)	NOT NULL	权限标识 `case:create`
`display_name`	VARCHAR(50)	NOT NULL	菜单显示名
`type`	VARCHAR(10)	NOT NULL	menu / button
`path`	VARCHAR(200)	NULL	前端路由路径
`icon`	VARCHAR(50)	NULL	图标名
`sort`	INT UNSIGNED	DEFAULT 0
`is_visible`	TINYINT	DEFAULT 1
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

`user_belongs_role` — 用户-角色关联

字段	类型	约束
`user_id`	BIGINT UNSIGNED	PK(联合), FK → users
`role_id`	BIGINT UNSIGNED	PK(联合), FK → roles

`role_belongs_menu` — 角色-菜单关联

字段	类型	约束
`role_id`	BIGINT UNSIGNED	PK(联合), FK → roles
`menu_id`	BIGINT UNSIGNED	PK(联合), FK → menus

4.2 案例核心域

`categories` — 建筑类型分类

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`name`	VARCHAR(50)	NOT NULL, UNIQUE	别墅 / 民宿 / 室内 / 景观 / 农村自建房 / 庭院
`slug`	VARCHAR(50)	NOT NULL, UNIQUE	villa / homestay / interior / landscape
`icon`	VARCHAR(50)	NULL
`sort`	INT UNSIGNED	DEFAULT 0
`case_count`	INT UNSIGNED	DEFAULT 0	缓存引用计数
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

`tags` — 统一标签字典

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`type`	VARCHAR(20)	NOT NULL	style / feature / specialty
`name`	VARCHAR(50)	NOT NULL	现代 / 落地窗 / 参数化设计
`sort`	INT UNSIGNED	DEFAULT 0
`reference_count`	INT UNSIGNED	DEFAULT 0	被引用次数
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：uk_tags_type_name (type, name), idx_tags_type

`cases` — 案例主表

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`category_id`	BIGINT UNSIGNED	FK → categories	建筑类型
`title`	VARCHAR(200)	NOT NULL	项目名称
`code`	VARCHAR(30)	NOT NULL, UNIQUE	项目编号 P-2024-8839
`cover_image`	VARCHAR(500)	NULL	封面图 URL
`status`	VARCHAR(20)	NOT NULL, DEFAULT 'draft'	draft / pending / published / archived
`is_featured`	TINYINT	DEFAULT 0	是否精选
`featured_sort`	INT UNSIGNED	DEFAULT 0	精选排序
— 户型布局 —
`layout`	VARCHAR(50)	NULL	3室2厅3卫
`floor_height`	VARCHAR(50)	NULL	3.6/3.3/3.3m
— 用地条件 —
`site_width`	VARCHAR(50)	NULL	面宽 ≥ 10.5m
`site_depth`	VARCHAR(50)	NULL	进深 ≥ 12.2m
`site_area`	DECIMAL(10,2)	NULL	占地面积 m²
`lighting_restriction`	VARCHAR(100)	NULL	采光限制
`site_shape`	VARCHAR(50)	NULL	用地形状
— 规模造价 —
`floors_above`	TINYINT UNSIGNED	NULL	地上层数
`floors_below`	TINYINT UNSIGNED	NULL	地下层数
`cost_min`	DECIMAL(12,2)	NULL	造价预估下限（万元）
`cost_max`	DECIMAL(12,2)	NULL	造价预估上限（万元）
`total_building_area`	DECIMAL(10,2)	NULL	建筑总面积 m²
`bedroom_count`	TINYINT UNSIGNED	NULL	卧室数量
`bedroom_note`	VARCHAR(100)	NULL	含1主卧套房
— 平面功能 —
`stair_type`	VARCHAR(100)	NULL	楼梯类型
`special_space`	VARCHAR(200)	NULL	特色空间 / 农村特色
`bedroom_config`	VARCHAR(200)	NULL	卧室配置
`leisure_space`	VARCHAR(200)	NULL	休闲空间
`kitchen_dining`	VARCHAR(200)	NULL	厨房餐厅
`bathroom_config`	VARCHAR(100)	NULL	卫生间配置
— 外观风格 —
`architecture_style`	VARCHAR(100)	NULL	建筑风格
`roof_type`	VARCHAR(100)	NULL	屋顶形式
`facade_material`	VARCHAR(200)	NULL	外立面材料
— 结构与性能 —
`structure_type`	VARCHAR(100)	NULL	结构类型
`foundation_type`	VARCHAR(100)	NULL	基础类型
`layout_logic`	VARCHAR(200)	NULL	户型逻辑
`wall_thickness`	VARCHAR(100)	NULL	墙体厚度
`seismic_rating`	VARCHAR(100)	NULL	抗震设防
`insulation_type`	VARCHAR(100)	NULL	保温形式
`roof_structure`	VARCHAR(200)	NULL	屋面构造
— 设计理念 —
`design_concept`	TEXT	NULL	设计理念富文本
`concept_style`	VARCHAR(100)	NULL	风格提炼
`concept_material`	VARCHAR(100)	NULL	材质提炼
`concept_target`	VARCHAR(100)	NULL	受众提炼
— 分类特有扩展 —
`meta`	JSON	NULL	分类特有参数（景观面积 / 客房数 / 主材等）
— 统计计数 —
`view_count`	INT UNSIGNED	DEFAULT 0
`like_count`	INT UNSIGNED	DEFAULT 0
`favorite_count`	INT UNSIGNED	DEFAULT 0
`download_count`	INT UNSIGNED	DEFAULT 0
`comment_count`	INT UNSIGNED	DEFAULT 0
— 时间 —
`published_at`	TIMESTAMP	NULL	发布时间
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP
`deleted_at`	TIMESTAMP	NULL

索引：uk_cases_code, idx_cases_category_id, idx_cases_status, idx_cases_is_featured, idx_cases_status_created (status, created_at), idx_cases_status_featured (status, is_featured, featured_sort)

meta JSON 用于存储不同分类的特有参数。例如：

景观类案例：{"landscape_area": 270, "main_vegetation": "黑松/苔藓", "paving_material": "花岗岩/白砂"}

民宿类案例：{"room_count": 12, "room_note": "12间套房"}

室内类案例：{"main_material": "微水泥/钢构"}

商业类案例：{"building_height": "120m", "function_layout": "办公/商业"}

`case_images` — 案例图库

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`case_id`	BIGINT UNSIGNED	FK → cases
`type`	VARCHAR(20)	NOT NULL	effect / floor_plan / elevation
`url`	VARCHAR(500)	NOT NULL	图片 URL
`thumbnail_url`	VARCHAR(500)	NULL	缩略图 URL
`sort`	INT UNSIGNED	DEFAULT 0
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：idx_case_images_case_type (case_id, type)

`case_files` — 案例文件资产

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`case_id`	BIGINT UNSIGNED	FK → cases
`stage`	VARCHAR(20)	NOT NULL	floor_plan / exterior / construction / structure / interior / courtyard
`file_name`	VARCHAR(200)	NOT NULL	原始文件名
`file_path`	VARCHAR(500)	NOT NULL	存储路径 / OSS key
`file_size`	BIGINT UNSIGNED	NOT NULL	字节数
`file_ext`	VARCHAR(20)	NOT NULL	dwg / pdf / rvt / jpg / png / zip
`mime_type`	VARCHAR(100)	NULL
`uploader_id`	BIGINT UNSIGNED	FK → users	上传者
`sort`	INT UNSIGNED	DEFAULT 0
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：idx_case_files_case_stage (case_id, stage)

stage 枚举值对应 PRD F5.1 的 6 个设计阶段：

floor_plan — 平面阶段

exterior — 外观阶段

construction — 施工图阶段

structure — 结构阶段

interior — 室内阶段

courtyard — 庭院阶段

`case_belongs_tag` — 案例-标签关联

字段	类型	约束
`case_id`	BIGINT UNSIGNED	PK(联合), FK → cases
`tag_id`	BIGINT UNSIGNED	PK(联合), FK → tags

`case_team_members` — 案例主创团队

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`case_id`	BIGINT UNSIGNED	FK → cases
`designer_id`	BIGINT UNSIGNED	FK → designers
`role`	VARCHAR(30)	NOT NULL	scheme / structure / mep (方案/结构/水电)
`is_lead`	TINYINT	DEFAULT 0	是否主创
`sort`	INT UNSIGNED	DEFAULT 0
`created_at`	TIMESTAMP

索引：uk_case_team_case_designer_role (case_id, designer_id, role), idx_case_team_designer_id

4.3 设计师域

`designers` — 设计师档案

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`name_cn`	VARCHAR(50)	NOT NULL	中文姓名
`name_en`	VARCHAR(100)	NULL	英文姓名
`title`	VARCHAR(50)	NULL	职位：方案主创设计师
`title_badge`	VARCHAR(50)	NULL	职位徽章：Lead Architect
`company`	VARCHAR(100)	NULL	所属机构
`city`	VARCHAR(50)	NULL	所在城市
`portrait`	VARCHAR(500)	NULL	个人写真 URL
`portrait_caption`	VARCHAR(100)	NULL	STUDIO PORTRAIT © 2023
`years_of_experience`	TINYINT UNSIGNED	NULL	从业年限
`project_count`	INT UNSIGNED	DEFAULT 0	完成项目数
`design_philosophy_quote`	TEXT	NULL	设计理念引用语
`design_philosophy`	TEXT	NULL	设计理念详述（富文本）
`follower_count`	INT UNSIGNED	DEFAULT 0	关注者数量
`view_count`	INT UNSIGNED	DEFAULT 0	主页浏览量
`is_active`	TINYINT	DEFAULT 1	是否公开展示
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP
`deleted_at`	TIMESTAMP	NULL

索引：idx_designers_is_active

`designer_awards` — 设计师荣誉奖项

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`designer_id`	BIGINT UNSIGNED	FK → designers
`year`	SMALLINT UNSIGNED	NOT NULL	获奖年份
`title`	VARCHAR(200)	NOT NULL	红点设计大奖 Red Dot Award
`description`	VARCHAR(300)	NULL	最佳室内设计类 - The Cloud Villa
`sort`	INT UNSIGNED	DEFAULT 0
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：idx_awards_designer_year (designer_id, year DESC)

`designer_belongs_tag` — 设计师-专长标签

字段	类型	约束
`designer_id`	BIGINT UNSIGNED	PK(联合), FK → designers
`tag_id`	BIGINT UNSIGNED	PK(联合), FK → tags (type=specialty)

4.4 运营内容域

`banners` — 首页轮播

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`title`	VARCHAR(200)	NOT NULL	大标题
`description`	TEXT	NULL	描述摘要
`image`	VARCHAR(500)	NOT NULL	背景大图 URL
`label`	VARCHAR(50)	NULL	标签文字：精选项目
`link_type`	VARCHAR(20)	NOT NULL, DEFAULT 'case'	case / topic / external
`link_url`	VARCHAR(500)	NOT NULL	跳转链接
`sort`	INT UNSIGNED	DEFAULT 0
`is_active`	TINYINT	DEFAULT 1	启用/停用
`published_at`	TIMESTAMP	NULL	展示日期
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：idx_banners_active_sort (is_active, sort)

`topics` — 专题合集

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`title`	VARCHAR(200)	NOT NULL	专题标题
`cover_image`	VARCHAR(500)	NULL	封面图
`description`	TEXT	NULL	富文本描述
`status`	VARCHAR(20)	NOT NULL, DEFAULT 'draft'	draft / published
`case_count`	INT UNSIGNED	DEFAULT 0	关联案例数
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP
`deleted_at`	TIMESTAMP	NULL

`topic_belongs_case` — 专题-案例关联

字段	类型	约束	说明
`topic_id`	BIGINT UNSIGNED	PK(联合), FK → topics
`case_id`	BIGINT UNSIGNED	PK(联合), FK → cases
`sort`	INT UNSIGNED	DEFAULT 0	专题内排序

4.5 用户互动域

`favorites` — 收藏

字段	类型	约束
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`case_id`	BIGINT UNSIGNED	FK → cases
`created_at`	TIMESTAMP

索引：uk_favorites_user_case (user_id, case_id), idx_favorites_case_id

`likes` — 点赞

字段	类型	约束
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`case_id`	BIGINT UNSIGNED	FK → cases
`created_at`	TIMESTAMP

索引：uk_likes_user_case (user_id, case_id), idx_likes_case_id

`comments` — 评论

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`case_id`	BIGINT UNSIGNED	FK → cases
`user_id`	BIGINT UNSIGNED	FK → users
`parent_id`	BIGINT UNSIGNED	NULL, FK → comments	回复嵌套
`content`	TEXT	NOT NULL	评论内容
`status`	VARCHAR(20)	DEFAULT 'pending'	pending / approved / rejected
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP
`deleted_at`	TIMESTAMP	NULL

索引：idx_comments_case_status (case_id, status), idx_comments_user_id, idx_comments_parent_id

`follows` — 关注设计师

字段	类型	约束
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`designer_id`	BIGINT UNSIGNED	FK → designers
`created_at`	TIMESTAMP

索引：uk_follows_user_designer (user_id, designer_id), idx_follows_designer_id

`newsletter_subscriptions` — 邮箱订阅

字段	类型	约束
`id`	BIGINT UNSIGNED	PK
`email`	VARCHAR(200)	NOT NULL, UNIQUE
`created_at`	TIMESTAMP

4.6 日志与系统域

`user_login_logs` — 登录日志

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`login_type`	VARCHAR(20)	NOT NULL	password / sms / qr / wechat / qq / enterprise_sso
`ip`	VARCHAR(45)	NULL
`login_location`	VARCHAR(100)	NULL	登录地理位置（由 IP 库反查填充）
`device_fingerprint`	VARCHAR(128)	NULL	设备指纹
`risk_level`	VARCHAR(10)	NULL	L1 / L2 / L3 / L4
`risk_reason`	VARCHAR(255)	NULL	命中策略摘要
`result`	VARCHAR(20)	NOT NULL, DEFAULT 'success'	success / failed / blocked
`user_agent`	VARCHAR(500)	NULL
`created_at`	TIMESTAMP

索引：idx_login_logs_user_created (user_id, created_at), idx_login_logs_ip_created (ip, created_at)

`user_sessions` — 活跃会话

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`session_token`	VARCHAR(255)	NOT NULL, UNIQUE	Refresh Token 标识（哈希值）
`device_name`	VARCHAR(100)	NULL	设备名称（如 Chrome on macOS）
`device_fingerprint`	VARCHAR(128)	NULL	设备指纹
`ip`	VARCHAR(45)	NULL	登录 IP
`login_location`	VARCHAR(100)	NULL	登录地理位置
`is_current`	TINYINT	NOT NULL, DEFAULT 0	是否为当前请求会话
`last_active_at`	TIMESTAMP	NULL	最近活跃时间
`expires_at`	TIMESTAMP	NOT NULL	会话过期时间
`revoked_at`	TIMESTAMP	NULL	被踢出/注销时间（非空即失效）
`created_at`	TIMESTAMP

索引：idx_sessions_user_active (user_id, revoked_at, expires_at), uk_sessions_token (session_token)

活跃会话表用于 F1.9 多端会话管理。创建会话时写入，Token 刷新时更新 last_active_at，退出/踢出时设置 revoked_at。定期清理过期记录。

`security_risk_events` — 风控事件日志

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	NULL, FK → users	可为空（未登录攻击流量）
`event_type`	VARCHAR(30)	NOT NULL	login / sms_send / register / reset_password / sso_callback
`risk_level`	VARCHAR(10)	NOT NULL	L1 / L2 / L3 / L4
`action`	VARCHAR(30)	NOT NULL	captcha_upgrade / rate_limit / temporary_block / blacklist
`ip`	VARCHAR(45)	NULL
`device_fingerprint`	VARCHAR(128)	NULL
`user_agent`	VARCHAR(500)	NULL
`payload`	JSON	NULL	事件上下文
`created_at`	TIMESTAMP

索引：idx_risk_events_user_created (user_id, created_at), idx_risk_events_level_created (risk_level, created_at), idx_risk_events_ip_created (ip, created_at)

风控事件日志不可编辑、不可删除，无 updated_at 和 deleted_at。

`security_blacklists` — 安全黑名单

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`target_type`	VARCHAR(20)	NOT NULL	ip / device / account
`target_value`	VARCHAR(255)	NOT NULL	IP、设备指纹或账号标识
`reason`	VARCHAR(255)	NULL	拉黑原因
`source`	VARCHAR(20)	NOT NULL, DEFAULT 'system'	system / manual
`status`	VARCHAR(20)	NOT NULL, DEFAULT 'active'	active / released / expired
`expire_at`	TIMESTAMP	NULL	为空表示永久
`created_by`	BIGINT UNSIGNED	NULL, FK → users	手动操作人（系统自动为空）
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：uk_blacklists_target (target_type, target_value), idx_blacklists_status_expire (status, expire_at)

`admin_operation_logs` — 管理端操作日志

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users	操作人
`action`	VARCHAR(30)	NOT NULL	create / update / delete / status_change
`resource_type`	VARCHAR(50)	NOT NULL	case / banner / topic / designer / comment
`resource_id`	BIGINT UNSIGNED	NOT NULL	操作对象 ID
`before_value`	JSON	NULL	变更前值
`after_value`	JSON	NULL	变更后值
`ip`	VARCHAR(45)	NULL
`created_at`	TIMESTAMP

索引：idx_op_logs_user_created (user_id, created_at), idx_op_logs_resource (resource_type, resource_id)

操作日志不可编辑、不可删除，无 updated_at 和 deleted_at。

`download_logs` — 文件下载日志

字段	类型	约束
`id`	BIGINT UNSIGNED	PK
`user_id`	BIGINT UNSIGNED	FK → users
`case_id`	BIGINT UNSIGNED	FK → cases
`case_file_id`	BIGINT UNSIGNED	FK → case_files
`ip`	VARCHAR(45)	NULL
`created_at`	TIMESTAMP

索引：idx_dl_logs_user_created (user_id, created_at), idx_dl_logs_case_id

`system_configs` — 系统配置

字段	类型	约束	说明
`id`	BIGINT UNSIGNED	PK
`group`	VARCHAR(50)	NOT NULL	site / seo / upload / comment / watermark / security
`key`	VARCHAR(100)	NOT NULL
`value`	TEXT	NULL
`created_at`	TIMESTAMP
`updated_at`	TIMESTAMP

索引：uk_configs_group_key (group, key)

5. 索引策略

必须索引

所有外键字段
status 字段
created_at 字段
唯一业务标识（cases.code、tags.type+name、users.phone）

核心复合索引

-- 案例列表查询（分类 + 状态 + 时间）
idx_cases_category_status_created (category_id, status, created_at)

-- 案例精选排序
idx_cases_featured_sort (status, is_featured, featured_sort)

-- 文件按阶段查询
idx_case_files_case_stage (case_id, stage)

-- 评论列表
idx_comments_case_status (case_id, status, created_at)

索引限制

单表索引不超过 6 个
避免冗余索引
定期检查未使用的索引

6. 百万级数据表设计

分页优化

-- 游标分页（案例列表推荐使用）
SELECT * FROM cases WHERE status = 'published' AND id > :last_id ORDER BY id DESC LIMIT 20;

大表 COUNT 优化

-- 使用 categories.case_count / tags.reference_count 缓存计数
-- 使用 cases.view_count / like_count / favorite_count 避免 COUNT JOIN

分区策略（日志表）

-- 管理操作日志
ALTER TABLE admin_operation_logs PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202601 VALUES LESS THAN (202602),
    PARTITION p202602 VALUES LESS THAN (202603),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 用户登录日志（数据量最大，优先分区）
ALTER TABLE user_login_logs PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202601 VALUES LESS THAN (202602),
    PARTITION p202602 VALUES LESS THAN (202603),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 风控事件日志
ALTER TABLE security_risk_events PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202601 VALUES LESS THAN (202602),
    PARTITION p202602 VALUES LESS THAN (202603),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

最后更新: 2026-02-28

29 KiB Raw Permalink Blame History Unescape Escape

Data Model — MySQL 数据建模

1. 设计原则

2. 命名规范

2.1 表命名规则

2.2 通用命名约定

2.3 各模块表名对照

3. ER 关系总览

4. 核心数据表

4.1 用户与权限域

users — 统一用户表

oauth_bindings — 第三方登录绑定

roles — 角色表

menus — 菜单/权限表

user_belongs_role — 用户-角色关联

role_belongs_menu — 角色-菜单关联

4.2 案例核心域

categories — 建筑类型分类

tags — 统一标签字典

cases — 案例主表

case_images — 案例图库

case_files — 案例文件资产

case_belongs_tag — 案例-标签关联

case_team_members — 案例主创团队