MySQL数据管理秘籍：数据类型和约束的深入理解

# 1. MySQL数据类型与约束简介

MySQL数据类型定义了数据在数据库中的存储方式和表示形式，约束则用于限制和验证数据，确保数据完整性和一致性。本章将介绍MySQL中常用的数据类型和约束，为后续章节的深入讨论奠定基础。

# 2. 数据类型的选择与应用

在MySQL数据库中，选择合适的数据类型对于数据存储、查询和性能至关重要。本章将深入探讨不同数据类型的特性、适用场景和优化策略。

### 2.1 整数类型

#### 2.1.1 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT

整数类型用于存储整数，包括正整数和负整数。MySQL提供了五种整数类型：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT。它们的区别在于存储范围和字节大小。

| 数据类型 | 存储范围 | 字节大小 |
|---|---|---|
| TINYINT | -128~127 | 1 |
| SMALLINT | -32768~32767 | 2 |
| MEDIUMINT | -8388608~8388607 | 3 |
| INT | -2147483648~2147483647 | 4 |
| BIGINT | -9223372036854775808~9223372036854775807 | 8 |

#### 2.1.2 UNSIGNED属性

UNSIGNED属性可以将整数类型的存储范围限制为非负整数，从而扩大存储范围。例如，TINYINT UNSIGNED的存储范围为0~255，而INT UNSIGNED的存储范围为0~4294967295。

### 2.2 浮点类型

#### 2.2.1 FLOAT、DOUBLE、DECIMAL

浮点类型用于存储小数和科学计数法表示的数字。MySQL提供了三种浮点类型：FLOAT、DOUBLE和DECIMAL。

| 数据类型 | 精度 | 范围 |
|---|---|---|
| FLOAT | 24 位 | ±1.7976931348623157e+308 |
| DOUBLE | 53 位 | ±5.0000000000000000e-324~±1.7976931348623157e+308 |
| DECIMAL(M,D) | M 位整数部分，D 位小数部分 | 根据M和D的值而定 |

FLOAT和DOUBLE的精度和范围有限，可能存在舍入误差。DECIMAL类型提供了更高的精度和可控的舍入行为。

#### 2.2.2 精度和范围

FLOAT和DOUBLE的精度由浮点数的二进制表示方式决定。FLOAT具有24位精度，而DOUBLE具有53位精度。这意味着FLOAT可以表示约7位十进制数字，而DOUBLE可以表示约16位十进制数字。

DECIMAL类型的精度和范围由M和D的值决定。M指定整数部分的位数，D指定小数部分的位数。例如，DECIMAL(10,2)可以存储整数部分最多为10位，小数部分最多为2位的数字。

### 2.3 字符串类型

#### 2.3.1 CHAR、VARCHAR、TEXT

字符串类型用于存储文本数据。MySQL提供了三种字符串类型：CHAR、VARCHAR和TEXT。

| 数据类型 | 长度限制 | 存储方式 |
|---|---|---|
| CHAR(N) | 固定长度，N个字符 | 固定长度，不足补空格 |
| VARCHAR(N) | 可变长度，最大长度为N个字符 | 可变长度，实际长度存储在数据行中 |
| TEXT | 可变长度，最大长度为65535个字符 | 可变长度，实际长度存储在数据行中 |

CHAR类型适合存储固定长度的文本数据，如身份证号或邮政编码。VARCHAR类型适合存储可变长度的文本数据，如姓名或地址。TEXT类型适合存储大文本数据，如文章或文档。

#### 2.3.2 长度限制和编码

CHAR和VARCHAR类型的长度限制由N指定。TEXT类型的最大长度为65535个字符。

字符串类型的编码方式影响着存储空间和查询性能。MySQL支持多种字符集和校对规则，如UTF-8、GBK和latin1。选择合适的编码方式可以优化存储空间和查询效率。

# 3.1 主键约束

#### 3.1.1 PRIMARY KEY

`PRIMARY KEY` 约束用于指定表中唯一标识每条记录的列或列组合。它具有以下特性：

- **唯一性：**`PRIMARY KEY` 列中的值必须在表中唯一，即不能出现重复的值。
- **非空性：**`PRIMARY KEY` 列不能包含空值（`NULL`）。
- **聚簇索引：**MySQL 会自动为 `PRIMARY KEY` 列创建聚簇索引，从而优化数据检索性能。

#### 3.1.2 唯一性、非空性

`PRIMARY KEY` 约束同时保证了列的唯一性和非空性。这意味着：

- **唯一性：**表中不会出现具有相同 `PRIMARY KEY` 值的记录。
- **非空性：**`PRIMARY KEY` 列中不允许插入 `NULL` 值。

例如，以下 SQL 语句创建了一个名为 `users` 的表，其中 `id` 列是 `PRIMARY KEY`：

CREATE TABLE users (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);

在该表中，`id` 列是唯一且非空的，这意味着：

- 不会插入具有相同 `id` 值的记录。
- `id` 列不能包含 `NULL` 值。

### 3.2 外键约束

#### 3.2.1 FOREIGN KEY

`FOREIGN KEY` 约束用于建立两个表之间的关系，确保表中的数据完整性。它具有以下特性：

- **引用完整性：**`FOREIGN KEY` 列的值必须引用另一个表（称为父表）中的 `PRIMARY KEY` 列。
- **级联操作：**当父表中的记录被删除或更新时，`FOREIGN KEY` 约束可以级联执行操作，例如删除或更新子表中的相关记录。

#### 3.2.2 引用完整性

`FOREIGN KEY` 约束确保了表之间数据的引用完整性。这意味着：

- **引用有效性：**`FOREIGN KEY` 列中的值必须在父表中存在。
- **级联操作：**当父表中的记录被删除或更新时，`FOREIGN KEY` 约束可以级联执行操作，以维护数据完整性。

例如，以下 SQL 语句创建了一个名为 `orders` 的表，其中 `customer_id` 列是 `FOREIGN KEY`，引用父表 `customers` 中的 `id` 列：

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

在该表中，`customer_id` 列是 `FOREIGN KEY`，它引用父表 `customers` 中的 `id` 列。这意味着：

- `orders` 表中的每个 `customer_id` 值都必须在 `customers` 表中存在。
- 当 `customers` 表中的记录被删除时，`orders` 表中引用该记录的记录也会被级联删除。

### 3.3 唯一约束

#### 3.3.1 UNIQUE

`UNIQUE` 约束用于确保表中某一列或列组合的值唯一。它具有以下特性：

- **唯一性：**`UNIQUE` 列中的值在表中必须唯一，但允许空值（`NULL`）。
- **非聚簇索引：**MySQL 会自动为 `UNIQUE` 列创建非聚簇索引，以优化数据检索性能。

#### 3.3.2 避免重复数据

`UNIQUE` 约束可用于避免表中出现重复数据。这意味着：

- **唯一性：**表中不会出现具有相同 `UNIQUE` 值的记录，但允许空值。
- **非聚簇索引：**`UNIQUE` 约束会创建非聚簇索引，从而优化基于 `UNIQUE` 列的查询性能。

例如，以下 SQL 语句创建了一个名为 `products` 的表，其中 `name` 列是 `UNIQUE`：

CREATE TABLE products (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) UNIQUE,
  price DECIMAL(10, 2) NOT NULL,
  PRIMARY KEY (id)
);

在该表中，`name` 列是 `UNIQUE`，这意味着：

- `products` 表中不会出现具有相同 `name` 值的记录，但允许 `name` 列包含 `NULL` 值。
- MySQL 会自动为 `name` 列创建非聚簇索引，以优化基于 `name` 列的查询性能。

# 4. 约束的应用与优化

### 4.1 性能优化

#### 4.1.1 索引的创建和使用

**索引**是一种数据结构，它可以快速查找数据，而无需扫描整个表。在 MySQL 中，索引可以创建在列或列的组合上。

**创建索引**

CREATE INDEX index_name ON table_name (column_name);

**使用索引**

当查询使用索引列时，MySQL 会使用索引来查找数据，而不是扫描整个表。这可以大大提高查询性能。

#### 4.1.2 数据分区

**数据分区**是一种将表中的数据分成更小的、更易于管理的部分的技术。这可以提高查询性能，尤其是在表非常大时。

**创建分区表**

CREATE TABLE table_name (
  column_name1 data_type,
  column_name2 data_type,
  ...
)
PARTITION BY RANGE (column_name) (
  PARTITION partition_name1 VALUES LESS THAN (value1),
  PARTITION partition_name2 VALUES LESS THAN (value2),
  ...
);

**使用分区表**

当查询使用分区键时，MySQL 会只扫描相关分区，而不是整个表。这可以大大提高查询性能。

### 4.2 数据完整性保障

#### 4.2.1 级联操作

**级联操作**允许在对父表进行操作时自动对子表进行相应操作。这可以确保数据完整性。

**级联删除**

ALTER TABLE child_table
ADD FOREIGN KEY (column_name) REFERENCES parent_table (column_name)
ON DELETE CASCADE;

**级联更新**

ALTER TABLE child_table
ADD FOREIGN KEY (column_name) REFERENCES parent_table (column_name)
ON UPDATE CASCADE;

#### 4.2.2 外键检查

**外键检查**确保子表中的每个外键值都引用父表中的现有值。这可以防止数据不一致。

**启用外键检查**

ALTER TABLE child_table
ADD FOREIGN KEY (column_name) REFERENCES parent_table (column_name)
ON DELETE RESTRICT;

**禁用外键检查**

ALTER TABLE child_table
DROP FOREIGN KEY foreign_key_name;

# 5. 高级约束与扩展

### 5.1 检查约束

**5.1.1 CHECK**

CHECK 约束允许定义自定义数据验证规则，确保数据符合特定的条件。语法如下：

CHECK (expression)

其中，`expression` 是一个布尔表达式，如果为真，则数据有效。例如：

CREATE TABLE users (
  id INT NOT NULL,
  age INT CHECK (age >= 18)
);

此约束确保 `age` 列中的值必须大于或等于 18。

### 5.1.2 自定义数据验证规则

CHECK 约束提供了灵活的数据验证机制。它允许使用各种比较运算符、逻辑运算符和函数来定义复杂的验证规则。例如：

CREATE TABLE products (
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) CHECK (price > 0)
);

此约束确保 `price` 列中的值必须为正数。

### 5.2 默认约束

**5.2.1 DEFAULT**

DEFAULT 约束允许为列指定一个默认值，当插入新行时，如果没有指定该列的值，则自动填充该默认值。语法如下：

DEFAULT value

其中，`value` 是一个常量或表达式。例如：

CREATE TABLE orders (
  id INT NOT NULL AUTO_INCREMENT,
  order_date DATE NOT NULL DEFAULT CURRENT_DATE
);

此约束确保 `order_date` 列中的值在插入新行时自动填充为当前日期。

### 5.2.2 自动填充默认值

DEFAULT 约束简化了数据插入操作，确保列中始终存在有效值。它可以防止插入空值，并确保数据的一致性。