当前位置: 澳门新濠3559 > 数据库 > 正文

以及事务隔离的机制,视图是由一张表或多张表

时间:2019-12-09 01:05来源:数据库
Queries: 当前在开发ERP系统,使用到的数据库为Mysql。下面介绍下如何开启事务,以及事务隔离的机制: 前言 本篇博客学习内容为视图、触发器、事务、存储过程、函数、数据备份及流

Queries:

当前在开发ERP系统,使用到的数据库为Mysql。下面介绍下如何开启事务,以及事务隔离的机制 :

前言

本篇博客学习内容为视图、触发器、事务、存储过程、函数、数据备份及流程控制。

这个状态变量表示,mysql系统接收的查询的次数,包括存储过程内部的查询

  1. 检查当前数据库使用的存储引擎。

视图

 

show engines;

什么是视图?

视图是由一张表或多张表的查询结果构成的一张虚拟表,建立一张视图后会在数据库中保留一个以 frm 后缀结尾的文件,只保留了数据结果,所有的数据都来自 sql 语句。

Questions:

图片 1

为什么使用视图?

在进行多表查询的时候,sql 语句会非常非常长,比如

select t1.student_id from (select student_id,num from score where course_id = (select cid from course where cname = '物理')) as t1 join(select student_id,num from score where course_id = (select cid from course where cname = '生物')) as t2 on t1.student_id = t2.student_id where t1.num > t2.num;

看是不是很长,这还只是三表查询,如果遇到更加复杂的表结构肯定会更长,不过那样的话对表的维护困难也加大了。如果每次都编写需要得到相同数据的 sql 语句会是一件很麻烦的事,可以把经常需要查询的 sql 语句转变为视图就可以避免重复写 sql 语句的问题。

视图除了可以减少 sql 语句的编写次数,还可以使用不同的视图来展示不同数据的访问,那么给某些用户设置权限不就可以了吗?注意,设置的权限要么只能看某张表的全部数据,要么只能看某张表中的某个 column 的数据,也就是列数据,列数据只是保存了字段名,比如说我要查看我的当月工资,是需要查看一行数据的,这样权限就帮不了忙了。(当然可以加 where 条件,在这里是介绍视图)

这个状态变量表示,mysql系统接收查询的次数,但是不包括存储过程内部的查询

  1. 修改前my.ini中的文件如下:

使用方法

 

图片 2

创建视图

mysql> create [or replace] view 视图名 [(column_list)] as select_statement;

加上 or replace 时如果已经存在相同视图则替换原有视图,column_list 指定哪些字段要出现在视图中。注意:由于是一张虚拟表,视图中的数据实际来源于其他表,所以在视图中的数据不会出现在硬盘上,也就是只会保存一份数据结构。

show engine innodb status->[Row Operations]->reads/s

  1. 修改my.ini的文件如下。

使用视图

视图是一张虚拟表,所以使用方式与普通表没有区别。

这个是通过reads计算的,

     3.1 修改了默认的存储引擎

查看视图

  1. 查看数据结构
mysql> desc view_name;
  1. 查看创建语句
mysql> show create view view_name;

reads=状态变量Innodb_buffer_pool_read_requests

     3.2 增加了mysql数据库的事务隔离级别, 如果不添加默认是REPEATABLE-READ.

修改视图

mysql> alter view_name select_statement;

 

图片 3

删除视图

mysql> drop view view_name;

 

 

具体使用

Com_Commit:

4. 只需要重启mysql的服务即可。

案例一:简化多表 sql 语句

# 准备数据mysql> create database db02 charset utf8;mysql> use db02;mysql> create table student(       s_id int,       name varchar,       math float,       chinese float);mysql> insert into student values(1,'tom',80,70),(2,'jack',80,80),(3,'rose',60,75);mysql> create table stu_info(       s_id int,       class varchar,       addr varchar;mysql> insert into stu_info values(1,'二班','安徽'),(2,'二班','湖南'),(3,'三班','黑龙江');# 创建视图包含编号、学生姓名、班级mysql> create view stu_v  as select student.s_id,student.name,stu_info.class from student,stu_info where student.s_id = stu_info.s_id;# 查看视图中的数据mysql> select * from stu_v;

执行commit的次数

    net stop mysql 

案例二:隔离数据

# 创建工资表mysql> create table salarys(       id int primary key,       name char,       salary double,       dept char;mysql> insert into salarys values       (1,'刘强东',800000,'市场'),       (2,'马云',899990,'市场'),       (3,'李彦宏',989090,'市场'),       (4,'马化腾',88889999,'财务');# 创建市场部视图mysql> create view dept_sc as select * from salarys where dept = '市场';mysql> select * from dept_sc;

注意:对视图数据的 insert update delete 会同步到原表中,但由于视图可能是部分字段,很多时候会失败。

总结:mysql 可以分担程序中的部分逻辑,但这样一来后续的维护会变得更麻烦。如果需要改表结构,那意味着视图也需要相应的修改,没有直接在程序中修改 sql 来的方便。

 

    net start mysql

触发器

handle_commit:

    图片 4

什么是触发器?

触发器是一段与表有关的 mysql 程序,当这个表在某个时间点发生了某种事件时,将会自动执行相应的触发器程序。

内部commit的次数

  1. 再次查询show engines,显示如下

何时使用触发器

当我们想要在一个表记录被更新时做一些操作时就可以说使用触发器,但是完全可以在 python 中来完成这个事情。

 

图片 5

创建触发器

通过初步执行,com_commit不会记录隐式提交的事务,所以如果开了隐式提交使用com_commit计算tps会不准。

  1. 事务隔离机制的说明,以下引用自别人的文章

语法

mysql> create trigger t_name t_time t_event on table_name for each rowbeginstmts...end

支持的时间点:事件发生之前和之后 before|after

支持的事件:update、insert、delete

在触发器中可以访问到将被修改的那一行数据,根据事件不同能访问的也不同,update 可用 old 访问旧数据,new访问新数据,insert 可用 new 访问新数据,delete 可用 old 访问旧数据。

可以将 new 和 old 看做一个对象,其中封装了修改的数据的所有字段。

而handle_commit,因为包含了内部的commit的次数,因此用于计算可能会比实际commit大。


使用触发器

 

案例

有 cmd 表和错误日志表,需求:在 cmd 执行失败时自动将信息存储到错误日志表中。

# 准备数据mysql> create table cmd(       id int primary key auto_increment,       user char,       priv char,       cmd char,       sub_time datetime, # 提交时间       success enum('yes','no')); # 0代表执行失败# 错误日志表mysql> create table errlog(       id int primary key auto_increment,       err_cmd char,       err_time datetime);# 创建触发器mysql> delimiter //mysql> create trigger trigger1 after insert on cmd for each rowbeginif new.success = 'no' then    insert into errlog values(null,new.cmd,new.sub_time);end if;end //delimiter;# 往表 cmd 中插入记录,触发触发器,根据 if 条件决定是否需要插入错误日志mysql> insert into cmd(       user,       priv,       cmd,       sub_time,       success) values       ('thales','0755','ls-l /etc',now,       ('thales','0755','cat /etc/password',now,       ('thales','0755','user ass xxx',now,       ('thales','0755','ps aux',now;# 查看错误日志表中的记录是否有自动插入mysql> select * from errlog;

delimiter用于修改默认的行结束符,由于在触发器中有多条 sql 语句需要使用分号来结束,但是触发器是一个整体,所以需要先更换默认的结束符(这里修改的只是客户端的结束符,服务端还是以分号结束),在触发器编写完后再讲结束符设置回分号

注意:外键不能触发事件,主表删除了某个主键,从表也会相应的删除数据,但是并不会执行触发器,并且触发器中不能使用事务,相同时间点的相同事件的触发器,不能同时存在。

  • 未提交读(READ UNCOMMITTED)。另一个事务修改了数据,但尚未提交,而本事务中的SELECT会读到这些未被提交的数据(脏读)。
  • 提交读(READ COMMITTED)。本事务读取到的是最新的数据(其他事务提交后的)。问题是,在同一个事务里,前后两次相同的SELECT会读到不同的结果(不重复读)。如两个会话a和b,由于是read committed所以只有当事务提交后才能被别的事务可见,当a执行查询后b执行插入,b执行commit提交事务,这时a再次查询结果确实不一样的,a的两次查询同属于一个事务,即为不可重复读。
  • 可重复读(REPEATABLE READ)。在同一个事务里,SELECT的结果是事务开始时时间点的状态,因此,同样的SELECT操作读到的结果会是一致的。直到提交事务后再查询才可以看到其他事务在本事务执行期间锁进行的更改操作。在MySQL中InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC)解决了幻读的问题。
  • 串行化(SERIALIZABLE)。读操作会隐式获取共享锁,可以保证不同事务间的互斥。
  • SQL 事务隔离级别 

删除触发器

 图片 6

语法

mysql> drop trigger trigger_name;# 删除上面创建的触发器mysql> drop trigger trigger1;

 

事务

 

什么是事务?

mysql 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也需要删除和该人员相关的信息,如信箱、文章等,这样,这些数据库操作就构成一个事务。事务是逻辑上的一组操作,要么都成功,要么都失败。

  • 在 mysql 中只有使用了 InnoDB 数据库引擎的数据库或表才支持事务;
  • 事务处理可以用来维护数据库的完整性,保证成批的 sql 语句要么都执行,要么都不执行;
  • 事务用来管理 insert、update、delete语句

 

事务的四个特性

一般来说,事务必须满足四个条件:原子性(Atomicity,或称不可分割性)、一致性(Consistency)、隔离性(Isolation,又称独立性)、持久性(Durability)。

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节,事务在执行过程中发生错误,会被回滚到事务开始前的状态,就像这个事务从来没有执行过一样;
  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性的完成预定的工作;
  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(Read committed)、可重复读(Repeatable read)和串行化(Serializable)。
  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

在 mysql 命令行的默认设置下,事务都是自动提交的,即执行 sql 语句后就会马上执行 commit 操作。因此要显式的开启一个事务必须使用命令 begin 或 start transaction,或者执行命令 set autocommit=0,用来禁止使用当前会话的自动提交。

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询:

事务控制语句

  • begin 或 start transaction:显式的开启一个事务;
  • commit:也可以使用 commit work,不过二者是等价的。commit 会提交事务,并使已对数据库进行的所有修改成为永久性的;
  • rollback:也可以使用 rollback work,二者也是等价的。回滚会结束用户的事务,并撤销正在进行的所有未提交的修改;
  • savepoint identifier:savepoint 允许在事务中创建一个保存点,一个事务中可以有多个 savepoint;
  • release savepoint identifier:删除一个事务的保存点,当没有指定的保存点时,执行该语句会抛出一个异常;
  • rollback to identifier:把事务回滚到标记点;
  • set transaction:用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有 read uncommitted、read committed、repeatable read和 serializable。

查看InnoDB系统级别的事务隔离级别:mysql> SELECT @@global.tx_isolation;

mysql 事务处理的两种方式

  1. BEGIN,ROLLBACK,COMMIT 来实现
  • BEGIN:开始一个事务
  • ROLLBACK:事务回滚
  • COMMIT:事务确认
  1. 直接使用 set 来改变 mysql 的自动提交模式
  • SET AUTOCOMMIT=0:禁止自动提交
  • SET AUTOCOMMIT=1:开启自动提交

 

事务的用户隔离级别

数据库使用者可以控制数据库工作在哪个级别下,就可以防止不同的隔离性问题。

  • read uncommitted:不做任何隔离,可能脏读、幻读;
  • read committed:可以防止脏读,不能防止不可重复读和幻读;
  • repeatable read:可以防止脏读,不可重复读,不能防止幻读;
  • serializable:数据库运行在串行化实现,所有问题都没有,就是性能低。

在MySQL中默认事务隔离级别是可重复读(Repeatable read).可通过SQL语句查询:

修改隔离级别

查看InnoDB系统级别的事务隔离级别:

查询当前级别

mysql> select @@tx_isolation;

 

修改级别

mysql> set global transaction isolation level Repeatable read;

    mysql> SELECT @@global.tx_isolation;

使用事务

start transaction:开启事务,在这条语句之后的 sql 将处在同一事务,不会立即修改数据库

commit:提交事务,让这个事务中的 sql 立即执行数据的操作

rollback:回滚事务,取消这个事务,这个事务不会对数据库中的数据产生任何影响。

 

案例:转账过程中发生异常

# 准备数据mysql> create table account(       id int primary key auto_increment,       name varchar,       money double);insert into account values(1,'赵大儿子',1000);insert into account values(2,'刘大牛',1000);insert into account values(3,'猪头三',1000);insert into account values(4,'王进',1000);insert into account values(5,'黄卉',1000);# 赵大儿子刘大牛佳转账1000块# 未使用事务update account set money = money - 1000 where id = 1;update account set moneys = money - 1000 where id = 1; # money打错了导致执行失败# 在python中使用事务处理sql = 'update account set money = money - 1000 where id = 1;'sql2 = 'update account set moneys = money + 1000 where id = 2;' # money打错了导致执行失败try:    cursor.execute    cursor.execute    conn.commit()except:    conn.rollback()

注意:事务的回滚的前提是能捕捉到异常,否则无法决定何时回滚,python 中很简单就可以实现,另外 mysql 中需要使用存储过程才可以捕获异常。

结果:

存储过程

+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set (0.00 sec)

什么是存储过程?

存储过程是一组任意的 sql 语句集合,存储在 mysql 中,调用存储过程时将会执行其包含的所有 sql 语句,与 python 中的函数类似。

 

为什么使用存储过程?

回顾触发器与视图其实都是为了简化应用程序中 sql 语句的书写,但是还是需要编写,而存储过程中可以包含任何的 sql 语句,包括视图、事务、控制流程等,这样一来,用用程序可以从 sql 语句中完全解放出来,mysql 可以替代应用程序完成数据相关的逻辑处理。

查看InnoDB会话级别的事务隔离级别:

三种开发方式对比

  1. 应用程序仅负责业务逻辑编写,所有与数据相关的逻辑都交给 mysql 来完成,通过存储过程

    优点:应用程序与数据处理完全解耦合,一对复杂的 sql 被封装成了一个简单的存储过程,考虑到网络环境因素,效率高,应用程序开发者不需要编写 sql 语句,开发效率高。

    缺点:python 语法与 mysql 语法区别巨大,学习成本高,并且各种数据库的语法大不相同,所以移植性非常差,应用程序开发者与 DBA 的跨部门沟通成本高,造成整体效率低。

  2. 应用程序不仅编写业务逻辑,还需要编写所有的 sql 语句

    优点:扩展性高,对于应用程序开发者而言,扩展性和维护性相较于第一种都有所提高。

    缺点:执行效率低,由于需要将对象的操作转化为 sql 语句,且需要通过网络发送大量的 sql 语句。

 

创建存储过程

  mysql> SELECT @@tx_isolation;

语法

mysql> create procedure pro_name(p_type p_name data_type)beginsql 语句......流程控制end

p_type:参数类型

in:表示输入参数

out:表示输出参数

inout:表示既能输入又能输出

p_name:参数名称

data_type:参数类型 mysql 支持的所有数据类型

 

案例:使用存储过程完成对 student 表的查询

delimiter //create procedure p1(in m int,in n int,out res int)begin    select *from student where chinese > m and chinese < n;    #select *from student where chineseXXX > m and chinese < n; 修改错误的列名以测试执行失败    set res = 100;end//delimiter ;set @res = 0;#调用存储过程call p1(70,80,@res);#查看执行结果select @res;

注意:存储过程的 out 类参数必须是一个变量,用来装输出数据的,不可是一个值

结果:
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

python 中调用存储过程

import  pymysql#建立连接conn = pymysql.connect(    host="127.0.0.1",    user="root",    password="admin",    database="db02")# 获取游标cursor = conn.cursor(pymysql.cursors.DictCursor)​# 调用用存储过程cursor.callproc("p1", #p1为存储过程名 会自动为为每个值设置变量,名称为 @_p1_0,@_p1_1,@_p1_2# 提取执行结果是否有结果取决于存储过程中的sql语句print(cursor.fetchall# 获取执行状态cursor.execute("select @_p1_2")print(cursor.fetchone

此处 pymysql 会自动将参数都设置一个变量所以可以直接传入一个值,当然值如果作为输出参数的话,传入什么都可以。

 

删除存储过程

drop procedure 过程名;

修改存储过程的意义不大,不如删除重写。

修改事务隔离级别:

查看存储过程

# 当前库所有存储过程名称mysql> select 'name' from mysql.proc where db = 'db02' and 'type' =      'procedure';# 查看创建语句mysql> show create procedure p1;

    mysql> set global transaction isolation level read committed;

存储过程中的事务应用

存储过程中支持任何的 sql 语句也包括事务。

    Query OK, 0 rows affected (0.00 sec)

案例:模拟转账中发送异常,进行回滚操作

delimiter //create PROCEDURE p5(    OUT p_return_code tinyint)BEGIN     DECLARE exit handler for sqlexception     BEGIN         -- ERROR         set p_return_code = 1;         rollback;     END;     # exit 也可以换成continue 表示发送异常时继续执行    DECLARE exit handler for sqlwarning     BEGIN         -- WARNING         set p_return_code = 2;         rollback;     END;     START TRANSACTION;     update account set money = money - 1000 where id = 1;    update account set moneys = money - 1000 where id = 1; # moneys字段导致异常    COMMIT;     -- SUCCESS     set p_return_code = 0; #0代表执行成功END //delimiter ;#在mysql中调用存储过程set @res=123;call p5;select @res;

总结:抛开沟通成本、学习成本,存储过程无疑是效率最高的处理方式。

    mysql> set session transaction isolation level read committed;

函数

    Query OK, 0 rows affected (0.00 sec)

内置函数

在SQL 语句中,表达式可用于一些诸如SELECT语句的ORDER BY 或 DELETE或 UPDATE语句的 WHERE 子句或 SET语句之类的地方。使用文本值、column值、NULL值、函数、 操作符来书 写 表达式。 本章叙述了可用于书写MySQL表达式的函数和操作符。

这些内置函数大大提高了我们的开发效率

 

字符相关函数

图片 7

InnoDB 的可重复读隔离级别和其他数据库的可重复读是有区别的,不会造成幻象读(phantom read),所谓幻象读,就是同一个事务内,多次select,可以读取到其他session insert并已经commit的数据。下面是一个小的测试,证明InnoDB的可重复读隔离级别不会造成幻象读。测试涉及两个session,分别为 session 1和session 2,隔离级别都是repeateable read,关闭autocommit

数学相关函数

图片 8

 

日期相关函数

图片 9

    mysql> select @@tx_isolation;   

其他函数

图片 10

    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set (0.00 sec)    

自定义函数

    mysql> set autocommit=off;

语法

mysql> create function f_name(paramters)return dataTypereturn value;

说明:paramters 只能是 in 输入参数、参数名、类型必须有返回值,不能加 begin 和 end,returns 后面是返回值的类型,这里不加分号,return 后面是要返回的值。

    Query OK, 0 rows affected (0.00 sec)

案例:将两数相加

mysql> create function addfuntion(a int,b int)returns int return a + b;# 执行函数mysql> select addfuntion;

注意:函数只能返回一个值,函数一般不涉及数据的增删查改,就是一个通用的功能,调用自定义的函数与调用系统的一直,不需要 call 使用 select 可获得返回值,函数中不能使用 sql 语句,就像在 java 中不能识别 sql 语句一样(没学过java。。。)

 

数据备份

session 1 创建表并插入测试数据

使用 mysqldump 程序进行备份

mysqldump -u -p db_name [table_name,,,] > fileName.sql

注意:这是命令行命令

可以选则要备份那些表,如果不指定代表全部备份

# 示例# 单库备份mysqldump -uroot -p123 db1 > db1.sqlmysqldump -uroot -p123 db1 table table2 > db1-table1-table2.sql# 多库备份mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql# 备份所有mysqldump -uroot -p123 --all-databases > all.sql

     mysql> create table test(i int) engine=innodb;

使用 mysql 进行恢复

  1. 退出数据库后
mysql -u -p < filenam.sql
  1. 不用退出数据库
    1. 创建空数据库
    2. 选择数据库
    3. 然后使用 source filename 来进行还原
mysql> use db1;mysql> source /root/db1.sql

    Query OK, 0 rows affected (0.00 sec)

数据库迁移

# 务必保证在相同版本之间迁移mysqldump -h 源ip -uroot -p123 --databases db1 | mysql -h 目标ip -uroot -p456

    mysql> insert into test values(1);
    Query OK, 1 row affected (0.00 sec)

流程控制

 

if 语句

if 条件 then 语句;end if;第二种 if else if 条件 then 语句1;else if 条件

then 语句2;else 语句3;end if;

session 2 查询,没有数据,正常,session1没有提交,不允许脏读
     mysql> select * from test;
     Empty set (0.00 sec)

案例:编写过程实现 输入一个整数 type 范围1-2 输出 type=1 or type=other;

mysql> create procedure showType(in type int,out result charbeginif type = 1 then set result = "type = 1";elseif type = 2 then set result = "type = 2";else set result = "type = other";end if;end

 

case 语句

与 switch 一样,进行选择执行

mysql> create procedure caseTest(in type int)beginCASE type when 1  then select "type = 1";when 2  then select "type = 2";else select "type = other";end case;end

session 1 提交事务

定义变量

mysql> declare 变量名 类型 default 值;mysql> declare i int default 0;

    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

while 循环

# 循环输出10次 hello mysqlmysql> create procedure showHello()begindeclare i int default 0;while i < 10 doselect 'hello mysql';end while;end

 

loop 循环

没有条件,需要自己定义结束语句

# 输出10次 hello mysqlmysql> create procedure showLoop()begindeclare i int default 0;aloop:loopselect 'hello loop';set i > 9 then leave aloop;end if;end loop aloop;end

session 2 查询,还是没有数据,没有产生幻象读
                        
    mysql> select * from test;
    Empty set (0.00 sec)
                        
当session2提交事务后才可以看到session1的插入数据;                       
以上试验版本:
                        
    mysql> select version();
    +-------------------------+
    | version()               |
    +-------------------------+
    | 5.0.37-community-nt-log |
    +-------------------------+
    1 row in set (0.00 sec)

repeat 循环

# 类似do while# 输出10次hello repeatmysql> create procedure showRepeat()begindeclare i int default 0;repeatselect "hello repeat";set i = i + 1;until i > 9end repeat;end# 输出0-100之间的奇数mysql> create procedure showjishu()begindeclare i int default 0;aloop: loopset i = i + 1;if i >= 101 then leave aloop; end if;if i % 2 = 0 then iterate aloop; end if;select i;end loop aloop;end

编辑:数据库 本文来源:以及事务隔离的机制,视图是由一张表或多张表

关键词: