MySQL 笔记6 -- 函数与事务

MySQL 笔记6 – 函数与事务

---

MySQL 系列笔记是笔者学习、实践MySQL数据库的笔记

课程链接： MySQL 数据库基础入门教程
参考文档：
MySQL 官方文档
SQL 教程

---

一、内置函数

---

1、函数

• 事先编写的实现某些功能的代码集合，可以直接调用
• 函数可以用在SELECT语句及其子句，也可以用在UPDATE,DELETE语句当中
• 格式：SELECT 函数
• 函数分类：字符串函数、数值函数、日期和时间函数、流程函数、其它函数

---

2、字符串函数

函数	说明
COMCAT(s1,s2…sn)	将传入的字符连接成一个字符串，任何字符串与null进行连接结果都是null
INSERT(str,x,y,instr)	将字符串str从x(从1开始)位置开始，y个字符长的子串替换为instr
LOWER(Str)/UPPER(str)	将字符串转成小/大写
LEFT(str,x)/RIGHT(str，x)	返回字符串最左/右边的x个字符，x=null 不返回任何字符
LPAD(str,n,pad)/RPAD(str,n,pad)	用字符串pad对str最左/右边进行填充，直到长度为n个字符
REPEAT(str,x)	返回str重复x次的结果
REPLACE(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
SUBSTRING(str,x,y)	返回字符串str中第x位置起y个字符长度的字符

---

3、数值函数

函数	说明
ABS(x)	返回X的绝对值
CEIL(x)	小数不为零部分上取整，即向上取最近的整数
FLOOR(x)	小数部分下取整，即向下取最近的整数
MOD(X,Y)	返回X/Y的模
RAND()	返回0-1的随机值
RAND()*n	返回0-n的随机值

---

4、日期和时间函数

函数：

函数	说明
CURDATE()	返回当前日期，只包含年月日
CURTIME()	返回当前时间，只包含时分秒
NOW()	返回当前日期和时间，年月日时分秒都包含
UNIX_TIMESTAMP	返回当前日期的时间戳
FROM_UNIXTIME(unixtime)	将一个时间戳转换成日期
WEEK(DATE)	返回当前是一年中的第几周
YEAR(DATE)	返回所给日期是那一年
HOUR(TIME)	返回当前时间的小时
MINUTE(TIME)	返回当前时间的分钟
DATE_FORMAT(date,fmt)	按字符串格式化日期date值
DATE_ADD(date,interval expr type)	计算日期相加
DATEDIFF(date1,date2)	计算两个日期相差的天数

格式化日期的格式符：

• 用在fmt中，形如’%M%D%Y’
• 可以在格式符中插入自定义的符号，形如’%M-%D-%Y’

格式符	说明
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位

---

5、流程函数

函数	说明
IF(value,t,f)	如果value是真，返回t,否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1否者返回value2
CASE WHEN value THEN t ELSE f END	当value是真，返回t,否则返回f

---

6、其它函数

函数	说明
DATABASE()	返回当前数据库名
VERSION()	返回当前数据库版本
USER()	返回当前登陆用户名
PASSWORD(STR)	对str进行加密
MD5()	返回str的MD5值

---

二、自定义函数

---

1、修改命令结束符

• 格式：DELIMITER x，将标准分隔符 ; 更改为自定义的 x
• 因为定义函数时，函数是作为一个整体的，而函数内是多条单独的语句。为了不让让mysql遇到 ；号就解释语句，需要在定义函数开头修改分隔符，完成后把分隔符还原为 ；

---

2、自定义函数

DELIMITER 自定义分隔符
CREATE FUNCTION 函数名() RETURNS 数据类型
begin
语句1；
语句2；
....
return 和要求的数据类型一样的数据；
end 自定义分隔符
DELIMITER ;

• 调用自定义函数格式跟内置函数一致

---

三、事务

---

1、事务

• 不可分割的操作，假设某操作有多个步骤组成，其中任意一个步骤操作失败，则认为事务失败，所有步骤全完成该操作才是成功
• 每条SQL语句都是一个事务，可以把多条SQL语句定义成一个事务
• 事务只对DML语句有效,对于DQL无效

---

2、事务的ACID

• 原子性（Atomicity）：事务包含的所有操作要么全部成功，要么全部失败回滚
• 一致性（Consistency）：事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态，让数据保持一定上的合理，如：一个商品出库时，仓库商品数量减1，对应用户的购物车中商品加1
• 隔离性（Isolation）：当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离
• 持久性（Durability）：一个事务一旦被提交了，就不能再回滚了，已经把数据保存到数据库当中了

---

3、事务的使用

操作	语句	说明
开启事务	start transaction
回滚事务	rollback	销执行的sql语句，必须在提交事务之前
提交事务	commit	事务中的所有语句全部执行完毕时提交事务，若没有发生异常，更新到数据库中

---

4、事务隔离级别

隔离级别：隔离级别越高，数据的完整性和一致性越好，并发性能越低

级别	说明
Read uncommitted（读未提交）	未提交事务B修改了数据，事务A读到就是事务B修改的数据；
Read committed（读已提交）	未提交事务B修改了数据，事务A读到的数据还是之前的；事务B提交后A读到的数据是B修改后的数据；
Repeatable read（可重复读）	未提交事务A开始读取数据后，在提交前读到的数据（select）都是此刻生成的版本，不管事务B是否修改数据是否提交；事务A提交前的insert、update和delete会更新版本为已提交事务B生成的版本；
Serializable（串行）	事务串行化顺序执行，事务A执行后才能执行事务B，同时执行会报错；

查看隔离级别：

• select @@global.tx_isolation,@@tx_isolation;

设置隔离级别：

• 全局的：set global transaction isolation level 隔离级别;
• 当前会话： set session transaction isolation level 隔离级别;

---

5、事务的并发

问题：

问题	说明
脏读	事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
不可重复读	事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致
幻读	未提交事务A查询数据后，已提交事务B刷新了数据库，事务A提交后再查询与上次查询的数据不一致；

各隔离级别会出现的问题：

隔离级别	脏读	不可重复读	幻读
Read uncommitted	是	是	是
Read committed	否	是	是
Repeatable read	否	否	是
Serializable	否	否	否

---

GOOD LUCK!

---