MySQL （一）：数据类型，完整性约束和表间关系

在当今数据驱动的时代，数据库作为数据存储与管理的核心工具，其重要性不言而喻。MySQL 作为一款广泛应用的开源数据库，凭借其高性能、高可靠性和丰富的功能，深受开发者喜爱。本文作为 MySQL 系列博客的开篇，将带你深入了解 MySQL 的基础概念、数据类型、完整性约束以及表间关系。

一、MySQL 介绍

1.1 关系型数据库概述

关系型数据库是基于关系模型建立的数据库，以行和列的形式存储数据，通过表格之间的关联来表达数据之间的关系。常见的关系型数据库有 SQL Server、Oracle、DB2、MariaDB 等。以安卓系统中的 SQLite 为例，它是一款进程内数据库，常用于移动端应用的本地数据存储，轻量级且易于集成。

与关系型数据库相对的是非关系型数据库，例如键值（k-v）存储的 Nosql、Redis，以及列式数据库 HBase 等。非关系型数据库在处理海量非结构化或半结构化数据时表现出色，而关系型数据库则在处理结构化数据、保证数据的一致性和完整性方面具有优势。

1.2 MySQL 的独特之处

MySQL 属于 Oracle 旗下的开源数据库，它区别于其他关系型数据库的一个很大特点是支持插件式存储引擎。这意味着开发者可以根据应用场景的不同，选择最适合的存储引擎。例如，InnoDB 引擎支持事务处理、外键约束，适合对数据完整性要求较高的应用；MyISAM 引擎则以其高性能的查询和插入操作，适用于只读或读多写少的场景。

MySQL 采用 C/S（客户端 / 服务器）模型，在架构设计上，使用 I/O 复用结合可伸缩线程池（select + 线程池）的方式。由于数据库操作涉及磁盘 I/O，在这种场景下，select 模型已经能够满足需求，没必要使用 epoll。这种架构设计使得 MySQL 能够高效地处理大量客户端请求，保证系统的稳定性和性能。

1.3 MySQL 的安装（ubuntu）

参考：【MySQL数据库】Ubuntu下的mysql_ubuntu mysql-CSDN博客

二、MySQL 数据类型

数据库操作中，磁盘 I/O 往往是最先遇到的性能瓶颈。MySQL 的数据类型定义了数据的大小范围，合理选择数据类型不仅能降低表占用的磁盘空间，还能减少磁盘 I/O 次数，提高访问效率。

2.1 整数类型

在定义整数类型时，需要注意一些细节。例如：

CREATE TABLE users(

age INT(9) unsigned NOT NULL DEFAULT 0

);

这里INT(9)中的9是数据显示时的宽度，和字符类型不同，数值型数据大小和数据类型强相关，并不会因为括号内的数字而改变占用的内存空间。

2.2 字符串类型

假设我们要创建一个存储用户昵称的表：

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL

);

使用VARCHAR类型存储用户名，既能满足可变长度的需求，又能节省存储空间。

2.3 日期和时间类型

例如，记录用户的注册时间：

CREATE TABLE user_registrations (

id INT AUTO_INCREMENT PRIMARY KEY,

registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

TIMESTAMP类型会自动记录插入数据时的时间，方便进行时间相关的统计和查询。

2.4 enum 和 set

ENUM类型用于定义一个字符串对象，该对象只能从预定义的列表中选取一个值；SET类型同样基于预定义的列表，但可以选取多个值。

CREATE TABLE products (

id INT AUTO_INCREMENT PRIMARY KEY,

size ENUM('S', 'M', 'L', 'XL'),

colors SET('red', 'green', 'blue')

);

在插入数据时，size字段只能是'S'、'M'、'L'、'XL'中的一个，而colors字段可以是'red'、'green'、'blue'的任意组合。

三、完整性约束

完整性约束用于确保数据库中数据的准确性和一致性，在 MySQL 中，常见的完整性约束有以下几种：

3.1 主键约束（PRIMARY KEY）

主键是表中的一列或多列组合，用于唯一标识表中的每一行记录。一个表只能有一个主键，且主键值不能为 NULL，也不能重复。例如，创建一个存储学生信息的表：

CREATE TABLE students (

student_id INT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

age INT

);

这里student_id作为主键，能快速定位和区分每一位学生的记录。

3.2 自增键约束（AUTO_INCREMENT）

自增键是一种特殊的主键，通常为整数类型，在插入新记录时，其值会自动递增。一般用于生成唯一的标识。如：

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

order_date DATE,

customer_id INT

);

每次向orders表插入新订单时，order_id都会自动生成一个唯一的递增数值。

3.3 唯一键约束（UNIQUE）

唯一键约束保证表中指定列的值是唯一的，但可以有一个 NULL 值（如果允许 NULL）。例如，在用户表中，用户的邮箱地址应具有唯一性：

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(100) UNIQUE,

username VARCHAR(50) NOT NULL

);

这样能避免不同用户使用相同的邮箱进行注册。

3.4 非空约束（NOT NULL）

非空约束规定列中不允许出现 NULL 值。在定义表结构时，若某列数据必须存在，就可以使用非空约束。如：

CREATE TABLE employees (

employee_id INT AUTO_INCREMENT PRIMARY KEY,

employee_name VARCHAR(50) NOT NULL,

department VARCHAR(50)

);

employee_name列必须有值，否则插入数据时会报错。

3.5 默认值约束（DEFAULT）

默认值约束为列指定一个默认值，当插入数据时若未指定该列的值，就会使用默认值。例如，记录用户注册时间时，若不手动指定时间，就使用当前时间：

CREATE TABLE user_registrations (

id INT AUTO_INCREMENT PRIMARY KEY,

registration_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

user_id INT

);

3.6 外键约束

外键用于建立表与表之间的关联关系，确保数据的一致性和完整性。比如，有orders表和customers表，orders表中的customer_id需要关联到customers表的主键customer_id：

CREATE TABLE customers (

customer_id INT PRIMARY KEY,

customer_name VARCHAR(50) NOT NULL

);

CREATE TABLE orders (

order_id INT AUTO_INCREMENT PRIMARY KEY,

order_date DATE,

customer_id INT,

FOREIGN KEY (customer_id) REFERENCES customers(customer_id)

);

通过外键约束，能保证orders表中的customer_id在customers表中存在，避免出现无效的关联数据。

但是由于数据库需要进行磁盘I/O操作，导致数据库往往是性能瓶颈所在，因此业务中逻辑相关操作往往交给业务层代码实现，所以外键约束并不常用。(下文中外键约束仅用于理解表间关系)

四、表与表之间的关系

在设计数据库时，表与表之间的关系建模是关键环节，直接影响数据的存储效率、查询性能以及数据完整性。常见的表间关系包括一对一、一对多和多对多。

(1) 一对一

一对一关系是指两张表中的记录通过特定字段形成一一对应的映射，通常用于将一张表中部分不常用或敏感字段拆分到另一张表，以减少主表冗余或满足安全性需求。

案例：以电商系统中用户信息表和用户扩展信息表为例。用户表存储基础登录信息（如用户名、密码），扩展表存储用户详细资料（如身份证号、收货地址）。

-- 用户基础信息表

CREATE TABLE users (

user_id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL,

password VARCHAR(128) NOT NULL

);

-- 用户扩展信息表

CREATE TABLE user_extensions (

extension_id INT AUTO_INCREMENT PRIMARY KEY,

user_id INT UNIQUE, -- 通过user_id建立一对一关联

id_card VARCHAR(18),

shipping_address TEXT,

FOREIGN KEY (user_id) REFERENCES users(user_id)

);

关系实现：通过在user_extensions表中设置user_id字段作为外键，并添加UNIQUE约束，确保每个user_id仅对应一条扩展记录。当插入数据时，若user_extensions表中已存在某个user_id，则新记录插入失败，从而保证一对一关系的唯一性。

（2） 一对多

一对多关系是最常见的表间关系，指一张表（主表）的一条记录对应另一张表（从表）的多条记录。通常通过在从表中引入主表的主键作为外键实现关联。

案例：以学校管理系统中的班级表和学生表为例，一个班级包含多名学生。

-- 班级表（主表）

CREATE TABLE classes (

class_id INT AUTO_INCREMENT PRIMARY KEY,

class_name VARCHAR(20) NOT NULL,

teacher VARCHAR(50)

);

-- 学生表（从表）

CREATE TABLE students (

student_id INT AUTO_INCREMENT PRIMARY KEY,

student_name VARCHAR(50) NOT NULL,

age INT,

class_id INT, -- 引入班级表的主键作为外键

FOREIGN KEY (class_id) REFERENCES classes(class_id)

);

关系实现：在students表中通过class_id字段关联classes表的class_id主键。插入学生记录时，class_id必须是classes表中已存在的值，确保数据一致性。例如，查询某个班级的所有学生时，可通过JOIN操作：

SELECT s.student_name, c.class_name

FROM students s

JOIN classes c ON s.class_id = c.class_id

WHERE c.class_name = '高三（1）班';

（3）多对多

多对多关系表示两张表中的多条记录可相互关联，由于数据库无法直接存储这种关系，需引入中间表（也称关联表）进行建模。中间表包含两张主表的主键字段，通过组合主键确保关系的唯一性。

案例：以图书管理系统中的书籍表和读者表为例，一本书可被多名读者借阅，一个读者可借阅多本书。

-- 书籍表

CREATE TABLE books (

book_id INT AUTO_INCREMENT PRIMARY KEY,

book_title VARCHAR(100) NOT NULL,

author VARCHAR(50)

);

-- 读者表

CREATE TABLE readers (

reader_id INT AUTO_INCREMENT PRIMARY KEY,

reader_name VARCHAR(50) NOT NULL,

contact VARCHAR(100)

);

-- 中间表（借阅记录表）

CREATE TABLE borrow_records (

book_id INT,

reader_id INT,

borrow_date DATE,

return_date DATE,

PRIMARY KEY (book_id, reader_id), -- 组合主键确保唯一性

FOREIGN KEY (book_id) REFERENCES books(book_id),

FOREIGN KEY (reader_id) REFERENCES readers(reader_id)

);

中间表设计要点：

1. 字段组成：包含关联的两张主表的主键字段（如book_id和reader_id），可按需添加其他属性（如借阅时间、归还时间）。
2. 主键设置：使用组合主键（PRIMARY KEY (book_id, reader_id)），确保同一条书籍与读者的关联关系不会重复记录。
3. 外键约束：通过外键关联主表主键，保证数据引用的完整性，例如删除书籍时，可通过外键级联操作同步删除相关借阅记录。

通过中间表，多对多关系被拆解为两个一对多关系，既保证数据的完整性，又能灵活实现复杂查询与业务逻辑。