MySQL高级

最新推荐文章于 2021-03-11 07:38:49 发布

ch.ran

最新推荐文章于 2021-03-11 07:38:49 发布

阅读量491

点赞数

分类专栏： LINUX # mysql

本文链接：https://blog.csdn.net/alucardXXOO/article/details/104401417

版权

LINUX 同时被 2 个专栏收录

30 篇文章 0 订阅

订阅专栏

mysql

11 篇文章 0 订阅

订阅专栏

实验环境

部门表
MariaDB [cr]> CREATE TABLE dept(
    -> deptnu INT PRIMARY KEY comment '部门编号',
    -> dname VARCHAR(50) comment '部门名称',
    -> addr VARCHAR(50) comment '部门地址'
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

员工表
MariaDB [cr]> CREATE TABLE employee(
    -> empno INT PRIMARY KEY comment '雇员编号',
    -> ename VARCHAR(50) comment '雇员姓名',
    -> job VARCHAR(50) comment '雇员职位',
    -> mgr INT comment '雇员上级编号',
    -> hiredate DATE comment '雇佣日期',
    -> sal DECIMAL(7,2) comment '薪资',
    -> deptnu INT comment '部门编号'
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建工资等级表
MariaDB [cr]> CREATE TABLE salgrade(
    -> grade INT PRIMARY KEY comment '等级',
    -> lowsal INT comment '最低薪资',
    -> higsal INT comment '最高薪资'
    -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入dept表数据
INSERT INTO dept VALUES (10, '研发部', '北京');
INSERT INTO dept VALUES (20, '工程部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '深圳');

插入emp表数据
INSERT INTO employee VALUES (1009, '唐僧', '董事长', NULL, '2010-11-17', 50000, 10);
INSERT INTO employee VALUES (1004, '猪八戒', '经理', 1009, '2001-04-02', 29750, 20);
INSERT INTO employee VALUES (1006, '猴子', '经理', 1009, '2011-05-01', 28500, 30);
INSERT INTO employee VALUES (1007, '张飞', '经理', 1009, '2011-09-01', 24500,10);
INSERT INTO employee VALUES (1008, '诸葛亮', '分析师', 1004, '2017-04-19', 30000, 20);
INSERT INTO employee VALUES (1013, '林俊杰', '分析师', 1004, '2011-12-03', 30000, 20);
INSERT INTO employee VALUES (1002, '牛魔王', '销售员', 1006, '2018-02-20', 16000, 30);
INSERT INTO employee VALUES (1003, '程咬金', '销售员', 1006, '2017-02-22', 12500, 30);
INSERT INTO employee VALUES (1005, '后裔', '销售员', 1006, '2011-09-28', 12500, 30);
INSERT INTO employee VALUES (1010, '韩信', '销售员', 1006, '2018-09-08', 15000,30);
INSERT INTO employee VALUES (1012, '安琪拉', '文员', 1006, '2011-12-03', 9500, 30);
INSERT INTO employee VALUES (1014, '甄姬', '文员', 1007, '2019-01-23', 7500, 10);
INSERT INTO employee VALUES (1011, '妲己', '文员', 1008, '2018-05-23', 11000, 20);
INSERT INTO employee VALUES (1001, '小乔', '文员', 1013, '2018-12-17', 8000, 20);

插入salgrade表数据
INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

自定义函数

函数注重返回值，不注重执行过程。

创建函数

DELIMITER //
CREATE 
	[DEFINER = {user | CURRENT_USER}]
	FUNCTION [IF NOT EXISTS] func_name([func_parameter[,...]]) 
	RETURNS type
	[SQL SECURITY {DEFINER | INVOKER}]
	[COMMENT 'string']
		BEGIN
			func_body;
			RETURN value;  #必须要有返回值
		END //
DELIMITER ;
	

func_parameter:
 param_name type

type:
 Any valid MariaDB data type

func_body:
 Valid SQL procedure statement

DEFINER：  #创建者
	user：指定'user'@'localhost'
	CURRENT_USER：当前用户

SQL SECURITY { DEFINER | INVOKER }：  #安全方式
	DEFINER：以创建函数的用户权限执行，如果DEFINER用户被删除，当其他用户调用该函数无权限执行
	INVOKER：以调用函数的用户权限执行，如果DEFINER用户被删除，当其他用户调用该函数有权限执行


#注：当开启binlog后，我们创建函数将不被信息，使用下面选项可以解决问题
#set global log_bin_trust_function_creators=TRUE;

查看函数

查看函数内容
show create function func_name;
查看所有函数
show function status like 'func_name';

删除函数

drop function func_name;

函数调用

select func_name([func_parameter[,...]]);

函数语法

局部变量

变量名不应该和表中字段或表名等相同
声明
DECLARE var_name... date_type [DEFAULT VALUE];
赋值
set var=value;
select col_name into var from tb_name where where_condition;  #通过select语句复制，每次查询只能返回1个值

if语句

if condition then
	statement
elseif condition then
	statement 
else
	statement
end if 

condition：
	is null
	is not null
	true：可由declare var int default true;声明
	false
	not true
	not false

case语句

CASE value 
WHEN value1 THEN
	statement 
WHEN value2 THEN
	statement
ELSE  #other value
	statement
END CASE

leave语句

leave label  #跳出循环（break）

iterate语句

iterate label  #跳出循环（continue）

loop语句

[label:] loop  #循环
	statement
	if condition then
		leave label
	end if
end loop

condition：
	is null
	is not null
	true：可由declare var int default true;声明
	false
	not true
	not false

repeat语句

[label:] repeat  #循环
	statement_list 
	until condition  #满足条件退出循环
end repeat

condition：
	is null
	is not null
	true：可由declare var int default true;声明
	false
	not true
	not false

while语句

[label:] while condition do  #循环
	statement_list 
end while

condition：
	is null
	is not null
	true：可由declare var int default true;声明
	false
	not true
	not false

操作

写一个函数根据salgrade表判断各员工的工资等级

delimiter //
MariaDB [cr]> create function judge_salgrade(sal_num int) returns int
    -> begin
    -> declare grade_sal int;
    -> declare low_sal int;
    -> declare hig_sal int;
    -> declare flag int default false;
    -> declare sal_list cursor for select grade,lowsal,higsal from salgrade;
    -> declare continue handler for not found set flag=true;
    -> open sal_list;
    -> while not flag do
    -> fetch sal_list into grade_sal,low_sal,hig_sal;
    -> if sal_num >= low_sal and sal_num <= hig_sal then
    -> return grade_sal;
    -> end if;
    -> end while;
    -> return 0;
    -> end $$
delimiter ;

游标

有数据缓冲的思想：游标的设计是一种数据缓冲区的思想，用来存放SQL语句执行的结果。先有数据基础：游标是在先从数据表中检索出数据之后才能继续灵活操作的技术。类似于指针：游标类似于指向数据结构堆栈中的指针，用来pop出所指向的数据，并且只能每次取一个。

语法

游标声明：
declare cur_name cursor for select_statement;

打开游标：
open cur_name;

使用游标：
declare var1 data_type;
declare var2 data_type;
...
fetch cur_name into var1,var2...  #变量对应select获取的值

游标结束：
declare flag int default true；
declare continue handler for not found set flag=false; #

关闭游标：
close cur_name

操作

遍历employee表，返回所有姓名
#字段是中文，变量应设置编码
#同意的代码函数执行返回编码错误，我不明白
\d //
MariaDB [cr]> create procedure getName()
    -> begin
    -> declare name_text text character set utf8 default '';  #必须给初始值，不然返回未null
    -> declare name_char varchar(256) character set utf8;
    -> declare flag int default true;
    -> declare cur_1 cursor for select ename from employee;
    -> declare continue handler for not found set flag=false;
    -> open cur_1;
    -> while flag do
    -> fetch cur_1 into name_char;
    -> set name_text=concat(name_text,",",name_char);
    -> end while;
    -> close cur_1;
    -> select name_text as names;
    -> end //
\d ;

视图

视图是一张虚拟表，是一系列查询的集合，是存储在数据库中的查询语句。

创建视图

CREATE
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW [IF NOT EXISTS] view_name [(column_list)]
    AS select_statement

DEFINER：  #创建者
	user：指定'user'@'localhost'
	CURRENT_USER：当前用户

SQL SECURITY { DEFINER | INVOKER }：  #安全方式
	DEFINER：以创建视图的用户权限执行，如果DEFINER用户被删除，当其他用户调用该视图无权限执行
	INVOKER：以调用视图的用户权限执行，如果DEFINER用户被删除，当其他用户调用该视图有权限执行

查看视图

查看视图内容
show create view view_name;
查看所有视图
show table status where comment='view';

删除视图

drop view view_name;

调用视图

select col_name... from view_name [where where_condition];

操作

创建视图列出所有视图
#表信息都保存在information_shema数据库中
MariaDB [cr]> create view show_view as
    -> select TABLE_NAME as view_name from information_schema.TABLES 
    -> where TABLE_TYPE='VIEW' and TABLE_SCHEMA=database();

触发器

触发器是当增删改时，条件触发，执行定义的SQL语句。一张表只能有一种类型触发器。

创建触发器

DELIMITER //
CREATE
    [DEFINER = { user | CURRENT_USER}]
    TRIGGER [IF NOT EXISTS] trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    [{ FOLLOWS | PRECEDES } other_trigger_name ]
    BEGIN
    	trigger_body
    END //
DELIMITER ;

trigger_time：
	before：	#在每行变化之后执行
	after：		#在每行变化之前执行

trigger_event：
	insert
	update
	delete

FOLLOWS | PRECEDES other_trigger_name：
	follows：	#在一个触发器后执行
	precedes：	#在一个触发器前执行

trigger_body:
	特殊对象：old、new
	old：		#delete事件中表示删除的旧数据，update事件中表示更新删除的旧数据，old.col_name引用
	new：		#insert事件中表示插入的新数据，update事件中表示更新插入的新数据，new.col_name引用

	语法参见 函数语法

查看触发器

查看触发器内容
show create trigger trigger_name;
查看所有触发器
show triggers [from db_name];

删除触发器

drop trigger [if exists] trigger_name;

操作

使用触发器创建审计

1、创建审计表格
MariaDB [cr]> create table audit(
    -> `aid` int unsigned primary key auto_increment,
    -> `user` varchar(30),
    -> `action` varchar(10),
    -> `empno` int(11),
    -> `time` datetime
    -> ) engine=innodb charset=utf8;
2、创建插入事件
delimiter //
MariaDB [cr]> create trigger audit_insert
    -> after insert on employee for each row
    -> begin
    -> insert into audit(user,action,empno,time) value(user(),'insert',new.empno,now());
    -> end //
delimiter ;
3、创建删除事件
delimiter //
MariaDB [cr]> create trigger audit_delete
    -> after delete on employee for each row
    -> begin
    -> insert into audit(user,action,empno,time) value(user(),'delete',old.empno,now());
    -> end //
delimiter ;
4、创建更新事件
delimiter //
MariaDB [cr]> create trigger audit_update
    -> after update on employee for each row
    -> begin
    -> insert into audit(user,action,empno,time) value(user(),'update',new.empno,now());
    -> end //
delimiter ;

根据dept表中部门编号，限制插入employee插入的部门编号

1、创建函数判断插入值是否在dept表中
delimiter //
MariaDB [cr]> create function exists_dept(dept_num int)
    -> returns int
    -> begin
    -> declare num int;
    -> declare flag int default 0;
    -> declare s_list cursor for select deptnu from dept;
    -> declare continue handler for not found set flag=1;
    -> open s_list;
    -> while 1 != flag do
    -> fetch s_list into num;
    -> if num = dept_num then
    -> return 1;
    -> end if;
    -> end while;
    -> return 0;
    -> end //
delimiter ;
2、创建触发器限制，如果触发器语句执行过程中错误，则实现事务回滚
delimiter //
MariaDB [cr]> create trigger limit_dept
    -> before insert on employee for each row
    -> begin
    -> declare msg varchar(20);
    -> if exists_dept(new.deptnu) then
    -> set msg='ok';
    -> else
    -> select xxx into msg;  #错误语句
    -> end if;
    -> end //
delimiter ;

存储过程

存储过程是一堆sql语句的集合，经编译保存在数据库中，执行速度块。

创建存储过程

CREATE
    [DEFINER = { user | CURRENT_USER}]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [SQL SECURITY { DEFINER | INVOKER }]
    [COMMENT 'string']
	begin
		routine_body
	end

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MariaDB data type

routine_body:
    Valid SQL procedure statement
    语法参见函数

查看存储过程

查看存储过程内容
show create procedure procedure_name;
查看所有存储过程
show procedure status [like 'pattern' | where expr]

删除存储过程

drop procedure procedure_name;

存储过程调用

drop procedure procedure_name

操作

定期创建employee表的副本

\d $$
MariaDB [cr]> create procedure backTable(in tName char(20))
    -> begin
    -> declare new_tName varchar(36);
    -> set new_tName=concat(tName,replace(replace(replace(now(),"-",""),":","")," ",""));
    -> set @sql_1=concat('create table ', new_tName, ' select * from ', tName);
    -> prepare pp_1 from @sql_1;
    -> execute pp_1;
    -> end $$
\d ;

添加定时任务
crontab -u root -e
* * * * * source /etc/profile;mysql -e 'call cr.backTable("employee")' && echo 'employee backup ok!'

事件

事件是用于执行定时或周期性的任务

创建事件

\d $$
CREATE
    [DEFINER = { user | CURRENT_USER}]
    EVENT 
    [IF NOT EXISTS]
    event_name 
    ON SCHEDULE schedule
    [ON COMPLETION [NOT] PRESERVE]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    DO
    BEGIN
    	sql_statement;
    END $$
\d ;
 
schedule:
    AT timestamp [+ INTERVAL interval] ...			#单次执行
    EVERY interval									#重复执行
    STARTS timestamp [+ INTERVAL interval] ...]		#开始执行时间
    ENDS timestamp [+ INTERVAL interval] ...]		#结束时间时间
 
interval:
    quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |
    WEEK | SECOND}

ON COMPLETION [NOT] PRESERVE：
	事件到期后，是否保留。

[ENABLE | DISABLE | DISABLE ON SLAVE]:
	enable:启用
	disable：禁用

开启事件

select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| OFF               |
+-------------------+

set global event_scheduler=on；
+-------------------+
| @@event_scheduler |
+-------------------+
| ON                |
+-------------------+

show processlist;  #能看见event_scheduler
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
| Id | User            | Host      | db   | Command | Time | State                       | Info             | Progress |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+
| 25 | root            | localhost | cr   | Sleep   | 3000 |                             | NULL             |    0.000 |
| 29 | root            | localhost | cr   | Query   |    0 | Init                        | show processlist |    0.000 |
| 30 | event_scheduler | localhost | NULL | Daemon  |  316 | Waiting for next activation | NULL             |    0.000 |
+----+-----------------+-----------+------+---------+------+-----------------------------+------------------+----------+

查看事件

查看事件内容
show create event event_name;
查看全部事件
show events [from schema_name] [like 'pattern']

删除事件

drop event event_name;

修改事件

ALTER
    [DEFINER = { user | CURRENT_USER }]
    EVENT event_name
    [ON SCHEDULE schedule]
    [ON COMPLETION [NOT] PRESERVE]
    [RENAME TO new_event_name]
    [ENABLE | DISABLE | DISABLE ON SLAVE]
    [COMMENT 'comment']
    [DO sql_statement]

操作

1、利用存储过程写的创建副本，在1个月内每天晚上10点和早上6点执行一次

每天6点备份
\d $$
MariaDB [cr]> create event back_event_at_06
    -> on schedule every 1 day
    -> starts date_add(date_add(curdate(), interval 1 day), interval 6 hour)
    -> ends date_add(curdate(),interval 31 day)
    -> on completion preserve enable
    -> do 
    -> begin
    -> call backTable("employee");
    -> end $$
\d ;
每天10点备份
\d $$
MariaDB [cr]> create event back_event_at_22
    -> on schedule every 1 day
    -> starts date_add(date_add(curdate(), interval 1 day), interval 22 hour)
    -> ends date_add(curdate(),interval 31 day)
    -> on completion preserve enable
    -> do 
    -> begin
    -> call backTable("employee");
    -> end $$
\d ;

2、三分钟后创建一次副本

\d $$
MariaDB [cr]> create event show_user
    -> on schedule at now() + interval 3 minute
    -> do 
    -> begin
    -> call backTable("employee");
    -> end $$
\d ;

索引

索引就是根据指定的字段创建一种特殊的数据结构存储，能够减少io次数，提供高效的查询。
我们查询时，要指明where特定的字段才能发挥索引的效果。
索引在增删改表时会重新去计算索引，影响性能。

索引结构
◦ hash
◦ b+tree
◦ b-tree
索引类型
普通索引：索引定义的单列允许插入空值和重复值
唯一索引：索引定义的单列允许插入空值，不允许插入重复值
主键索引：索引定义的单列不允许插入控制和重复值
组合索引：索引定义的多列，非多个单列索引组成
全文索引：用在文本字段，检索文本中的字符
空间索引：支持空间数据

创建索引

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX
    [IF NOT EXISTS] index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option]

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    WITH PARSER parser_name
    COMMENT 'string'

alter table tb_name add [primary | unique ] fulltext] index index_name (col_name,...);

查看索引

show index from tb_name;

删除索引

drop index index_name on tb_name;

操作

创建测试表
MariaDB [cr]> create table test(
    -> col1 int,
    -> col2 int,
    -> col3 int,
    -> col4 int,
    -> col5 int,
    -> col6 text);
插入20万条数据
\d //
MariaDB [cr]> create procedure insert_test() 
    -> begin
    -> declare i int default 0;
    -> while i < 200000 do
    -> insert into test value(i,i,i,i,i,md5(i));
    -> set i=i+1;
    -> end while;
    -> end //
\d ;
执行存储过程
MariaDB [cr]> call insert_test();
Query OK, 200000 rows affected (5 min 15.038 sec)
MariaDB [cr]> select * from test where col1=199900;
+--------+--------+--------+--------+--------+----------------------------------+
| col1   | col2   | col3   | col4   | col5   | col6                             |
+--------+--------+--------+--------+--------+----------------------------------+
| 199900 | 199900 | 199900 | 199900 | 199900 | d95920548730019743e68d131488bed4 |
+--------+--------+--------+--------+--------+----------------------------------+
1 row in set (0.140 sec)   #没有索引耗时0.14秒


创建普通索引
MariaDB [cr]> create index col1 on test(col1);
Query OK, 0 rows affected (0.562 sec)
MariaDB [cr]> select * from test where col1=199900;
+-------+-------+-------+-------+-------+----------------------------------+
| col1  | col2  | col3  | col4  | col5  | col6                             |
+-------+-------+-------+-------+-------+----------------------------------+
| 19990 | 19990 | 19990 | 19990 | 19990 | 482018fcb72d6725497bebbe9bb83833 |
+-------+-------+-------+-------+-------+----------------------------------+
1 row in set (0.001 sec)  #普通索引耗时0.001秒

创建主键索引
MariaDB [cr]> alter table test add primary key(col2);
Query OK, 0 rows affected (2.374 sec)
MariaDB [cr]> select * from test where col2=199900;
+--------+--------+--------+--------+--------+----------------------------------+
| col1   | col2   | col3   | col4   | col5   | col6                             |
+--------+--------+--------+--------+--------+----------------------------------+
| 199900 | 199900 | 199900 | 199900 | 199900 | d95920548730019743e68d131488bed4 |
+--------+--------+--------+--------+--------+----------------------------------+
1 row in set (0.001 sec)  #主键索引耗时0.001秒

创建唯一索引
MariaDB [cr]> create unique index col3 on test(col3);
Query OK, 0 rows affected (0.566 sec)
MariaDB [cr]> select * from test where col3=199900;
+--------+--------+--------+--------+--------+----------------------------------+
| col1   | col2   | col3   | col4   | col5   | col6                             |
+--------+--------+--------+--------+--------+----------------------------------+
| 199900 | 199900 | 199900 | 199900 | 199900 | d95920548730019743e68d131488bed4 |
+--------+--------+--------+--------+--------+----------------------------------+
1 row in set (0.001 sec)  #唯一索引耗时0.001秒

创建组合索引
MariaDB [cr]> create index col4_col5 on test(col4,col5);
Query OK, 0 rows affected (0.623 sec)
MariaDB [cr]> select * from test where col4=199900;
+--------+--------+--------+--------+--------+----------------------------------+
| col1   | col2   | col3   | col4   | col5   | col6                             |
+--------+--------+--------+--------+--------+----------------------------------+
| 199900 | 199900 | 199900 | 199900 | 199900 | d95920548730019743e68d131488bed4 |
+--------+--------+--------+--------+--------+----------------------------------+
1 row in set (0.001 sec)  #组合索引最左前缀原则，耗时0.001秒
MariaDB [cr]> select * from test where col5=199900;
+--------+--------+--------+--------+--------+----------------------------------+
| col1   | col2   | col3   | col4   | col5   | col6                             |
+--------+--------+--------+--------+--------+----------------------------------+
| 199900 | 199900 | 199900 | 199900 | 199900 | d95920548730019743e68d131488bed4 |
+--------+--------+--------+--------+--------+----------------------------------+
1 row in set (0.135 sec)  #而组合索引右边这个字段耗时和没有索引一样

创建全文索引
create fulltext index index_name on tb_name(col_name);   #innodb不支持，myisam支持
select * from tb_name where match(col_name) against('string');   #测试语句

锁

实现对统一数据占用时并发控制。

锁级别
　　共享锁（读锁）：其他事务可读不可写
　　排他锁（写锁）：其他事务不可读不可写

锁粒度
　　行级锁
　　粒度小，锁冲突概率低，并发能力高，但开销大，加锁慢，有死锁。
　　在引擎层面实现，自动加锁。对于insert、delete、update语句在通过索引条件检索数据时使用行级锁，否则使用表锁；对于select语句不会加锁。
　　
　　表级锁
　　开销小，加锁快，无死锁；但粒度大，锁冲突概率高，并发能力低。
　　自动加锁。对于select语句会自动给涉及的所有表加读锁，insert、delete、update语句会自动给涉及的表加写锁。

显示加锁

全局

读锁

flush tables with read lock;

行级

读锁

select * from tb_name where where_condition lock in share mode;

写锁

select * from tb_name where where_condition for update;

表级

读锁

lock table tb_name read;
flush tables tb_name with read lock;

写锁

lock table tb_name write;
flush tables tb_name with write lock;

解锁

unlock tables;

事务

事务是一组原子性的SQL操作，这些语句要么都成功执行，要么都失败回滚。默认自动提交autocommit打开，mysql的每条sql语句都是一条单独的事务。

ACID

原子性（A）：atomicity
事务一个最基本执行单位，语句要么全执行，要么全不执行，最核心的特性。
一致性（C）：consistency
数据库总是从一个一致性状态转换为另一个一致性状态，保证事务执行前后都是合法的数据状态。
隔离性（I）：isolation
事务之间在提交前互不可见，保证事务执行尽可能不受其他事务影响。
隔离有多种隔离级别。
持久性（D）：durability
一旦事务提交，其所做的修改会永久保存于数据库中，保证事务提交后不会因为宕机等原因导致数据丢失。

隔离级别

READ UNCOMMITTED (读未提交)
存在脏读、不可重复度、幻读问题
READ COMMITTED (读提交)
存在不可重复度、幻读问题
REPEATABLE READ (可重读)
存在幻读问题
SERIALIZABILE (可串行化)
存在加锁读问题

隔离带来的问题

脏读
A事务未提交数据，B事务读到了A未提交的数据称之为脏读。
不可重复度
A事务提交前和提交后，B事务两次读同一条记录，由于A事务提交了update，导致B事务两次读到的条目数据不同，称为不可重复读。
幻读
A事务提交前和提交后，B事务两次读同一张表，由于A事务提交了insert、delete，导致B事务两次读到的表显示条目增多或减少，称之为幻读。
加锁读
解决上诉问题，但加锁读使操作串行化，严重影响并行，同时锁本身需要消耗相当系统资源。

undo log 和 redo log

undo log
undo log实现原子性的关键，事务对数据库所有修改，InnoDB都会生成对应的undo log，如果事务执行失败或调用了rollback，便可根据undo log回滚数据。
redo log
redo log实现持久性的关键，redo log采用的是WAL（Write-ahead logging，预写式日志），事务提交对数据库的所有修改，InnoDB都会先将修改写入日志，如果数据写入到磁盘时宕机，便可根据redo log重做修改。

隔离级别设置

set tx_isolation='isolation_level'

isolation_level:
	READ-UNCOMMITTED
	READ-COMMITTED
	REPEATABLE-READ
	SERIALIZABLE

事务操作

设置自动提交
set autocommit={on | off}  #本次会话生效，写入配置文件永久有效
MariaDB [cr]> show variables like 'autocommit';

开始事务
start transaction

保存节点
savepoint identifier

回滚节点
rollback TO identifier

释放节点
RELEASE SAVEPOINT identifier

回滚事务
rollback

提交事务
commit