MySQL必知必会---探索mysql及实例探析：事务、存储过程及触发器

上一篇：高级应用篇
下一篇：实践应用篇

进阶应用篇

存储过程（重难点）

为以后的使用而保存的一条或多条mysql语句的集合。模糊上可将其视为批文件。
它是函数。对，就相当于C语言中的函数、c++/java中的方法。可以调用，提高SQL运行效率。
一般情况下，普通权限的用户只有使用存储过程的权限，而没有编写的权限，对，存储过程也是为了用户查询方便（下面会说到“建立智能场景”），同样是面向用户考虑的。

执行存储过程：mysql称存储过程的执行为调用，因此mysql执行存储过程的语句为CALL。CALL接收存储过程的名字以及需要传递给他的任意参数。如下为某程序执行存储过程：

CALL productpricing(@pricelow,
								  @pricehigh,
								  @priceaverage);

其中，返回产品的最低（pricelow）、最高（pricehigh）、和平均价格（priceaverage）。

创建存储过程：直接上命令：

create procedure pro()
begin
	select avg(prod_price) as priceaverage
	from products;
end;

此存储过程名为Pro，用create procedure pro()定义。如果存储过程接收参数，它们将在（）中列举出来。
其中，begin和end语句用来限定存储过程体，过程体仅仅是一个select语句。
如果你使用的是mysql命令行实用程序，应注意：mysql命令行实用程序和mysql默认的一样，将“;”视为分隔符，它们最终不会称为存储过程的成分，这会使SQL语句错误。解决办法：在开头加一行：delimiter // ——告诉命令行实用程序用//作为新的语句结束分隔符，然后将end处的;改为//，最后再加一行：delimiter ;——为回复原来的语句分隔符

然后，如何使用？

CALL pro();

执行刚创建的存储过程；（一般在这里括号里传入变量）

为什么Pro后面要有（）？——存储过程实际上是一种函数。

删除存储过程

drop procedure pro;

请注意，没有使用后面的（），只给出存储过程名。
but，如果指定过程不存在，则上面语句将会错误。我们可以这样使用：drop procedure if exists; （关于 if exists，后面会再见到，其在建立智能存储过程中应用广泛）

使用参数：一般，存储过程并不显示结果，而是把结果返回给你指定的变量。
变量：内存中一个特定的位置，用来临时存储数据。
以下是Pro的修改版本：

create procedure pro(
		out p1 decimal(8,2),
		out p2 decimal(8,2),
		out p3 decimal(8,2)
)
begin
		select min(prod_price)
		into p1
		from products;
		select max(prod_price)
		into p2
		from products;
		select avg(prod_price)
		into p3
		from products;
end;

此过程接收三个参数：p1存储产品最低价格，p2存储产品最高价格，p3存储产品平均价格。每个参数必须有指定的类型。关键字out指出相应的参数来从存储过程中传出一个值（返回给调用者）。
mysql支持IN（传递给存储过程）、out（从存储过程中传出）、和INOUT（对存储过程传入和传出）类型的参数
IN输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT输出参数:该值可在存储过程内部被改变，并可返回
INOUT输入输出参数:调用时指定，并且可被改变和返回

Ⅰ.IN参数例子
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)
-> BEGIN
-> SELECT p_in;
-> SET p_in=2;
-> SELECT p_in;
-> END;
-> //
mysql > DELIMITER ;

执行结果:
mysql > SET @p_in=1;
mysql > CALL demo_in_parameter(@p_in);
±-----+
| p_in |
±-----+
| 1 |
±-----+
±-----+
| p_in |
±-----+
| 2 |
±-----+

mysql> SELECT @p_in;
±------+
| @p_in |
±------+
| 1 |
±------+
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

Ⅱ.OUT参数例子
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)
-> BEGIN
-> SELECT p_out;
-> SET p_out=2;
-> SELECT p_out;
-> END;
-> //
mysql > DELIMITER ;

执行结果:
mysql > SET @p_out=1;
mysql > CALL sp_demo_out_parameter(@p_out);
±------+
| p_out |
±------+
| NULL |
±------+
±------+
| p_out |
±------+
| 2 |
±------+

mysql> SELECT @p_out;
±------+
| p_out |
±------+
| 2 |
±------+

Ⅲ.INOUT参数例子
创建:
mysql > DELIMITER //
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
-> BEGIN
-> SELECT p_inout;
-> SET p_inout=2;
-> SELECT p_inout;
-> END;
-> //
mysql > DELIMITER ;

执行结果:
mysql > SET @p_inout=1;
mysql > CALL demo_inout_parameter(@p_inout) ;
±--------+
| p_inout |
±--------+
| 1 |
±--------+
±--------+
| p_inout |
±--------+
| 2 |
±--------+

mysql > SELECT @p_inout;
±---------+
| @p_inout |
±---------+
| 2 |
±---------+
如前所见，存储过程是一系列select语句，用来检索值，通过INTO关键字保存到相应变量。
mysql中有全局变量和局部变量两种：
1.局部变量：以关键字DECLARE声明，后跟变量名和变量类型，如：declare a int
也可以用关键字DEFAULT为变量指定默认值，如：declare a int default 10
2.全局变量：mysql中的会话变量即为全局变量，会话变量在整个过程中有效，其以字符“@”起始，如：

delimiter //
create procedure p2()
begin
set @t=1;
begin
set @t=2;
select @t;
end;
select @t;
end;
delimiter //

为了调用修改过的存储过程，必须指定3个变量名，即：

CALL productpricing(@pricelow,
								  @pricehigh,
								  @priceaverage);

所有mysql变量都必须以@开始。
由于篇幅原因，具体事宜可关注我其他博文，在此不详说。。。

当然，这其中最重要的就是，它支持各种条件语句，如：IF语句（首先判断IF后条件是否为真，为真时执行后面THEN后的语句，为假则继续判断）、CASE语句（首先从WHEN后的value中查找与CASE后的value相等的值，若找到则执行该分支的内容）、WHILE循环语句（首先判断condition条件是否为真，为真时执行循环体）、LOOP循环语句（LOOP允许某特定于巨的重复执行，实现一个简单的循环构造，退出循环用LEAVE语句）、REPEAT循环语句（先执行一次循环体，然后判断condition条件是否为真，为真时退出循环）
示例：
1.LOOP

delimiter //
create procedure edxloop(out sum int)
begin
declare i int default 1;
declare s int default 0;
loop_lable:loop
set s=s+i;
set i=i+1;
if i>100 then
leave loop_lable;
end if;
end loop;
set sum=s;
end
delimiter //

call exloop(@s);
.................................
select @s;

存储函数：和存储过程一样的套路，只是，create后面的procedure变成了function。如下：

delimiter //
create function name_of_student(std_id int)
return varchar(15)
begin
return(select name from syidentinfo where std=std_id);
end
delimiter //

两个return缺一不可，第一个返回的是参数的类型，第二个是查询结果。

调用存储过程和存储函数：1.调用存储过程：前面提到过call，yes，通过前面的示例想必这一点完全不必多说，OK，To proceed to the next item!
2.调用存储函数：直接select 函数名（参数）;即可
查看存储过程和存储函数的定义：SHOW CREATE语句
show create procedure/function 名;
查看存储函数和存储过程的状态：如：show procedure status like ‘film_in_stock’; (查看过程film_in_stock的信息)
删除存储过程和存储函数：drop procedure/function [IF EXISTS] 名;

触发器

上面“存储函数”中的“DELIMITER //”与创建触发器时是一样的，故而在此说说触发器。

先说说触发器吧，其实学过java的人都知道里面有一个可谓是“神往已久”的东西，叫“监视器”，常见如：鼠标监视器，可以实现鼠标操作；时间监视器、颜色监视器等。。。
而数据库中的触发器和这原理差不多：在执行某项“需要监视的操作”时设置触发器，在执行时引发触发器，让系统完成既定的程序。

创建触发器：
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句
beffore和after：指定触发器执行的时间（在触发时间之前还是之后）；
for each row：表示任何一条记录上的操作满足触发事件都会触发该触发器；
执行语句：指触发器被触发后执行的程序

例如：创建一个名为timelog的表

create table timelog(
id int primary key auto_increment,
savetime varchar(50) not null
);

创建名为save_time的触发器

delimiter //
create trigger save_time before insert
on studentinfo for each row
insert into timelog(savetime) values(now());
delimiter //

insert那一句放入的是“now（）”，所以讲返回一条消息：运行这条操作的时间！
这就是触发器的作用。
上面是单一事件，若是多个事件怎么办？
在上面创建的timelog的基础上，另外创建一个名为timeinfo的数据表

create table timeinfo(
id int primary key,
info varchar(50) not null
);

然后创建一个由DELETE触发的多个语句的触发器，如下：

delimiter //
create trigger delete_time_info after delete
on studentinfo for each row
begin
insert into timelog(savetime) values(now());
insert into timeinfo(info) values(now());
end
delimiter //

执行删除操作的代码（触发触发器的命令）为：
DELETE FROM studentinfo where name=‘Chirs’;
然后，用select语句分别查看timelog数据表和timeinfo数据表，可看到返回结果。

查看触发器：用SHOW TRIGGERS;查看触发器基本信息；
删除触发器：用 DROP TRIGGER 触发器名称;

来看一下触发器的完整样例吧：
一、创建MySQL实例数据表：
在mysql的默认的测试test数据库下，创建两个表t_a与t_b：

/*Table structure for table `t_a` */
DROP TABLE IF EXISTS `t_a`;
CREATE TABLE `t_a` (
  `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;
 
/*Data for the table `t_a` */
LOCK TABLES `t_a` WRITE;
UNLOCK TABLES;
 
/*Table structure for table `t_b` */
DROP TABLE IF EXISTS `t_b`;
CREATE TABLE `t_b` (
  `id` smallint(1) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `groupid` mediumint(8) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=57 DEFAULT CHARSET=latin1;
 
/*Data for the table `t_b` */
LOCK TABLES `t_b` WRITE;
UNLOCK TABLES;

在t_a表上分创建一个CUD（增、改、删）3个触发器，将t_a的表数据与t_b同步实现CUD，注意创建触发器每个表同类事件有且仅有一个对应触发器，为什么只能对一个触发器，不解释啦，看MYSQL的说明帮助文档吧。

二、创建MySQL实例触发器：
在实例数据表t_a上依次按照下面步骤创建tr_a_insert、tr_a_update、tr_a_delete三个触发器
1、创建INSERT触发器trigger_a_insert：

DELIMITER $$
   USE `test`$$
    --判断数据库中是否存在tr_a_insert触发器
DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_insert`$$
--不存在tr_a_insert触发器，开始创建触发器
--Trigger触发条件为insert成功后进行触发
CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr_a_insert` AFTER INSERT ON `t_a` 
    FOR EACH ROW BEGIN
        --Trigger触发后，同时对t_b新增同步一条数据
        INSERT INTO `t_b` SET username = NEW.username, groupid=NEW.groupid;
    END;
$$
 
DELIMITER;

2、创建UPDATE触发器trigger_a_update：

DELIMITER $$

    USE `test`$$
--判断数据库中是否存在tr_a_update触发器
DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_update`$$
--不存在tr_a_update触发器，开始创建触发器
--Trigger触发条件为update成功后进行触发
CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr_a_update` AFTER UPDATE ON `t_a` 
    FOR EACH ROW BEGIN 
    --Trigger触发后，当t_a表groupid,username数据有更改时，对t_b表同步一条更新后的数据
      IF new.groupid != old.groupid OR old.username != new.username THEN
        UPDATE `t_b` SET groupid=NEW.groupid,username=NEW.username WHEREusername=OLD.username AND groupid=OLD.groupid;
      END IF;
          
    END;
$$
 
DELIMITER ;

3、创建DELETE触发器trigger_a_delete：

DELIMITER $$

  USE `test`$$
--判断数据库中是否存在tr_a_delete触发器
DROP TRIGGER /*!50032 IF EXISTS */ `tr_a_delete`$$
--不存在tr_a_delete触发器，开始创建触发器
--Trigger触发条件为delete成功后进行触发
CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `tr_a_delete` AFTER DELETE ON `t_a` 
    FOR EACH ROW BEGIN
        --t_a表数据删除后，t_b表关联条件相同的数据也同步删除
        DELETE FROM `t_b` WHERE username=Old.username AND groupid=OLD.groupid;
    END;
$$
 
DELIMITER ;

三、测试MySQL实例触发器：
分别测试实现t_a与t_b实现数据同步CUD(增、改、删)3个Triggers

1、测试MySQL的实例tr_a_insert触发器：
在t_a表中新增一条数据，然后分别查询t_a/t_b表的数据是否数据同步，测试触发器成功标志，t_a表无论在何种情况下，新增了一条或多条记录集时，没有t_b表做任何数据insert操作，它同时新增了一样的多条记录集。
下面来进行MySQL触发器实例测试：

–t_a表新增一条记录集
INSERT INTO t_a (username,groupid) VALUES (‘sky54.net’,123)

    --查询t_a表
    SELECT id,username,groupid FROM `t_a`
   
    --查询t_b表
    SELECT id,username,groupid FROM `t_b`

在应用触发器中条件语句判断问题时，还应有一些“策略”：如
我们需要限制范围内数据，例如：余额只能大于100这样的条件，我们可以使用触发器来实现。

DELIMITER $$
CREATE
    TRIGGER `test`.`remaining_BeforeInsert` BEFORE INSERT ON `test`.`user`
    FOR EACH ROW BEGIN
    IF `user`.`remaining` < 100 THEN  
        SET `user`.`remaining` = 100;  
    END IF;  
    END$$
DELIMITER ;

注意：在应用完触发器后，一定要将所创建的触发器删除，否则在执行某些数据库操作时，会因数据的变化而造成错误。
重点警示：“触发条件”和“触发内容”操作的表对象不能是同一个表，尤其，绝对 不能都是对触发器内容对象操作！！！对照第一个示例，千万记住这句话。

MySQL事务（极重点）

先说，mysql变量真是一个神奇的东西，不仅可以通过存储过程和存储函数模拟c/c++/java的函数（方法），mysql中还增加了对变量的查看(select @变量)。
这和我们要说的事务有什么关系呢？ 没有。
在mysql中，事务由单独单元的一个或多个SQL语句组成，在这个单元中，每个mysql语句相互依赖。
再先说一下，事务处理数据有什么应用？
网购，用户登录网站，浏览信息，将喜欢的商品放入购物车，然后在线支付，当用户付款完毕，通知商家发货，注意，此时商家是没有接到货款的，当用户收到货物时，点击确认收货，商家收到货款，整个交易完成。试想当用户选择商品后，在发货过程中选择取消订单，这是商家并没有得到货款将取消操作，如果不用事务处理，则用户取消操作后，商家仍然给用户发货，会造成一些不愉快。故而在整个交易过程中，必须采取事务回滚操作。
简单来说，事务就是给操作过程加一个“保险”。
提示：mysql事务只针对InnoDB和BDB类型表，而我们平时创建表时默认的是MyISAM类型表。

原子性：即事务的整体性和不可分割性，所有事务共进退。或者说，原子的执行是一个全部发生或全部失败的整体过程。在一个原子操作中，如果事务的任何一个语句失败，前面执行的语句都将被返回，一保证数据的整体性不被破坏。
一致性：基于mysql的日记处理机制，记录数据库的所有变化，为事务恢复提供跟踪记录。如：用户A向用户B转账5000元，用户B却发现自己账户只增加了3000元，是不是很桑心。。。
持久性：提交的事务即使mysql系统崩溃了仍然坚持运行。当一个事务完成，数据库的日志已经被更新时，持久性即可发挥其特有功效。而且，在mysql中，如果系统崩溃或者数据存储介质被破坏，通过使用日志，系统能够恢复在重启前进行的最后一次成功更新。
孤立性

默认情况下，InnoDB表的持久性最久。

事务的创立
过程有点麻烦：初始化事务、创建事务、应用select语句查询数据是否被录入、提交事务。
1.创建一个表：create table table_name(field-defintions) type=INNODB/BDB;
table_name是表名，field-defintions是表内字段
若用户希望将已经存在的表支持事务处理，可应用ALTER命令指定：alter table table_name type=INNODB/BDB;
2.初始化事务：start transaction;
3.创建事务：初始化成功后，向表中插入数据，如：
insert into connection(email,cellphone,qq,sid) values(‘[email protected]’,123456789,8975645,3);
3.查看：如：select * from connection where sid=3;
4.提交事务：在用户没有提交事务前，当其他用户连接mysql服务器时，用select查询则不会显示增加的事务。
提交事务命令：COMMIT

撤销事务（事务回滚）：ROLLBACK命令。如果执行回滚操作，则在输入start transaction命令后的所有SQL语句都将执行回滚操作。
如果用户提交事务后，没有提交事务，则事务默认为自动回滚状态。
那事务回滚怎么使用呢？我们需要设置一个savepoint变量，我称之为：回滚标签。需要回滚时，秩序rollback到这个标签的地方即可。如：

savepoint test;
...
...
rollback to savepoint test;

事务不支持嵌套使用，当用户在未结束第一个书屋而又重新打开一个事务时，前一个事务会自动提交，同样mysql命令中很多命令都会隐藏执行COMMIT命令；

MySQL行为
1.关于mysql自动提交：如果用户不希望通过控制MySQL自动提交参数，可以更改提交模式，如：使用“SET AUTOCOMMIT=0;”命令关闭自动提交参数，此后，只有当用户输入COMMIT后，mysql才能将数据表中的资料提交到数据库中。
我们可以通过查看（select）“@@AUTOCOMMIT”变量来查看当前自动提交状态。

mysql孤立级
1.基于ANSI/ISO SQL规范，MySQL提供4中孤立级，分别是：SERIALIZABLE（序列化）、REPEATABLE READ（可重读）、READ COMMITTED（提交后读）、READ UNCOMMITTED（未提交读）。
2.修改事务的孤立级：mysql默认的孤立级为“可重读”，用户可以通过命令：set global transaction isolation level 孤立级名;来修改级别；可 用select命令获取当前事务的孤立级变量的值：select @@tx_isolation;
3.如果一般用户想要修改事务的孤立级，必须首先获取SUPER权限。

表锁定？——MyISAM中模拟InnoDB/BDB事务的东西
简称，伪事务。
用途：避免用户操作数据库过程中受到干扰。
设置表锁定代替事务的基本步骤如下：
（1）为指定表添加锁定：LOCK TABLES table_name lock_type,…
其中，lock_type是指锁定类型，分为两种：以读方式（READ）和以写方式（WRITE）
一.以读方式：锁定用户其他方式操作（如：删除，插入，更新）
以studentinfo表为例：lock table studentinfo read;
执行此步后，再向表中插入数据，会报错！
二.以写方式：设置用户可以修改表中数据，但是除了自己以外其他会话中的用户不能进行任何读操作。
lock table studentinfo write;
此后，进行select * from studentinfo时，将不会显示结果。
（2）删除表锁定：UNLOCK TABLES; 将会释放当前处于锁定状态的数据表。

关于“事务”，我在一本不知道啥的书上找到了这样一个测试，或许能更增进对其的印象

下一篇：实践篇