MySQL07-函数&事务

1. 函数

函数是指一段可以直接被另外一段程序调用的程序或代码，在MySQL中，函数已经被内置，只需要进行相关调用操作即可。其中，常用的有字符串函数、数值函数、日期函数和流程函数四类，函数可配合 SQL 的增删查改语句使用。

1.1 字符串函数

MySQL中内置了很多字符串函数，其中常用的如下：

1. concat(s1, s2, ..., sn)，字符串拼接，将 s1, s2, ..., sn 拼接成一个字符串

select concat('Hello', 'World');
-- 输出：HelloWorld

2. lower(str)，将字符串全部转为小写

select lower('Hello World');
-- 输出：hello world

3. upper(str)，将字符串全部转为大写

select upper('Hello World');
-- 输出：HELLO WORLD

4. lpad(str, len, pad)，左填充，用字符串 pad 对 str 的左边进行填充，达到 len 个字符串长度

select lpad('01', 5, '-');
-- 输出：---01

5. rpad(str, len, pad)，右填充，用字符串 pad 对 str 的右边进行填充，达到 len 个字符串长度

select rpad('01', 5, '-');
-- 输出：01---

6. trim(str)，去掉字符串头部和尾部的空格

select trim(' Hello World ');
-- 输出：hello world

7. substring(str, start, len)，切片，返回字符串 str 从 start 位置起的 len 个长度的字符串，start 的起始索引为 1

select substring('Hello World', 1, 5);
-- 输出：hello

1.2 数字函数

MySQL中常用的数字函数如下：

1. ceil(x)，对数字向上取整

select ceil(1.5);
-- 输出：2

2. floor(x)，对数字向下取整

select floor(1.5);
-- 输出：1

3. mod(x,y)，返回 x/y 的模

select mod(6, 4);
-- 输出：2

4. rand()，括号内无需设定值，返回 0-1 间的随机小数

select rand();
-- 输出：0-1间的随机小数

5. round(x,y)，求参数 x 的四舍五入值，并保留 y 位小数

select round(3.14159, 3);
-- 输出：3.142

1.3 日期函数

MySQL中常用的数字函数如下：

1. curdate()，返回当前日期（年月日）

select curdate();
-- 输出：yyyy-mm-dd

2. curtime()，返回当前时间（时分秒）

select curtime();
-- 输出：hh:mm:ss

3. now()，返回当前日期和时间（年月日时分秒）

select now();
-- 输出：yyyy-mm-dd hh:mm:ss

4. year(date)、month(date)、day(date)，返回指定 date 时间值的年份、月份、日期

select year('2024-01-10'); -- 输出：2024
select month('2024-01-10'); -- 输出：1
select day('2024-01-10'); -- 输出：10

5. date_add(date, interval expr type)，返回 date 时间值加上一个时间间隔 expr 后的时间值，其中 type 的值可为second、minute、hour、day、week、month、year等时间单位。

select date_add('2024-01-10', interval 6 week);
-- 输出：2024-02-21

6. datediff(date1, date2)，返回起始时间 date1 和结束时间 date2 之间的天数

select datediff('2024-01-10', '2025-01-10');
-- 输出：-366

1.4 流程控制函数

1. if(value, t, f)，如果 value 为 true，则返回 t，否则返回 f

select if(false, 'Ok', 'Error'); -- 输出：Error
select if(true, 'Ok', 'Error'); -- 输出：Ok

2. ifnull(value1, value2)，如果 value1 不为 null，返回 value1，否则返回 value2

select ifnull('Ok', 'Error'); -- 输出：Ok
select ifnull(' ', 'Error'); -- 输出：（什么也不输出）
select ifnull(null, 'Error'); -- 输出：Error

3. case when [ val1 ] then [ res1 ] ... else [ default ] end，如果 val1 为 true，返回 res1，否则返回 default 默认值

select stuname,
(case when stuage >= 18 then '成年' else '未成年' end)
from student;

4. case [ expr ] when [ val1 ] then [ res1 ] ... else [ default ] end，如果 expr 的值等于 val1，返回 res1，否则返回 default 默认值

select stuname,
(case addr when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '家庭地址'
from student;

1.5 案例实践

# 将数据库中学生的学号由 3 位数统一为 6 位数，不足六位的在前补 0，如 111 补为 000111

update student set stuid = lpad(stuid, 5, '0');
-- 通过 lpad() 函数对学号不足六位的在前补 0

# 通过数据库的函数，生成一个六位数的随机验证码。

select lpad(round(rand()*1000000, 0), 6, '0');
-- 通过 rand() 函数生成一个 0-1 间的随机小数，因为需要六位数字，将这个小时乘以 1000000，得到一个带小数的六位数
-- 通过 round() 函数将上一步得到的六位数四舍五入并保留 0 位小数
-- 通过 lpad() 函数，防止出现类似于 0.011111 这样的数字乘以 1000000 后不满足六位数的情况，在其前补 0

# 查询所有员工的入职天数，并根据入职天数倒序排序。

select name, datediff(curdate(), entnydate) as 'entrydays' from employee order by entrydays desc;
-- 通过 curdate() 函数获取当前日期
-- 通过 datediff() 函数求出当前日期和数据库中员工入职日期 entnydate 相差的天数

# 统计班级各个学员的成绩，分数 >= 85 为优秀，分数 >= 60 为及格，否则不及格

select id ,name,
(case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) '数学'，
(case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' end )'英语',
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' end )'语文'
from score;
-- 通过 case...when...then...else 函数对各分数段进行分类，分别返回不同的值

2. 事务

在 MySQL 中，事务是一组 SQL 语句的集合，是一个不可分割的工作单位，事务会把所有数据库操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

2.1 事务操作

在 MySQL 中，事务的默认提交方式是自动提交，也就是说，当执行一条 DML 语句，MySQL 会立即将这条语句作为一个事务隐式提交。也就是说，在 MySQL 默认设置中，每一条 SQL 语句都是一个事务。这种情况下，如果一个功能需要多个 SQL 语句联合实现，当其中一个 SQL 语句出现错误，剩下的语句就不再执行，进而会影响到数据的一致性，如以下例子

# 模拟银行转账，在 account 表中存储了张三和李四的账户余额各 2000 元，通过一组 SQL 语句模拟张三给李四转账 1000 元
-- 1. 查询张三账户余额
select * from account where name = '张三'; -- 输出 2000
-- 2. 将张三账户余额 -1000
update account set money = money - 1000 where name = '张三';
-- 3. 将李四账户余额+1000
update account set money = money + 1000 where name = '李四';

# 可以看到，要想实现这个转账，需要分成三个 SQL 语句，并且要保证三个 SQL 语句依次无误实现
# 假设以上第 2 条语句运行后报错，剩下的语句就无法执行，转账就会失败，这时张三的1000元会减去，而李四的钱还是没有增加
# 这样一来，数据就出现了问题，数据的一致性丧失

要想避免这个问题，可以将几条 SQL 语句编为一个事务，在同一个事务里的 SQL 语句将被看做一个整体，要么全部执行，要么全部不执行，可以有效维护数据的一致性和完整性。要想将一组 SQL 语句编为一个事务，需要手动更改事务设置，改自动提交事务为手动提交，以下是两种手动提交事务的设置方法：

方法一（隐式）：

-- 查看事务提交方式
select @@AUTOCOMMIT;
-- 设置事务提交方式，1 为自动提交，0 为手动提交，该设置只对当前会话有效
set @@AUTOCOMMIT = 0;

# SQL 语句

-- 如果 SQL 语句执行成功，则提交事务
commit;
-- 如果 SQL 语句执行时报错，则回滚事务
rollback;

方法二（显式）：

-- 开启事务
start transaction;
或：
begin;

# SQL 语句

-- 如果 SQL 语句执行成功，则提交事务
commit;
-- 如果 SQL 语句执行时报错，则回滚事务
rollback;

无论使用哪种方法，一旦开启手动提交事务后，只有手动输入 commit; 确认才会改变数据库中的数据。

此外，还能在事务中设置保存点，可以使事务回滚到该保存点，而无需全部回滚：

# 设置事务保存点
savepoint savepoint_name;
# 回滚到事务保存点
rollback to savepoint savepoint_name;

说明：

1. 事务在执行过程中发生错误，应该使用 rollback; 语句回滚，回滚后数据将恢复到该事务执行前的状态。
2. 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务；
3. 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行；
4. 事务用来管理 insert、update、delete 语句；

2.2 事务的四大特性(ACID)

一般来说，事务必须满足以下 4 个条件（ACID）：

原子性(Atomicity)：事务是不可分割的最小操作单元，事务中的所有操作要么全部完成，要么全部不完成，不会结束在中间某个环节。
一致性(Consistency)：在事务开始之前和事务结束以后，必须使所有数据都保持一致状态，数据库的完整性不能被破坏。
隔离性(Isolation)：是数据库系统提供的隔离机制，数据库允许多个并发事务同时对其数据进行读写和修改，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
持久性(Durability)：事务处理结束后（提交或回滚），对数据的修改就是永久的，即便系统故障也不会丢失。

2.3 并发事务问题

并发事务是指多个用户或应用程序同时对同一个数据库进行读取和修改操作的情况。当多个事务同时访问数据库时，由于每个事务都不知道其他事务的存在，所做的操作可能会互相覆盖影响，就会导致一些问题。并发事务的主要问题有以下四个：

1. 更新丢失 (Lost update)
   当两个或多个事务读入同一个记录并修改，最后一个事务提交的结果覆盖了前面事务提交的结果，导致前面事务的修改丢失，就造成了更新丢失错误。

2. 脏读 (Dirty Reads)
   一个事务对一条记录做了修改，但还没有提交事务，这时，另一个事务也来读取同一条记录，读取的却是前一个事务修改后的值，之后前一个事务又进行了回滚，记录中的数据恢复到修改前的状态，也就是说后一个事务读取了一个数据库中从未提交的数据，读取的值和数据库中的值不一致，这种现象被叫做脏读。

3. 不可重复读 (Non-Repeatable Reads)
   一个事务读取了一条记录，这个事务还没结束，这时另外一个事务也访问了同一记录，并对该数据进行了修改，此时第一个事务又一次读取了这条记录，发现读到的结果与之前的结果不同。这种一个事务先后读取同一数据，但获得的值不同的情况，就称为不可重复读。

4. 幻读 (Phantom Reads)
   一个事务查询到数据表中有 3 条记录，之后另一个事务新插入了一条记录，此时数据表中存在 4 条记录，此时如果前一个事务要插入一样的第 4 条记录，就会发现第 4 条记录已存在，这种类似读取幻觉的情况就叫做幻读。

不可重复读和幻读的情况很类似，但不可重复读的重点是修改，同样条件下，读取过的数据，再次读取发现值不一样；幻读的重点在于新增或者删除，同样条件下，第一次和第二次读出来的记录数不一样。

2.4 事务隔离级别

2.4.1 隔离级别概述

为了避免并发事务对数据造成的影响，最常用的方法是利用封锁技术进行并发控制。封锁技术的缺点是会造成死锁和性能下降，为了兼顾并发效率与异常控制，定义了以下 4 种隔离级别：

1. 读未提交 (Read uncommitted)
   如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读到此行未提交的数据。该隔离级别可以通过“排他写锁”实现。

2. 读已提交 (Read committed)
   读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行数据，也就是说读取数据的事务可以读取到其他事务已经提交的数据，未提交的写入事务则读取不到。

3. 可重复读 (Repeatable read)
   读取数据的事务将会禁止写入事务（但允许读事务），写入事务则禁止任何其他事务。也就是说，一个读取事务开启后，无论其他写入事务如何进行修改操作，读取的数据始终是一样的。这是 MySQL 默认的隔离级别。

4. 可串行化 (Serializable)
   要求事务串行化执行，事务只能一个接着一个地执行，不能并发执行。串行化是最高的事务隔离级别，同时数据也最安全，但性能极低，实际开发中很少使用。

这 4 种隔离级别可以解决的并发事务问题如下表：

隔离级别	更新丢失	脏读	不可重复读	幻读
读未提交 (Read uncommitted)	√	√	√	√
读已提交 (Read committed)	√	×	√	√
可重复读 (Repeatable read，默认)	×	×	×	√
可串行化 (Serializable)	×	×	×	×

• √ 表示该隔离级别下依然可能发生该类并发问题，× 表示该隔离级别下已经解决该类并发问题
• 各隔离等级的性能：Read uncommitted > Read committed > Repeatable read > Serializable
• 各隔离等级的数据安全性：Serializable > Repeatable read > Read committed > Read uncommitted
• 各隔离等级的开发性能：Read uncommitted > Read committed > Repeatable read > Serializable

2.4.2 隔离级别设置

查看事务隔离级别：

# 查看系统隔离级别
select @@global.tx_isolation;
# 查看会话隔离级别（MySQL5.0以上版本）
select @@tx_isolation;
# 查看会话隔离级别（MySQL8.0以上版本）
select @@transaction_isolation;

设置事务隔离级别：

# 设置会话隔离级别为读未提交
set [session|global] transaction isolation level read uncommitted;
# 设置会话隔离级别为读已提交
set [session|global] transaction isolation level read committed;
# 设置会话隔离级别为可重复读
set [session|global] transaction isolation level repeatable read;
# 设置会话隔离级别为可串行化
set [session|global] transaction isolation level serializable;

-- session 是会话级别，表示只针对当前会话有效，global 表示对所有会话有效。