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 整數類型

數據類型	字節	最小值（有符號）	最大值（有符號）	最小值（無符號）	最大值（無符號）
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));

中間表設計要點：