当前位置：首页 > news >正文

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

news 2025/6/27 10:31:35

在当今数据驱动的时代，数据库作为数据存储与管理的核心工具，其重要性不言而喻。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 整数类型

数据类型	字节	最小值（有符号）	最大值（有符号）	最小值（无符号）	最大值（无符号）
TINYINT	1	-128	127	0	255
SMALLINT	2	-32768	32767	0	65535
MEDIUMINT	3	-8388608	8388607	0	16777215
INT	4	-2147483648	2147483647	0	4294967295
BIGINT	8	-9223372036854775808	9223372036854775807	0	18446744073709551615

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

CREATE TABLE users(age INT(9) unsigned NOT NULL DEFAULT 0);

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

2.2 字符串类型

数据类型	描述	最大长度
CHAR(M)	固定长度字符串，M字节	255 个字符
VARCHAR(M)	可变长度字符串	65535 个字符（受限于行的最大长度）
TEXT	文本字符串	65535 个字符
MEDIUMTEXT	中等长度文本字符串	16777215 个字符
LONGTEXT	长文本字符串	4294967295 个字符

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

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,username VARCHAR(50) NOT NULL);

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

2.3 日期和时间类型

数据类型	描述	格式
DATE	日期值	YYYY-MM-DD
TIME	时间值或持续时间	HH:MM:SS
DATETIME	日期和时间组合	YYYY-MM-DD HH:MM:SS
TIMESTAMP	时间戳	YYYY-MM-DD HH:MM:SS
YEAR	年份值	YYYY 或 YY

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

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_nameFROM students sJOIN classes c ON s.class_id = c.class_idWHERE 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));

中间表设计要点：