MySQL 之多表设计详解

在实际应用场景中，我们经常需要处理包含多种数据实体及其之间复杂关系的业务逻辑，例如电商平台的用户、商品、订单，社交网络的用户、帖子、评论等等。如果将所有数据都堆砌在一张表中，不仅会造成数据冗余、难以维护，还会严重影响查询效率。

为了解决这些问题，我们需要采用多表设计，即将数据分散到多个逻辑关联的表中，并通过建立表与表之间的关系，来保证数据的一致性和完整性，提高数据库的性能和可扩展性。

一、关系模型与关系类型

关系模型是数据库设计的核心概念，它将现实世界中的事物抽象为实体，并将实体之间的联系抽象为关系，最终以二维表的结构来组织和存储数据。

在关系模型中，常见的实体关系类型包括：

• 一对一 (1:1) 关系: 一个实体最多只与另一个实体关联，例如用户与其身份证信息。

• 一对多 (1:n) 关系: 一个实体可以与多个其他实体关联，例如一个用户可以拥有多条订单。

• 多对多 (m:n) 关系: 多个实体可以与多个其他实体关联，例如一个学生可以选择多门课程，一门课程也可以被多个学生选择。

二、MySQL中的五种基本约束

约束是数据库用来维护数据完整性和一致性的重要机制，它限制了数据表中可以存储的数据类型和范围。MySQL提供了五种基本约束，分别是：

1. 主键约束 (PRIMARY KEY):

   • 唯一标识数据表中的每一条记录。

   • 不能为空值 (NOT NULL)。

   • 一个表只能有一个主键。

   • 通常使用自增整数类型 (INT UNSIGNED AUTO_INCREMENT)。

   CREATE TABLE users (
       user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       username VARCHAR(255) NOT NULL
   );

2. 外键约束 (FOREIGN KEY):

   • 用于建立和维护不同数据表之间的关系。

   • 关联的字段必须拥有相同的数据类型和长度。

   • 外键字段的值必须在关联表的主键字段中存在，或者为 NULL。

   CREATE TABLE orders (
       order_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       user_id INT UNSIGNED NOT NULL,
       order_date DATE NOT NULL,
       FOREIGN KEY (user_id) REFERENCES users(user_id)
   );

3. 唯一约束 (UNIQUE):

   • 保证字段值的唯一性，不允许重复值。

   • 可以为空值 (NULL)，但只能有一个空值。

   • 一个表可以有多个唯一约束。

   CREATE TABLE products (
       product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       product_name VARCHAR(255) NOT NULL,
       sku VARCHAR(255) NOT NULL UNIQUE
   );

4. 非空约束 (NOT NULL):

   • 确保字段值不能为空值 (NULL)。

   CREATE TABLE customers (
       customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       customer_name VARCHAR(255) NOT NULL,
       phone VARCHAR(20)
   );

5. 默认值约束 (DEFAULT):

   • 当插入新记录时，如果没有为该字段指定值，则使用默认值。

   CREATE TABLE articles (
       article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
       title VARCHAR(255) NOT NULL,
       status VARCHAR(20) DEFAULT 'draft'
   );

三、多表设计实战案例：电商平台数据库

假设我们要设计一个简单的电商平台数据库，包含用户、商品、订单三个核心实体，以及它们之间的关系。

1. 确定实体和关系

• 用户: 可以注册、登录、浏览商品、下单、查看订单等。

• 商品: 包含名称、价格、库存、描述等信息。

• 订单: 记录用户的购买信息，包括下单时间、商品、数量、总价等。

粗略的实体关系图（E-R）如下：

[用户] 1:n [订单]
n:m [商品]

• 用户与订单之间是一对多关系，一个用户可以有多个订单，一个订单只能属于一个用户。

• 商品与订单之间是多对多关系，一个订单可以包含多个商品，一个商品可以出现在多个订单中。

2. 创建数据表

根据实体关系图，我们可以创建以下数据表：

用户表 (users)

字段名	数据类型	约束	说明
user_id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	用户ID
username	VARCHAR(255)	NOT NULL UNIQUE	用户名
password	VARCHAR(255)	NOT NULL	密码
email	VARCHAR(255)	NOT NULL UNIQUE	邮箱
address	VARCHAR(255)		地址

商品表 (products)

字段名	数据类型	约束	说明
product_id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	商品ID
product_name	VARCHAR(255)	NOT NULL	商品名称
price	DECIMAL(10,2)	NOT NULL	价格
stock	INT UNSIGNED	NOT NULL	库存
description	TEXT		商品描述

订单表 (orders)

字段名	数据类型	约束	说明
order_id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	订单ID
user_id	INT UNSIGNED	NOT NULL	用户ID
order_date	TIMESTAMP	NOT NULL DEFAULT CURRENT_TIMESTAMP	下单时间
total_amount	DECIMAL(10,2)	NOT NULL	总金额

订单详情表 (order_items)

字段名	数据类型	约束	说明
item_id	INT UNSIGNED	PRIMARY KEY AUTO_INCREMENT	订单详情ID
order_id	INT UNSIGNED	NOT NULL	订单ID
product_id	INT UNSIGNED	NOT NULL	商品ID
quantity	INT UNSIGNED	NOT NULL	数量

3. 建立外键关系

-- 在订单表添加外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id) REFERENCES users(user_id);

-- 在订单详情表添加外键约束
ALTER TABLE order_items
ADD CONSTRAINT fk_order_items_orders
FOREIGN KEY (order_id) REFERENCES orders(order_id),
ADD CONSTRAINT fk_order_items_products
FOREIGN KEY (product_id) REFERENCES products(product_id);

4. 多对多关系的实现

我们注意到，商品和订单之间是多对多关系，需要创建一个中间表来关联它们。这个中间表通常包含两个外键，分别指向商品表和订单表的主键。

示例数据：

假设用户表 (users) 中已经存在一个用户，其 user_id 为 1。现在，该用户购买了两个商品，分别是 product_id 为 1 的 "T 恤" 和 product_id 为 2 的 "牛仔裤"，数量分别为 2 件和 1 件。

首先，我们需要在 orders 表中插入一条新的订单记录：

INSERT INTO orders (user_id, total_amount) VALUES (1, 150.00); 
-- 假设总金额为 150.00 元

执行完上述语句后，新插入的订单记录会自动获得一个自增的 order_id，假设为 101。

接下来，我们需要在 order_items 表中插入两条订单详情记录，分别对应购买的 "T 恤" 和 "牛仔裤"：

INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 1, 2);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 2, 1);

通过以上步骤，我们就成功地将用户购买商品的信息存储到了数据库中，并利用多表设计和外键约束，保证了数据的一致性和完整性。

希望通过本文的学习，让各位看官可以掌握MySQL 多表设计原则，并将其应用到实际项目中，感谢各位看官的观看，下期见，谢谢~