前言
现在写 SQL，一句话就能生成。但当你看到 AI 吐出一堆 JOIN、子查询、索引提示，你真的知道它在干什么吗？更现实的是：你在做 RAG 项目，AI 帮你生成了向量检索的表结构，结果 embedding 字段用了 VARCHAR，维度对不上，检索全错；或者 AI 写了条看起来很帅的查询，上线后数据库 CPU 飙到 100%，因为你根本没意识到它漏了索引。AI 是杠杆，但杠杆需要支点。这个支点就是你得知道：表该怎么设计，字段类型怎么选、主键、外键、索引是干什么的、WHERE 和 JOIN 的执行顺序、什么时候该用事务，什么时候不该......等等。
你不需要成为 DBA，但你需要能看懂 AI 写的代码、能判断它对不对、能把它改对。这篇文章就是帮你建立这个"支点"——用简洁的话语，讲清楚 MySQL 的核心基础。
一、相关概念
- 数据库：数据存储的仓库；
- 数据库管理系统：操纵和管理数据库的大型软件，比如 Oracle、MySQL、SQL Server、Hive 等等；
- SQL：操作关系型数据库的编程语言，是一套标准；
- 层级关系：数据库容器 → 数据库 → 表
- 关系型数据库(RDBMS)：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
二、数据类型
2.1 数值类型
| 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128, 127) | (0, 255) | 小整数值 |
| SMALLINT | 2 bytes | (-32768, 32767) | (0, 65535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8388608, 8388607) | (0, 16777215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 |
| BIGINT | 8 bytes | (-2^63, 2^63-1) | (0, 2^64-1) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402823466 E+38, 3.402823466351 E+38) | 0 和 (1.175494351 E-38, 3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 bytes | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
例子：
-- TINYINIT 类型字段，无负数
age tinyint unsigned
-- DOUBLE 类型字段，4 代表整体长度，1 代表小数位数
score DOUBLE(4,1)
2.2 字符类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0–255 bytes | 定长字符串，使用时需要指定长度，实际字符不满足长度也会占用空间。 |
| VARCHAR | 0–65535 bytes | 变长字符串，使用时需要指定长度，会根据实际字符长度计算占用空间。 |
| TINYBLOB | 0–255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0–255 bytes | 短文本字符串 |
| BLOB | 0–65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0–65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0–16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0–16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0–4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0–4 294 967 295 bytes | 极大文本数据 |
带 BLOB 的描述二进制数据，带 TEXT 的描述文本数据。
2.3 日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
三、SQL
3.1 通用语法
- SQL 语句可以单行或多行书写，以分号结尾。
- SQL 语句可以使用空格/缩进（可以是一个或者多个）来增强语句的可读性。
- MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
- 单行注释：-- 注释内容 或 # 注释内容(MySQL特有)；
- 多行注释：/* *注释内容 ** /。
3.2 分类
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
|---|---|---|
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
3.3 DDL 常用操作
注意：[…]内的表示可选参数，实际不写[]
3.3.1 查询
-- 查询所有数据库
SHOW DATABASES;
-- 查询当前数据库
SELECT DATABASE();
-- 查询当前数据库所有表
SHOW TABLES;
-- 查询表结构
DESC 表名;
-- 查询指定表的建表语句
SHOW CREATE TABLE 表名;
3.3.2 创建
-- 创建数据库，字符集可省略
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
-- 创建表
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT '字段1注释'],
...
字段n 字段n类型 [COMMENT '字段n注释']
) [COMMENT 表注释];
3.3.3 修改
-- 添加字段
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
-- 修改字段数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
-- 修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
-- 修改表名
ALTER TABLE 表名 RENAME TO 新表名;
3.3.4 删除
-- 删除数据库
DROP DATABASE [IF EXISTS] 数据库名;
-- 删除表
DROP TABLE [IF EXISTS] 表名;
-- 删除指定表，并重新创建该表
TRUNCATE TABLE 表名;
-- 删除字段
ALTER TABLE 表名 DROP 字段名;
3.3.5 使用
USE 数据库名;
3.4 DML 常用操作
3.4.1 添加数据
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的；
- 字符串和日期型数据应该包含在引号中；
- 插入的数据大小，应该在字段的规定范围内。
-- 给指定字段添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...);
-- 给全部字段添加数据，值1 就是表中第一个字段的值
INSERT INTO 表名 VALUES(值1,值2,...);
-- 批量添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
3.4.2 修改数据
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,...[WHERE 条件];
3.4.3 删除数据
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据；
- DELETE 语句不能删除某一个字段的值(可以使用 UPDATE)。
DELETE FROM 表名 [WHERE 条件];
3.5 DQL 常用操作
DQL 的执行顺序：from → where → group by → select → order by → limit。
3.5.1 基础查询
SELECT 字段名1, 字段名2,... FROM 表名;
-- 查询所有字段
SELECT * FROM 表名;
-- 设置别名 AS 可以省略
SELECT 字段名1[AS 别名], 字段名2[AS 别名],... FROM 表名;
-- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
3.5.2 条件查询
运算符列表：
| 比较运算符 | 功能 | 逻辑运算符 | 功能 | |||
|---|---|---|---|---|---|---|
| 大于 | AND 或 && | 并且(多个条件同时成立) | ||||
| >= | 大于等于 | OR 或 | 或者(多个条件任意一个成立) | |||
| < | 小于 | NOT 或 ! | 非，不是 | |||
| <= | 小于等于 | |||||
| = | 等于 | |||||
| <> 或 != | 不等于 | |||||
| BETWEEN ... AND ... | 在某个范围之内(含最小、最大值) | |||||
| IN(...) | 在in之后的列表中的值，多选一 | |||||
| LIKE 占位符 | 模糊匹配(_匹配单个字符, %匹配任意个字符) | |||||
| IS NULL | 是NULL | |||||
SELECT 字段列表 FROM 表名 WHERE 条件列表;
-- 查询 emp 表姓名为两个字的员工信息
select * from emp where name like '__';
-- 查询 emp最后一位为 X 的身份证号
select * from 表mp where idcard like '%X';
3.5.3 聚合函数
将一列数据作为一个整体，进行纵向计算，null 值不参与计算。
常见聚合函数：
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
-- 统计 emp 表员工平均年龄
select avg(age) from emp;
3.5.4 分组查询
where 与having 区别
执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
判断条件不同：where 不能对聚合函数进行判断，而 having 可以。
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
-- 根据性别分组，统计 emp 表男性和女性的数量
select gender, count(*) from emp group by gender;
-- 查询 emp 表年龄小于 45 的员工，并根据工作地址分组，获取员工数量大于等于 3 的工作地址
select workadress, count(*) from emp where age < 45 group by workadress having count(*) >= 3;
注意
- 执行顺序 where > 聚合函数 > having；
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
3.5.5 排序查询
排序方式有两种：ASC 升序(默认值)，DESC 降序。
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2...;
-- 根据年龄对 emp 表公司员工进行升序排序，asc 可以省略
select * from emp order by age asc;
-- 根据年龄对 emp 表公司员工进行升序排序，年龄相同，在按入职时间降序排序
select * from emp order by age asc, entrydate desc;
3.5.6 分页查询
- 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
-- 查询 emp 表第一页员工数据，每页展示 10 条记录
select * from emp limit 0,10;
select * from emp limit 10;
-- 查询 emp 表第二页员工数据，每页展示 10 条记录
select * from emp limit 10,10;
3.6 DCL 常用操作
- 主机名可以用 '%' 通配，表示任意主机都可以访问该 SQL 服务器；
- 这类 SQL 开发人员操作的较少，主要是 DBA(数据库管理员) 使用。
3.6.1 查询用户
USE mysql;
SELECT * FROM user;
3.6.2 创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
3.6.3 修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH '旧密码' BY '新密码';
3.6.4 删除用户
DROP USER '用户名'@'主机名';
3.6.5 权限控制
MySQL 中定义了很多种权限，但是常用的就以下几种：
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
权限相关操作：
- 多个权限之间，使用逗号分隔，用 all 代表所有；
- 授权时，数据库名和表名可以使用*进行通配，代表所有。
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
-- 撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
四、函数
这里指 mySQL 中的内置函数，我们只需要理解并调用。
4.1 常用字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,...Sn) | 字符串拼接，将 S1, S2, ... Sn 拼接成一个字符串 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串 pad 对 str 的左边进行填充，达到 n 个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串 pad 对 str 的右边进行填充，达到 n 个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串 str 从 start 位置起的 len 个长度的字符串 |
-- 填充示列
select lpad('01', 5, '-'); -- 输出'---01'
select rpad('01', 5, '-'); -- 输出'01---'
-- 截取示例
select substring('Hello MySQL', 1, 5); -- 输出'Hello'
4.2 常用数值函数
| 函数 | 功能 |
|---|---|
| CEIL(x) | 向上取整 |
| FLOOR(x) | 向下取整 |
| MOD(x,y) | 返回 x/y 的模，取余 |
| RAND() | 返回 0~1 内的随机数 |
| ROUND(x,y) | 求参数 x 的四舍五入的值，保留 y 位小数 |
-- 生成一个六位数的随机验证码
-- 1. 通过 rand 获取 0-1 之间随机数并乘以一万百得到 5-6 位的数字
-- 2. 通过 round 四舍五入获取整数部分
-- 3. 通过 lpad 补足 6 位数
select lpad(round(rand()*1000000, 0), 6, '0');
4.3 常用日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date, INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔 expr 后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间 date1 和 结束时间 date2 之间的天数，可能是负数 |
-- 获取当前时间 70 天后的时间
select date_add(now(), interval 70 day);
-- 查询 emp 表所有员工入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as 'entrydays' from emp order by entrydays desc;
4.4 常用流程函数
| 函数 | 功能 |
|---|---|
| IF(value, t, f) | 如果 value 为 true，则返回 t，否则返回 f |
| IFNULL(value1, value2) | 如果 value1 不为空，返回 value1，否则返回 value2 |
| CASE WHEN [val1] THEN [res1] ... ELSE [default] END | 如果 val1 为 true，返回 res1，... 否则返回 default 默认值 |
| CASE [expr] WHEN [val1] THEN [res1] ... ELSE [default] END | 如果 expr 的值等于 val1，返回 res1，... 否则返回 default默认值 |
-- 查询 emp 表的员工姓名或地址，如果地址是北京/上海展示为”一线城市“，其他展示“二线城市“
select
name,
(case workadress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;
五、约束
约束是作用于表中字段上的规则，用于限制存储在表中的数据，保证数据库中数据的正确、有效性和完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
一个字段可以添加多个约束。
-- 用户表示例
create table user(
id int primary key auto_increment comment '主键',
name varchar(10) not null unique comment '姓名',
age check (age > 0 && age <= 120) comment '年龄',
) comment '用户表';
-- 外键约束创建方式
-- 1. 在创建表时
CREATE TABLE 表名(
[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
);
-- 2. 现有字段
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名);
-- 删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
外键约束的删除/更新行为规则，通过 ON UPDATE / ON DELETE 设置：
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 RESTRICT 一致) |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。(与 NO ACTION 一致) |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。 |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持) |
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名) ON UPDATE CASCADE ON DELETE CASCADE;
六、多表查询
指从多张表中查询数据。
笛卡尔积：笛卡尔乘积是指在数学中，两个集合 A 集合和 B 集合的所有组合情况。(在多表查询时，需要消除无效的笛卡尔积)
6.1 表结构关系
各个表结构之间的联系一般分为三种：一对多/多对一、多对多、一对一。
- 一对多/多对一：在多的一方建立外键，指向一的一方的主键。
- 多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。
- 一对一：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的(UNIQUE)。多用于单表拆分，比如基础字段表和详情字段表，以提升操作效率。
6.2 内连接查询
查询交集部分数据。
隐式内连接
SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;
-- 查询员工和所属部门情况，并通过连接条件消除笛卡尔积
select * from emp, dept where emp.dept_id = dept.id;
显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
-- 查询员工和所属部门情况，并通过连接条件消除笛卡尔积
select * from emp inner join dept on emp.dept_id = dept.id;
6.3 外连接查询
左外连接：查询左表所有数据以及交集数据；
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件...;
-- 查询员工 emp 表所有数据和所属部门 dept 信息
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id;
右外连接：查询右表所有数据以及交集数据。
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 连接条件...;
-- 查询部门 dept 所有数据和对应员工信息
select d.*, e.name from emp e right outer join dept d on e.dept_id = d.id;
6.4 自连接查询
当前表与自身的连接查询，自连接必须使用表别名。可以是内连接也可以是外连接。
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
-- 在员工表 emp 中，查询员工及其所属领导(内连接)
select a.name, b.name from emp a, emp b where a.manager_id = b.id;
-- 在员工表 emp 中，查询员工及其所属领导(左外连接)
select a.name '员工', b.name '领导' from emp a left join emp b on a.manager_id = b.id;
6.5 联合查询
对于联合查询(union)，就是把多次查询结果合并起来，形成一个新的查询结果集。不带 ALL 会自动去重。
多张表的列数和字段类型必须保持一致。
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B...;
6.6 子查询
SQL 语句中嵌套 SELECT 语句，称为嵌套查询，又称子查询。
子查询外部的语句可以是 INSERT/UPDATE/DELETE/SELECT 任何一个。
SELECT * FROM table1 WHERE column1 = (SELECT column1 FROM table2);
-- 在员工表 emp 中，查询部门是在线公司的员工
-- 先查出在线公司的部门 id，在根据 dept_id 查询对应人员
select name, dept_id from emp where dept_id in (select id from dept where dept_name = '在线公司');
-- 查询所有部门并统计员工数量
select d.id, d.dept_name, (select count(*) from emp e where e.dept_id = d.id) '人数' from dept d;
按查询结果分类：
- 标量子查询(子查询结果为单个值)；
- 列子查询(子查询结果为一列，可以是多行)；
- 行子查询(子查询结果为一行，可以是多列)；
- 表子查询(子查询结果为多行多列)，常用操作符 IN。
七、事务
事务是一组操作的集合，是不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败。
7.1 事务操作
通过修改提交方式来控制事务：
-- 查看/设置事务提交方式 @@表示查看系统变量
SELECT @@autocommit;
SET @@autocommit = 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
通过命令显示开启事务：
-- 开启事务
START TRANSACTION 或 BEGIN;
7.2 四大特性
- 原子性(Atomicity)：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
7.3 并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另外一个事务还没有提交的数据。 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。 |
7.4 事务隔离级别
用来解决并发事务所引发的问题。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | ✅ | ✅ | ✅ |
| Read committed | ❌ | ✅ | ✅ |
| Repeatable Read(mySQL 默认) | ❌ | ❌ | ✅ |
| Serializable | ❌ | ❌ | ❌ |
-- 查看事务隔离级别
SELECT @@transaction_isolation;
-- 设置事务隔离级别 session 表示当前会话，global 表示所有会话
SET [SESSION|GLOBAL] TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | ...];
事务隔离级别越高，数据越安全，但性能越低。
八、索引
索引（index）是帮助 MySQL 高效获取数据的数据结构（有序） 。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
优势：
- 提高数据检索的效率，降低数据库的IO成本；
- 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
劣势：
- 索引列也是要占用空间的；
- 索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低.
8.1 索引结构
| 索引结构 | 描述 | 支持引擎 |
|---|---|---|
| B+Tree 索引（默认） | 最常见的索引类型，大部分引擎都支持 B+ 树索引 | InnoDB、MyISAM、Memory |
| Hash 索引 | 底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询 | InnoDB （自适应 hash 功能）、Memory |
| R-tree (空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少 | MyISAM |
| Full-text (全文索引) | 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene/Solr/ES | InnoDB（v5.6 之后）、MyISAM |
B+Tree 结构：
MySOL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。相较于二叉树层级更少，搜索效率更高；相较于 BTree 一页中存储的键值更少，性能更高；相较于 Hash，B+Tree 支持范围匹配及排序操作。
8.2 索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建, 只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在 InnoDB 中，根据索引的存储形式，又可以分为以下两种：
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 (Clustered Index) | 将数据存储与索引放到了一块，索引结构的叶子节点挂的是行数据 | 必须有，而且只有一个 |
| 二级索引 (Secondary Index)（辅助索引、非聚集索引） | 将数据与索引分开存储，索引结构的叶子节点挂的是对应的主键 | 可以存在多个 |
聚集索引选取规则：
- 如果存在主键，主键索引就是聚集索引；
- 如果不存在主键，将使用第一个唯一(UNIQUE)索引作为聚集索引；
- 如果表没有主键，或没有合适的唯一索引，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引。
8.3 索引语法
-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX 索引名 ON 表名(字段名1,...);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
DROP INDEX 索引名 ON 表名;
8.4 性能分析
8.4.1 查看 SQL 执行频率
MySQL客户端连接成功后，通过 show [session | [global] status 命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 INSERT、UPDATE、DELETE、SELECT 的访问频次：
SHOW GLOBAL STATUS LIKE 'Com____'; -- 匹配以 Com 开头，后面恰好跟 n 个任意字符的所有字符串
8.4.2 查看慢查询日志
慢查询日志记录了所有执行时间超过指定参数 (long_query_time，单位:秒，默认10秒) 的所有 SQL 语句的日志。MySQL 的慢查询日志默认没有开启，需要在MySQL的配置文件 (/etc/my.cnf) 中配置如下信息：
# 开启 MySQL 慢日志查询开关
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long_query_time=2
8.4.3 查看 profiles 详情
show profiles 能够在做 SQL 优化时帮助我们了解时间都耗费到哪里去了。通过 have profiling 参数，能够看到当前 MySQL 是否支持 profile 操作：
SELECT @@have_profiling;
-- 默认是关闭的，可以通过下面命令开启
SET profiling=1;
-- 查看每一条 SQL 耗时基本情况
SHOW profiles;
-- 查看指定 query_id 的 SQL
SHOW profile FOR query query_id;
-- 查看指定 query_id 的 SQL 语句的 CPU 情况
SHOW profile cpu FOR query query_id;
8.4.4 查看 explain 执行计划
EXPLAIN 或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
-- 直接在 select 语句之前加上关键字 explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
九、存储过程/存储函数
存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
-- 创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN
--SQL语句;
END;
-- 调用
CALL 名称([参数]);
-- 查询
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='数据库名'; --查询指定数据库的存储过程及状态信息
SHOW CREATE PROCEDURE 存储过程名称; --查询某个存储过程的定义
-- 删除
DROP PROCEDURE [IF EXISTS] 存储过程名称;
9.1 系统变量
系统变量是 MySQL 服务器提供，不是用户定义的，属于服务器层面。分为全局变量(GLOBAL)、会话变量(SESSION)。
-- 查看系统变量
SHOW [SESSION | GLOBAL] VARIABLES; -- 查看所有系统变量
SHOW [SESSION | GLOBAL] VARIABLES LIKE '......'; -- 可以通过 LIKE 模糊匹配方式查找变量
SELECT @@[SESSION | GLOBAL] 系统变量名; -- 查看指定变量的值
-- 设置系统变量
SET [SESSION | GLOBAL] 系统变量名 = 值;
SET @@[SESSION | GLOBAL]系统变量名 = 值;
9.2 用户定义变量
用户定义变量是用户根据需要自己定义的变量，用户变量不用提前声明，在用的时候直接用"@变量名"使用就可以。其作用域为当前连接。
-- 赋值
SET @var_name = expr [, @var_name = expr] ... ;
SET @var_name := expr [, @var_name := expr] ... ;
SELECT @var_name := expr [, @var_name := expr] ... ;
SELECT 字段名 INTO @var_name FROM 表名;
-- 使用
SELECT @var_name ;
9.3 局部变量
局部变量是根据需要定义的在局部生效的变量，访问之前，需要 DECLARE 声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的 BEGIN...END 块。
-- 声明
DECLARE 变量名 变量类型 [DEFAULT ...] ;
-- 变量类型就是数据库字段类型：INT、BIGINT、CHAR、VARCHAR、DATE、TIME 等。
--赋值
SET 变量名 = 值 ;
SET 变量名 := 值 ;
SELECT 字段名 INTO 变量名 FROM 表名 ... ;
9.4 IF 判断
-- 语法
IF 条件1 THEN
......
ELSEIF 条件2 THEN --可选
......
ELSE --可选
...
END IF;
9.5 参数
| 类型 | 含义 | 备注 |
|---|---|---|
| IN | 该类参数作为输入，也就是需要调用时传入值 | 默认 |
| OUT | 该类参数作为输出，也就是该参数可以作为返回值 | |
| INOUT | 既可以作为输入参数，也可以作为输出参数 |
用法：
CREATE PROCEDURE 存储过程名称 (IN/OUT/INOUT 参数名 参数类型)
BEGIN
SQL语句
END ;
-- out 参数在传入时使用用户自定义变量接收
9.6 CASE
-- 语法一
CASE 表达式
WHEN 表达式的值是value1 THEN sql语句1;
[WHEN 表达式的值是value2 THEN sql语句2...;]
[ELSE sql语句;]
END CASE;
--语法二
CASE
WHEN 条件表达式1 THEN sql语句1;
[WHEN 条件表达式2 THEN sql语句2...;]
[ELSE sql语句;]
END CASE;
9.7 循环
WHILE 循环是有条件的循环控制语句。满足条件后，再执行循环体中的SQL语句。具体语法为：
-- 先判定条件，如果条件为 true，则执行逻辑，否则，不执行逻辑
WHILE 条件 DO
SQL逻辑 ...;
END WHILE;
REPEAT 是有条件的循环控制语句，当满足条件的时候退出循环。具体语法为：
-- 先执行一次逻辑，然后判定逻辑是否满足，如果满足，则退出。如果不满足，则继续下一次循环
REPEAT
SQL 逻辑...;
UNTIL 条件
END REPEAT;
LOOP 实现简单的循环，如果不在 SQL 逻辑中增加退出循环的条件，可以用其来实现简单的死循环。LOOP 可以配合以下两个语句使用：LEAVE 配合循环使用，退出循环；ITERATE 必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环。
-- begin_label 和 end_label 是自定义的标记
[begin_label:] LOOP
SQL逻辑...
END LOOP[end_label];
LEAVE label; -- 退出指定标记的循环体
ITERATE label; -- 直接进入下一次循环
9.8 游标/光标
游标(CURSOR)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下：
-- 声明游标
DECLARE 游标名称 CURSOR FOR 查询语句;
-- 打开游标
OPEN 游标名称;
-- 获取游标记录
FETCH 游标名称 INTO 变量[,变量];
-- 关闭游标
CLOSE 游标名称;
9.9 条件处理程序
条件处理程序(Handler)可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。具体语法为：
DECLARE handler_action HANDLER FOR condition_value [, condition_value]... statement;
-- handler_action 可选值
CONTINUE: 继续执行当前程序
EXIT: 终止执行当前程序
-- condition_value 可选值
SQLSTATE: 状态码，如 02000
SQLWARNING: 所有以 01 开头的 SQLSTATE 代码的简写
NOT FOUND: 所有以 02 开头的 SQLSTATE 代码的简写
SQLEXCEPTION: 所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 代码的简写
9.10 存储函数
存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的。具体语法如下：
CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic...]
BEGIN
-- SQL语句
RETURN ...;
END;
-- characteristic 说明:
DETERMINISTIC: 相同的输入参数总是产生相同的结果
NO SQL: 不包含 SQL 语句
READS SQL DATA: 包含读取数据的语句，但不包含写入数据的语句
十、锁
锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源(CPU、RAM、/O)的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。
10.1 全局锁
锁定数据库中的所有表。全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。其典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性。
# 加锁
flush tables with read lock
# 解锁
unlock tables
10.2 表级锁
每次操作锁住整张表。。锁定粒度大，发生锁冲突的概率最高，并发度最低。应用在 MyISAM、InnoDB、BDB 等存储引擎中。
10.2.1 表锁
表锁分为两类，表共享读锁（read lock）、表独占写锁（write lock）。
读锁：当前客户端只能读不能写，其他客户端可以读，写入操作被阻塞；
写锁：当前客户端既能读也能写，其他客户端读写操作都被阻塞。
# 加锁
lock tables 表名... read/write
# 解锁
unlock tables / 客户端断开连接
10.2.2 元数据锁（meta data lock, MDL）
元数据也可与理解为表结构，MDL 加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL 锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免 DML 与 DDL 冲突，保证读写的正确性。
在 MySQL5.5 中引入了MDL，当对一张表进行增删改查的时候，加 MDL 读锁(共享)；当对表结构进行变更操作的时候，加 MDL写锁(排他)。
10.2.3 意向锁
为了避免DML在执行时，加的行锁与表锁的冲突，在 InnoDB 中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
- 意向共享锁(IS)：由语句 select...lock in share mode 添加。与表锁共享锁（read）兼容，与表锁排他锁（write）互斥。
- 意向排他锁(IX)：由 insert、update、delete、select...for update 添加。与表锁共享锁及表锁排他锁都互斥，意向锁之间不互斥。
10.3 行级锁
每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。应用在 InnoDB 存储引擎中。InnoDB 的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。如果不通过索引检索数据，那么 InnoDB 将对表中所有记录加锁，此时会升级为表锁。对于行级锁，主要分为三类。
10.3.1 行锁
锁定单个行记录的锁，防止其他事务对此行进行 update 和 delete。在 RC、RR 隔离级别下都支持。有以下两种：
- 共享锁(S)：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
- 排他锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。
10.3.2 间隙锁/临键锁
间隙锁：锁定索引记录间隙(不含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行 insert，产牛幻读。
临键锁：next-key lock，行锁和间隙锁组合，同时锁住数据并锁住数据前面的间隙 Gap。
默认情况下，InnoDB 在 REPEATABLE READ 事务隔离级别运行，InnoDB 使用 next-key lock 进行搜索和索引扫描，以防止幻读。
- 索引上的等值查询(唯一索引)，给不存在的记录加锁时，优化为间隙锁。
- 索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，next-key lock 退化为间隙锁。
- 索引上的范围查询(唯一索引)--会访问到不满足条件的第一个值为止。
十一、总结
这篇文章总结了 MySQL 中最核心的概念和日常用法——从建表、数据类型到增删改查、索引和事务。掌握这些，至少能让你在面对 AI 生成的 SQL 时，知道它在做什么、哪里可能有问题。
但数据库的世界远不止这些。我们没聊到的还有：
- 存储引擎（InnoDB vs MyISAM 的选择与差异）
- 性能调优（慢查询分析、执行计划解读）
- 运维管理（备份恢复、主从复制、分库分表）
- 高级特性（视图、触发器、事件调度器）
这些不是基础的范畴，但当你涉及到系统架构设计时，它们会逐个找上门来。
AI 可以帮你写代码，但无法替你承担线上事故的责任。 先把基础打牢，再让 AI 当你的加速器。
