S09-01 MySQL-基础认知
[TOC]
环境搭建
MySQL 5.7 手动安装与配置
以 Windows 环境下的 Zip 压缩包（免安装版）为例，详细说明 MySQL 5.7 的手动安装与配置流程。
1. 准备工作
下载安装包：访问 MySQL 官方 Archive 镜像，下载对应架构的 Zip 压缩包（例如
mysql-5.7.44-winx64.zip）。选择安装路径：将下载好的 Zip 压缩包解压至不含中文与特殊字符的路径，例如
D:\Environment\mysql-5.7.44。
2. 文件配置
在 MySQL 安装根目录下（即
bin目录同级位置），新建名为my.ini的配置文件。使用文本编辑器打开
my.ini，写入以下配置项（注意修改basedir与datadir为实际路径）：ini[mysqld] # 设置端口 port=3306 # 设置 MySQL 的安装目录 basedir=D:\Environment\mysql-5.7.44 # 设置 MySQL 数据库的数据存放目录 datadir=D:\Environment\mysql-5.7.44\data # 允许最大连接数 max_connections=200 # 允许连接失败的次数 max_connect_errors=10 # 服务端使用的字符集默认为 utf8 character-set-server=utf8 # 创建新表时默认使用的存储引擎 default-storage-engine=INNODB # 默认使用 mysql_native_password 插件认证 default_authentication_plugin=mysql_native_password # 跳过安全检查 # skip-grant-talbes [mysql] # 设置 mysql 客户端默认字符集 default-character-set=utf8 [client] # 设置客户端连接默认端口与字符集 port=3306 default-character-set=utf8
3. 环境变量
打开“系统属性”：按
Win + R键，输入sysdm.cpl并回车。进入“高级”选项卡，点击“环境变量”。
在“系统变量”列表中找到
Path，点击“编辑”。点击“新建”，将 MySQL 的
bin路径添加到变量列表中（例如C:\Environment\mysql-5.7.44\bin），连续点击“确定”保存。
4. 初始化服务
以管理员身份运行 CMD：按下
Win + X，选择“命令提示符（管理员）”或“终端（管理员）”。初始化数据库数据目录：输入以下命令（使用
--initialize-insecure会创建默认无密码的 root 账号）：cmdmysqld --initialize-insecure --user=mysql执行完毕后，安装根目录下会自动生成
data文件夹。安装 Windows 系统服务：
cmdmysqld -install MySQL57提示
Service successfully installed.即表示服务注册成功。
5. 启动与登录
启动服务：
cmd# 启动服务 net start MySQL57 # 停止服务 net stop MySQL57无密码登录：
cmdmysql -u root -p出现
Enter password:提示时直接敲回车即可进入。修改 root 初始密码：在 MySQL 命令行终端中执行以下 SQL 语句：
sqlALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewPassword123!'; FLUSH PRIVILEGES;修改完成后输入
exit;即可退出命令行。
Navicat 安装与使用
Navicat 是目前主流的图形化数据库管理工具，支持连接 MySQL、MariaDB、PostgreSQL、SQL Server、SQLite 等多种数据库。它提供了直观的可视化界面，极大地简化了数据库的设计、管理与维护工作。
软件安装
获取安装包：访问 Navicat 官方网站或授权渠道，下载对应操作系统（Windows / macOS / Linux）的安装程序文件（如
Navicat Premium或Navicat for MySQL）。运行安装向导：双击下载好的安装包文件，进入安装向导界面。
接受许可协议：阅读并勾选“我接受许可协议”选项，点击“下一步”。
选择安装路径：设置软件安装目录，建议更改为非系统盘路径（如
D:\Program Files\PremiumSoft\Navicat Premium）。完成安装流程：保持默认配置，依次点击“下一步”直到完成安装，最后勾选“运行 Navicat”并点击“完成”。
建立连接
标准数据库连接
打开 Navicat 客户端，点击左上角的“连接”按钮，在下拉菜单中选择“MySQL”。
在弹出的连接配置窗口中输入以下关键信息：
- 连接名：自定义该连接的标识名称（例如
Local_MySQL）。 - 主机：本地连接填写
localhost或127.0.0.1；远程连接填写服务器 IP 地址。 - 端口：默认为
3306（如服务器修改了端口，需填写对应的端口号）。 - 用户名：填写数据库账号（如
root）。 - 密码：填写该数据库账号对应的密码。
- 连接名：自定义该连接的标识名称（例如
点击窗口左下角的“测试连接”按钮。若弹出“连接成功”提示，点击“确定”保存该连接。
在左侧连接列表中双击刚创建的连接，连接图标变为绿色即表示已成功打开该数据库实例。
SSH 隧道连接
当远程 MySQL 服务器仅允许本地访问（
127.0.0.1）或受到防火墙限制时，切换至连接设置界面的“SSH”选项卡。勾选“使用 SSH 隧道”。
填入远程服务器的 SSH 主机 IP、端口（默认
22）、用户名以及验证方式（服务器的登录密码或 SSH 私钥文件）。切换回“常规”选项卡，将“主机”填写为
127.0.0.1，“端口”填写为远程服务器内部 MySQL 监听的端口。点击“测试连接”，验证成功后保存。
常规使用
新建数据库与表
新建数据库：在打开的连接上右键点击，选择“新建数据库”。设置数据库名称（如
shop_db），字符集推荐选择utf8mb4，排序规则选择utf8mb4_general_ci或utf8mb4_0900_ai_ci。
设计表结构：双击展开数据库，在“表”节点上右键选择“新建表”。在列视图中添加字段，依次指定字段名、数据类型（如
VARCHAR、INT、DATETIME）、长度、是否允许 NULL，并设置主键与自增属性。点击“保存”并输入表名。
数据可视化编辑
- 数据查阅与变更：双击任意表即可进入数据表格视图，直接在单元格内修改、新增或删除记录。点击底部的“对勾”图标或按下
Ctrl + S提交修改。 - 数据过滤与排序：点击视图上方的“筛选”按钮，可以按特定条件快速筛选数据，避免手动编写复杂的 SQL 过滤条件。
执行 SQL 脚本
- 新建查询：点击顶部工具栏的“新建查询”按钮，进入 SQL 编辑器。
- 编写与执行：在编辑器中输入 SQL 逻辑，选中需要运行的代码段，点击“运行”或按下快捷键
Ctrl + R（macOS 为Cmd + R）。执行结果将在下方结果集区域呈现。 - 导入 SQL 文件：在数据库名称上右键选择“运行 SQL 文件”，选中本地
.sql备份脚本并执行，可快速恢复表结构与数据。
高级功能
数据与结构同步
Navicat 提供了结构与数据比对工具，常用于开发环境与生产环境之间的差异修复：
- 在顶部菜单栏依次选择“工具” -> “结构同步”或“数据同步”。
- 分别选择“源数据库”与“目标数据库”，点击“对比”。
- 系统将自动生成差异报告与对应的
ALTER或INSERT语句，确认无误后点击“运行”即可完成同步。
数据导入与导出
- 导出数据：右键点击数据表，选择“导出向导”。支持导出为 Excel（
.xlsx）、CSV、JSON、XML、TXT 等多种格式，并可设置自定义分隔符与字段映射。 - 导入数据：选择“导入向导”，选中本地的 Excel 或 CSV 文件，系统会自动识别字段类型并批量插入表中。
自动备份与计划任务
- 点击顶部工具栏的“自动化”或“自动运行”，创建新的自动运行任务。
- 将指定的数据库备份任务拖入批处理任务窗口中。
- 设置触发日程（例如每周一凌晨 3:00 执行），Navicat 将按计划自动完成备份并保存文件。
概述
MySQL 三层结构
MySQL 采用典型的层次化数据组织模型，自上而下分为三大核心层级：数据库管理系统（DBMS）、数据库（DB）以及数据表（Table）。这三者的包容关系为：DBMS 管理一个或多个 DB，每个 DB 包含一个或多个 Table。
结构概述
- DBMS：数据库管理软件本身，负责接收 SQL 指令并调度底层资源。
- DB：逻辑上的数据隔离容器，用于按业务模块组织不同类别的数据。
- Table：存储具体数据记录的二维结构，是数据库中保存数据的最小逻辑单元。
数据库存储映射
在计算机物理体系中，所有非易失性数据的持久化存储都依赖于存储介质（如 SSD 或 HDD）。数据库管理系统（DBMS）虽然向使用者提供了高度抽象的 SQL 接口与二维表视图，但其底层物理本质完全由操作系统文件系统中的目录与文件构成。
映射关系：
逻辑上的“数据库”、“表”与物理磁盘上的“目录”、“文件”存在直接的映射对应关系。以 MySQL（InnoDB 引擎）为例：
数据库映射为目录
当在 MySQL 中执行
CREATE DATABASE shop;时，DBMS 并不存在什么神秘的容器，而是在操作系统的datadir（数据存放目录）下直接创建一个名为shop的子文件夹。数据表映射为文件
当在
shop库中执行CREATE TABLE users (...);时，DBMS 会在该文件夹下创建对应的物理文件：.ibd文件（独立表空间文件）：在默认开启独立表空间模式下，一张表对应一个.ibd文件。表中所有的列定义、行记录数据以及索引（B+ 树结构）全部序列化存储在此文件中。.frm文件（表结构文件，MySQL 8.0 之前）：存储该表的元数据信息，如列名、字段类型、索引定义等。在 MySQL 8.0 及之后，这些信息被整合进统一的系统数据字典中。
数据行映射为二进制字节
表中的每一行记录，在
.ibd文件内部被划分为固定大小的 页（Page，默认 16KB）。行数据按特定的存储格式（如 Compact、Dynamic）转换为一串二进制字节流动写入页中。
表的组成
在 MySQL 的逻辑视图中，表（Table） 是关系型数据库存储数据的核心对象，表现为一个由行（Row） 和列（Column） 构成的二维表格。使用者通过 SQL 语句与该二维结构进行交互，而无需关心底层的物理存储细节。
元数据
元数据用于定义表的整体属性与框架规则：
- 表名：在当前数据库作用域内唯一标识该表。
- 存储引擎：指定表的事务特性与存储机制（如 InnoDB 或 MyISAM）。
- 字符集与排序规则：定义文本数据的编码格式与字符串比较规则（如
utf8mb4和utf8mb4_0900_ai_ci）。
字段与数据类型
列（Column）也称字段，对应实体的一项属性：
- 字段名：列的标识名称（如
user_id、email）。 - 数据类型：规定该列能存储的数据形态（如整数
INT、字符串VARCHAR、时间DATETIME等）。 - 列属性：定义列的特性，例如是否允许为空（
NULL/NOT NULL）、是否自动递增（AUTO_INCREMENT）以及默认值（DEFAULT）。
- 字段名：列的标识名称（如
记录
行（Row）也称记录或元组，代表表中的一条具体业务实体数据：
- 每一个数据行包含该表所有字段对应的数据值。
- 数据行是逻辑数据操作（插入、删除、修改、查询）的基本单位。
索引
索引（Index）是基于表字段建立的逻辑结构，用于提升数据查询效率：
- 主键索引：绑定主键建立，决定数据在逻辑与主键索引树上的排列顺序。
- 二级索引：基于普通字段或组合字段建立，提供额外的快捷检索路径。
约束
约束（Constraint）是作用于字段之上的规则，用于保证数据的完整性：
- 主键（Primary Key）：唯一标识表中的某一条记录，且值不能为空。
- 唯一约束（Unique）：确保某列或列组合中的值在全表中唯一。
- 外键（Foreign Key）：建立当前表字段与另一张表主键之间的引用关系，约束跨表参照完整性。
SQL 语句分类
在 MySQL 中，SQL（结构化查询语言）根据其对数据库及其中数据的操作类型，主要划分为四大类（部分分类体系中会单独拆出 DQL 与 TCL，形成五大类）。
SQL 语句分类：SQL 语句按照功能的不同，通常划分为以下几大类：
DDL（Data Definition Language）：数据定义语言，用于维护数据库对象结构。
DML（Data Manipulation Language）：数据操作语言，用于增删改表中具体记录。
DQL（Data Query Language）：数据查询语言，用于检索和筛选数据。
DCL（Data Control Language）：数据控制语言，用于管理权限与安全。
TCL（Transaction Control Language）：事务控制语言，用于管理数据库事务的提交与回滚。
基本使用
命令行连接
MySQL 命令行客户端（mysql）是连接与管理 MySQL 数据库最基础且高效的工具。以下为命令行连接的具体语法、选项参数、典型场景及错误排查说明。
基础语法
MySQL 命令行连接的标准命令结构如下：
mysql -h <主机名或IP> -P <端口号> -u <用户名> -p[密码] [数据库名]参数与值之间可以紧挨着书写，也可以用空格分隔，但 -p（密码）参数例外：如果选择在命令行直接传入密码，必须与 -p 紧紧挨着（如 -p123456），否则系统会将后面的内容误判为数据库名。出于安全考虑，推荐使用隐式交互输入密码。
常用选项
下表列出了命令行连接时最常调用的参数说明：
| 参数选项 | 完整名称 | 功能说明 | 默认值 |
|---|---|---|---|
-h | --host | 指定要连接的数据库服务器主机名或 IP 地址 | localhost |
-P | --port | 指定数据库服务的监听端口 | 3306 |
-u | --user | 指定用于登录数据库的用户名 | 当前系统用户名 |
-p | --password | 提示输入密码或直接传入密码 | 无密码 |
-D | --database | 登录后默认选中的数据库名称 | 无 |
-S | --socket | 在 Linux 上使用套接字文件连接本地服务 | /tmp/mysql.sock |
-e | --execute | 连接后直接执行给定的 SQL 语句并退出 | 无 |
典型场景
场景一：本地登录
隐式交互登录（推荐，密码不留历史记录）：
bashmysql -u root -p输入命令后按下回车，系统会提示
Enter password:，此时输入密码（输入时终端不显示任何字符），再按回车即可。直接指定数据库：
bashmysql -u root -p my_database验证通过后，将直接切换到
my_database数据库上下文中。
场景二：远程登录
标准远程连接（通过 IP 地址与非默认端口）：
bashmysql -h 192.168.1.100 -P 3306 -u admin -p强制使用 TCP/IP 协议连接：
在使用
localhost连接时，Linux 系统默认使用 Unix Socket 通信。如需强制通过 TCP/IP 协议连接本地实例，可明确指定-h 127.0.0.1或增加--protocol参数：bashmysql -h 127.0.0.1 -u root -p --protocol=tcp
进阶技巧
免交互执行 SQL
使用 -e 参数可以在不保持交互式终端的情况下直接执行 SQL，适合脚本自动化任务：
mysql -u root -p'YourPassword' -e "SELECT version(), now();"指定字符集连接
当客户端与服务端的默认字符集不一致导致中文乱码时，可以在连接时指定字符集：
mysql -u root -p --default-character-set=utf8mb4使用配置文件免密连接
为了避免在脚本中明文写入密码或频繁手动输入，可以在用户家目录下创建 .my.cnf 文件（Windows 下为 my.ini）：
[client]
host = localhost
user = root
password = YourPassword
database = my_database在 Linux 系统下需设置文件权限限制访问：
chmod 600 ~/.my.cnf配置完成后，在终端直接输入 mysql 即可快速登录。
JDBC 操作 MySQL
JDBC（Java Database Connectivity） 是 Java 连接和执行数据库操作的统一标准规范。标准操作流程包含以下 6 个步骤：
引入驱动：通过
Class.forName()将对应数据库的驱动类加载到内存中。建立连接：使用
DriverManager.getConnection(url, user, password)获取Connection对象。创建执行对象：优先使用
PreparedStatement预编译对象，以防止 SQL 注入风险。绑定参数与执行：为 SQL 占位符（
?）绑定参数，执行executeQuery()（查询）或executeUpdate()（更新）。处理结果集：对查询返回的
ResultSet结果集进行遍历，转换为 Java 对象。释放资源：按照
ResultSet -> Statement -> Connection的逆序关闭资源。
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcGoodsDemo {
// 数据库连接配置（请根据本地实际情况修改）
private static final String URL = "jdbc:mysql://localhost:3306/db01?useSSL=false&serverTimezone=UTC&characterEncoding=utf8";
private static final String USER = "root";
private static final String PASSWORD = "root";
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
// 1. 注册 JDBC 驱动（MySQL 8.0+ 驱动类名）
Class.forName("com.mysql.cj.jdbc.Driver");
// 2. 获取数据库连接
conn = DriverManager.getConnection(URL, USER, PASSWORD);
// 3. 创建 Statement 对象
stmt = conn.createStatement();
// 任务 1: 创建商品表 hsp_goods
String createTableSql = "CREATE TABLE IF NOT EXISTS hsp_goods ("
+ "id INT PRIMARY KEY, "
+ "name VARCHAR(64), "
+ "price DECIMAL(10, 2), "
+ "introduce TEXT"
+ ")";
stmt.executeUpdate(createTableSql);
System.out.println("表 hsp_goods 创建成功！");
// 任务 2: 添加 2 条商品数据
String insertSql1 = "INSERT INTO hsp_goods VALUES (1, '华为手机', 5999.00, '国产高性能旗舰手机')";
String insertSql2 = "INSERT INTO hsp_goods VALUES (2, '小米笔记本', 4500.50, '轻薄高性价比办公本')";
int rows1 = stmt.executeUpdate(insertSql1);
int rows2 = stmt.executeUpdate(insertSql2);
System.out.println("成功插入 " + (rows1 + rows2) + " 条商品数据！");
// 任务 3: 删除表 hsp_goods
String dropTableSql = "DROP TABLE IF EXISTS hsp_goods";
stmt.executeUpdate(dropTableSql);
System.out.println("表 hsp_goods 已成功删除！");
} catch (ClassNotFoundException e) {
System.err.println("JDBC 驱动加载失败，请检查 Jar 包路径！");
e.printStackTrace();
} catch (SQLException e) {
System.err.println("数据库操作出现异常！");
e.printStackTrace();
} finally {
// 使用传统的 close() 方式按逆序手动关闭资源
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}字符处理
CHARACTER SET
CHARACTER SET（字符集）
字符集与校对规则（COLLATE）构成了 MySQL 字符串处理的基础：
- 字符集（CHARACTER SET）：将文本字符（如字母、汉字、Emoji 符号）映射为二进制字节流的编码集合。
- 物理存储影响：字符集的“最大字节长度（
Maxlen）”决定了一个字符最多占用多少存储空间，这会直接影响VARCHAR字段的长度限制以及索引字节数的计算。
在 MySQL 8.0 中，默认字符集已从早期的 latin1 彻底变更为 utf8mb4。
常用字符集
MySQL 提供了数十种字符集，但在实际生产研发中，常用的主要有以下几类：
| 字符集名称 | 单字符占用字节数 | 特性与适用场景 |
|---|---|---|
| utf8mb4 | 1 ~ 4 字节 | 推荐默认。完整的 UTF-8 编码，支持全世界所有语言字符、生僻字及 Emoji 表情。 |
| utf8 / utf8mb3 | 1 ~ 3 字节 | 遗留 UTF-8 实现，无法存储 4 字节的 Emoji 或补充平面字符。MySQL 8.0 已将其标注为废弃（Deprecated）。 |
| gbk | 1 ~ 2 字节 | 简体中文国标字符集，适合纯中文系统且存储空间极为受限的场景，不支持其他语言字符。 |
| latin1 | 1 字节 | 单字节 ISO-8859-1 编码，不支持中文字符，仅在早期 MySQL 版本中作为默认值存在。 |
DDL 层级配置
MySQL 支持多粒度的字符集配置，遵循从上到下的继承机制：服务器默认 数据库默认 数据表默认 字段列定义。如果低层级未显式指定，将自动沿用上一层级的设置。
数据库级别
定义新建表时的默认字符集：
-- 建库时指定字符集
CREATE DATABASE order_system
CHARACTER SET utf8mb4;
-- 修改数据库默认字符集（不影响已存在的表）
ALTER DATABASE order_system
CHARACTER SET utf8mb4;数据表级别
定义新建列时的默认字符集：
-- 建表时指定字符集
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50)
) DEFAULT CHARACTER SET utf8mb4;
-- 修改表默认字符集（仅影响后续新增的字段）
ALTER TABLE users
DEFAULT CHARACTER SET utf8mb4;字段列级别
对具体的文本列（VARCHAR、CHAR、TEXT）单独指定字符集，优先级最高：
-- 建表时设置不同列的字符集
CREATE TABLE multilingual_data (
id BIGINT PRIMARY KEY,
title VARCHAR(100) CHARACTER SET utf8mb4,
legacy_code VARCHAR(50) CHARACTER SET latin1
);
-- 修改已有字段的字符集
ALTER TABLE users
MODIFY COLUMN username VARCHAR(50) CHARACTER SET utf8mb4;转换机制
修改表或列的字符集时，DEFAULT CHARACTER SET 与 CONVERT TO 存在本质区别。
语法区别
仅修改元数据默认值：
sql-- 仅修改表的默认字符集属性，表中已有数据和已有列的字符集保持不变 ALTER TABLE users DEFAULT CHARACTER SET utf8mb4;全量重构转换数据：
sql-- 将表默认字符集、所有文本列的字符集均转换为 utf8mb4，并自动重写已有数据字节流 ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4;
存储与索引计算
字符集变更会直接改变索引与字段的物理占用上限。以 InnoDB 索引单列最大字节数限制 字节为例：
- 在
gbk（最大 字节）下，VARCHAR索引列最大长度约为 个字符。 - 在
utf8mb4（最大 字节）下，VARCHAR索引列最大长度约为 个字符。
如果将原本为 gbk 的 VARCHAR(1000) 索引字段直接转换至 utf8mb4，可能会触发 ERROR 1071 (42000): Specified key was too long 报错。
生产变更步骤
将线上千万级大表从旧字符集（如 gbk 或 utf8mb3）平滑升级至 utf8mb4 时，需遵循以下标准化操作流程：
评估字段长度与索引上限：检查待转换表中所有建有索引的文本列长度，确保在
utf8mb4（ 字节/字符）下不会超过 字节索引限制。测试数据编码兼容性：在测试环境进行备份还原，并执行
ALTER TABLE ... CONVERT TO ...转换，验证是否存在字符截断（Truncation）或乱码现象。执行无锁平滑变更：由于
CONVERT TO操作会引发表重构（Rebuild Table）并施加排他锁，生产大表必须配合gh-ost或pt-online-schema-change工具在后台同步增量 Binlog 完成转换。同步变更客户端连接参数：更新应用程序的数据库连接字符串（如 JDBC 中的
characterEncoding=UTF-8），确保连接层与数据库存储层的编码一致。
COLLATE
COLLATE（排序规则 / 校对规则）
在 MySQL 的字符处理体系中，CHARACTER SET（字符集）与 COLLATE（排序规则）是协同工作的：
- 字符集（CHARACTER SET）：定义字符在计算机中的二进制编码存储格式（例如
utf8mb4使用 1 至 4 个字节来存储 UTF-8 字符）。 - 排序规则（COLLATE）：定义在该字符集编码下，字符之间的比较算法与大小关系（例如比较
'A'和'a'是否相等，或者'a'和'á'是否相同）。
字符集与排序规则存在一对多的关系：一个字符集包含一种默认排序规则（Default Collation）以及多种可选排序规则。
命名规范
MySQL 中的 COLLATE 命名大多遵循严密的格式标准，通常由字符集名称、标准/语言以及敏感性标识符拼接而成：
字符集名称_语言/Unicode标准_重音敏感度_大小写敏感度
utf8mb4_0900_ai_ci 的结构含义如下
utf8mb4：对应的字符集名称。0900：基于 Unicode 9.0.0 规范的排序算法。ai(Accent Insensitive)：重音不敏感（即'a'与'á'被视为相同字符）。若为as(Accent Sensitive) 则表示重音敏感。ci(Case Insensitive)：大小写不敏感（即'A'与'a'被视为相同字符）。若为cs(Case Sensitive) 则表示大小写敏感。bin(Binary)：二进制排序规则。按字节流的二进制数值严格比较，完全区分大小写与重音。
常见排序规则对比：
| 排序规则 | 说明 | 大小写敏感 | 重音敏感 | 典型适用场景 |
|---|---|---|---|---|
| utf8mb4_0900_ai_ci | MySQL 8.0 默认规则 | 不敏感 | 不敏感 | 推荐使用。符合 Unicode 9.0 标准，多语言比对精准 |
| utf8mb4_general_ci | MySQL 5.7 常用默认 | 不敏感 | 不敏感 | 早期版本默认值，比对性能略快但特殊多语言精度较低 |
| utf8mb4_unicode_ci | 基于 Unicode 4.0 算法 | 不敏感 | 不敏感 | 按照标准 Unicode 排序，精度高于 general_ci |
| utf8mb4_bin | 二进制值直接比较 | 敏感 | 敏感 | 用于密码哈希、令牌（Token）或需严格区分大小写的列 |
DDL 层级配置
排序规则在 DDL 中存在严格的层级继承关系（继承顺序：服务器 数据库 数据表 字段列）。如果低层级未指定，将自动继承上一层级的默认配置。
数据库级别
定义新建表时的默认排序规则：
-- 创建数据库时指定 COLLATE
CREATE DATABASE app_db
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
-- 修改数据库默认排序规则
ALTER DATABASE app_db
DEFAULT COLLATE utf8mb4_bin;表级别
定义数据表中新建文本列时的默认排序规则：
-- 建表时指定表级 COLLATE
CREATE TABLE users (
id BIGINT PRIMARY KEY,
username VARCHAR(50)
) ENGINE=InnoDB
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;
-- 修改表默认排序规则（仅对后续新增列生效）
ALTER TABLE users DEFAULT COLLATE utf8mb4_bin;
-- 修改表排序规则，并强制转换已有列的数据编码与排序规则（会重写整表数据）
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;列级别
针对具体的文本列（VARCHAR、CHAR、TEXT）精准配置，具有最高优先级：
-- 建表时对不同字段分别配置不同的排序规则
CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
-- 搜索用户名时不区分大小写
nickname VARCHAR(50) COLLATE utf8mb4_0900_ai_ci NOT NULL,
-- 安全令牌需要严格区分大小写
access_token VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL
);
-- 修改已有字段的排序规则
ALTER TABLE accounts
MODIFY COLUMN nickname VARCHAR(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;业务影响与隐患
COLLATE 的设置直接作用于数据的存储逻辑与查询行为，配置错误可能引发严重的业务缺陷。
唯一约束生效逻辑
在设置了唯一索引（UNIQUE KEY）的字段上，COLLATE 决定了数据的判重标准：
- 在
utf8mb4_0900_ai_ci下，'Admin'与'admin'会被判定为相同记录，插入第二条时会触发唯一键冲突（Duplicate entry）报错。 - 在
utf8mb4_bin下，'Admin'与'admin'被判定为不同记录，允许同时存在。
JOIN 联表与索引失效
当两张进行关联（JOIN）的数据表在其关联字段上使用了不同的 COLLATE 时（如一个为 utf8mb4_general_ci，另一个为 utf8mb4_0900_ai_ci）：
- MySQL 在比对时会触发隐式校对规则转换（Implicit Conversion）。
- 转换会导致关联字段上的索引完全失效，进而触发全表扫描，引起查询性能骤降。
- 极高概率报出错误：
ERROR 1267 (HY000): Illegal mix of collations。
生产变更步骤
在千万级以上的生产环境数据表修改字段或表的 COLLATE 时，为确保服务稳定与数据一致性，建议按照以下步骤执行：
检查表与关联列的校对规则：通过
SHOW CREATE TABLE核对目标表及其依赖外键或关联查询表的COLLATE，确保关联字段规则完全统一。排查唯一性数据碰撞：如果将字段从
utf8mb4_bin（区分大小写）改为utf8mb4_0900_ai_ci（不区分大小写），需提前运行GROUP BY LOWER(col) HAVING COUNT(*) > 1检查是否存在仅大小写有别的数据，防止变更时引发唯一索引报错。选择低峰期或无锁工具：直接对大表执行
ALTER TABLE ... CONVERT TO ...会对数据表施加写锁并重建数据页。大表变更必须配合gh-ost或pt-online-schema-change等工具平滑切表。验证索引命中与慢查询：变更完成后，使用
EXPLAIN分析核心查询语句，验证JOIN与WHERE条件是否仍能高效命中索引。
数据类型
概述
MySQL 数据类型主要分为数值类型、字符串类型、日期与时间类型、二进制与位类型以及复合与 JSON 类型。
选型建议：
最小可用原则：在满足业务发展预期的前提下，优先选择占用字节最少的数据类型以减少 I/O 开销与内存消耗。
避免 NULL 列：尽量声明为
NOT NULL并指定默认值，NULL列需要额外的字节标记且增加索引维护成本。主键选择：推荐使用
BIGINT UNSIGNED或有序二进制，避免随机字符串导致索引页频繁分裂。
整数类型
概述
MySQL 中的整数类型主要用于存储无小数部分的数值。根据存储字节数与取值范围的不同，MySQL 提供了 5 种标准的整数类型，并支持多种修饰属性。
整数类型占用空间越小，在磁盘和内存（Buffer Pool）中占用的页面越紧凑，索引查找效率越高。
| 类型 | 存储字节 | 有符号取值范围 (SIGNED) | 无符号取值范围 (UNSIGNED) |
|---|---|---|---|
| TINYINT | 1 字节 | -128 到 127 | 0 到 255 |
| SMALLINT | 2 字节 | -32,768 到 32,767 | 0 到 65,535 |
| MEDIUMINT | 3 字节 | -8,388,608 到 8,388,607 | 0 到 16,777,215 |
| INT / INTEGER | 4 字节 | -2,147,483,648 到 2,147,483,647 | 0 到 4,294,967,295 |
| BIGINT | 8 字节 | -2⁶³ 到 2⁶³ - 1 | 0 到 2⁶⁴ - 1 |
核心属性：
整数类型支持以下核心修饰符与扩展属性：
1. 符号修饰符：
- 默认为
SIGNED（有符号），允许正负值。 - 声明
UNSIGNED（无符号）后，下限固定为 0，正数上限扩大一倍。 - 注意风险：在无符号整型之间进行减法运算若结果为负，默认 SQL 模式下会抛出
BIGINT UNSIGNED value is out of range异常。
2. 自增属性：
- 用于自动生成连续的正整数序列，通常绑定主键。
- 必须搭配索引（通常为
PRIMARY KEY或UNIQUE索引），且每个表中最多只能存在一个AUTO_INCREMENT列。
3. 显示宽度与 ZEROFILL：
显示宽度
M：如INT(11)或TINYINT(4)，数字M仅指示客户端格式化显示时的最小字符数，完全不影响存储字节大小和实际数值范围。ZEROFILL：自动启用
UNSIGNED，在数值位数不足M时在左侧以 0 补齐（例如INT(5) ZEROFILL存入12显示为00012）。版本演进：MySQL 8.0.17 起已弃用整数类型的显示宽度
(M)和ZEROFILL属性。sqlCREATE TABLE sequence_generators ( seq_id INT(6) ZEROFILL AUTO_INCREMENT PRIMARY KEY, -- 关键配置：不足6位时高位自动补0（MySQL 8.0.17+已弃用） counter_val BIGINT UNSIGNED NOT NULL DEFAULT 0 -- 关键配置：声明无符号以获取双倍正数存储容量 ); SELECT seq_id, counter_val FROM sequence_generators WHERE counter_val >= 0;
选型准则：
最小适配原则：严格根据业务数值上限选择占用空间最小的类型。减少单行数据长度可显著提升 B+ 树单页节点容纳量，进而降低树高并减少磁盘 I/O。
主键前瞻规划：自增主键如果预估突破 21 亿（
INT UNSIGNED上限），应在一开始直接采用BIGINT UNSIGNED，避免后续在线 DDL 更改主键类型的巨大锁表代价。避免无符号减法运算：涉及计算或相减逻辑的字段，尽量避免直接在 SQL 中对
UNSIGNED字段做减法，或者在计算时显式转换（CAST(a AS SIGNED) - CAST(b AS SIGNED)）。
TINYINT
TINYINT
布尔别名：
BOOL和BOOLEAN是TINYINT(1)的同义词，0视为false，非0值均视为true。适用场景：枚举状态码、逻辑删除标记（
is_deleted）、用户性别、年龄、极小数值的计数器。sqlCREATE TABLE sys_user_status ( user_id BIGINT UNSIGNED NOT NULL, account_status TINYINT NOT NULL DEFAULT 1, -- 关键配置：使用有符号TINYINT存储不同生命周期状态码 is_active TINYINT UNSIGNED NOT NULL DEFAULT 1 -- 关键配置：使用无符号TINYINT作为布尔开关标记 ); SELECT user_id, account_status FROM sys_user_status WHERE is_active = 1;
SMALLINT
SMALLINT
取值特点：无符号模式下最大可达 65,535。
适用场景：网络端口号（0 到 65535）、小型机构部门编号、商品分类等级、年份偏移量。
sqlCREATE TABLE network_services ( service_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, service_name VARCHAR(50) NOT NULL, port_number SMALLINT UNSIGNED NOT NULL -- 关键配置：端口号范围为0-65535，与无符号SMALLINT容量完全匹配 ); SELECT service_name, port_number FROM network_services WHERE port_number <= 1024;
MEDIUMINT
MEDIUMINT
取值特点：无符号模式下支持千万级（约 1677 万）上限。
适用场景：行政区划代码、中小规模业务表的主键、省市区层级 ID。当数据量确定在千万以内时，相比
INT每行可节省 1 字节，在大表索引中优势明显。sqlCREATE TABLE geo_regions ( region_code MEDIUMINT UNSIGNED NOT NULL PRIMARY KEY, -- 关键配置：行政区划代码通常在千万以内，节省索引存储 region_name VARCHAR(64) NOT NULL, parent_code MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 ); SELECT region_code, region_name FROM geo_regions WHERE parent_code = 110000;
INT
INT（INTEGER）
取值特点：无符号模式下支持约 42.9 亿，能满足绝大多数单表体量的数据标识需求。
适用场景：中型单表自增主键、文章阅读量、点赞数、外键关联字段。
sqlCREATE TABLE content_articles ( article_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, -- 关键配置：常规单表主键支持至42亿行记录 author_id INT UNSIGNED NOT NULL, view_count INTEGER UNSIGNED NOT NULL DEFAULT 0 -- 关键配置：统计阅读量，避免有符号范围提前溢出 ); SELECT article_id, view_count FROM content_articles WHERE view_count > 1000 ORDER BY view_count DESC;
BIGINT
BIGINT 占用 8 个字节（64 位），提供极大的取值范围。
取值特点：无符号上限为 （约 1844 亿亿），理论上在数据库生命周期内不会耗尽。
适用场景：分布式全局唯一 ID（如雪花算法 Snowflake ID）、海量订单表主键、毫秒/微秒级 Unix 时间戳、大规模金融流水号。
sqlCREATE TABLE order_transactions ( txn_id BIGINT UNSIGNED NOT NULL PRIMARY KEY, -- 关键配置：高频写入的分布式ID使用BIGINT避免主键耗尽 user_id BIGINT UNSIGNED NOT NULL, created_timestamp_ms BIGINT UNSIGNED NOT NULL ); SELECT txn_id, created_timestamp_ms FROM order_transactions WHERE user_id = 900120260817001 ORDER BY txn_id DESC;
浮点与定点
MySQL 中带小数的数值类型主要分为近似值浮点型（FLOAT、DOUBLE） 与精确值定点型（DECIMAL、NUMERIC） 两大类。浮点数遵循 IEEE 754 标准进行二进制编码，定点数则以压缩二进制格式存储十进制数以保证绝对精度。
概述
浮点数与定点数的核心差异在于表示方式、精度保证及计算开销。
| 类型 | 存储字节 | 精度特性 | 典型有效位数 | 算术运算方式 |
|---|---|---|---|---|
| FLOAT | 4 字节 | 近似值（单精度） | 约 7 位十进制数 | CPU 硬件 FPU 计算 |
| DOUBLE | 8 字节 | 近似值（双精度） | 约 15 到 17 位十进制数 | CPU 硬件 FPU 计算 |
| DECIMAL / NUMERIC | 变长（按位打包） | 精确值（定点数） | 最多 65 位（无精度损失） | MySQL 引擎软件模拟计算 |
存储机制：
浮点数与定点数在底层有着完全不同的二进制编码与计算路径。
浮点数（IEEE 754）：十进制小数转换为二进制时，大部分数值（如 0.1、0.7）为无限循环二进制小数，受限于尾数位长度截断，天然存在舍入误差。
定点数（Packed Decimal）：MySQL 将数字拆分为十进制块并直接压缩存储，运算由内部专用数学库逐位计算，杜绝累加与截断误差，但在 CPU 运算速度上略低于硬件 FPU。
sqlCREATE TABLE precision_demo ( id INT PRIMARY KEY, float_col FLOAT NOT NULL, decimal_col DECIMAL(10, 2) NOT NULL ); INSERT INTO precision_demo (id, float_col, decimal_col) VALUES (1, 0.7, 0.7); SELECT id, float_col, decimal_col FROM precision_demo WHERE float_col = 0.7; -- 陷阱：FLOAT由于二进制无法精确存储0.7导致等值比较落空返回空集 SELECT id, float_col, decimal_col FROM precision_demo WHERE decimal_col = 0.70; -- 正确：DECIMAL保持绝对精确，等值检索严格匹配
选型规范：
金融与交易场景一律使用 DECIMAL：所有涉及货币、结算、计费、优惠券折扣的列，严禁使用
FLOAT或DOUBLE。浮点类型严禁进行等值查询：避免在 SQL 的
WHERE子句中对FLOAT/DOUBLE字段执行=或!=条件判断，应转换为范围查询或改用DECIMAL。弃用
(M, D)浮点修饰：定义FLOAT与DOUBLE时遵循标准 SQL 语法，不附加(M, D)参数，防止发生不可控的四舍五入截断。科学计算与传感器首选 DOUBLE：对性能要求高、数据吞吐量大且允许微量误差的指标，优先选择
DOUBLE或FLOAT以利用硬件加速计算。
FLOAT
FLOAT 属于单精度浮点数，占用 4 字节（32 位）存储空间，包含 1 位符号位、8 位指数位与 23 位尾数位。
有效精度：理论有效精度为 24 位二进制数（约 7 位十进制有效数字）。
取值范围：有符号非零值范围约为 到 。
参数语法：
FLOAT(p)：p表示二进制精度位数。 时解析为FLOAT， 时自动提升为DOUBLE。FLOAT(M, D)：M为总显示位数，D为小数位数。从 MySQL 8.0.17 开始，显式指定(M, D)的语法已被标记为废弃。
适用场景：物理传感器数据（温度、湿度、气压）、图像图形坐标、允许存在微小舍入误差的科学统计指标。
sqlCREATE TABLE sensor_readings ( reading_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, temperature FLOAT NOT NULL, -- 关键配置：单精度浮点适合容忍微小舍入误差的传感器数值 humidity FLOAT NOT NULL -- 关键配置：避免使用已废弃的 FLOAT(M,D) 语法 ); SELECT reading_id, ROUND(SUM(temperature), 2) AS total_temp FROM sensor_readings WHERE temperature > 0.0 GROUP BY reading_id;
DOUBLE
DOUBLE（别名 DOUBLE PRECISION、REAL）属于双精度浮点数，占用 8 字节（64 位）存储空间，包含 1 位符号位、11 位指数位与 52 位尾数位。
有效精度：理论有效精度为 53 位二进制数（约 15 到 17 位十进制有效数字）。
取值范围：有符号非零值范围约为 到 。
参数语法：
DOUBLE(M, D)语法同样在 MySQL 8.0.17+ 中被废弃，推荐使用不带参数的标准声明。适用场景：天文与物理模拟、地理空间坐标（高精度经纬度）、大型多维统计分析。
sqlCREATE TABLE telemetry_metrics ( sim_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, orbital_velocity DOUBLE PRECISION NOT NULL, -- 关键配置：双精度浮点提供约15-17位有效精度支持空间计算 geo_latitude DOUBLE NOT NULL -- 关键配置：经纬度定位采用8字节浮点提供米级以下精度 ); SELECT sim_id, AVG(orbital_velocity) AS avg_velocity FROM telemetry_metrics WHERE orbital_velocity > 0.0 GROUP BY sim_id;
DECIMAL
DECIMAL（同义词 NUMERIC、DEC、FIXED）是精确值定点数类型。在 MySQL 内部，数字按每 9 位十进制数字打包为 4 字节的二进制格式存储，不存在二进制转换舍入误差。
参数语法：
DECIMAL(M, D)M（Precision，精度）：总有效数字个数，取值范围 ，默认为 10。D（Scale，标度）：小数点后的位数，取值范围 且 ，默认为 0。存储空间计算机制：整数部分和小数部分分别计算并占用存储，每 9 个十进制位占用 4 字节，不足 9 位的剩余部分按下表折算：
| 剩余位数 | 占用字节数 |
|---|---|
| 0 | 0 字节 |
| 1 到 2 位 | 1 字节 |
| 3 到 4 位 | 2 字节 |
| 5 到 6 位 | 3 字节 |
| 7 到 9 位 | 4 字节 |
以
DECIMAL(18, 4)为例：整数部分 14 位（9 位 + 5 位，占用 字节），小数部分 4 位（占用 2 字节），共占用 9 字节存储空间。
适用场景：银行账户余额、订单交易金额、计费单价、税率、财务报表审计。
sqlCREATE TABLE account_ledgers ( ledger_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, account_balance DECIMAL(18, 4) NOT NULL DEFAULT 0.0000, -- 关键配置：金融资产余额必须使用定点数规避分毫误差 tax_rate NUMERIC(5, 4) NOT NULL DEFAULT 0.0000 -- 关键配置：NUMERIC 与 DECIMAL 语法完全等价 ); SELECT ledger_id, SUM(account_balance) AS total_balance FROM account_ledgers WHERE account_balance > 0.0000 GROUP BY ledger_id;
字符串
MySQL 字符串类型主要用于存储字符文本数据，核心类型包括定长字符串 CHAR、变长字符串 VARCHAR 以及长文本 TEXT 系列。各类型在存储机制、尾随空格处理、内存分配与索引策略上存在明确分工。
概述
MySQL 中的字符串存储受字符集（Character Set）影响，定义长度 M 通常表示字符数而非字节数。不同类型的物理占用空间与管理方式如下：
| 类型 | 最大定义长度 (M) | 实际存储空间 | 空间分配机制 | 存储位置 |
|---|---|---|---|---|
| CHAR(M) | 0 到 255 字符 | 固定 字节（ 为字符集最大单字字节） | 静态固定分配，不足补空格 | 行内存储（In-page） |
| VARCHAR(M) | 0 到 65,535 字节 | 实际字符字节数 + 1 或 2 字节长度前缀 | 动态按需分配 | 行内存储（超长触发溢出） |
| TINYTEXT | 字节 (255 B) | 实际字符字节数 + 1 字节长度前缀 | 动态分配 | 行内或行外溢出页 |
| TEXT | 字节 (64 KB) | 实际字符字节数 + 2 字节长度前缀 | 动态分配 | 行外溢出页为主 |
| MEDIUMTEXT | 字节 (16 MB) | 实际字符字节数 + 3 字节长度前缀 | 动态分配 | 行外溢出页 |
| LONGTEXT | 字节 (4 GB) | 实际字符字节数 + 4 字节长度前缀 | 动态分配 | 行外溢出页 |
字符集与排序：
字符串类型的物理存储与比较逻辑高度依赖字符集（CHARACTER SET）与排序规则（COLLATION）。
字符长度与字节长度计算：
- 在
utf8mb4字符集中，一个字符可能占用 1 到 4 字节。 - 函数
CHAR_LENGTH()返回字符个数，函数LENGTH()返回物理占用的字节总数。
- 在
排序规则后缀含义：
_ci（Case Insensitive）：大小写不敏感，'A' = 'a'判定为真。_cs（Case Sensitive）：大小写敏感。_bin（Binary）：按字符的二进制编码逐字节进行精确比对。_ai_ci（Accent/Case Insensitive）：MySQL 8.0 默认排序规则utf8mb4_0900_ai_ci，不区分重音符号且不区分大小写。
sqlCREATE TABLE localization_terms ( term_id INT UNSIGNED PRIMARY KEY, term_key VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL, term_value VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL -- 关键配置：不区分大小写及重音的国际化文本比较 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; -- 关键配置：MySQL 8.0推荐全表默认字符集与排序规则 SELECT term_id, term_key, term_value FROM localization_terms WHERE term_key = 'app_title';
行限制与溢出：
InnoDB 与 MySQL Server 层对单行记录施加了严格的物理尺寸约束。
单行 65,535 字节上限：除
TEXT和BLOB外部存储指针外，表中所有列定义的实际占用字节总和不能超过 65,535 字节（包含 NULL 标志位与变长长度列表）。- 字符集为
latin1时，单字段VARCHAR最大可定义长度为 65,532（）。 - 字符集为
utf8mb4时，单字段VARCHAR最大可定义字符数为 。
- 字符集为
行溢出存储（Off-page Storage）：
- 在 InnoDB 的
DYNAMIC与COMPRESSED行格式下，当单行记录大小超过数据页（通常为 16 KB）约一半时，超长的VARCHAR或TEXT数据将被剥离存放到溢出页中。 - 原数据页内仅保留一个 20 字节的指针指向溢出页链表，确保 B+Tree 索引树具备较高的分支因子与查询深度。
- 在 InnoDB 的
选型规范：
定长与变长界限：固定长度或极短字符（ 字符）优先使用
CHAR；其余动态内容统一使用VARCHAR。拒绝盲目扩大 VARCHAR(255)：按实际业务最长可能出现的字符数定义精度，防止内存临时表占用与网络传输膨胀。
大字段分表隔离：
TEXT、MEDIUMTEXT等大字段应尽量与高频检索的主表进行垂直拆分，独立成扩展表以保持主表聚簇索引页紧凑。统一统一字符集：全局统一使用
utf8mb4与utf8mb4_0900_ai_ci（或utf8mb4_bin），杜绝因字符集隐式类型转换导致的索引失效问题。
CHAR
CHAR 是固定长度的字符类型，定义形式为 CHAR(M)，其中 。
存储机制：分配固定的存储空间。当存入的字符数小于 时，MySQL 会在右侧填充空格（
0x20）补齐到指定长度。检索行为：在读取检索数据时，系统默认会自动剔除尾部的填充空格；若启用了
PAD_CHAR_TO_FULL_LENGTH模式，则会保留填充空格。适用场景：长度严格固定的文本标识，例如 MD5/SHA256 哈希值、UUID、身份证号、国家/货币二字码（ISO Code）。
sqlCREATE TABLE user_identities ( user_id BIGINT UNSIGNED PRIMARY KEY, country_code CHAR(2) NOT NULL, -- 关键配置：定长2字符国家代码使用CHAR避免长度前缀开销 id_card_hash CHAR(32) NOT NULL -- 关键配置：MD5固定32位字符哈希使用CHAR提升检索效率 ); SELECT country_code, LENGTH(country_code) AS byte_len, CHAR_LENGTH(country_code) AS char_len FROM user_identities WHERE country_code = 'CN';
VARCHAR
VARCHAR 是可变长度的字符类型，定义形式为 VARCHAR(M)，其中 表示最大允许存储的字符数。
存储机制：仅占用实际存储字符所需要的字节空间，额外增加 1 至 2 字节的长度前缀记录实际字符字节数：
- 当字段声明的最大可能字节数 时，占用 1 字节长度前缀。
- 当字段声明的最大可能字节数 时，占用 2 字节长度前缀。
空格处理：MySQL 5.0.3 及更高版本中，
VARCHAR在存储与检索时均严格保留末尾空格。内存分配风险：虽然磁盘按实际字节存储，但 MySQL 服务端在内存中构建临时表或执行排序时，通常会按照声明的最大长度 分配固定内存缓冲区，因此不宜无节制放大 的值。
适用场景：长度不确定的大多数常规文本字段，如用户名、电子邮箱、商品标题、详细住址。
sqlCREATE TABLE user_profiles ( profile_id BIGINT UNSIGNED PRIMARY KEY, username VARCHAR(50) NOT NULL, -- 关键配置：长度在255字符以内仅需1字节记录实际长度 email VARCHAR(255) NOT NULL -- 关键配置：变长字符串按实际内容占用空间并保留尾随空格 ); SELECT profile_id, username, email FROM user_profiles WHERE username = 'developer' ORDER BY profile_id;
TEXT 系列
TEXT 系列用于存储超长文本数据，分为 TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT 四种级别。
容量梯度：
TINYTEXT：最大支持 255 字节（1 字节长度前缀）。TEXT：最大支持 65,535 字节（约 64 KB，2 字节长度前缀）。MEDIUMTEXT：最大支持 16,777,215 字节（约 16 MB，3 字节长度前缀）。LONGTEXT：最大支持 4,294,967,295 字节（约 4 GB，4 字节长度前缀）。
技术限制：
- 默认值限制：在 MySQL 8.0.13 之前，
TEXT列不允许声明直接字面量的DEFAULT值。 - 索引规则：不能对全字段直接建立普通索引，必须显式指定前缀索引长度（如
INDEX idx_summary (summary(64))）。 - 内存临时表下沉：包含
TEXT列的查询在无法利用内存临时表时，容易退化到磁盘临时表进行排序与分组，影响执行性能。
- 默认值限制：在 MySQL 8.0.13 之前，
适用场景：长篇博客正文、商品详细图文描述、代码日志记录、XML/HTML 原始报文。
sqlCREATE TABLE article_publications ( article_id BIGINT UNSIGNED PRIMARY KEY, summary TEXT, -- 关键配置：64KB以内的文章摘要使用标准TEXT类型存储 content MEDIUMTEXT NOT NULL, -- 关键配置：长篇文章及富文本内容选用MEDIUMTEXT防止超限 raw_payload LONGTEXT ); SELECT article_id, summary FROM article_publications WHERE article_id = 1001;
时间日期
MySQL 日期与时间数据类型用于记录日历日期、时刻、时间间隔以及系统审计时间戳。在 MySQL 5.6.4 及更高版本中，时间类型引入了对微秒级小数秒（Fractional Seconds Precision, FSP）的支持。
概述
各类时间类型在存储空间、取值范围以及时区敏感性方面存在明确划分：
| 类型 | 存储字节 (基础 + 小数秒) | 标准显示格式 | 取值范围 | 时区感知 |
|---|---|---|---|---|
| DATE | 3 字节 | YYYY-MM-DD | 1000-01-01 至 9999-12-31 | 否 |
| TIME | 3 字节 + (0~3 字节) | [-]HH:MM:SS[.fraction] | -838:59:59.000000 至 838:59:59.000000 | 否 |
| YEAR | 1 字节 | YYYY | 1901 至 2155 | 否 |
| DATETIME | 5 字节 + (0~3 字节) | YYYY-MM-DD HH:MM:SS[.fraction] | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | 否 |
| TIMESTAMP | 4 字节 + (0~3 字节) | YYYY-MM-DD HH:MM:SS[.fraction] | 1970-01-01 00:00:01 UTC 至 2038-01-19 03:14:07 UTC | 是 (UTC 转换) |
毫秒与微秒：
TIME、DATETIME 与 TIMESTAMP 允许通过 Type(N) 的语法指定小数秒精度（Fractional Seconds Precision, FSP），其中 取值范围为 。
小数秒部分在基础存储之上额外占用的物理字节数如下：
| 小数秒位数 (N) | 精度范围 | 额外存储字节 |
|---|---|---|
| 0 | 秒级（无小数部分） | 0 字节 |
| 1 到 2 位 | 10 毫秒至 100 毫秒 | 1 字节 |
| 3 到 4 位 | 毫秒至 100 微秒 | 2 字节 |
| 5 到 6 位 | 10 微秒至微秒 | 3 字节 |
例如
DATETIME(6)总共占用 字节，TIMESTAMP(3)总共占用 字节。若未显式指定 ，默认精度为 0（即不记录小数秒）。
时区与底层：
时区设置直接影响 TIMESTAMP 的存取解析，而对 DATETIME 则完全透明。
全局与会话时区：通过变量
time_zone（如+08:00或SYSTEM）控制连接的解析基准。显式转换函数：针对
DATETIME列需要进行跨时区换算时，可通过内置函数CONVERT_TZ(dt, from_tz, to_tz)手动执行时区变换。sqlSET time_zone = '+08:00'; SELECT NOW() AS beijing_time;SET time_zone = '+00:00'; SELECT NOW() AS utc_time; -- 观察：同一个 TIMESTAMP 字段在不同会话时区下读取会展示不同的本地时间 SELECT CONVERT_TZ('2026-08-18 10:00:00', '+08:00', '+00:00') AS converted_utc FROM DUAL;
选型规范：
生命周期跨越 2038 年的业务一律使用 DATETIME：对出生日期、保单到期日、长周期合同、贷款期限等未来时间，严禁使用
TIMESTAMP。审计更新时间首选 TIMESTAMP 或 DATETIME(3)：系统内置的
created_at/updated_at，若系统规模涉及全球跨时区部署且在 2038 年范围内，使用TIMESTAMP可天然规避时区转换处理；若追求更长生命周期与毫秒精度，推荐使用DATETIME(3) DEFAULT CURRENT_TIMESTAMP(3)。严禁使用字符串或浮点数存储时间：避免使用
VARCHAR存时间字符串（会导致范围索引失效与额外存储开销），避免使用FLOAT/DOUBLE存储时间戳（会引入浮点舍入误差）。按需声明精度：高频并发流水通常推荐配置为
DATETIME(3)或DATETIME(6)，避免秒级并发导致主键或时间范围检索发生重合碰撞。
DATE
DATE 类型仅用于存储日历日期，不包含任何时间（时分秒）成分。
存储机制：固定占用 3 个字节，在内部将年月日压缩为一个 24 位整数（计算公式为 ）进行紧凑存储。
适用场景：出生日期、员工入职日期、法定节假日安排、合同签署日等纯自然日期。
sqlCREATE TABLE employee_schedules ( emp_id INT UNSIGNED PRIMARY KEY, hire_date DATE NOT NULL, -- 关键配置：仅记录年月日信息，占用固定3字节 birth_date DATE NOT NULL -- 关键配置：无需关注时区与时分秒的自然日期 ); SELECT emp_id, hire_date, DATEDIFF(CURDATE(), hire_date) AS days_worked FROM employee_schedules WHERE hire_date <= CURDATE();
TIME
TIME 类型不仅可以表示一天的特定时刻，还可以表示两个事件之间的历时（时间间隔）。
存储机制：基础部分占用 3 字节，按符号位、小时、分钟、秒分别打包存储。
取值范围：范围高达约 35 天（ 到 ），且允许为负数，因此能够记录累计持续时间或时间差。
适用场景：单日作息排班时刻（如每日 09:00:00 开工）、秒表计时、音视频播放总时长、异步任务累计耗时。
sqlCREATE TABLE task_executions ( task_id BIGINT UNSIGNED PRIMARY KEY, elapsed_time TIME(3) NOT NULL, -- 关键配置：支持记录跨天累积运行耗时与毫秒精度 shift_start TIME NOT NULL DEFAULT '09:00:00' -- 关键配置：记录一天内的常规排班时刻 ); SELECT task_id, elapsed_time, shift_start FROM task_executions WHERE elapsed_time > '01:30:00.000';
YEAR
YEAR 类型是专门用于存储 4 位年份的极紧凑单字节类型。
- 存储机制：占用 1 字节，底层存储值为实际年份减去 1900 的偏移量（取值 1 到 255 对应 1901 到 2155，0 代表
0000年）。 - 版本演进：MySQL 早期支持的 2 位年份
YEAR(2)已在现代版本中被完全移除，仅保留标准的 4 位YEAR（或YEAR(4)）。 - 适用场景：车辆出厂年份、学术论文发表年份、财务会计结算归档年度。
DATETIME
DATETIME 用于完整记录“日期 + 时间”，表现为与时区无关的静态绝对时刻。
- 存储机制：从 MySQL 5.6.4 开始，基础存储空间从原有的 8 字节优化至 5 字节（由 4 字节的日期时间打包整数与 1 字节的分秒余量组成）。
- 时区无关性：客户端写入什么时间字面量，数据库即原样存储与读取，即使服务器修改系统时区，字段值也保持绝对不变。
- 适用场景：跨时区预定的航班起降时刻、会议预约、酒店入住退房时间、历史档案记录。
TIMESTAMP
TIMESTAMP 用于记录自 Unix 纪元（1970-01-01 00:00:00 UTC）以来的时间戳。
时区转换机制：
- 写入时：MySQL 将客户端当前连接时区的时间转换为 UTC（世界协调时） 保存到磁盘。
- 读取时：MySQL 将存储的 UTC 时间转换回当前连接的会话时区输出。
2038 年问题：内部以 4 字节有符号整数（32 位）记录自纪元以来的秒数，因此其有效上限截至 2038-01-19 03:14:07 UTC。
适用场景：分布式系统审计字段（
created_at、updated_at）、数据行版本控制、订单状态变更流水。sqlCREATE TABLE order_lifecycle ( order_id BIGINT UNSIGNED PRIMARY KEY, event_time DATETIME(3) NOT NULL, -- 关键配置：存储业务发生绝对时间，跨时区查询保持原值不变 created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), -- 关键配置：入库时间自动转为UTC存储，读出时按客户端时区转换 updated_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) -- 关键配置：记录修改时自动刷新时间戳 ); SELECT order_id, event_time, created_at, updated_at FROM order_lifecycle WHERE created_at >= '2026-01-01 00:00:00.000' ORDER BY order_id;
二进制与位
MySQL 二进制与位数据类型用于处理原始字节流、固定位集合、加密散列值以及未解释的非结构化二进制数据（如图片特征、压缩包、固件等）。与字符类型不同，二进制类型没有字符集（Character Set）与排序规则（Collation）的概念，所有比较与排序操作均基于字节的纯数值大小逐字节进行。
概述
MySQL 二进制与位类型涵盖位字段 BIT、定长二进制串 BINARY、变长二进制串 VARBINARY 以及二进制大对象 BLOB 家族。
| 类型 | 长度定义 | 物理存储占用 | 尾部填充与对齐 | 比较机制 |
|---|---|---|---|---|
| BIT(M) | 位 | 字节 | 高位自动补 0 | 按二进制数值比较 |
| BINARY(M) | 字节 | 固定 字节 | 右侧填充 0x00（不自动截断） | 逐字节 ASCII 数值比对 |
| VARBINARY(M) | 字节 | 实际字节数 + 1 或 2 字节前缀 | 无填充，保留精确字节 | 逐字节 ASCII 数值比对 |
| TINYBLOB | 最多 字节 (255 B) | 实际字节数 + 1 字节前缀 | 无填充 | 逐字节数值比对 |
| BLOB | 最多 字节 (64 KB) | 实际字节数 + 2 字节前缀 | 无填充 | 逐字节数值比对 |
| MEDIUMBLOB | 最多 字节 (16 MB) | 实际字节数 + 3 字节前缀 | 无填充 | 逐字节数值比对 |
| LONGBLOB | 最多 字节 (4 GB) | 实际字节数 + 4 字节前缀 | 无填充 | 逐字节数值比对 |
字符与二进制差异：
二进制类型（BINARY、VARBINARY、BLOB）与字符类型（CHAR、VARCHAR、TEXT）在存储语义与检索行为上存在本质区别。
排序与大小写敏感度：字符类型依赖
COLLATION规则（如_ci大小写不敏感），而二进制类型严格基于字节的底层数值编码逐位比较，天然具备绝对的大小写敏感性。长度计算函数：
CHAR_LENGTH()计算字符个数（受字符集编码影响）。LENGTH()或OCTET_LENGTH()：计算底层占用的纯物理字节数。
空格与空字节截断：
CHAR在检索时移除尾部空格0x20；BINARY在存储时使用0x00填充且读取时不移除。
选型规范：
UUID 存储优化：避免使用
VARCHAR(36)存储连字符格式的 UUID，使用BINARY(16)配合UUID_TO_BIN()和BIN_TO_UUID()可减少超过 55% 的存储空间并提升 B+Tree 索引检索性能。多状态位聚合：当存在大量互斥或组合布尔属性时，优先使用
BIT(N)或整型位掩码聚合，避免在表中创建过多冗余的TINYINT列。大文件严禁入库：图片、音视频、大型 PDF 文档严禁直接存入
MEDIUMBLOB或LONGBLOB，应上传至对象存储（OSS/S3），数据库仅使用VARCHAR或VARBINARY记录文件哈希与访问路径。严格区分 VARBINARY 与 VARCHAR：需要按精确字节顺序比对且不涉及任何国际化字符集转换的数据（如加密散列值、Token），统一使用
VARBINARY。
BIT
BIT 类型用于存储位字段值，支持通过位掩码管理多个布尔标志。
存储与范围：
BIT(M)中的 表示位数，取值范围为 。存储空间计算公式为 字节（即 整数除法），例如BIT(1)占用 1 字节，BIT(9)占用 2 字节。字面量表示：支持二进制前缀表示法
b'val'、B'val'或0bval（如b'101'、0b1100）。客户端输出与运算：直接查询
BIT字段时，终端通常以 ASCII 控制字符形式显示。需配合BIN()、HEX()函数或+0算术转换进行显式格式化，支持&、|、^、~、<<、>>等位运算符。sqlCREATE TABLE device_settings ( device_id INT UNSIGNED PRIMARY KEY, switch_flags BIT(8) NOT NULL DEFAULT b'00000000', -- 关键配置：8位二进制位图紧凑存储布尔开关 operation_mask BIT(8) NOT NULL DEFAULT b'00000001' -- 关键配置：二进制掩码用于按位与或快速过滤 ); SELECT device_id, BIN(switch_flags) AS binary_status, HEX(switch_flags) AS hex_status FROM device_settings WHERE (switch_flags & b'00000001') = b'00000001';
BINARY
BINARY 是固定长度的二进制字节串，定义格式为 BINARY(M)，其中 代表字节数而非字符数。
存储与填充：存储空间严格等于 字节。当插入的字节序列长度小于 时，MySQL 会在右侧自动填充零字节（
0x00）补齐。检索与比对：与
CHAR（检索时剔除尾随空格）不同，BINARY在读取与比较时不会剔除尾部的0x00。任何包含不同数量0x00填充的记录在=条件判断中均被判定为不相等。适用场景：MD5（16 字节原始二进制）、SHA-256（32 字节原始二进制）、IPv6 地址（16 字节原始二进制）、UUID（16 字节无符号二进制）。
sqlCREATE TABLE security_credentials ( user_id BIGINT UNSIGNED PRIMARY KEY, uuid_bin BINARY(16) NOT NULL, -- 关键配置：16字节紧凑二进制存储标准UUID以节省索引空间 token_hash VARBINARY(64) NOT NULL, -- 关键配置：变长二进制存储动态加密摘要且保留精确字节 raw_salt BINARY(32) NOT NULL ); SELECT user_id, HEX(uuid_bin) AS hex_uuid, OCTET_LENGTH(token_hash) AS byte_length FROM security_credentials WHERE uuid_bin = UNHEX('123e4567e89b12d3a456426614174000');
VARBINARY
VARBINARY 是可变长度的二进制字节串，定义格式为 VARBINARY(M)，其中 代表最大允许存储的字节数。
- 存储开销：仅占用实际存储内容的物理字节数，额外附加 1 字节（当 时）或 2 字节（当 时）的长度前缀。
- 无填充特性：不会在末尾填充
0x00，严格保留写入时的每一个原始字节，无论是0x20（空格）还是0x00（空字节）均作为数据内容完整保存并参与比对。 - 适用场景：AES 对称加密生成的变长密文、Protocol Buffers 序列化字节流、非对称加密公私钥数据块。
BLOB 系列
BLOB（Binary Large Object）用于存储超长非结构化二进制数据，分为 TINYBLOB、BLOB、MEDIUMBLOB 与 LONGBLOB 四个级别。
容量阶梯：
TINYBLOB：最大 255 字节（1 字节长度前缀）。BLOB：最大 65,535 字节（约 64 KB，2 字节长度前缀）。MEDIUMBLOB：最大 16,777,215 字节（约 16 MB，3 字节长度前缀）。LONGBLOB：最大 4,294,967,295 字节（约 4 GB，4 字节长度前缀）。
物理存储机制：在 InnoDB 的
DYNAMIC行格式下，超过数据页阈值的BLOB数据会被剥离至外部溢出页（Off-page），原行记录仅保留 20 字节的指针引用。索引限制：与
TEXT类似，若要对BLOB列建立索引，必须显式指定前缀索引长度（例如INDEX idx_data (raw_data(32))）。sqlCREATE TABLE system_attachments ( attachment_id BIGINT UNSIGNED PRIMARY KEY, file_signature TINYBLOB, -- 关键配置：255字节以内小块文件魔数与签名校验信息 compressed_data BLOB NOT NULL, -- 关键配置：64KB以内的二进制压缩包或离线特征向量 firmware_image MEDIUMBLOB -- 关键配置：16MB以内微控制器固件二进制镜像 ); SELECT attachment_id, OCTET_LENGTH(compressed_data) AS raw_bytes FROM system_attachments WHERE attachment_id = 1001;
复合与JSON
MySQL 中的复合类型（ 数据类型用于存储非标量或半结构化数据。复合类型通过底层整数映射实现离散枚举值的空间紧凑存储，而原生 ENUM、SET）与 JSONJSON 类型则通过二进制树状结构实现文档的快速键值定位与局部变更。
概述
复合与 JSON 类型在存储模式、成员容量与检索方式上存在显著区别：
| 类型 | 允许成员数量 / 容量 | 底层存储方式 | 存储空间占用 | 索引支持策略 |
|---|---|---|---|---|
| ENUM | 最多 65,535 个元素 | 内部整型索引映射（1-based） | 1 或 2 字节 | 原生 B+Tree 索引 |
| SET | 最多 64 个独立成员 | 二进制位图（Bitmap） | 1 至 8 字节 | 原生 B+Tree 索引 |
| JSON | 单文档最大约 1 GB (受到 max_allowed_packet 限制) | 二进制树状结构（BSON 类似格式） | 动态占用（含键值偏移索引头） | 生成列索引 / 多值索引 |
选型规范：
审慎使用 ENUM 与 SET：修改
ENUM或SET的枚举成员定义属于 DDL 操作，高并发大表可能触发元数据锁（MDL）阻塞业务。若枚举成员可能频繁增删，优先采用独立的字典关联表或使用TINYINT UNSIGNED配合应用层常量。规避过度 JSON 化：高频出现在
WHERE条件过滤、GROUP BY分组或JOIN关联条件中的核心业务字段，必须拆解为标准关系型列；JSON仅用于承载变动频繁、结构松散的扩展属性与第三方回调原始报文。JSON 查询必须结合索引：直接在
WHERE子句中使用JSON_EXTRACT会导致全表物理反序列化扫描，生产环境中凡涉及检索条件的 JSON 字段，必须绑定生成列索引或多值索引。
ENUM
ENUM（枚举类型）是一个字符串对象，其取值必须显式枚举自建表时预定义的离散常量列表。
存储机制：MySQL 并不直接在行中存储字符串字面量，而是存储字符串对应的数字索引：
- 枚举成员数 时：占用 1 字节（存储范围 1 到 255）。
- 枚举成员数在 之间时：占用 2 字节。
- 特殊索引位：索引
0表示插入非法值时的错误空字符串''；NULL值的索引为NULL。
排序行为：
ENUM字段执行ORDER BY排序时，默认按照枚举列表定义的先后顺序（数字索引大小）排序，而非字符串的字典顺序。若需按字母排序，需显式使用CAST(col AS CHAR)。使用陷阱：避免使用数字字符串作为枚举元素（例如
ENUM('1', '2', '3')），否则容易在按整数处理与按字符串处理之间产生逻辑混淆。sqlCREATE TABLE user_roles ( user_id BIGINT UNSIGNED PRIMARY KEY, status ENUM('pending', 'active', 'suspended') NOT NULL DEFAULT 'pending', -- 关键配置：单选枚举底层以1字节整型存储节约空间 privileges SET('read', 'write', 'execute', 'admin') NOT NULL -- 关键配置：多选集合底层以位掩码映射存储 ); SELECT user_id, status FROM user_roles WHERE status = 'active' ORDER BY status;
SET
SET（集合类型）用于表示零个或多个预定义值的组合，适合表达多选属性。
位图存储原理：每个成员按声明顺序对应二进制位图中的一个 Bit 位：
- 第 1 个成员对应
- 第 2 个成员对应
- 第 个成员对应
- 字段存储值为所有被选成员数值的“按位或”求和结果。
存储字节计算： 个成员占用 1 字节； 个成员占用 2 字节；依此类推，最多支持 64 个成员（占用 8 字节）。
自动规范化：写入包含重复项或无序项的集合字符串时（如
'write,read,read'），MySQL 会自动去重并按定义顺序规范化存储（重整为'read,write'）。专用检索函数：可使用
FIND_IN_SET('val', col)或按位运算符&进行集合成员判断。
JSON
MySQL 5.7+ 引入了原生 JSON 数据类型，并在 MySQL 8.0 进行了大幅增强。
二进制存储机制：
JSON类型不是简单的长文本（TEXT），而是在写入时自动解析为二进制文档结构。内部包含键名（Key）与值（Value）的偏移量索引字典，读取指定节点时无需做整行全文本反序列化扫描，定位复杂度接近 或 。语法与路径提取：
->（JSON_EXTRACT）：提取属性，返回带双引号的 JSON 格式值。->>（JSON_UNQUOTE(JSON_EXTRACT(...))）：提取属性并去除引号，直接输出纯文本。
局部更新机制（Partial Update）：在 MySQL 8.0 中，使用
JSON_SET()、JSON_REPLACE()或JSON_REMOVE()修改现有文档且新值尺寸未超过旧值分配块时，InnoDB 可直接在原物理存储页就地更新，无需重写整个 JSON 字段。sqlCREATE TABLE user_profiles ( profile_id BIGINT UNSIGNED PRIMARY KEY, attributes JSON NOT NULL, -- 关键配置：原生二进制JSON格式提供快速键值寻址与写入校验 preferences JSON NOT NULL -- 关键配置：存储半结构化非固定扩展属性 ); SELECT profile_id, attributes->>'$.theme' AS theme_name, JSON_EXTRACT(preferences, '$.notifications.email') AS email_notify FROM user_profiles WHERE JSON_EXTRACT(attributes, '$.theme') = 'dark'; -- 关键配置：通过路径表达式精确抽取JSON属性值
JSON 索引
由于 JSON 字段无法直接建立全字段 B+Tree 索引，MySQL 提供了两种核心索引加速方案。
生成列索引（Generated Column Index）：
- 通过从 JSON 文档中抽取特定标量标量值构建虚拟列（
VIRTUAL）或持久化存储列（STORED）。 - 对生成列建立常规 B+Tree 索引，使针对该 JSON 属性的查询直接命中索引。
- 通过从 JSON 文档中抽取特定标量标量值构建虚拟列（
多值索引（Multi-Value Index）：
- MySQL 8.0.17+ 引入，专门用于对 JSON 数组中的每个独立元素建立二级索引条目。
- 配合
MEMBER OF()、JSON_CONTAINS()、JSON_OVERLAPS()函数实现毫秒级数组包含查询。
sqlCREATE TABLE order_metadata ( order_id BIGINT UNSIGNED PRIMARY KEY, metadata JSON NOT NULL, -- 关键配置：JSON文档存储复杂元数据 tag_list JSON NOT NULL, -- 关键配置：数组类型JSON字段 region_code VARCHAR(32) GENERATED ALWAYS AS (metadata->>'$.region') STORED, INDEX idx_region (region_code), INDEX idx_tags ((CAST(tag_list AS CHAR(32) ARRAY))) ); SELECT order_id, region_code FROM order_metadata WHERE 'vip' MEMBER OF(tag_list); -- 关键配置：利用MySQL 8.0多值索引直接在JSON数组内高效检索
约束
概述
MySQL 中的约束（Constraints） 是作用于表中列或表级别的规则，用于限制存储在表中的数据类型与取值范围，确保数据库中数据的完整性、有效性与一致性。
| 约束分类 | 关键字 | 作用范围 | 核心功能 |
|---|---|---|---|
| 主键约束 | PRIMARY KEY | 列级 / 表级 | 唯一标识行记录，非空且唯一 |
| 非空约束 | NOT NULL | 列级 | 强制字段值不能为 NULL |
| 唯一约束 | UNIQUE | 列级 / 表级 | 确保字段或字段组合中的取值唯一 |
| 外键约束 | FOREIGN KEY | 表级 | 建立跨表关联，维护引用完整性 |
| 默认约束 | DEFAULT | 列级 | 插入记录未指定值时赋予缺省值 |
| 检查约束 | CHECK | 列级 / 表级 | 验证字段值是否满足指定的布尔条件表达式 |
约束管理与查询
通过系统元数据视图可以完整检索当前数据库实例中定义的约束详情。
-- 查询指定表的所有约束元数据
SELECT
CONSTRAINT_NAME,
CONSTRAINT_TYPE,
TABLE_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'order_items';
主键约束 PRIMARY KEY
主键约束（ 是 MySQL 中最基础且关键的完整性约束，用于唯一标识数据表中的每一行记录。PRIMARY KEY）
核心概念：
主键约束在数据库底层同时具备物理组织与逻辑约束的双重特性：
- 唯一非空：主键列隐式包含
NOT NULL与UNIQUE属性，不允许出现重复值，也不允许存储NULL。 - 单一性：一张数据表有且仅能定义一个主键。
- 聚簇索引：在 InnoDB 存储引擎中，数据表本身就是按照主键顺序构建的 B+ 树（聚簇索引），行数据的物理存储直接挂载在主键索引的叶子节点上。
单列主键：
单列主键由表中的单一字段构成，通常搭配自增属性（AUTO_INCREMENT）生成代理主键。
-- 列级主键：在定义字段时直接声明列级主键
CREATE TABLE user_account (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL
);
-- 表级主键：在表末尾单独声明表级主键
CREATE TABLE user_account_v2 (
user_id INT AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
PRIMARY KEY (user_id)
);复合主键：
当单列无法唯一定位一条记录时，可以将两个或更多列联合定义为复合主键。复合主键只能通过表级约束语法声明。
-- 多个字段组合构成联合主键
CREATE TABLE order_items (
order_id INT NOT NULL,
item_id INT NOT NULL,
quantity INT DEFAULT 1,
PRIMARY KEY (order_id, item_id)
);约束维护：
通过 ALTER TABLE 语句的 ADD PRIMARY KEY () 和 DROP PRIMARY KEY 可以对现有表的主键进行追加或移除操作。
-- 为已有表追加主键约束
ALTER TABLE user_account
ADD PRIMARY KEY (user_id);
-- 移除主键约束（若包含自增属性需先修改字段去除自增）
ALTER TABLE user_account
MODIFY user_id INT,
DROP PRIMARY KEY;元数据查询：
主键在系统底层统一命名为 PRIMARY，可通过元数据视图检索指定表的主键列配置。
-- 查询指定表的主键列信息
SELECT
COLUMN_NAME,
CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'user_account'
AND CONSTRAINT_NAME = 'PRIMARY';设计原则：
采用自增整数优先：推荐使用
INT或BIGINT自增列作为主键，天然保证单调递增写入，避免 B+ 树节点频繁分裂与碎片化。尽量缩短主键长度：InnoDB 的所有二级索引叶子节点都会存储主键值，主键越短，二级索引占用的内存与磁盘空间越小。
保持主键不可变：避免对主键执行
UPDATE操作，修改主键会导致整行数据在物理页中的移动及所有二级索引的同步更新。
非空约束 NOT NULL
非空约束（ 是 MySQL 中用于确保数据完整性的基础列级约束，强制指定列必须包含具体的数据值，禁止写入 NOT NULL）NULL。
核心概念：
非空约束在数据库底层与存储引擎层面具有以下核心特征：
- 列级规则：非空约束只能作用于单一字段，不能作为多列组合的表级约束声明。
- 严格拦截：在严格 SQL 模式（
STRICT_TRANS_TABLES）下，若插入或更新操作尝试将该列置为NULL且未提供合法的默认值，MySQL 会立即报错并拒绝执行。 - 存储优化：InnoDB 行记录头中包含 NULL 值列表（Null Bitmap）。若整张表中所有字段均定义为
NOT NULL，该位图结构将不再占用额外的头部字节。
建表声明：
在创建数据表时，可直接在字段类型后声明 NOT NULL，通常结合 DEFAULT 为非空字段提供初始值。
-- 建表时在字段定义后直接添加非空约束
CREATE TABLE user_account (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
nickname VARCHAR(50) NOT NULL DEFAULT '匿名用户'
);表结构修改：
通过 ALTER TABLE ... MODIFY 语句可以动态追加或移除现有字段的非空约束。
-- 为已有字段追加非空约束（需确保该列无 NULL 存量数据）
ALTER TABLE user_account
MODIFY username VARCHAR(50) NOT NULL;
-- 移除字段的非空约束
ALTER TABLE user_account
MODIFY username VARCHAR(50) NULL;NULL 与空值辨析：
在 MySQL 中，NULL 与空字符串（''）或数字 0 存在本质区别：
| 对比维度 | NULL | 空字符串 '' / 数字 0 |
|---|---|---|
| 物理含义 | 数据缺失、未知或不存在 | 确定且合法的空文本或数值零 |
| 内存占用 | 由行头 Null Bitmap 标记，不占常规数据位 | 占用对应类型的实际存储空间 |
| 非空约束校验 | 触发约束报错（拦截） | 正常写入（通过校验） |
| 逻辑比较 | 需使用 IS NULL / IS NOT NULL | 支持标准 = / <> 运算符 |
| 聚合函数 | COUNT(col) 统计时自动忽略 | 计入 COUNT(col) 统计结果 |
检索与校验：
非空字段与包含 NULL 的列在查询与函数处理上需采用专用语法。
-- 筛选存在缺失的脏数据记录
SELECT
id,
username
FROM user_account
WHERE username IS NULL;
-- 使用 IFNULL 函数处理潜在的空值转换
SELECT
id,
IFNULL(nickname, '未设置昵称') AS display_name
FROM user_account;最佳实践：
默认优先定义为 NOT NULL：建表时尽量将字段设置为
NOT NULL，避免引入三值逻辑（TRUE/FALSE/UNKNOWN）增加业务查询条件的复杂度。配合明确的默认值：对于非必填业务字段，推荐指定业务意义明确的默认值（如
0、''或'UNSET'），以替代NULL。加约束前清洗存量数据：对线上已有存量数据的表添加
NOT NULL前，必须先执行UPDATE将历史NULL值替换为默认值，否则执行ALTER TABLE会失败报错。
唯一约束 UNIQUE
唯一约束（ 是 MySQL 中用于保障数据列或多列组合唯一性的完整性约束，防止在表中写入重复的数据记录。UNIQUE）
核心概念：
唯一约束在逻辑规则与物理存储层面具备以下核心特征：
- 多次定义：与主键不同，一张数据表中可以同时定义多个唯一约束。
- 允许空值：默认情况下允许包含
NULL值，且根据 SQL 标准与 MySQL 实现，多个NULL值互不相等，因此同一唯一列中可插入多行NULL。 - 自动构建索引：声明唯一约束时，MySQL 会自动在底层创建对应的唯一二级索引（Unique Secondary Index）以支撑高效的重复性校验。
| 约束类型 | 允许数量 | 允许包含 NULL | 底层索引类型 | 核心应用场景 |
|---|---|---|---|---|
主键约束 (PRIMARY KEY) | 仅 1 个 | 否（强制 NOT NULL） | 聚簇索引 (InnoDB) | 标识行的物理/逻辑唯一身份 |
唯一约束 (UNIQUE) | 允许多个 | 是（允许多个 NULL） | 唯一二级索引 | 业务唯一字段（如邮箱、手机号、身份证） |
索引底层：
在 InnoDB 存储引擎中，唯一约束的校验完全依赖于唯一二级索引（B+ 树结构）。
- 校验机制：每次执行插入或更新时，数据库引擎沿 B+ 树检索定位对应节点，确认当前键值是否已存在；若存在且非
NULL，则直接中断写入并抛出1062 - Duplicate entry错误。 - 回表定位：唯一二级索引的叶子节点存储的数据为对应记录的主键值，通过主键值回表即可查询到完整的聚簇索引行记录。
单列唯一：
单列唯一约束作用于单个数据列，支持在列定义后内联声明，或在表定义末尾显式命名声明。
-- 列级直接声明唯一约束
CREATE TABLE user_account (
user_id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100) UNIQUE
);
-- 表级声明并指定约束名称
CREATE TABLE user_account_v2 (
user_id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(100),
CONSTRAINT uk_user_email UNIQUE (email)
);复合唯一：
复合唯一约束作用于两个或多个列的组合，只有当参与约束的所有字段取值全部相同时，才判定为重复冲突；只要其中任意一列取值不同，即可正常插入。
-- 声明多字段联合唯一约束
CREATE TABLE course_enrollment (
enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT NOT NULL,
course_id INT NOT NULL,
semester VARCHAR(20) NOT NULL,
CONSTRAINT uk_student_course_semester UNIQUE (student_id, course_id, semester)
);约束维护：
对已有数据表可以通过 ALTER TABLE 语句追加唯一约束，或通过删除底层对应的唯一索引来移除约束。
-- 为已有表追加唯一约束
ALTER TABLE user_account
ADD CONSTRAINT uk_user_email UNIQUE (email);
-- 移除唯一约束（通过删除底层对应的唯一索引实现）
ALTER TABLE user_account
DROP INDEX uk_user_email;空值特性：
NULL 在 SQL 标准中代表“未知”，MySQL 将每个 NULL 视作独立的不同值：
单列唯一：在没有
NOT NULL约束修饰时，可以向唯一列连续插入多行NULL而不会发生冲突。复合唯一：只要复合键的其中任意一列取值为
NULL，MySQL 就不会触发唯一性冲突校验。业务防范：若业务逻辑要求“某字段要么唯一，要么不允许留空”，必须将该字段显式声明为
NOT NULL UNIQUE。sql-- 查询指定表的唯一索引元数据（NON_UNIQUE 为 0 即表示唯一索引） SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_account' AND NON_UNIQUE = 0; -- 校验表中存量数据是否存在重复冲突 SELECT email, COUNT(*) AS count_num FROM user_account WHERE email IS NOT NULL GROUP BY email HAVING count_num > 1;
最佳实践：
规范约束命名：推荐采用
uk_表名_列名（如uk_user_email）的前缀命名方式，便于后续维护与排查报错。权衡写入性能：普通索引在更新时可以使用 Change Buffer 优化随机 I/O，而唯一索引由于在写入前必须读取数据页以校验唯一性，无法使用 Change Buffer。在高并发写入场景下应避免无节制添加唯一约束。
软删除冲突解决：在包含软删除标记（如
is_deleted）的业务表中，传统唯一约束可能因多次删除同一数据而引发冲突，可通过联合复合唯一键（business_id, is_deleted）或引入删除时间戳来规避。
外键约束 FOREIGN KEY
外键约束（ 是 MySQL 中用于在两张表之间建立引用关联并维护参照完整性（Referential Integrity）的核心机制，用于确保从表中的数据有效引用主表中存在的主键或唯一键。FOREIGN KEY）
核心概念：
外键约束涉及两张具有主从关联关系的数据表：
- 主表（父表）：被引用的数据表。被外键引用的字段必须具备唯一性（通常为主键
PRIMARY KEY或具有UNIQUE索引的列）。 - 从表（子表）：定义外键约束的数据表。从表外键列的数据取值必须严格落在父表对应列的已有取值范围内，或显式填入
NULL（若未声明NOT NULL）。 - 存储引擎限制：MySQL 中仅 InnoDB 存储引擎完整支持外键约束，MyISAM 等引擎仅支持语法解析而不执行实际校验。
建表声明：
外键约束属于表级约束，通常在从表建表定义的末尾使用 FOREIGN KEY ... REFERENCES 语法进行声明。
-- 创建从表并建立关联主表的外键约束
CREATE TABLE order_items (
item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
price DECIMAL(10,2),
CONSTRAINT fk_items_orders FOREIGN KEY (order_id)
REFERENCES orders (order_id)
);级联操作：
当父表中的关联记录发生修改（UPDATE）或删除（DELETE）时，MySQL 提供了多种级联处理规则来维护数据一致性：
| 级联动作 | 触发行为 | 适用场景 |
|---|---|---|
RESTRICT / NO ACTION | 默认行为。若子表存在关联记录，直接拒绝并报错拦截父表的删除或修改。 | 防止误删核心关联数据 |
CASCADE | 父表记录被删除或主键被修改时，子表中所有关联记录同步被自动删除或更新。 | 主从强绑定的明细数据（如订单与订单项） |
SET NULL | 父表记录被删除或修改时，子表中的外键字段自动更新为 NULL（子表外键列必须允许 NULL）。 | 弱关联关系（如用户注销后保留其历史评论） |
SET DEFAULT | 父表记录变动时，子表外键列重置为定义的默认值（InnoDB 引擎暂不支持该选项）。 | 缺省归位场景 |
-- 定义级联更新与置空删除的外键策略
CREATE TABLE employee (
emp_id INT PRIMARY KEY,
dept_id INT,
CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id)
REFERENCES department (dept_id)
ON DELETE SET NULL
ON UPDATE CASCADE
);约束维护：
可以通过 ALTER TABLE 语句为已有数据表动态追加外键，或按约束名称卸载已有的外键规则。
-- 为已有表追加外键约束
ALTER TABLE order_items
ADD CONSTRAINT fk_items_orders FOREIGN KEY (order_id)
REFERENCES orders (order_id);
-- 移除指定名称的外键约束
ALTER TABLE order_items
DROP FOREIGN KEY fk_items_orders;元数据查询：
通过系统视图可以快速检索数据库中所有的外键关联拓扑与列映射关系。
-- 查询指定表中的外键约束映射信息
SELECT
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'order_items'
AND REFERENCED_TABLE_NAME IS NOT NULL;物理外键与逻辑外键：
在工程架构设计中，外键通常分为数据库物理外键与应用层逻辑外键：
| 对比维度 | 物理外键（Database Foreign Key） | 逻辑外键（Application Foreign Key） |
|---|---|---|
| 一致性保障 | 数据库底层强一致性拦截，彻底杜绝孤儿数据 | 依赖应用程序代码校验与事务，存在漏网风险 |
| 写入性能 | 插入/更新/删除需额外检查父表或加锁，吞吐量较低 | 无数据库额外开销，写入吞吐量高 |
| 并发与死锁 | 易引发跨表共享锁升级，增加高并发下的死锁概率 | 无底层级联锁竞争，降低死锁风险 |
| 分布式支持 | 不支持分库分表与微服务跨库关联 | 天然支持分布式架构与跨库数据解耦 |
最佳实践：
命名规范：推荐统一使用
fk_从表名_主表名_关联列规范命名，便于识别和维护。索引优化：创建外键时 MySQL 会自动在从表的外键列建立索引；如需手动优化，应确保该索引契合最左前缀原则。
架构选型建议：
- 传统单体架构、中小型企业系统或对数据强一致性有极高要求且并发较低的场景，推荐使用物理外键；
- 在互联网高并发、分库分表或微服务架构下，推荐使用逻辑外键，将校验逻辑上移至业务代码层。
默认约束 DEFAULT
默认约束（ 用于在向数据表中插入新记录时，若未显式指定该列的具体数值，系统会自动将预先设定的值填入该字段中。DEFAULT）
- 核心目的：简化数据录入操作，防止因遗漏字段导致非法空值，保障数据完整性。
- 适用类型：支持固定常量（如数字、字符串）以及特定函数表达式（如当前时间戳）。
建表定义
在创建新数据表时，可以直接在字段类型后通过 DEFAULT 关键字声明默认值。
-- 创建包含默认约束的数据表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
status VARCHAR(20) DEFAULT 'active',
-- 自动填充当前系统时间
create_time DATETIME DEFAULT CURRENT_TIMESTAMP
);触发默认值
当执行数据插入时，默认约束的处理逻辑如下：
省略列名：未在
INSERT列表中包含该列，自动触发默认约束填充。显式 DEFAULT：在
VALUES中指定关键字DEFAULT，同样触发默认填充。sql-- 插入时省略 status 和 create_time 字段 INSERT INTO users (username) VALUES ('zhangsan');
修改默认值
对于已经存在的表，可以使用 ALTER TABLE 语句为字段追加或更改默认值。
-- 修改已有字段的默认值为 pending
ALTER TABLE users
ALTER COLUMN status SET DEFAULT 'pending';删除默认值
若某字段不再需要自动填充默认值，可直接移除该约束。
-- 移除 status 字段的默认约束
ALTER TABLE users
ALTER COLUMN status DROP DEFAULT;关键特性与注意事项
- 显式写入 NULL 的区别：如果向包含默认值的列显式插入
NULL（例如VALUES ('lisi', NULL)），MySQL 会直接存入NULL，而不会触发默认值填充。若需完全杜绝NULL，应同时配合NOT NULL约束使用。 - 表达式与函数支持：MySQL 8.0 及以上版本全面支持将通用表达式包裹在圆括号内作为默认值（例如
DEFAULT (CONCAT('USER_', id))或DEFAULT (CURRENT_DATE)）。
检查约束 CHECK
检查约束（ 用于限定数据表中字段的取值范围或合法条件。当执行数据插入（CHECK）INSERT）或更新（UPDATE）操作时，数据库会自动评估设定的布尔表达式，只有当表达式结果为 TRUE 或 UNKNOWN（NULL 值）时才允许写入。
- 核心目的：在数据库底层强制执行业务规则，避免脏数据或非法数值入库。
- 版本支持：MySQL 8.0.16 及以上版本开始全面支持并强制执行 CHECK 约束；在早期版本中，CHECK 子句仅能被解析但会被系统直接忽略。
列级定义
列级定义适用于针对单个字段的取值规则进行限制，直接声明在字段类型的后方。
-- 定义列时直接添加检查条件
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT CHECK (age >= 0 AND age <= 120), -- 年龄必须在 0 到 120 岁之间
score DECIMAL(5, 2) CHECK (score >= 0) -- 成绩必须大于 0
);表级定义
表级定义独立于字段声明之外，支持多列之间的联合逻辑校验，并且可以显式为约束赋予名称以方便维护。
-- 表级定义支持多列关联校验并显式命名
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
start_date DATE NOT NULL,
end_date DATE NOT NULL,
CONSTRAINT chk_order_dates CHECK (end_date >= start_date) -- 结束日期必须大于或等于开始日期
);状态控制
MySQL 支持为检查约束指定强制状态，便于在数据批量导入或临时调试时控制约束的生效与挂起。
ENFORCED（默认）：强制生效，违反规则的操作会被拒绝。
NOT ENFORCED：挂起约束，系统不会验证写入数据，但约束定义仍保留在表中。
sql-- 为已有表添加检查约束并指定强制生效 ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary >= 2000) ENFORCED;
变更与删除
若业务规则发生变化，可以通过 ALTER TABLE 语句对已有表的检查约束进行删除或追加。
-- 根据约束名称删除检查约束
ALTER TABLE employees
DROP CHECK chk_salary;核心规则与限制
- NULL 值的判定特性：如果被校验的列值为
NULL，布尔表达式将计算为UNKNOWN。在 SQL 标准与 MySQL 实现中，UNKNOWN视为通过校验。若需禁止NULL，必须显式配合NOT NULL约束。 - 函数使用限制：CHECK 表达式中禁止使用非确定性函数（如
NOW()、CURRENT_TIMESTAMP()、RAND()、UUID()等）。 - 引用范围限制：CHECK 约束中不能包含子查询，也不能引用其他表的字段或带有
AUTO_INCREMENT属性的列。
自动增长 AUTO_INCREMENT
在 MySQL 中，自动增长（ 是一种列级别的特殊属性，通常用于在插入新记录时为该列自动生成唯一的数值型标识符。它常与主键结合使用，作为数据表中的代理主键（Surrogate Key）。AUTO_INCREMENT）
自动增长机制维护一个针对当前数据表的内部计数器：
- 默认起始值：默认为
1。 - 默认递增步长：默认为
1。 - 基本行为：当向表中插入记录且未指定该列的值（或显式传入
NULL/0）时，MySQL 会自动将计数器的当前值赋给该列，随后计数器按步长递增。
约束规则
在表结构设计中，使用 AUTO_INCREMENT 必须遵循以下限制：
| 规则维度 | 具体约束要求 |
|---|---|
| 列数量限制 | 一张数据表内最多只能存在一个 AUTO_INCREMENT 列。 |
| 索引要求 | 该列必须被定义为索引的一部分，通常是 PRIMARY KEY 或 UNIQUE 索引。 |
| 数据类型 | 必须应用于整数类型（如 TINYINT、INT、BIGINT）或浮点类型（生产环境极少使用浮点型）。 |
| 默认值限制 | 不能为自增列设置默认值（即不能使用 DEFAULT 语法）。 |
| 非空属性 | 自动增长列在绝大多数情况下隐式或显式声明为 NOT NULL。 |
语法与定义
建表时定义
在创建表时，直接在字段类型后声明 AUTO_INCREMENT 并指定为主键：
CREATE TABLE users (
-- 定义自增主键
user_id INT AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
PRIMARY KEY (user_id)
);对已有表添加自增属性
如果需要将已有表的普通列改为自增列，需确保该列已存在索引：
ALTER TABLE users
-- 修改已有字段为自增属性
MODIFY user_id INT AUTO_INCREMENT;插入机制
在向包含自增列的表写入数据时，MySQL 根据传入的不同数值执行不同的处理策略：
INSERT INTO users (user_id, username)
-- 显式传入 NULL 或 0 触发自增
VALUES (NULL, '张三');
INSERT INTO users (username)
-- 忽略自增字段直接插入
VALUES ('李四');缺省、传入
NULL或0：系统自动分配当前计数器值作为该列的值，并将计数器加 1。显式传入具体正整数：
如果传入值小于当前计数器，数据正常写入，计数器保持不变。
如果传入值大于或等于当前计数器，数据写入成功，并将计数器直接更新为
传入值 + 1。
获取最新生成值：可以在同一数据库连接中通过函数获取当前会话最新生成的自增值，该值在并发环境下是连接隔离且安全的：
sqlSELECT -- 查询当前连接最近生成的自增ID LAST_INSERT_ID();
计数重置
自增计数器支持手动调整与重置，但调整时存在一定的范围与机制约束。
手动修改自增值
ALTER TABLE users
-- 设置下一个自增起始值为 100
AUTO_INCREMENT = 100;注意：设置的新起始值不能小于当前表中该列已存在的最大值；若设置的值小于已有最大值，MySQL 会静默忽略该操作或自动校正为
MAX(column) + 1。
清空表对自增计数器的影响
DELETE FROM table_name;：仅删除行数据，不重置自增计数器。后续插入仍从原最大值继续递增。TRUNCATE TABLE table_name;：直接重建表结构并释放存储空间，自增计数器会被彻底重置回初始值（通常为1）。
步长配置
MySQL 允许通过系统变量控制全局或会话级别的自增步长与起始偏移量，这在双主复制（Master-Master Replication）或多主分片架构中用于避免主键冲突。
SET
-- 设置当前会话的自增步长
@@session.auto_increment_increment = 2;
SET
-- 设置当前会话的自增初始偏移量
@@session.auto_increment_offset = 1;auto_increment_increment：每次自增跳跃的跨度。auto_increment_offset：自增起始基数（取值范围为1至65535）。
关键机制与注意事项
ID 不连续现象
自增 ID 保证唯一与趋势递增，但不保证绝对连续。常见断号原因包括：
事务回滚：当事务分配了自增 ID 后发生异常并回滚，已消耗的 ID 不会退还。
唯一键冲突：插入操作因违反其他唯一约束失败时，已分配的自增 ID 同样被跳过。
批量插入预分配：批量插入（如
INSERT INTO ... SELECT）为提高吞吐量会预申请批量 ID，未用完的 ID 会直接作废。
持久化存储机制
- MySQL 5.7 及更早版本：自增计数器仅保存在内存中。数据库重启后，InnoDB 会执行类似
SELECT MAX(id) FROM table FOR UPDATE;的逻辑来重新初始化计数器，可能导致重启后复用被回滚或删除的 ID。 - MySQL 8.0 及以上版本：引入了自增计数器持久化机制。每次计数器变更都会写入 Redo Log，重启后直接从引擎元数据与日志中恢复状态，彻底解决了计数器重置问题。
数据类型溢出
当自增列达到数据类型的上限时，再次插入将报主键冲突错误（Duplicate entry）。
| 类型 | 有符号最大值 | 无符号（UNSIGNED）最大值 | 建议应用场景 |
|---|---|---|---|
INT | 约 21.4 亿 | 约 42.9 亿 | 小型项目或字典配置表 |
BIGINT | 约 | 约 | 业务核心表、日志表、高并发流水表 |
实战：商店售货系统表设计
题目：现有一个商店售货系统的数据库
shop_db，记录客户及其购物情况，由下面三个表组成：
- 商品
goods：商品号 goods_id，商品名 goods_name，单价 unitprice，商品类别 category，供应商 provider- 客户
customer：客户号 customer_id,姓名 name,住址 address,电邮 email 性别 sex,身份证 card_id- 购买
purchase：购买订单号 order_id，客户号 customer_id,商品号 goods_id,购买数量 nums建表，在定义中要求声明 [进行合理设计]:
- 每个表的主外键
- 客户的姓名不能为空值
- 电邮不能够重复
- 客户的性别
男|女（check 或 ENUM）- 单价 unitprice 在 1.0 - 9999.99 之间（check）
实现：
根据图片中的表结构和约束要求，编写的完整 MySQL 建表 SQL 语句如下：
-- 1. 创建并使用数据库
CREATE DATABASE IF NOT EXISTS shop_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE shop_db;
-- 2. 创建商品表 (goods)
CREATE TABLE IF NOT EXISTS goods (
goods_id INT AUTO_INCREMENT COMMENT '商品号',
goods_name VARCHAR(100) NOT NULL DEFAULT '' COMMENT '商品名',
unitprice DECIMAL(6, 2) NOT NULL DEFAULT 0 COMMENT '单价',
category VARCHAR(50) COMMENT '商品类别',
provider VARCHAR(100) COMMENT '供应商',
-- 约束 (1)：商品表主键
PRIMARY KEY (goods_id),
-- 约束 (5)：单价在 1.0 到 9999.99 之间
CONSTRAINT chk_goods_unitprice CHECK (unitprice >= 1.0 AND unitprice <= 9999.99)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品信息表';
-- 3. 创建客户表 (customer)
CREATE TABLE IF NOT EXISTS customer (
customer_id INT AUTO_INCREMENT COMMENT '客户号',
name VARCHAR(50) NOT NULL COMMENT '姓名', -- 约束 (2)：姓名不能为空
address VARCHAR(255) COMMENT '住址',
email VARCHAR(100) COMMENT '电邮',
sex ENUM('男', '女') NOT NULL DEFAULT '男' COMMENT '性别', -- 约束 (4)：限制性别为[男|女]
card_id CHAR(18) COMMENT '身份证号',
-- 约束 (1)：客户表主键
PRIMARY KEY (customer_id),
-- 约束 (3)：电邮不能重复
CONSTRAINT uk_customer_email UNIQUE (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户信息表';
-- 4. 创建购买记录表 (purchase)
CREATE TABLE IF NOT EXISTS purchase (
order_id INT AUTO_INCREMENT COMMENT '购买订单号',
customer_id INT NOT NULL COMMENT '客户号',
goods_id INT NOT NULL COMMENT '商品号',
nums INT NOT NULL DEFAULT 0 COMMENT '购买数量',
-- 约束 (1)：购买表主键
PRIMARY KEY (order_id),
-- 约束 (1)：外键关联客户表
CONSTRAINT fk_purchase_customer FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
-- 约束 (1)：外键关联商品表
CONSTRAINT fk_purchase_goods FOREIGN KEY (goods_id)
REFERENCES goods(goods_id)
ON UPDATE CASCADE
ON DELETE RESTRICT,
-- 附加约束：购买数量必须大于 0
CONSTRAINT chk_purchase_nums CHECK (nums > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='购买订单表';设计与约束对应说明：
主外键约束 (要求 1)：
goods表与customer表分别设置goods_id和customer_id为PRIMARY KEY。purchase表作为关系表，设置order_id为主键，并通过FOREIGN KEY分别关联customer(customer_id)和goods(goods_id)，保证参照完整性。
姓名非空 (要求 2)：
customer.name声明了NOT NULL。
电邮唯一 (要求 3)：
- 通过
CONSTRAINT uk_customer_email UNIQUE (email)确保同一邮箱只能注册一次。
- 通过
性别约束 (要求 4)：
- 使用
ENUM('男', '女')实现原生限定；在 MySQL 8.0 中也可以使用VARCHAR(2) + CHECK (sex IN ('男', '女'))。
- 使用
单价范围检查 (要求 5)：
- 使用
DECIMAL(6, 2)精确存储金额，并通过CHECK (unitprice >= 1.0 AND unitprice <= 9999.99)限制范围（注：MySQL 8.0.16 及以上版本正式支持 CHECK 约束生效）。
- 使用