SQL 第二篇：表结构设计（为什么企业要拆成 3 张表）

一、前言上一篇我们已经完成了 CRUD。但是你会发现一个问题用户的信息越来越多比如用户名密码手机号邮箱性别生日收货地址默认地址省市区这时候很多初学者会这样干全部塞进 user 表最后 user 表会越来越臃肿。所以这一篇我们正式进入❗ 表结构设计建模这一步才是真正开始接近企业开发。二、企业为什么不能只用一张 user 表很多人最开始会这样设计CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, username VARCHAR(64), password VARCHAR(128), phone VARCHAR(20), email VARCHAR(100), real_name VARCHAR(50), province VARCHAR(50), city VARCHAR(50), detail_address VARCHAR(200) );看起来没问题。但实际上账号信息用户资料收货地址是三种完全不同的数据。三、企业里的真实做法拆表所以企业里通常会拆成user 用户主表user_detail 用户详情表user_address 用户地址表这样设计后结构更清晰更容易扩展更符合业务划分查询性能更稳定四、第一张表user用户主表1. 建表语句CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, username VARCHAR(64) NOT NULL UNIQUE COMMENT 用户名, password VARCHAR(128) NOT NULL COMMENT 密码, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 );2. 这张表存什么这一张表只存登录相关信息比如用户名密码时间它是账号体系核心表五、重点字段讲解企业必须懂1️⃣ id主键id BIGINT PRIMARY KEY AUTO_INCREMENT含义每条数据唯一标识为什么用 BIGINT因为企业数据量可能很大。INT 最大约 21 亿 BIGINT 更安全所以很多企业直接统一主键默认 BIGINTAUTO_INCREMENT 是什么表示主键自动增长插入第一条id 1第二条id 2数据库自动维护。六、username 为什么 UNIQUEusername VARCHAR(64) NOT NULL UNIQUE重点UNIQUE 唯一索引意味着用户名不能重复比如zhangsan只能有一个。否则会报错。七、password 为什么 VARCHAR(128)很多人会问密码不是123456吗 为什么128因为企业里不会直接存明文密码。而是加密后的字符串比如BCrypt MD5 SHA加密后长度会很长。所以一般VARCHAR(128)甚至更大。八、create_time 和 update_time企业高频1️⃣ create_timecreate_time DATETIME DEFAULT CURRENT_TIMESTAMP表示数据创建时间插入时自动生成。2️⃣ update_timeupdate_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP表示数据更新时间每次 UPDATE 自动刷新。九、第二张表user_detail用户详情表1. 建表语句CREATE TABLE user_detail ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL UNIQUE COMMENT 用户ID, real_name VARCHAR(50) COMMENT 真实姓名, phone VARCHAR(20) COMMENT 手机号, email VARCHAR(100) COMMENT 邮箱, gender TINYINT DEFAULT 0 COMMENT 性别0-未知 1-男 2-女, birthday DATE COMMENT 生日, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间 );十、为什么拆出 user_detail因为用户资料 ≠ 登录信息很多业务里登录频率很高但是用户资料很少修改所以企业通常会拆表。十一、这里最关键的字段user_iduser_id BIGINT NOT NULL UNIQUE这个字段是❗ user_detail 属于哪个 user为什么 UNIQUE因为一个用户只有一份详情所以user 1 —— 1 user_detail这就叫一对一关系十二、第三张表user_address用户地址表1. 建表语句CREATE TABLE user_address ( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT 主键ID, user_id BIGINT NOT NULL COMMENT 用户ID, receiver_name VARCHAR(50) NOT NULL COMMENT 收货人姓名, phone VARCHAR(20) NOT NULL COMMENT 收货人电话, province VARCHAR(50) NOT NULL COMMENT 省, city VARCHAR(50) NOT NULL COMMENT 市, district VARCHAR(50) NOT NULL COMMENT 区/县, detail_address VARCHAR(200) COMMENT 详细地址, is_default TINYINT DEFAULT 0 COMMENT 是否默认0-否 1-是, create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新时间, INDEX idx_user_id (user_id) );十三、为什么 user_address 不加 UNIQUE因为一个用户可以有多个地址比如家 公司 父母家所以同一个 user_id 可以出现多次这就是一对多关系十四、为什么加索引INDEX idx_user_id (user_id)因为后面经常会这样查SELECT * FROM user_address WHERE user_id 1;所以user_id 是高频查询字段企业里通常会加索引。十五、三张表关系核心最终关系user 和 user_detail 1对1user 和 user_address 1对多十六、核心这一篇最重要的不是语法。而是 开始理解“为什么企业要拆表” 数据库拆表规则最终笔记版❗ 总原则一张表只做一件事。-----------------------------------怎么判断一张表是不是“做了多件事”① 职责不同账号、资料、地址不是一类数据。② 出现一对多一个用户多个地址、多个订单。③ 字段太多一张表几十上百字段说明职责可能混乱。④ 修改频率不同高频修改字段不要影响核心主表。⑤ 出现大字段富文本、长内容、JSON 单独拆。⑥ 查询场景不同登录、用户中心、收货页关注的数据不同。----------------------------------- 最终本质拆表不是死记规则而是判断这张表是不是还只做一件事----------------------------------- 案例user→ 账号登录user_detail→ 用户资料user_address→ 收货地址账号 ≠ 资料 ≠ 地址所以拆表。十七、一句话总结企业数据库设计本质是按业务职责拆表而不是把所有字段塞进一张表。下一篇下一篇进入SQL 第三篇表关系设计user_id 到底是什么真正讲透一对一 一对多 外键 user_id以及为什么 JOIN 能把表拼起来