赵工的个人空间

在MySQL中，事务由单独单元的一个或多个SQL语句组成。在这个单元中，每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分隔的整体，一旦单元中某条SQL语句执行失败或产生错误，整个单元就会回滚，所有受到影响的数据将返回到事务开始以前的状态。如果单元中的所有SQL语句均执行成功，则事务被顺利执行。ACID指出每个事务型RDBMS必须遵守的4个属性，原子性、一致性、孤立性和持久性。
通过InnoDB和BDB类型表，MySQL事务能够满足事务安全ACID测试，但MyISAM类型表就不能支持事务，只能通过伪事务对表实现事务处理。

1.MySQL事务的创建：

创建事务一般过程是初始化事务、创建事务、应用SELECT语句查询数据是否被录入、提交事务。如果用户不在操作完数据库后执行事务提交，系统会默认执行回滚操作。如果用户在提交事务前选择撤销事务，则撤销前的所有事务将被撤销，数据库会回到初始状态。
在创建事务过程中，需要创建一个InnoDB或BDB类型的数据表。语法为：
CREATE TABLE tablename(field_defintions) TYPE=INNODB/BDB;
其中，tablename为表名；field_defintions为表内定义的字段等属性；TYPE指定数据表的类型，既可以是InnoDB类型，也可以是BDB类型。
如果希望让已经存在的表支持事务处理，可以用ALTER TABLE指定数据表的类型：
ALTER TABLE tablename TYPE=INNODB/BDB;
更改完成表的类型后，即可使数据表支持事务处理。

1）初始化事务：

初始化MySQL事务，首先声明初始化MySQL事务后所有的SQL语句为一个单元。在MySQL中，应用START TRANSACTION来标记一个事务的开始；也可以使用BEGIN或者BEGIN WORK命令初始化事务。通常，START TRANSACTION后面跟随的是组成事务的SQL语句。

2）创建事务：

初始化事务成功后，可以创建事务。示例：
start transaction;
insert into connect(email,cellphone,QQ,sid)
values('mrsoft@mrsoft.com',7856456789,92343432,3);

3）应用SELECT语句查看数据是否被正确输入：

事务创建成功后，建议通过SELECT语句查看数据是否被正确输入。示例：
SELECT * FROM connection WHERE sid=3;

4）提交事务：

在用户没有提交事务之前，当其他用户连接MySQL服务器时，应用SELECT语句查询结果，则不会显示没有提交的事务。当且仅当用户成功提交事务后，其他用户才可以通过SELECT语句查询事务结果。因为事务具有孤立性，当事务处在处理过程中时，其实MySQL并未将结果写入磁盘中，这些正在处理的事务相对于其他用户是不可见的。
一旦数据被正确插入，用户可以使用COMMIT提交事务。一旦当前执行事务的用户提交当前事务，则其他用户就可以通过会话查询结果。

5）撤销事务：

撤销事务，又被称为事务回滚，即事务被开启、用户输入的SQL语句被执行后，如果用户想要撤销刚才的数据库操作，可使用ROLLBACK撤销数据库中的所有变化。如果执行一个回滚操作，则在START TRANSACTION指令后的所有SQL语句都将执行回滚操作。
输入回滚操作后，可以通过SELECT语句查看是否执行了回滚操作。

6）事务的存在周期：

事务的周期由用户在命令提示符中输入START TRANSACTION指令开始，直至用户输入COMMIT结束。
事务不支持嵌套功能，当第一个事务未结束又重新打开一个事务时，则前一个事务会自动提交。MySQL中的很多命令都会隐藏执行COMMIT。

2.MySQL行为：

在MySQL中存在两个可以控制行为的变量，分别是AUTOCOMMIT变量和TRANSACTION ISOLACTION LEVEL变量。

1）自动提交：

在MySQL中，如果不更改其自动提交变量，系统会自动向数据库提交结果。用户在执行数据库操作过程中，不需要使用START TRANSACTION语句开始事务，应用COMMIT或者ROLLBACK提交事务或执行回滚操作。如果用户希望通过控制MySQL自动提交参数，可以更改提交模式，这一更改过程是通过设置AUTOCOMMIT变量来实现的。
SET AUTOCOMMIT=0;
此时，关闭自动提交功能，只有当用户输入COMMIT命令后，MySQL才将数据表中的资料提交到数据库中。如果不提交事务，而终止MySQL会话，数据库将会自动执行回滚操作。
可以通过查看@@AUTOCOMMIT变量来查看当前自动提交状态：
SELECT @@AUTOCOMMIT;

2）事务的孤立级：

孤立级在整个事务中起到了很重要的作用，如果没有事务的孤立性，不同的SELECT语句将会在同一事务的环境下检索到不同的结果，这将导致数据的不一致性，给不同的用户造成困扰。孤立性强制保持每个事务的独立性，依次来保证事务中看到一致的数据。
MySQL提供了4种孤立级：
·SERIALIZABLE：序列号，以序列的形式对事务进行处理。只有当事务提交后，用户才能从数据库中查看数据的变化。该孤立级运行会影响MySQL的性能，因为要占用大量资源，以保证使大量事务在任意时间不被用户看到。
·REPEATABLE READ：可重读。事务在该孤立级上不会被看成一个序列，不过当前在执行事务的过程中，用户仍然看不到事务的过程；直到事务提交为止，用户才能看到事务的变化结果。
·READ COMMITTED：提交后读。在这一级的事务，用户可以看到其他事务添加的新记录。其安全性比重复读安全性要低。
·READ UNCOMMITTED：未提交读。其他用户可以在该孤立级上看到未提交的事务，容易产生虚幻读操作。

3）修改事务的孤立级：

MySQL中，可以使用TRANSACTION ISOLACTION LEVEL变量修改事务孤立级。MySQL的默认孤立级是REPEATABLE READ（可重读），用户可以使用SELECT命令获取当前事务孤立级变量的值。
SELECT @@tx_isolation;
如果希望修改事务的孤立级，可以通过SET命令设置其不同值，来修改事务的孤立级。
SET GLOBAL TRANSACTION ISOLACTION LEVEL read committed;
如果要修改事务的孤立级，必须首先获取SUPPER优先权。

4）事务和性能：

应用不同的孤立级的事务可能会对系统造成一系列影响，可能会对系统稳定性和安全性等造成影响。有些数据库操作不需要事务处理，用户需要选择合适的数据表类型，考虑数据表具有完善性能，高效执行时不会对系统增加额外的负担。
当系统中存在处理量很大的数据库或多种复杂事务时，会因长时间等待而导致系统性能下降。小事务可以快速变化或退出，其他在队列中准备就绪的事务就不会受到明显影响，所以应用小事务可以保证系统的性能。
事务的孤立性越高，安全性越高，但性能越低。只有选择适当的孤立级才能有效提高MySQL系统性能。

5）死锁：

当两个或多个处于不同序列的用户打算同时更新某相同的数据库时，因互相等待对方释放权限而导致双方一直处于等待状态，称为死锁。当两个事务相互等待操作对方释放所持有的资源，导致两个事务都无法操作对方持有的资源，这种无限期的等待被称为死锁。
MySQL的InnoDB表处理程序具有检查死锁功能。如果该处理程序发现用户在操作过程中产生死锁，将立刻通过撤销操作来撤销其中一个事务，以便使死锁消失。这样，就可以使另一个事务获取对方所占有的资源而执行逻辑操作。

3.伪事务：

在MySQL中，InnoDB和BDB类型表可以支持事务处理，但MyISAM类型表并不能支持事务处理。对于该类型表，可以选择应用表锁定来替代事务，称为伪事务。

1）表锁定：

为了避免多用户环境中同一时间有多个用户对数据库中指定表进行操作，要求仅当某用户释放表的锁定后其他用户才可以访问这些修改后的数据表。基本步骤为：
①为指定数据表添加锁定：
LOCK TABLES tablename lock_type,...
其中，lock_type为锁定类型，包括以读方式锁定表、以写方式锁定表。
②执行数据表的操作：
可以添加、删除或者更改部分数据。
③数据表解锁：
完成对锁定数据表的操作后，对该表解锁，释放该表的锁定状态。
UNLOCK TABLES

2）以读方式锁定数据表：

以读方式锁定数据表，删除、插入、更新都不被允许，直至用户进行解锁。
lock table studentinfo read;
有一种变体类型READ LOCAL锁定，可以执行INSERT操作。

3）以写方式锁定数据表：

表的写锁定，本用户可以修改数据表中的数据，但其他用户不能进行任何读操作。
lock table studentinfo write;

4）应用表锁定实现伪事务：

实现伪事务的一般步骤为：
①对数据库中的数据表进行锁定操作，可以对多个表做不同的方式锁定。
LOCK TABLE tablename1 lock_type1,tablename2 lock_type2,...
②执行数据库操作，向锁定的数据表中执行添加、删除、修改等操作。用户可以对锁定的数据表执行上述操作，在执行过程中该伪事务所产生的结果不会被其他用户更改。
③释放锁定的数据表，以便让正在队列中等待查看或操作的其他用户可以查询数据表中的数据或对操作表执行各种数据的操作。
如果存在其他会话要求访问已锁定的多个表格，则该会话必须被迫等待当前锁定用户释放锁定表，才允许其他会话访问该数据表，表锁定使不同会话执行的数据库操作彼此独立。应用数据表锁定方式可以使不支持事务类型的表实现伪事务。

二、MySQL触发器：

触发器是由MySQL的基本命令事件来触发某种特定操作。满足触发器的触发条件时，数据库系统就会自动执行触发器中定义的程序语句。 触发器是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

1.创建触发器：

可以使用CREATE TRIGGER语句创建触发器。语法格式为：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
在上面的语法中，命名为tbl_name的表是与触发器相关联的。tbl_name表必须引用永久性表，不能是TEMPORARY表或视图。而trigger_time是触发器的动作时间，可以是BEFORE或AFTER，以指明触发器是在激活它的语句之前或之后触发。
trigger_event指明了激活触发程序的语句的类型。trigger_event可以是下述值之一：
·INSERT：将新行插入表时激活触发程序。例如INSERT、LOAD DATA和REPLACE语句
·UPDATE：更改某一行时激活触发程序。例如通过UPDATE语句
·DELETE：从表中删除某一行时激活触发程序。例如通过DELETE和REPLACE语句
trigger_stmt是当触发器激活时执行的语句。如果需要执行多个语句，可使用BEGIN…END复合语句结构，这样就能使用存储过程中允许的语句来实现复杂功能。
对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某个表，不能有两个BEFORE UPDATE触发程序，但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
同时，MySQL还对触发器进行了扩充，那就是使用OLD和NEW关键字对触发器相关联的表进行引用。示例：
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2=NEW.a1;
DELETE FROM test3 WHERE a3=NEW.a1;
UPDATE test4 SET b4=b4+1 WHERE a4=NEW.a1;
END
一般放在BEGIN和END之间的多条语句必须用“;”分开。在创建触发器过程中需要更改分隔符，也需要使用DELIMITER语句，将结束符号更改。触发器创建完成后，可以换回分号。
delimiter //
create trigger delete_time after delete on studentinfo for each row
begin
insert into timelog(savetime) values (now());
insert into timeinfo(info) values ('deleteact');
end
//
delimiter;
触发器创建成功后，当执行删除操作后，timelog与timeinfo表中将会插入两条相关记录。

2.查看触发器：

查看触发器，是指查看数据库中已存在的触发器定义、状态和语法等信息。基本形式：
SHOW TRIGGERS;
进入MySQL数据库，选择相应的数据库，然后查看该数据库中存在的触发器。此指令只能查看所有触发器的内容，并不能查看某个触发器的信息。
在MySQL中，所有触发器的定义都存在该数据库的triggers表中，可以通过triggers表来查看数据库中所有触发器的详细信息。语句为：
SELECT * FROM infomation_schema.triggers;
其中，infomation_schema是MySQL中默认存在的数据库，有用于记录触发器信息的数据表。如果想要查看某个指定触发器的内容，可以通过where子句应用TRIGGER字段作为查询条件。代码：
SELECT * FROM infomation_schema.triggers WHERE TRIGGER_NAME='trigger_name';

3.应用触发器：

在MySQL中，触发器按以下顺序执行：BEFORE触发器、表操作、AFTER触发器。其中表操作包括常用的数据库操作命令，如INSERT、UPDATE、DELETE。
触发器中不能包含START TRANSCATION、COMMIT或ROLLBACK等关键字，也不能包含CALL语句。触发执行非常严格，任何错误都可能导致程序无法向下执行，已经更新过的数据表不能回滚，设计时一定要注意触发器的逻辑严密性。

4.删除触发器：

删除触发器，指删除原来已经在某个数据库中创建的触发器，用DROP TRIGGER语句。语法格式为：
DROP TRIGGER trigger_name
在应用完成触发器后，一定要将触发器删除，否则在执行某些数据库操作时会造成数据的变化。

三、MySQL视图

视图是从一个或多个实际表中获得的，这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。视图提供了一个或多个表中查看数据的替代方法，实际上是表上的一个动态窗口。
视图允许多个用户查看同一数据的不同表示。例如，几个用户可以同时存取一个关于雇员的数据库，一个用户可以查看关于某些雇员而非其他雇员的数据，而另一个用户可以查看关于所有雇员的某些数据而非他们的薪水。这些用户的每一个都在操作一个从该表派生的视图。每个视图都显示为一个表并有自己的名称。
使用视图的优点是可以使用它们来控制对敏感数据的存取。不同的人可以存取数据的不同列或行。

1.创建视图：

可以用CREATE VIEW语句创建视图。语法格式为：
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED|LOCAL] CHECK OPTION]
如果给定了OR REPLACE子句，该语句还能替换已有的视图。而语句中的select_statement是一个SELECT语句，它给出了视图的定义，该语句可从基表或其他视图进行选择。
视图定义服从下述限制：
·SELECT语句不能包含FROM子句中的子查询
·SELECT语句不能引用系统或用户变量
·SELECT语句不能引用预处理语句参数
·在存储子程序内，定义不能引用子程序参数或局部变量
·在定义中引用的表或视图必须存在。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句
·在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。
·在视图定义中命名的表必须已存在
·不能将触发程序与视图关联在一起
在视图定义中允许使用ORDER BY，但是如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。示例：
mysql>use text;
mysql>create view view_score as select name,score from students;
成功创建视图view_score之后，可以简单地使用SELECT语句查询视图：
mysql>select * from view_score;

2.修改视图：

前面已经提到，可以在CREATE VIEW语句创建视图的时候给定OR REPLACE子句来替换已有的视图。除此之外，还能使用ALTER VIEW语句修改视图。语法格式为：
ALTER [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED|LOCAL] CHECK OPTION]

3.删除视图：

可以用DROP VIEW语句来删除视图。语法格式为：
DROP VIEW [IF EXISTS]
View_name[, view_name]…
[RESTRICT|CASCADE]
DROP VIEW能够删除1个或多个视图。