原创

MySQL数据库约束与多表查询详解

一、数据库约束概述

数据库约束（Constraint）是关系型数据库用于保证数据完整性与一致性的重要机制。通过在表结构中定义约束规则，数据库可以在数据写入或修改时自动校验数据合法性，从而避免无效数据、重复数据或违反业务规则的数据进入数据库。

在 MySQL 中，常见的约束类型包括：

  • NOT NULL（非空约束）
  • DEFAULT（默认值约束）
  • UNIQUE（唯一约束）
  • PRIMARY KEY（主键约束）
  • FOREIGN KEY（外键约束）
  • CHECK（检查约束）

不同版本的 MySQL 对约束的支持存在差异，例如：

约束类型 MySQL 5.7 MySQL 8.0
NOT NULL 支持 支持
DEFAULT 支持 支持
UNIQUE 支持 支持
PRIMARY KEY 支持 支持
FOREIGN KEY 支持（InnoDB） 支持
CHECK 解析但不生效 正式支持

其中 CHECK 约束在 MySQL 5.7 之前只是语法解析，并不会真正执行校验，从 MySQL 8.0.16 开始才真正生效。

数据库约束通常在 表结构设计阶段定义，也可以通过 ALTER TABLE 在后期添加。

合理使用约束不仅可以保证数据安全，还能在一定程度上减少应用层逻辑复杂度。

二、NOT NULL 与 DEFAULT 约束

1 NOT NULL 约束

NOT NULL 用于限制字段不能存储 NULL 值。通常用于业务中必须存在的数据字段，例如用户名、订单号等。

示例：创建用户表

CREATE TABLE user (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    password VARCHAR(100) NOT NULL,
    create_time DATETIME NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在上述表结构中：

  • username 不允许为空
  • password 不允许为空
  • create_time 不允许为空

如果插入数据时未提供这些字段值，MySQL 会直接报错。

示例：

INSERT INTO user (email) VALUES ('test@example.com');

错误：

ERROR 1364 (HY000): Field 'username' doesn't have a default value

2 DEFAULT 约束

DEFAULT 用于指定字段的默认值。当插入数据时，如果没有提供该字段值，则自动使用默认值。

示例：

CREATE TABLE user_profile (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    gender TINYINT DEFAULT 0,
    status TINYINT DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

字段含义：

字段 含义
gender 性别
status 状态
create_time 创建时间

插入数据示例：

INSERT INTO user_profile (user_id) VALUES (1);

生成的数据：

gender = 0
status = 1
create_time = 当前时间

DEFAULT 约束能够减少应用层代码复杂度，并保证字段值统一。

三、UNIQUE 约束

UNIQUE 约束用于保证字段值唯一。常用于：

  • 用户名
  • 邮箱
  • 手机号
  • 订单号

示例：用户表

CREATE TABLE account (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_username (username),
    UNIQUE KEY uk_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

唯一约束特点：

  1. 不允许重复
  2. 允许多个 NULL（MySQL 特性）
  3. 自动创建唯一索引

示例：

INSERT INTO account(username,email) VALUES ('tom','tom@mail.com');
INSERT INTO account(username,email) VALUES ('tom','abc@mail.com');

第二条语句会报错：

Duplicate entry 'tom' for key 'uk_username'

联合唯一约束

在实际业务中，有些字段需要 组合唯一。

例如：

一个用户只能给同一本书评分一次。

建表示例：

CREATE TABLE book_rating (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    book_id BIGINT UNSIGNED NOT NULL,
    score TINYINT NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_user_book (user_id, book_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

该约束保证：

(user_id, book_id) 组合唯一

但单独字段可以重复。

四、PRIMARY KEY 主键约束

主键用于唯一标识一条记录，是数据库表最核心的约束。

主键特点：

  1. 唯一
  2. 不允许 NULL
  3. 一个表只能有一个主键
  4. 默认创建聚簇索引（InnoDB）

示例

CREATE TABLE article (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id BIGINT UNSIGNED NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

id 作为主键具有以下特性：

  • 自动递增
  • 唯一
  • 不允许 NULL

复合主键

有些表没有单一主键，需要使用组合主键。

示例：用户收藏表

CREATE TABLE user_favorite (
    user_id BIGINT UNSIGNED NOT NULL,
    article_id BIGINT UNSIGNED NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, article_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

该表含义：

一个用户只能收藏同一篇文章一次。

五、FOREIGN KEY 外键约束

外键用于维护 表与表之间的关联关系。

例如：

用户表
订单表

订单必须属于某个用户。

用户表

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单表

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    status TINYINT DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

外键行为

规则 说明
RESTRICT 禁止删除
CASCADE 级联删除
SET NULL 设置为 NULL
NO ACTION 与 RESTRICT 类似

例如：

ON DELETE CASCADE

表示删除用户时，自动删除该用户所有订单。

需要注意：

  • 外键仅在 InnoDB 引擎支持
  • 大型系统通常 不使用外键，而通过应用层保证数据一致性

原因包括：

  • 影响性能
  • 降低系统解耦
  • 不利于分库分表

六、CHECK 约束

CHECK 用于限制字段取值范围。

MySQL 版本区别：

版本 支持情况
MySQL 5.7 不生效
MySQL 8.0.16+ 生效

示例：

CREATE TABLE product (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT chk_stock CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

该约束保证：

price > 0
stock >= 0

如果违反条件：

INSERT INTO product(name,price,stock)
VALUES ('phone', -100, 10);

会报错。

七、多表查询基础

在实际业务系统中，数据通常分散在多个表中，需要通过多表查询获取完整信息。

MySQL 多表查询主要包括：

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN

示例数据结构：

用户表：

CREATE TABLE user_info (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单表：

CREATE TABLE order_info (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id BIGINT UNSIGNED,
    amount DECIMAL(10,2),
    PRIMARY KEY (id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

八、INNER JOIN

INNER JOIN 返回两张表 匹配成功的数据。

示例：

SELECT
    u.id,
    u.username,
    o.amount
FROM user_info u
INNER JOIN order_info o
ON u.id = o.user_id;

执行逻辑：

user_info.id = order_info.user_id

只有匹配成功的记录才会返回。

示例结果：

| id | username | amount | | -- | -------- | ------ | | 1 | tom | 100 | | 1 | tom | 200 | | 2 | jack | 300 |

九、LEFT JOIN

LEFT JOIN 返回 左表全部数据。

如果右表不存在匹配，则返回 NULL。

示例：

SELECT
    u.id,
    u.username,
    o.amount
FROM user_info u
LEFT JOIN order_info o
ON u.id = o.user_id;

结果特点：

user_info 的所有记录都会返回

示例：

| id | username | amount | | -- | -------- | ------ | | 1 | tom | 100 | | 2 | jack | NULL | | 3 | alice | 200 |

这种查询非常适合：

  • 查询用户及其订单
  • 查询商品及销量
  • 查询文章及评论数

十、RIGHT JOIN

RIGHT JOIN 返回 右表全部数据。

示例：

SELECT
    u.username,
    o.amount
FROM user_info u
RIGHT JOIN order_info o
ON u.id = o.user_id;

结果特点：

order_info 所有记录都会返回

但在实际开发中：

RIGHT JOIN 使用较少

通常通过 调换表顺序使用 LEFT JOIN。

十一、CROSS JOIN

CROSS JOIN 产生 笛卡尔积。

示例：

SELECT *
FROM tableA
CROSS JOIN tableB;

如果：

tableA = 10 行
tableB = 5 行

结果：

10 × 5 = 50 行

通常只用于：

  • 测试
  • 数据组合
  • 构造测试数据

在业务 SQL 中应谨慎使用。

十二、多表查询优化

多表查询在大规模数据场景下容易产生性能问题，需要合理优化。

1 使用索引

JOIN 字段必须建立索引。

示例：

CREATE INDEX idx_user_id
ON order_info(user_id);

否则会产生 全表扫描。

2 小表驱动大表

MySQL 通常采用 嵌套循环连接。

执行逻辑：

for left_table
    for right_table

因此：

小表在前
大表在后

性能更好。

3 减少 SELECT *

建议只查询需要字段。

错误示例：

SELECT *
FROM user_info u
LEFT JOIN order_info o
ON u.id = o.user_id;

推荐：

SELECT
    u.id,
    u.username,
    o.amount
FROM user_info u
LEFT JOIN order_info o
ON u.id = o.user_id;

4 使用 EXPLAIN 分析

示例：

EXPLAIN
SELECT
    u.username,
    o.amount
FROM user_info u
LEFT JOIN order_info o
ON u.id = o.user_id;

关注字段：

字段 含义
type 访问类型
key 使用索引
rows 扫描行数

十三、复杂多表查询示例

假设一个简单电商系统：

用户表：

CREATE TABLE ecommerce_user (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(50),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

商品表：

CREATE TABLE product (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10,2),
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单表：

CREATE TABLE orders (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    user_id BIGINT UNSIGNED,
    create_time DATETIME,
    PRIMARY KEY (id),
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

订单商品表：

CREATE TABLE order_item (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    order_id BIGINT UNSIGNED,
    product_id BIGINT UNSIGNED,
    quantity INT,
    PRIMARY KEY (id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

查询：

用户购买商品记录

SQL：

SELECT
    u.username,
    p.name AS product_name,
    p.price,
    oi.quantity,
    o.create_time
FROM orders o
JOIN ecommerce_user u ON o.user_id = u.id
JOIN order_item oi ON o.id = oi.order_id
JOIN product p ON oi.product_id = p.id;

结果示例：

username product_name price quantity create_time
tom iPhone 5999 1 2025-01-01
jack MacBook 12999 1 2025-01-02

该 SQL 涉及 四表 JOIN。

总结

数据库约束是保证数据完整性的基础机制，包括：

  • NOT NULL
  • DEFAULT
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK

合理设计约束可以减少非法数据，并降低应用层复杂度。

多表查询是关系型数据库最核心的能力之一。通过 JOIN 可以将多个表的数据关联起来，完成复杂数据分析与业务查询。

常见 JOIN 包括：

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • CROSS JOIN

在实际开发中，需要注意：

  • JOIN 字段建立索引
  • 避免不必要字段查询
  • 使用 EXPLAIN 分析 SQL
  • 控制 JOIN 表数量

通过合理设计表结构、约束规则和查询策略，可以显著提升数据库系统的稳定性、数据安全性以及查询性能。

正文到此结束
所属分类：Mysql
相关文章
热门推荐
评论插件初始化中...
Loading...
标签云
Callable接口 版本控制 Jackson 规范化 方法 MyBatis Plus JVM 启动原理 Thymeleaf SQL ArrayList NOT NULL 文件处理 异常处理 质量保证 系统架构 Bean注册 Thread 元组 链表 伪类选择器 开发效率 内聚度 数据库运维 系统设计 SpringCloud JPA 工具 窗口函数 索引 SQL分组 SQL教程 INNER JOIN 子查询 gcc
本文目录