在实际应用场景中,我们经常需要处理包含多种数据实体及其之间复杂关系的业务逻辑,例如电商平台的用户、商品、订单,社交网络的用户、帖子、评论等等。如果将所有数据都堆砌在一张表中,不仅会造成数据冗余、难以维护,还会严重影响查询效率。
为了解决这些问题,我们需要采用多表设计,即将数据分散到多个逻辑关联的表中,并通过建立表与表之间的关系,来保证数据的一致性和完整性,提高数据库的性能和可扩展性。
关系模型是数据库设计的核心概念,它将现实世界中的事物抽象为实体,并将实体之间的联系抽象为关系,最终以二维表的结构来组织和存储数据。
在关系模型中,常见的实体关系类型包括:
约束是数据库用来维护数据完整性和一致性的重要机制,它限制了数据表中可以存储的数据类型和范围。MySQL提供了五种基本约束,分别是:
主键约束 (PRIMARY KEY):
CREATE TABLE users (
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255) NOT NULL
);外键约束 (FOREIGN KEY):
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)
);唯一约束 (UNIQUE):
CREATE TABLE products (
product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
sku VARCHAR(255) NOT NULL UNIQUE
);非空约束 (NOT NULL):
CREATE TABLE customers (
customer_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(255) NOT NULL,
phone VARCHAR(20)
);默认值约束 (DEFAULT):
CREATE TABLE articles (
article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
status VARCHAR(20) DEFAULT 'draft'
);假设我们要设计一个简单的电商平台数据库,包含用户、商品、订单三个核心实体,以及它们之间的关系。
粗略的实体关系图(E-R)如下:

[用户] 1:n [订单]
n:m [商品]根据实体关系图,我们可以创建以下数据表:
用户表 (users)
字段名 | 数据类型 | 约束 | 说明 |
|---|---|---|---|
user_id | INT UNSIGNED | PRIMARY KEY AUTO_INCREMENT | 用户ID |
username | VARCHAR(255) | NOT NULL UNIQUE | 用户名 |
password | VARCHAR(255) | NOT NULL | 密码 |
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 | 数量 |
-- 在订单表添加外键约束
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);我们注意到,商品和订单之间是多对多关系,需要创建一个中间表来关联它们。这个中间表通常包含两个外键,分别指向商品表和订单表的主键。
示例数据:
假设用户表 (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 多表设计原则,并将其应用到实际项目中,感谢各位看官的观看,下期见,谢谢~