第四章 数据库设计与创建：从需求到SQL落地

好，咱们进入实战环节的第一个硬骨头——数据库设计。

说实话，很多新手容易犯一个毛病：上来就写SQL，表结构想到哪写到哪。结果项目做到一半，发现字段不够用，关联关系乱成一锅粥。我早期带团队时就吃过这个亏，后来学乖了——先做需求分析，再画ER图，最后才动手建表。这个顺序，千万别搞反。

4.1 需求分析：搞清楚你要存什么

做数据库设计，第一步不是打开MySQL，而是拿张纸，把业务场景捋清楚。

咱们这个电商项目，核心就两个实体：用户和商品。你想想看，用户要注册、登录、修改个人信息；商品要上架、下架、展示详情。这些操作背后，都需要数据库来支撑。

具体来说，用户模块需要记录：

  • 用户的唯一标识（用户ID）
  • 登录凭证（用户名、密码）
  • 联系方式（手机号、邮箱）
  • 个人资料（昵称、头像、性别、生日）
  • 账户状态（是否激活、注册时间）

商品模块则需要：

  • 商品的唯一标识（商品ID）
  • 基本信息（名称、描述、价格、库存）
  • 分类信息（所属类目）
  • 图片和详情（主图、轮播图、富文本描述）
  • 上下架状态、创建时间等
我的经验：需求分析阶段，我习惯用Excel列一个字段清单。每个字段写上名称、类型、长度、是否必填、默认值。这样后面写SQL时，基本不会漏字段。

4.2 ER图设计：把关系画出来

需求理清了，接下来画ER图。ER图说白了就是实体关系图，它帮你把数据之间的关联可视化。

咱们这个项目，用户和商品之间是什么关系？

  • 一个用户可以拥有多个商品（卖家视角）
  • 一个商品只能属于一个用户（卖家）

所以，这是典型的一对多关系。在ER图中，我们用菱形表示关系，用直线连接实体。

我画ER图时有个习惯：先画核心实体，再画关联实体。比如用户和商品是核心，订单、购物车这些是后续章节再扩展的。别一口气想画全，容易乱。

ER图核心要素：

  • 矩形：代表实体（如用户、商品）
  • 椭圆：代表属性（如用户名、价格）
  • 菱形：代表关系（如拥有、属于）
  • 连线：标明1对1、1对N、N对N

嗯，这里要注意：主键属性我习惯在椭圆下面加下划线，外键属性用虚线椭圆表示。这个约定在团队协作中特别有用，一看就知道哪个字段是关联用的。

4.3 MySQL数据库创建：动手建库

ER图画好了，咱们开始写SQL。第一步，创建数据库。

我个人习惯给数据库命名时用项目缩写加下划线。比如这个电商项目，就叫 shop_db。字符集选 utf8mb4，排序规则用 utf8mb4_general_ci。为什么？因为 utf8mb4 支持emoji，你想想看，用户昵称里带个😊，如果用 utf8 就存不进去了。

-- 创建数据库
CREATE DATABASE IF NOT EXISTS shop_db
  DEFAULT CHARACTER SET utf8mb4
  DEFAULT COLLATE utf8mb4_general_ci;

-- 使用数据库
USE shop_db;
避坑指南：我曾经在项目上线后才发现字符集用的是latin1，结果用户输入中文直接变乱码。那次教训让我养成了建库时必设字符集的习惯。千万别偷懒用默认值！

4.4 用户表SQL：从字段到约束

数据库建好了，接下来创建用户表。表名我习惯用 t_ 前缀，后面跟业务名。用户表就叫 t_user。

字段设计上，有几个点要特别注意：

  • id：用BIGINT，自增主键。别用INT，用户量大了会溢出
  • username：唯一索引，长度50就够了
  • password：存加密后的密文，长度至少128（我用的是bcrypt）
  • create_time：用datetime类型，别用timestamp（有2038年问题）
CREATE TABLE t_user (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL COMMENT '用户名',
  password VARCHAR(128) NOT NULL COMMENT '密码（加密后）',
  nickname VARCHAR(50) DEFAULT NULL COMMENT '昵称',
  phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
  email VARCHAR(100) DEFAULT NULL COMMENT '邮箱',
  avatar VARCHAR(255) DEFAULT NULL COMMENT '头像URL',
  gender TINYINT DEFAULT 0 COMMENT '性别：0未知 1男 2女',
  birthday DATE DEFAULT NULL COMMENT '生日',
  status TINYINT DEFAULT 1 COMMENT '状态：1正常 0禁用',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  UNIQUE KEY uk_username (username),
  UNIQUE KEY uk_phone (phone),
  KEY idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
我的习惯：每个字段都加COMMENT注释。这样以后查表结构时，直接 SHOW CREATE TABLE 就能看到字段含义，不用翻文档。另外，update_time用 ON UPDATE CURRENT_TIMESTAMP 可以自动记录修改时间，省心。

4.5 商品表SQL：关联与索引

商品表稍微复杂一点，因为它要关联用户表。外键字段 user_id 指向 t_user.id。

商品表的核心字段：

  • id：BIGINT自增主键
  • user_id：外键，关联卖家
  • title：商品标题，长度200
  • price：用DECIMAL(10,2)，精确到分
  • stock：库存量，INT类型
  • status：上下架状态，0下架 1上架
CREATE TABLE t_product (
  id BIGINT NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  user_id BIGINT NOT NULL COMMENT '卖家用户ID',
  title VARCHAR(200) NOT NULL COMMENT '商品标题',
  description TEXT COMMENT '商品描述',
  price DECIMAL(10,2) NOT NULL COMMENT '价格（元）',
  stock INT NOT NULL DEFAULT 0 COMMENT '库存数量',
  category_id INT DEFAULT NULL COMMENT '分类ID',
  main_image VARCHAR(255) DEFAULT NULL COMMENT '主图URL',
  images JSON DEFAULT NULL COMMENT '轮播图URL列表（JSON数组）',
  status TINYINT DEFAULT 0 COMMENT '状态：0下架 1上架',
  sales_count INT DEFAULT 0 COMMENT '销量',
  create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id),
  KEY idx_user_id (user_id),
  KEY idx_category_id (category_id),
  KEY idx_status_create (status, create_time),
  CONSTRAINT fk_product_user FOREIGN KEY (user_id) REFERENCES t_user(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

索引设计要点：

  • idx_user_id：按卖家查询商品时用到
  • idx_category_id：按分类筛选时用到
  • idx_status_create：联合索引，按上架状态和时间排序展示

索引不是越多越好。我见过有人给每个字段都加索引，结果写入性能暴跌。记住：索引是给查询用的，不是装饰品。

4.6 避坑总结：数据库设计三不要

最后，分享三个我踩过的坑：

  1. 不要用中文做字段名——虽然MySQL支持，但后续ORM映射、代码可读性都会出问题。老老实实用英文小写加下划线。
  2. 不要忽略外键约束——有些人为了性能不用外键，结果数据出现孤儿记录。我建议小项目还是加上，数据一致性比那点性能损失重要得多。
  3. 不要用TEXT存大字段——商品描述用TEXT没问题，但如果存富文本HTML，建议用 MEDIUMTEXT。我曾经用TEXT存了一篇带图片base64的文章，直接截断了，用户投诉说内容不全。

好了，数据库设计这块就聊到这儿。下一章咱们开始搭建后端项目，用Spring Boot把数据库连起来。到时候你会发现，表结构设计得好，写代码就像顺水推舟——丝滑得很。

← 上一章 📖 返回目录 下一章 →