MySQL

使用的是 mysql Ver 8.0.30。

MySQL 数据类型

MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。

整数类型

当使用 int 类型创建一个字段的时，最终会显示为 int(5)，5 表示数据的显示长度，单独使用没有效果只有配合 ZEROFILL 关键字的时候才有效果；当数据长度不足 5 位的时候，前面会补 0。

使用 UNSIGNED 声明无符号数据类型

浮点类型

使用 DECIMAL(M,D) 的方式表示高精度小数，其中 M 被称为精度 0 <= M <= 65，D 被称为标度 0 <= D <= 30，并且 D < M。

例如 DECIMAL(5,2) 取值范围就是 -999.99~999.99。

日期与时间类型

当日期保存为 TIMESTAMP 类型时，服务器通过会话时区将日期转换为 UTC，当获取数据时再将 UTC 转换为会话时区。

一般存注册时间、商品发布时间等，不建议使用 DATETIME 存储，而是使用时间戳（SELECT UNIX_TIMESTAMP();），因为 DATETIME 虽然直观，但不便于计算。

字符串类型

CHAR(M) 不足 M 长度会在右侧填充空格以达到指定长度，而 VARCHAR(M) 并不会补齐。

SQL 运算符

SQL 算术运算符，假设变量 a 的值是 10，变量 b 的值是 20，以下为各运算符执行结果：

SQL 比较运算符，假设变量 a 的值是 10，变量 b 的值是 20，以下为各运算符执行结果：

SQL 逻辑运算符，这是在 SQL 所有的逻辑运算符的列表：

字符集的相关操作

在 MySQL 8.0 版本之前，默认的字符集为 latin1，如果设置为 utf8 则指向 utf8mb3，从 MySQL 8.0 开始，数据库的默认编码改为 utf8mb4，从而避免上述乱码的问题。

通过下面命令查看和修改字符集：

# 查看 MySQL 字符集编码
show variables like '%character%';

# 查看数据库或表的字符集
show create database <数据库名>;
show create table <表名>;

# 修改字符集
alter database <数据库名> character set 'utf8';
alter table <表名> convert to character set 'utf8';

下面是查看 MySQL 字符集编码的返回结果：

• character_set_client：服务器解码请求时使用的字符集
• character_set_connection：服务器处理请求时把请求字符串从 character_set_client 转为 character_set_connection
• character_set_database：当前数据库的字符集
• character_set_results：服务器向客户端返回数据时使用的字符集
• character_set_server：服务器级别的字符集

字符集比较规则

字符集的意思就是一个字符所占用的最大字节长度，utf8 字符集表示一个字符需要占用 1~4 个字节；但是并不是所有字符都需要占用 4 个字节，而是占用 1~3 个字节就可以表示了。

在某些方面占用过多会影响系统的存储和性能，所以设计 MySQL 的设计者偷偷定义了两个概念：

• utf8mb3：阉割过的 utf8 字符集，只占用 1~3 个字节表示字符，MySQL 中的 utf8 就是 utf8mb3 的别名
• utf8mb4：正宗的 utf8 字符集，只占用 1~4 个字节表示字符

通过 show CHARSET; 查看 MySQL 都支持哪些字符集：

上图中，MySQL 版本一共支持 41 中字符集，其中的 Default collation 列表示这个字符集的默认的比较规则，里面包含着该比较规则主要用于哪种语言，比如 utf8_polish_ci 表示一波兰语的规则比较，utf8_general_ci 是一种通用的比较规则；使用 show collation like 'utf8%'; 获取比较规则。

名称后缀意味着该比较规则是否区分语言中的重音、大小写，具体可以用的值如下：

比如 utf8_general_ci 这个比较规则是以 ci 结尾的，说明不区分大小写。

子查询

子查询指一个查询语句嵌套在另一个查询语句的内部。

SQL 中子查询的使用大大增加了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从一个表中先计算得出一个数据结果，然后与这个数据结果进行比较。

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

外层的 SELECT 称为外查询，内部的 SELECT 称为子查询或内查询。

单行子查询

单行子查询就是，子查询只返回一行数据，一行数据可以有多个列；可以直接使用的操作符：=、>、>=、<、<=、!=。

下面是一个简单的单行子查询例子，通过身份证号码从 table2 中获取 id，然后通过 table1 的 id 找到个人信息：

SELECT * FROM table1 
WHERE id = (
    SELECT id # 这里保证了返回一列
    FROM table2 
    WHERE column_id = <身份证号> # 这里保证了返回一行数据
);

但是现在想通过这个人所在的地区和性别，查询出这个地区的人，那么会写成下面这个样子：

SELECT * FROM table1 
WHERE area = (
    SELECT area FROM table2 WHERE column_id = <身份证号>
)
AND sex = (
    SELECT sex FROM table2 WHERE column_id = <身份证号>
);

如果你的条件还要增加，那么还要继续添加子查询，所以可以改成成对查询：

SELECT * FROM table1 
WHERE (area,sex) = (
    SELECT area,sex FROM table2 WHERE column_id = <身份证号>
);

单行子查询只要返回一行就行了，如果需要返回多列可以使用成对查询。

HAVING 子查询

查询其它部门的最低工资大于 50 号部门的最低工资，并获取这些部门的 ID 和最低工资：

SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id # 2.对所有部门进行分组
HAVING MIN(salary) > ( # 3.进行条件筛选，注意这里没有使用 WHERE 因为它不能使用聚合函数
    SELECT MIN(salary) # 1.先查询出 50 号部门的最低工资
    FROM employees
    WHERE department_id = 50
);

CASE 子查询

location_id 为 1800 则 department_id 显示 Canada 否则显示 USA。

SELECT
    CASE department_id WHEN (
        SELECT department_id FROM employees WHERE location_id = 1800
    ) THEN 'Canada' ELSE 'USA' "location"
FROM employees

多行子查询

多行子查询，就是子查询返回了多行数据，当然也可以有多列；可以直接使用的操作符：IN、ANY（SOME）、ALL。

ANY 用法：

SELECT * FROM table1 WHERE column_name > ANY (<子查询可以返回多行>);

可以将 ANY 理解为 Java 中的 ||，只有满足任意一个条件就行；假设子查询返回的结果集为 100,500,900，那么只要 column_name 的值大于其中一个就行了。

假设 column_name 值为 200 那么它大于 100 所以满足条件，假设 column_name 值为 1000 那么它大于 900 所以满足条件。

而 ALL 可以理解为 Java 中的 &&，所有条件都必须满足才行。

IN 和 ANY 也差不错，只不过 IN 处理的是 =。

相关子查询

在内查询的语句中，使用了外查询的表。

获取每个部门的平均工资，找到每个部门中大于这个平均工资的员工。

SELECT *
FROM table1 t1
WHERE 工资 > (
    SELECT AVG(工资)
    FROM table2 t2
    # 每个部门的平均工资，所以不能是一个固定值，所以这个值获取的是 t1 表中的部门 ID
    WHERE department_id = t1.`department_id`
);

t1 表中有多行数据，所以每行数据都会传递到子查询中，然后获取这行数据的 department_id 值并计算出部门的平均值，最后再将这个平均值和当前这行数据的工资进行比较。

ORDER BY 子查询

根据 name 进行排序，name 实在 table2 表中。

SELECT *
FROM table1 t1
ORDER BY (
    SELECT name
    FROM table2 t2
    WHERE t2.id = t1.id
);

当子查询返回 name 的值后，会将当前行的数据放入虚表中（如果处理第二行的数据，那么当前行是 2），每次放入的时候都会通过 name 的值进行排序。

子查询后面是可以设置表名的，这就相当于将子查询的结果当作一张表使用；如果不设置表名，那么只是使用子查询的结果值。

另外在 SELECT 语句中，除了 GROUP BY 和 LIMIT 之外，其他位置都可以使用子查询。

事务

事务就是一组 SQL，这些 SQL 要么都被提交，要么都取消，就是为了保证数据的 最终一致性。

事务的 ACID 特性

• 原子性(Atomicity)：要么都被提交，要么都取消。
• 一致性(Consistency)：就是保证事务失败后，回滚之前的操作。
• 隔离性(Isolation)：就是事务在并发执行的时候，通过 隔离级别 保证数据不出问题。
• 持久性(Durability)：事务提交，就是对数据库做修改，然后等待数据库刷盘到 重做日志 中。

事务的状态

• 活动的（active）：事务正在执行 SQL。
• 部分提交的（partially committed）：事务中的 SQL 已经执行完了，准备将数据写到硬盘上。
• 提交的（committed）：数据成功写到硬盘上了。
• 失败的（failed）：在事务执行时候出现了某些错误或人为的停止了事务。
• 中止的（aborted）：当回滚操作执行完毕(数据库恢复到了执行事务之前的状态)。

显示事务与隐式事务

• 隐式事务：就是将每条增、删、改语句都包装为一个单独的事务，并且会添加排它锁。

• 显示事务：就是通过 BEGIN; 关键字开启事务。

  begin;
  insert into dbtest values(7);
  rollback; # 或者使用 commit 提交
  

  当然还可以使用 SAVEPOINT <保存点名字> 关键字声明一个保存点，当出现异常后通过 ROLLBACK TO <保存点名字> 回滚到某个保存点，通过保存点避免多次执行相同语句。

并发问题与四种隔离级别

下面是 5 个并发问题：脏读、不可重复读、幻读。

剩下的 2 个并发问题：

• 更新丢失：两个或多个事务同时更新相同的数据，但只有一个事务的更新被保留，而其他事务的更新丢失。
• 死锁：多个事务相互等待对方释放资源，导致所有事务无法继续执行。

四种隔离级别

通过 SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '<隔离级别>' 设置隔离级别，通过 select @@transaction_isolation; 查看隔离级别。

每个数据库厂商对 SQL标准 的支持可能不能一样，MySQL 的 REPEATABLE-READ 隔离级别，已经解决了 幻读。

为了解决并发问题，MySQL提供了不同的隔离级别：读未提交、读已提交、可重复读、串行化。

1. 读未提交：事务可以读取到其它事务未提交的数据；可能导致脏读、不可重复读和幻读问题。

   

2. 读已提交：事务只能读取已经提交的数据；这样可以避免脏读，但是仍然可能出现不可重复读和幻读问题。

   

3. 可重复读（Innodb 默认）：在这个事务开始的时候，会通过 MVCC 创建一个快照，以后的所有读操作都使用这个快照，解决了脏读、不可重复读和幻读问题。

   

4. 串行化：事务必须排队执行，不能并行。

   

锁

一条普通的 SELECT 语句是没有锁的，给一条普通的 SELECT 语句加上锁，就是把查询出来的数据加上锁，阻塞其它事务，防止操作这些数据。

假设事务1，修改了 id = 1 的这一条数据，事务2 获取 id = 1 的时候 SELECT 语句加上了锁，那么这条 SELECT 语句会阻塞，一直等待事务1提交或回滚才会返回。

共享锁 (S 锁)和排它锁 (X 锁)：

共享锁 (S 锁)：其它的事务可以读，但是写的时候必须等待。

SELECT ... LOCK IN SHARE MODE [nowait | skip locked]; 或  SELECT ... FOR SHARE [nowait | skip locked];
# nowait: 事务被阻塞后立即返回错误.
# skip locked: 返回没有加锁的数据.

排它锁 (X 锁)：其它的事务不能读也不能写。

SELECT ... FOR UPDATE [nowait | skip locked];

这两个锁的作用和 Java 中的可重入读写锁是一样的。

1.事务 A 中 SELECT 使用排它锁，事务 B 中是可以使用普通的 SELECT。

2.在 InnoDB 中，这种写法是行锁，表锁是下面这两个：
   共享锁：LOCK TABLES test_name READ;
   排它锁：LOCK TABLES test_name WRITE;

意向锁

假设你要给某张表添加一个表级别的共享锁或排它锁，它（InnoDB）会先检查这张表有没有行锁；然后这里就使用了意向锁，就是为了提高检查速度。添加了行锁后，就会自动(在表级别)添加意向锁。

• 意向共享锁：使用了行级别的共享锁，就会自动添加意向共享锁。
• 意向排它锁：使用了行级别的排它锁，就会自动添加意向排它锁。

意向锁之间没有互斥（如果互斥，那么这个意向锁的作用就和表锁一样了），它主要就是和表级别的共享锁和排它锁互斥：

记录锁（行锁）

一个记录锁，锁住一行数据。下面这个例子可以说它是一个排它锁的记录锁，当然也可以加加共享锁。

select * from xxx where id = 2 for update;

间隙锁（Gap 锁）

锁定一个区间范围，禁止在这个区间中插入数据（可以用来解决幻读）。

-- 因为锁定的是一个区间，所以共享锁和拍它锁的效果是一样的。
select * from xxx where id = 5 for share;
select * from xxx where id = 5 for update;

-- 假设 id 3 到 8 之前没有任何数据，这里锁定的又是 id 5，所以插入数据时 id 是 4 到 7 的话，会阻塞。
-- 间隙锁就就是给数值之间加锁，id 只要是 3 和 8 之间的就行了。
--
-- 如果 id = 8 之后没有数据了，那么这个时候间隙锁就是从 9 开始一直到无穷大。

1.id 列有索引，查询的值不存在，就会优化为间隙锁。

2.id 列有没有索引，查询的值不存在，就会给表中的所有行添加记录锁。

如果有索引的列和没有索引的列同时为查询条件，会按照有索引的列添加间隙锁。

临键锁（Next-Key 锁）

就是间隙锁加记录锁；间隙锁只是锁定了一个区间范围（4 到 7），并没有锁定边界（3 和 8），临键锁就是给边界加上记录锁。

-- 重点是 =, 会给 3 和 8 加记录锁.
-- 这里写 share 和 update, 对 3 和 8 有区别.
select * from xxx where id >= 3 and id <= 8 for update;

间隙锁和临键锁，必须是 REPEATABLE-READ 隔离级别, 并且只对数值类型的字段有用，还有就是它们的查询条件都可以是一个范围，是临键锁还是间隙锁就看有没有等号。

REPEATABLE-READ 级别下会有间隙锁, 会提高死锁发生的概率; 死锁就是两个事务相互等待.

查看锁

select OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_DATA from performance_schema.data_locks;

SELECT 语句的执行顺序

比如你写了一个 SQL 语句，那么它的关键字顺序和执行顺序是下面这样的：

(9) SELECT (10) DISTINCT <column>,
(6) AGG_FUNC(<column> or <expression>), ...
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(7) WITH {CUBE|ROLLUP}
(8) HAVING<having_condtion>
(11) ORDER BY<order_by_list>
(12) LIMIT <limit_number>

1. 对 FROM 子句中的表生成虚拟表（virtual table）VT1

2. 通过 ON 条件筛选 VT1 表，并将满足条件的数据放到 VT2 中

3. 根据内连接和外连接的不同，根据 VT2 生成的 VT3 也不同：

   • LEFT JOIN：把左表记为保留表
   • RIGHT JOIN：把右表记为保留表
   • FULL JOIN：把左右表都作为保留表

4. 通过 WHERE 条件筛选 VT3 表，并将满足的数据放到 VT4 中。

5. 如果 SQL 语句中存在 group by，则会对虚表 VT4 进行分组，产生出 VT5，紧接着会执行聚合函数。

6. 聚合函数

7. 对 VT5 应用 ROLLUP 或 CUBE 选项，生成 VT6

   CUBE 和 ROLLUP 区别如下：

   • CUBE：生成的结果数据集显示了所选列中值的所有组合的聚合
   • ROLLUP：生成的结果数据集显示了所选列中值的某一层次结构的聚合

8. Group by 执行完毕，会继续执行 having 过滤，从而生成 VT7

9. 执行完上述语句后，就会执行 select 语句了，将虚表 table7 中的列，针对 select 进行筛选，随后生成出虚表 table8

10. 将重复的行从虚拟表 VT8 中移除，产生虚拟表 VT9

11. 将虚拟表 VT9 中的行按 ORDER BY 子句中的列/列表排序，生成游标 VC10 ，注意不是虚拟表。因此使用 ORDER BY 子句查询不能应用于表达式。同时，ORDER BY子句的执行顺序为从左到右排序，是非常消耗资源的

12. 从 VC10 的开始处选择指定数量行，生成虚拟表 VT11，并返回调用者。

参考资料

https://learnku.com/articles/39938

https://juejin.cn/post/6987373561836994590

https://blog.amis.com/database-transaction-isolation-a1e448a7736e

https://buildatscale.tech/transaction-isolation-level-in-innodb/

https://javaguide.cn/database/mysql/mysql-logs.html

https://www.rsthe.com/archives/mysqlredo日志

https://en.wikipedia.org/wiki/ACID

https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

本栏目推荐文章
• Jmeter 如何连接mysql数据库？
• 如何通过Navicat把MySQL远程数据库导入到本地数据库
• 【LeetCode 2701. 连续递增交易】MySQL用户变量编程得到严格递增连续子序列的开始位置，结束位置，长度等统计信息
• 【Leetcode 2474. 购买量严格增加的客户】MySQL用户变量编程解决严格递增连续子序列问题
• mysql发生连接异常Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
• 【LeetCode 2494. 合并在同一个大厅重叠的活动】MySQL用户变量编程解决区间合并问题
• 如何处理Mysql死锁
• 【LeetCode1747. 应该被禁止的 Leetflex 账户】MySQL用户变量编程；尝试维护一个multiset
• Mysql时间常用函数
• 【Leetcode1949. 坚定的友谊】使用MySQL在无向图中寻找{"CompleteTripartite", {1, 1, 3}}这个pattern