做数据库这行十来年我最怕接手的不是那种几百万行的大表而是那种没人说得清为什么这么设计的库。字段名像拼音缩写外键一半靠代码兜着中间表里堆着一堆没人敢删的历史列。往下追根问底十有八九会发现同一个问题当初压根没画 ER 图或者画了但只是交作业时贴进文档里之后就和真实表结构彻底脱节了。ER 图Entity-Relationship Diagram实体-联系图说白了就是数据库设计阶段的施工图。它不写具体字段类型也不管你用 MySQL 还是 Oracle只回答三件事这个系统里有哪些东西实体、这些东西各自有哪些特征属性、它们之间怎么产生关系联系。听起来简单但真正画过教学管理系统 ER 图、银行储蓄系统 ER 图的人都知道难的不是画框框和菱形难的是在要不要把它拆成独立实体这个属性挂在哪张表上这条线该是 1:N 还是 M:N这几个岔路口做对选择。选错一次后面建表、写 SQL、做数据同步的时候就得花十倍时间往回改。这篇内容我按自己实际做项目的顺序来写先从 ER 图在整个设计链路里的位置讲起再拆实体、属性、联系、主键这些核心元素的表示法与判定标准然后重点落在ER 图怎么变成能跑的建表语句这个最容易被卡住的环节最后把从 SQL 反推 ER 图、工具选型、结构变更后的图同步维护、以及课程设计和面试里被追问的高频点一起讲透。不管你是在做数据库课程设计的学生还是刚接手一套老库需要梳理结构的开发或者要准备计算机三级数据库、数据库相关考试的复习下面这些内容都能直接拿去用有对照表、有能跑的脚本、有 DDL 示例也有我踩过的坑。1. ER图在数据库设计链路里到底站哪个位置很多人把 ER 图当成一门考试要考的画图技能实际项目里能不能用上无所谓。这个认知偏差是后面所有混乱的源头。ER 图不是文档装饰品它是需求语言和 SQL 建表语句之间唯一的翻译层缺了它需求里一句一个学生可以选多门课一门课可以被多个学生选就直接跳到建表中间那步M:N 必须拆成中间表、中间表要不要带成绩和学期就全靠个人经验硬猜。1.1 三层数据模型的分工别混着用数据库设计行业里通常把建模分成三层概念模型、逻辑模型、物理模型。三层回答的问题完全不同混在一起画图就会变成谁都看不懂的四不像。层次关注什么典型元素是否绑定具体DBMS概念模型业务里有什么、彼此什么关系实体、属性、联系、基数不绑定逻辑模型主键、外键、规范化程度、索引策略表、列、PK/FK、唯一约束基本不绑定但受范型影响物理模型字段类型、字符集、分区、存储引擎VARCHAR(50)、utf8mb4、InnoDB强绑定概念 ER 图就是给业务方看的里面出现varchar(50)这种字样是错的出现选课记录这种业务名词是对的。逻辑 ER 图是给开发看的要标出主键、外键和中间表。物理模型一般交给建模工具从逻辑模型生成或者直接手写 DDL。我的习惯是概念图和逻辑图分两个文件存概念图用中文业务名词逻辑图用英文表名列名并和 DDL 保持一致。这样业务方评审时不会被stu_no这种字段搞晕开发看逻辑图时也不会被学生这种中文名卡住。1.2 跳过 ER 图直接建表会付出什么代价我经手过一套内部审批系统的重构原始状态是这样的需求方说一个申请单可以有多个附件附件也可能被多个申请单引用。当初的开发直接建了application表和attachment表然后在attachment表里加了一个application_id字段。这个设计把多对多硬压成了一对多后半个需求彻底丢失。上线半年后产品要加附件复用功能才发现历史数据里同一个物理文件被重复上传了上千次硬盘占用翻了三倍改起来还要写数据清洗脚本。这类问题不是个案它有三个稳定的表现形式。第一是关系被降级M:N 被写成 1:N1:N 被写成字段冗余短期能跑长期数据一致性全崩。第二是属性挂错表把课程学分挂在选课记录上而不是课程上导致同一门课不同学生的学分能不一致。第三是主键选错用业务可变的字段做主键比如手机号用户一换号所有关联表的引用全断。这三类问题的修复成本大概是设计阶段多花两个小时画图的三到十倍而且往往伴随着停机迁移。所以我在任何项目里都坚持一件事建表前必须先有一张能让业务方点头的 ER 图哪怕是手绘拍照也行。1.3 ER 图真正的价值是逼你把话说清楚画 ER 图最累的不是画是问。我以前做教务系统的时候追着一个需求方问了三个问题直接把后面三张表的结构改了第一个问题一个学生能不能同时属于两个班级回答是不行于是 学生→班级 是 N:1班级主键可以直接放进学生表当外键。第二个问题一个老师能不能带多个班的同一门课回答是可以于是出现了授课任务这个独立实体而不是把老师直接挂在课程上。第三个问题选课之后成绩是谁录的回答是任课老师于是选课记录和授课任务之间又出现了一条依赖关系。这三个问题如果只在脑子里过一遍很可能得出差不多就行的结论。一旦必须画成图你就必须为每条线选一个基数符号选不出来就说明需求还没想清楚。这就是我常跟新人说的一句话ER 图的价值不在图纸本身在于它强迫你把模糊需求变成明确的、必须二选一的判断。另外ER 图是唯一一份业务方、开发、测试三方都能看懂的中间产物。测试同学拿它设计用例特别顺手每个 M:N 关系至少测三个方向新增、删除中间记录、两端删除时的级联行为。这一点在后面第 5 节的排查清单里我还会展开。2. 实体、属性、联系、主键核心元素怎么判定和表示画图之前先把符号体系搞清楚。ER 图的符号主要分两派陈氏Chen符号和鸦爪Crows Foot符号。国内教材、课程设计、计算机三级数据库里基本都用陈氏符号矩形表示实体、椭圆表示属性、菱形表示联系、线上标 1 或 N工程界用鸦爪符号多因为信息密度高一张 A4 能画下几十张表。元素陈氏符号鸦爪符号常见误区实体矩形表框把订单明细当属性而不是实体属性椭圆表内列把可计算的派生值画成属性主键属性名加下划线列前标 PK复合主键只标一列联系菱形表间连线把外键关系画成实体基数线上标 1/N/M端点鸦爪/短线1:N 和 N:1 方向画反2.1 实体与属性的边界什么时候必须拆表判断一个名词是实体还是属性我的经验标准有三条命中任意一条就拆成实体。第一条它有没有自己的、和主体无关的属性。比如地址如果只需要存一个字符串那是属性但如果要存省市区编码、邮编、联系人、是否默认地址而且一个人可以有多个地址那就是独立的地址实体学生表和地址表是 1:N。第二条它会不会被多个主体共享。比如学院学生属于学院老师也属于学院那就必须独立成实体两边都放外键。如果你把它当成学生的属性写进学生表老师的学院信息就得再抄一份改一次名要改两处。第三条它有没有独立生命周期。比如订单明细它看起来像订单的一部分但它有自己的数量、单价快照、折扣而且要在订单之外被单独查询统计那就必须独立成实体。把它塞成订单表里的 JSON 字段后期做销售分析时会非常痛苦。反过来有几类东西不要画成实体一是纯派生值比如年龄由生日算出来总金额由明细汇总出来画进 ER 图会让图变得臃肿且容易失同步二是纯技术字段比如created_at、updated_at、is_deleted概念图里一般不出现逻辑图里统一加上就行三是枚举型的小字典比如性别除非业务要求可维护比如要支持自定义性别选项并多语言否则直接作为属性。2.2 主键怎么表示以及选主键的四个硬条件ER 图主键怎么表示是搜索量很高的问题说明很多人在这里被卡过。标准做法是在陈氏符号里主键属性名下方画一条实线下划线。如果是复合主键参与主键的每一个属性都要加下划线不是只标第一个。有些教材还会用虚线表示候选键唯一但非主键用浅色椭圆表示可空属性这些属于加分项不是必须。在鸦爪符号或者逻辑 ER 图里更常见的做法是在列名后标 PK或者用加粗、加锁图标外键标 FK。复合主键就在多列上分别标 PK并在表级说明里写清楚列顺序。提示复合主键的列顺序在真实数据库里会影响索引可用性画图时最好在旁边备注顺序否则建表的人很容易按字母顺序建导致最左前缀原则用不上。选主键这件事我给的标准是四个条件同时满足才可以用值不为空、值全局唯一、值基本不变、值尽量短。能满足这四条的现实中很少。所以我在生产项目里几乎都用两类主键一是无意义自增整数或分布式 IDBIGINT UNSIGNED AUTO_INCREMENT、雪花 ID、UUID 转二进制二是天然稳定且短的业务码比如学号、组织机构编码。学号这类业务码要不要当主键我的做法是在概念图里把学号标为主键因为它在业务语义上确实是标识符在逻辑图和物理表里用自增 ID 当主键学号加唯一索引。这样业务方看到的图和数据库实际结构略有差异但我会在图注里写清楚业务主键 vs 物理主键的映射避免评审时产生误解。2.3 联系的基数、参与度以及最容易画反的地方基数Cardinality表示一个实体实例能关联多少个另一端的实例。1:1、1:N、M:N 这三种说法大家都熟但实际画图时错得最多的是方向和参与度。方向问题学生 N —— 属于 —— 1 班级这条线上靠近学生端标 N靠近班级端标 1。很多人习惯在中间菱形两侧都写 N 和 1但不写清楚哪边对哪边导致后面建表时把外键放错表。我的规矩是外键总是放在多的那一端画图时在多的一端线上标 N并顺手标注FK→指向一端的主键。参与度Participation分全参与和部分参与用双线或单线区分。全参与的意思是每个实例都必须参与这条联系。比如选课记录必须属于某个学生那么从选课记录看学生是全参与反过来学生必须选课吗如果允许一个新生还没选课那就是部分参与。参与度直接影响建表时的外键能不能为 NULL以及要不要写触发器或者应用层校验。基数组合外键放哪里是否可为空典型场景1:1任一端选参与度全的一端取决于参与度用户与其实名认证信息1:N放在 N 端N 端全参与则非空班级与学生M:N新建中间表两端都非空学生与课程还有一个高频疑问多对多联系能不能带属性能而且经常必须带。选课这个 M:N 联系上就挂着成绩学期选课时间三个属性。在陈氏符号里这些属性的椭圆直接连到菱形上而不是连到学生或课程的矩形上。这个画法很多人做错把成绩连到学生上结果一门课只能有一个成绩。我建议画图时养成一个习惯凡是只有在两个东西同时存在时才有意义的属性一律挂到菱形上。2.4 弱实体与依赖关系什么时候实体不能独立存在弱实体是必须依赖另一个实体才能存在的实体陈氏符号里用双线矩形表示联系用双线菱形。典型例子是订单明细依赖订单交易流水依赖账户。弱实体的主键通常是所属实体主键 局部键的组合在图上把所属实体的主键作为虚线或标记词部分键加上下划线。这里有个坑我踩过弱实体的标识符不要用自增 ID 的思维去理解。订单明细的主键在业务上其实是(order_id, line_no)line_no是明细在订单里的序号。如果图里只标一个自增 ID 当主键业务方根本看不出同一订单里明细序号不能重复这条规则等到数据出问题了才回头补唯一约束。对应的两个概念还要区分清楚依赖联系Identifying Relationship父实体删除时子实体随之删除和非依赖联系Non-identifying父删除时子可以保留只是外键置空或受限。在鸦爪符号里前者用实线后者用虚线。这个区分不是学术洁癖它直接决定你建表时ON DELETE写CASCADE还是RESTRICT。银行流水这种数据绝对不能级联删除写了CASCADE就等于给自己埋一颗定时炸弹。3. 从ER图落到建表语句转换规则与可直接抄的DDL图画得再漂亮最后要落地成 DDL。这中间的转换规则是有明确定义的但很多教学材料只讲结论不讲理由导致照着画没问题、自己想就出错。这一节我按规则为什么真实 SQL来写示例统一用教学管理系统因为它同时包含了 1:N、M:N 和分类继承三种情况。3.1 一对一和一对多外键放哪、要不要建唯一索引一对一1:1的转换有两种做法外键法把一端主键放到另一端当外键并加唯一约束和共享主键法两张表共用同一个主键值。我一般选共享主键法因为省一个字段而且天然保证唯一。典型场景是用户和用户实名认证信息认证信息表的user_id既是主键又是外键。一对多1:N的规则很简单把一端的主键作为外键放到多端的表里。这里有两个容易忽略的点。第一外键列必须建索引MySQL 在建外键时会自动建但如果业务上经常按这个外键过滤最好显式建一个复合索引比如(class_id, name)。第二如果多端是全参与外键列要写NOT NULL这比在应用层校验靠谱得多。-- 班级表一端 CREATE TABLE class ( class_id INT UNSIGNED NOT NULL AUTO_INCREMENT, class_name VARCHAR(60) NOT NULL, grade_year SMALLINT NOT NULL COMMENT 入学年份, major_id INT UNSIGNED NOT NULL, PRIMARY KEY (class_id), UNIQUE KEY uk_class_name (class_name), KEY idx_major (major_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 学生表多端class_id 是外键 CREATE TABLE student ( student_id CHAR(10) NOT NULL COMMENT 学号业务主键, id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 物理主键, name VARCHAR(50) NOT NULL, gender CHAR(1) NOT NULL DEFAULT M, birth_date DATE NULL, class_id INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_student_no (student_id), KEY idx_class_name (class_id, name), CONSTRAINT fk_student_class FOREIGN KEY (class_id) REFERENCES class (class_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci;注意上面这个处理业务主键student_id加了唯一索引而不是主键物理主键用自增。这是我前面说的概念图业务主键、物理表代理主键的落法。唯一索引必须加否则学号重复了数据库不会拦你。3.2 多对多的中间表三个必须加的字段和两个必须建的索引M:N 联系必须拆成中间表这是最没有争议的规则。但中间表怎么建细节很多。我把必须做的事列成清单两端外键列都要有且都NOT NULL。如果联系本身有属性成绩、学期、数量就作为中间表的普通列。如果联系是弱实体性质比如选课记录在学期内唯一主键用(端1, 端2, 附加维度)复合主键而不是自增 ID 加一个唯一索引——复合主键本身就能拦重复成本更低。两个方向都要能查所以除复合主键外的另一端要单独建索引否则查某门课所有学生会走全表扫描。加created_at记录写入时间后面做数据同步、增量抽取、审计都用得上。-- 课程表 CREATE TABLE course ( course_id INT UNSIGNED NOT NULL AUTO_INCREMENT, course_code VARCHAR(20) NOT NULL COMMENT 课程号, course_name VARCHAR(80) NOT NULL, credit DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT 学分, PRIMARY KEY (course_id), UNIQUE KEY uk_course_code (course_code) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 选课记录M:N 拆出来的中间表主键是复合主键 CREATE TABLE course_selection ( student_id CHAR(10) NOT NULL, course_id INT UNSIGNED NOT NULL, term VARCHAR(12) NOT NULL COMMENT 学期如 2024-2025-1, score DECIMAL(5,1) NULL COMMENT 成绩允许为空表示未录入, selected_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (student_id, course_id, term), KEY idx_course_term (course_id, term), KEY idx_student_term (student_id, term), CONSTRAINT fk_cs_student FOREIGN KEY (student_id) REFERENCES student (student_id), CONSTRAINT fk_cs_course FOREIGN KEY (course_id) REFERENCES course (course_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有个真实教训。早期我建的选课表主键是(student_id, course_id)没带学期。结果遇到重修场景同一学生同一门课第二次选课直接主键冲突。后来加了term字段才解决但老数据里已经有几千条被INSERT IGNORE静默丢掉的记录只能靠日志回捞。这件事之后我在任何看起来两端就够了的中间表上都会多问一句会不会有时间维度或者状态维度上的重复3.3 分类与继承怎么落三种策略的取舍教学管理系统里常有学生和教师都是人员的情况或者账户下面分储蓄账户信用卡账户。这类继承关系在关系数据库里有三种落地方式各有取舍。策略表结构优点缺点适用场景单表继承一张表加类型字段子类专属列可空查询简单、无 JOIN列数膨胀、无法加非空约束子类差异小类表继承父表存公共列每个子类一张表存专属列约束完整、结构清晰查询要 JOIN、写入要事务子类差异大且字段多具体表继承每个子类一张独立表各自含公共列单表查询快公共列重复、跨类型查询要 UNION子类之间几乎不联查我做过一个银行储蓄系统 ER 的梳理账户体系用的是类表继承account存账号、余额、状态、开户网点deposit_account存存期、利率、到期日credit_account存额度、账单日。这么设计的原因是储蓄和信用卡的字段差异太大塞一张表里会有十几个长期为空的列而且监管报表要按类型分别汇总分开查更清晰。反过来说如果只是个人客户和企业客户这种只有三四个字段差异的我就直接单表加类型字段省掉无谓的 JOIN。选择策略的判断标准我给三个子类间差异列占全部列的比例超过 40% 就用类表继承跨类型查询频率高就用单表继承子类之间几乎不联查就用具体表继承。这不是硬规定但能覆盖八成场景。4. 工具选型与实操怎么画以及怎么从现有SQL反推ER图前面讲的都是应该怎么做这一节讲用什么做。实际工作里ER 图的需求分成两类新项目从零画老项目从已有的 SQL 反推。这两类需求适合的工具完全不同很多人用错工具导致效率极低。4.1 三类工具对比手绘、在线建模、专业建模工具类型代表上手成本适合场景主要短板手绘/白板纸笔、白板、通用画图工具极低需求讨论、课程设计初稿无法生成 DDL、无法版本化在线建模dbdiagram 类在线工具、各类可视化建模平台低中小项目、需要共享链接评审大图卡顿、复杂约束表达弱专业建模各类桌面建模工具、数据库自带设计器高企业级项目、多数据库适配学习曲线陡、界面偏重面向代码文本化建模PlantUML 等中需要纳入 Git 版本管理布局不直观改完要重新渲染我现在的组合是这样的需求评审阶段用在线工具画一张概念图改起来快业务方能直接点开链接看进入开发后把逻辑模型写成文本化建模脚本和 DDL 一起进 Git 仓库每次评审都对着 diff 看物理层直接用数据库自带的工具反向导出保证图和线上表结构一致。这里特别说一下文本化建模的价值。我维护过一个有八十多张表的系统前期用图形工具画每次改结构都要重新打开软件、拖动布局、导出图片、上传文档改一次至少二十分钟大家慢慢就懒得改了半年后图和实际结构差了十几张表。换成文本脚本之后改一行文本、提交一次 Git图自动重新生成成本降到一分钟图纸再也没失同步过。PlantUML 的 ER 语法大致是这样我给出一个片段做示范startuml entity 学生 as student { * student_id : CHAR(10) PK -- name : VARCHAR(50) gender : CHAR(1) class_id : INT FK } entity 课程 as course { * course_id : INT PK -- course_code : VARCHAR(20) UK credit : DECIMAL(3,1) } entity 选课记录 as cs { * student_id : CHAR(10) PK,FK * course_id : INT PK,FK * term : VARCHAR(12) PK -- score : DECIMAL(5,1) } student ||--o{ cs course ||--o{ cs enduml这段文本可以直接被渲染工具转成图配合 CI 就能做到DDL 提交后图自动更新。4.2 从已有 SQL 反推MySQL 导出 ER 关系的两条路径接手老系统时最需要的就是从 SQL 生成 ER 图。MySQL 上有两条路我按适用规模分开讲。路径一是用图形客户端自带的反向工程功能。主流客户端基本都支持连接数据库后自动扫描表和约束生成图优点是一键、直观缺点是表超过五十张之后自动布局会乱成一团需要手工整理而且图不容易版本化。路径二是直接查information_schema自己出图。这条路可控性最强能精确控制哪些表参与、哪些约束要显示。核心是三个视图TABLES拿表和注释COLUMNS拿列和类型KEY_COLUMN_USAGE拿主键、唯一键和外键。下面这段 SQL 是我常用的外键清单查询直接跑就能看出哪张表的哪一列指向了谁。SELECT kcu.TABLE_NAME AS child_table, kcu.COLUMN_NAME AS child_col, kcu.CONSTRAINT_NAME AS fk_name, kcu.REFERENCED_TABLE_NAME AS parent_table, kcu.REFERENCED_COLUMN_NAME AS parent_col, rc.DELETE_RULE AS on_delete, rc.UPDATE_RULE AS on_update FROM information_schema.KEY_COLUMN_USAGE kcu JOIN information_schema.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_SCHEMA kcu.TABLE_SCHEMA AND rc.CONSTRAINT_NAME kcu.CONSTRAINT_NAME WHERE kcu.TABLE_SCHEMA your_db_name AND kcu.REFERENCED_TABLE_NAME IS NOT NULL ORDER BY kcu.TABLE_NAME, kcu.CONSTRAINT_NAME, kcu.ORDINAL_POSITION;顺手再补一个找主键和唯一键的查询画图时用得上SELECT tc.TABLE_NAME, tc.CONSTRAINT_TYPE, -- PRIMARY KEY / UNIQUE / FOREIGN KEY kcu.COLUMN_NAME, kcu.ORDINAL_POSITION FROM information_schema.TABLE_CONSTRAINTS tc JOIN information_schema.KEY_COLUMN_USAGE kcu ON kcu.CONSTRAINT_SCHEMA tc.CONSTRAINT_SCHEMA AND kcu.CONSTRAINT_NAME tc.CONSTRAINT_NAME AND kcu.TABLE_NAME tc.TABLE_NAME WHERE tc.TABLE_SCHEMA your_db_name AND tc.CONSTRAINT_TYPE IN (PRIMARY KEY,UNIQUE) ORDER BY tc.TABLE_NAME, tc.CONSTRAINT_TYPE, kcu.ORDINAL_POSITION;注意information_schema里的表名和列名在不同数据库产品上大小写行为不一致。Oracle 里对象名默认大写MySQL 在 Linux 下受lower_case_table_names影响。脚本里统一加UPPER()或者统一转小写再比对能避免明明有表却匹配不上这种莫名其妙的空结果。4.3 一个能跑的小脚本把表结构直接转成建模文本上面两条查询的结果手工抄进图里还是很累。我写过一个几十行的 Python 脚本查完直接输出建模文本文件再交给渲染工具。核心逻辑就是把外键关系和主键信息拼成文本代码不长但很实用。import pymysql DB your_db_name conn pymysql.connect(host127.0.0.1, port3306, userreadonly, passwordyour_password, databaseDB, charsetutf8mb4) cols_sql SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_COMMENT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA %s ORDER BY TABLE_NAME, ORDINAL_POSITION fk_sql SELECT TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA %s AND REFERENCED_TABLE_NAME IS NOT NULL with conn.cursor() as cur: cur.execute(cols_sql, (DB,)) cols cur.fetchall() cur.execute(fk_sql, (DB,)) fks cur.fetchall() fk_map {(t, c): (pt, pc) for t, c, pt, pc in fks} tables {} for t, c, ctype, nullable, key, comment in cols: tables.setdefault(t, []).append((c, ctype, nullable, key, comment)) lines [startuml, hide circle, skinparam linetype ortho] for t, cs in tables.items(): lines.append(fentity {t} as {t} {{) for c, ctype, nullable, key, comment in cs: if key PRI: mark PK elif key UNI: mark UK else: mark if (t, c) in fk_map: mark (mark ,FK) if mark else FK lines.append(f {* if key PRI else } {c} : {ctype}{mark}) lines.append(}) lines.append() for t, c, pt, pc in fks: lines.append(f{pt} ||--o{{ {t}) lines.append(enduml) open(er.puml, w, encodingutf-8).write(\n.join(lines)) print(已生成 er.puml共, len(tables), 张表)这个脚本我拿一个 63 张表的库试过生成的文本渲染出来结构基本正确需要人工干预的主要是两处一是布局可以调linetype参数二是某些明显是历史遗留但没建外键的关联需要手工补线。这两处正好是需要人判断的地方交给脚本反而危险。提示用只读账号跑这类脚本不要用业务账号。另外密码不要硬编码在脚本里读环境变量传进去我见过把带密码的脚本提交到代码仓库导致事故的案例。4.4 银行储蓄系统 ER 拆解一个典型的强约束场景拿银行储蓄系统练手特别合适因为它同时具备弱实体、M:N、严格的外键约束三个特征。我给一个我梳理过的精简模型帮助你理解这类系统 ER 图的画法。核心实体有五组客户、账户、卡、交易流水、机构网点。关系是这样的客户与账户是 M:N支持联名账户账户与卡是 1:N一个账户可以挂多张卡账户与交易流水是 1:N 且交易流水是弱实体流水必须依附账户存在机构与账户是 1:N账户归属开户机构机构与柜员是 1:N。几个设计决策值得说。第一联名账户必须用中间表customer_account并且在这个中间表上挂持卡关系类型主卡人、附属人和生效日期。直接在一个表上放两个客户 ID 字段是不行的因为联名人数可能超过两个。第二交易流水表的主键我建议用(account_id, txn_seq)或者独立的流水号加唯一索引ON DELETE必须是RESTRICT绝对不能CASCADE——删一个账户把十年流水全删掉这在任何金融场景里都是重大事故。第三金额字段一律用DECIMAL(18,2)或者按分存整数BIGINT不要用FLOAT/DOUBLE浮点误差在金融场景里是硬伤我之前见过对账差一分钱查了三天的案子根源就是金额字段用了双精度。-- 交易流水弱实体严格禁止级联删除 CREATE TABLE txn_flow ( account_id BIGINT UNSIGNED NOT NULL, txn_seq INT UNSIGNED NOT NULL COMMENT 账户内流水序号, txn_time DATETIME(3) NOT NULL, direction TINYINT NOT NULL COMMENT 1借 2贷, amount DECIMAL(18,2) NOT NULL, balance DECIMAL(18,2) NOT NULL COMMENT 交易后余额快照, remark VARCHAR(200) NULL, PRIMARY KEY (account_id, txn_seq), KEY idx_txn_time (txn_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; ALTER TABLE txn_flow ADD CONSTRAINT fk_txn_account FOREIGN KEY (account_id) REFERENCES account (account_id) ON DELETE RESTRICT ON UPDATE RESTRICT;余额快照这一列是很多初学者会漏掉的。它属于派生数据理论上可以由流水累加算出来但在对账场景里必须有快照否则一旦有流水缺失或者补录余额就永远对不上。这里就体现了一个原则ER 图里不画派生属性但物理表里为了性能和可追溯性经常要冗余派生列。5. 常见问题与排查技巧实录图会画了脚本也能跑了接下来是真实工程里最容易卡住的部分。这一节的问题都是我实际被问过或者亲身踩过的按发生频率排序。5.1 画完图建表报错五个高频坑第一坑是保留字。表名用order、group、desc、key这类词MySQL 直接报语法错误。我在教务系统里见过有人建了class表在 MySQL 里能跑但某些数据库产品上会出问题。规范做法是加前缀统一命名比如t_order、sys_group或者用反引号包起来——但反引号只在 MySQL 有效跨库迁移时又会炸。第二坑是字符集和排序规则不一致导致外键建不上。父表和子表的字符集、排序规则必须完全一致utf8mb4_general_ci和utf8mb4_0900_ai_ci混用就会报 3780 错误。我的做法是整个库统一utf8mb4 一种排序规则写在建库语句里所有表继承默认值。第三坑是唯一约束已经有重复数据。给老表加唯一索引时提示Duplicate entry说明历史数据里已经有重复值。处理顺序是先查重复GROUP BY ... HAVING COUNT(*) 1再决定是清洗、合并还是改用普通索引。千万别直接删数据我处理过一个案例重复的客户手机号里有一半是真正的不同客户只是手机号被填成了同一个客服电话。第四坑是外键字段类型不匹配。父表主键是INT UNSIGNED子表外键写成INT有符号类型不完全一致时外键创建会失败。这种错误信息很隐晦报的往往是无法创建外键约束需要你去对比SHOW CREATE TABLE的输出才能发现。第五坑是级联规则写错。默认是RESTRICT有人为了方便测试删除改成CASCADE结果测试环境的数据全被连锁删除。我的规矩是任何CASCADE都要在评审时单独提出来说明理由否则一律RESTRICT。报错现象大概率原因排查指令语法错误表名/列名命中保留字SHOW CREATE TABLE t看是否需要转义3780 外键字符集不一致排序规则不统一SHOW TABLE STATUS LIKE t看 CollationDuplicate entry已有重复数据SELECT c, COUNT(*) FROM t GROUP BY c HAVING COUNT(*) 1无法创建外键约束类型或引擎不一致对比SHOW CREATE TABLE两端输出数据被误删ON DELETE CASCADE查information_schema.REFERENTIAL_CONSTRAINTS5.2 表结构变更之后怎么让ER图不失效这是最容易被忽略的问题ER 图的价值随时间衰减。上线三个月后加了三张表、改了五个字段图没更新之后新人看到的就是一张假图比没有图更危险因为他会基于错误信息做判断。我的做法是把图纳入版本管理和数据库迁移脚本放在同一个目录用同一次提交修改。具体来说所有 DDL 变更走迁移脚本哪怕是手写的 SQL 文件变更提交时必须同步更新文本化建模文件在 CI 里加一步对比——建模文件里的表结构哈希和从测试库反向导出的哈希不一致就报错。听起来有点重但实现成本很低一个脚本加一条流水线规则就够了。对于已经维护了很多年的系统一步到位不现实。我建议的做法是先做一次全量对齐用第 4.3 节的脚本从生产库导出建模文本作为基线提交之后只维护这之后的变化历史的差异标注在文件头部注明基线建立于某日期此前历史差异未追溯。这样至少能保证增量部分是准确的。还有一个场景是数据库结构同步工具的使用。这类工具在多个环境开发、测试、预发、生产之间同步结构时很方便但用之前一定先看它生成的差异 SQL。我见过工具生成DROP COLUMN直接在生产执行的情况原因是两边列顺序不同导致比对错位。用同步工具的稳妥流程是先在预发环境跑一次人工审阅生成的 SQL确认没有破坏性语句DROP、TRUNCATE、类型收窄再上生产并且执行前做一次结构备份。5.3 常见问题速查表把上面这些整理成一张表出问题时直接对照。问题判断方法处理建议1:N 还是 M:N 分不清问一端能不能对应多个另一端 双向问双向都问一遍任一方向为多就是 M:N中间表要不要自增 ID看是否有第三维度会重复有天然复合键就用复合主键别加多余自增主键要不要用业务字段看值会不会变会变就加代理主键 业务列唯一索引外键要不要建看是否强一致要求核心交易表必建日志类可只在应用层保证图太大画不下表数超过 30 张按业务域拆成多张子图 一张全局概览图循环依赖外键建不上A 依赖 B、B 依赖 A拆出一个中间表打破循环或延后其中一条外键历史库没外键怎么推图靠命名规律xx_id猜结合代码里的 JOIN 语句验证最后一行值得展开说。老库里没建外键是常态尤其是互联网业务。这时候推 ER 图的办法是拿代码里的 SQL 做数据挖掘把mapper文件、ORM 注解、存储过程里的 JOIN 条件全提出来统计a.xxx_id b.id这类连接出现的频率出现次数高的基本就是真实关系。我用这个方法梳理过一个完全没有外键的订单系统二十多张表的关系基本猜对了只有一张历史归档表判断错误后来人工确认补上了。5.4 课程设计和面试里被反复追问的点如果你是在做数据库课程设计或者准备数据库相关考试和面试下面这几个问题被问到的概率极高我把答题要点也一并写上。ER 图里的主键怎么表示——答属性名加下划线复合主键每个参与属性都要加下划线候选键用虚线外键在逻辑图里用 FK 标注。一个联系能不能有多条线连到同一个实体——能这叫自反联系递归联系比如员工与员工的上下级关系或者课程与课程的先修关系。自反联系建表时外键指向自己的主键比如prerequisite_course_id指向course_id。多值属性和复合属性怎么处理——多值属性比如一个人多个手机号必须拆成独立表不能塞在一列里用逗号分隔。复合属性比如地址拆成省市区看使用方式需要单独查询和统计就拆列只是展示用就整存。ER 图和关系模式的转换有几步——四步实体转表、1:1 和 1:N 加外键、M:N 建中间表、多值属性拆表。把这句话背熟基本能应对大部分笔试和面试。还有一个常被追问的点是范式。第三范式要求非主属性不传递依赖于主键。选课记录里如果放课程名就违反了 3NF因为课程名依赖课程号课程号依赖主键形成传递依赖。图里怎么体现就是课程名这个属性必须挂在课程实体上不能挂在选课这个菱形上。这也是检查 ER 图是否规范化的一个快捷方法看每个非主属性是不是只依赖它所属实体的主键。6. 交付前我会走的检查清单和几个省时间的做法一张 ER 图最终要交付给团队或者作为课程设计成果交付前我会自己过一遍检查清单。这份清单是我被评审打回好几次之后总结出来的。6.1 交付前的十二条自查每个实体都有明确的主键复合主键标全了下划线。每条连线的两端基数都标了没有出现两端都是 1 或者两端都是 N 却没说明的。所有 M:N 联系都对应一个中间表中间表在逻辑图里出现。中间表上挂的联系属性在该在的位置没有挂到实体上。弱实体用双线矩形或明确的标识符标注依赖联系的删除规则写清楚。自反联系画出来了并且标注了方向上下级、先修关系。派生属性没有出现在概念图里或者在图上标注了派生。多值属性都拆成了独立实体或独立表。命名统一表名用单数还是复数、前缀规则、字段名风格下划线全库一致。每个外键列都有索引在逻辑图里标注。字符集、排序规则、存储引擎在建库层级统一声明。图有版本号和最后更新时间和当前迁移脚本版本对应。第十二条最容易被跳过但我认为它最重要。一个没写日期的 ER 图三个月后就没人知道它对应哪个版本也没人敢改。6.2 几个能省大量时间的小技巧第一个技巧是画图顺序倒过来。很多人从实体开始画画到一半发现关系理不清。我的做法是先画联系把业务里所有的动词列出来——选课、授课、属于、打卡、支付、退款——每个动词就是一个联系然后问这个动词连接哪两个名词最后才补实体的属性。这个方法能显著减少漏掉关系的情况因为业务需求里动词的数量通常远少于名词但关系才是容易漏的部分。第二个技巧是给每张子图配一句业务描述。这张图描述学生从入学到毕业的学籍流转比教务模块 ER 图有用得多评审时业务方一眼就知道该看什么也更容易发现遗漏。第三个技巧是保留一版手绘草稿。我习惯把最初的需求讨论草稿拍照存进项目文档标注日期。项目后期出现当初怎么定的这类争议时这份草稿往往比正式文档更有说服力因为它记录了双方当场确认的原始信息。第四个技巧是用真实数据抽样验证。图设计完成、表建好之后灌一批接近真实的测试数据比如一万条选课记录、三千个学生跑几个典型查询看执行计划。有几个设计问题只有数据上量后才会暴露中间表缺索引导致全表扫描、外键列选择性差、复合主键顺序不合理导致范围查询用不上索引。这些问题在空表上做测试永远发现不了我踩过太多次现在凡是涉及中间表的改动都会灌数据跑一遍EXPLAIN。我自己这些年最大的体会是ER 图这件事上花的时间从来不会浪费。我见过为了赶进度跳过设计、两周建了四十张表的项目上线三个月后花了两个月重构原因就是关系设计错了导致数据对不上。也见过老老实实画了三天图、被业务方打回两次的项目后面两年加功能都很顺因为每次改动都能在图上找到位置。图省事和真省事在数据库这一行往往是反义词。后面如果你要把这套流程继续往深里走我建议的方向是补上数据字典和字段级血缘这两层让 ER 图不只是关系图而是能一路追到报表字段的完整链路那时候它才真正变成一个团队的资产而不只是某次设计的产出物。
