实现MySQL触发器的实际操作步骤

以下的文章主要讲述的是实现MySQL触发器的实际操作步骤、以及存储过程、自定义函数与视图的简单示例介绍，如果你对MySQL触发器的实际操作步骤以及存储过程的实际操作感兴趣的话，你就可以浏览以下的文章了，示例实现如下效果： test数据库有userinfo用户信息

以下的文章主要讲述的是实现MySQL触发器的实际操作步骤、以及存储过程、自定义函数与视图的简单示例介绍，如果你对MySQL触发器的实际操作步骤以及存储过程的实际操作感兴趣的话，你就可以浏览以下的文章了，示例实现如下效果：

test数据库有userinfo用户信息表 和userinfolog用户信息日志表

1.建立一个userinfo表新增记录时的触发器 将新增日志加入到userinfolog

2.建立一个向userinfo表新增记录的存储过程

3.根据userinfo表的出生日期字段 我们将建立一个简单算得年龄的自定义函数

4.创建一个userinfo的视图 调用年龄函数

准备相关表

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> use test;  </span></span></li><li><span>mysql</span><span class="tag">></span><span> create table userinfo(userid int,username varchar(10),userbirthday date);  </span></li><li class="alt"><span>mysql</span><span class="tag">></span><span> create table userinfolog(logtime datetime,loginfo varchar(100));  </span></li><li><span>mysql</span><span class="tag">></span><span> describe userinfo; </span></li></ol>

1.MySQL触发器的实现：

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> delimiter |  </span></span></li><li><span>mysql</span><span class="tag">></span><span> create trigger beforeinsertuserinfo  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> before insert on userinfo  </span></li><li><span>-</span><span class="tag">></span><span> for each row begin  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> insert into userinfolog values(now(),CONCAT(new.userid,new.username));  </span></li><li><span>-</span><span class="tag">></span><span> end;  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> |  </span></li><li><span>mysql</span><span class="tag">></span><span> delimiter ;  </span></li><li class="alt"><span>mysql</span><span class="tag">></span><span> show triggers; </span></li></ol>

2.存储过程

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> delimiter //  </span></span></li><li><span>mysql</span><span class="tag">></span><span> create procedure spinsertuserinfo(  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> puserid int,pusername varchar(10)  </span></li><li><span>-</span><span class="tag">></span><span> ,puserbirthday date  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> )  </span></li><li><span>-</span><span class="tag">></span><span> begin  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> insert into userinfo values(puserid,pusername,puserbirthday);  </span></li><li><span>-</span><span class="tag">></span><span> end;  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> //  </span></li><li><span>mysql</span><span class="tag">></span><span> show procedure status like 'spinsertuserinfo';  </span></li><li class="alt"><span>mysql</span><span class="tag">></span><span> call spinsertuserinfo(1,'zhangsan',current_date);  </span></li><li><span>mysql</span><span class="tag">></span><span> select * from userinfo; </span></li></ol>

3.自定义函数

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> update userinfo  </span></span></li><li><span>-</span><span class="tag">></span><span> set </span><span class="attribute">userbirthday</span><span>=</span><span class="attribute-value">'2000.01.01'</span><span> </span></li><li class="alt"><span>-</span><span class="tag">></span><span> where </span><span class="attribute">userid</span><span>=</span><span class="attribute-value">'1'</span><span>;  </span></li><li><span>mysql</span><span class="tag">></span><span> drop function if exists fngetage;  </span></li><li class="alt"><span>mysql</span><span class="tag">></span><span> delimiter //  </span></li><li><span>mysql</span><span class="tag">></span><span> create function fngetage(pbirthday date)  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> returns integer  </span></li><li><span><a style="color:transparent">本文来源gao($daima.com搞@代@#码(网5</a>-</span><span class="tag">></span><span> begin  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> return year(now()) - year(pbirthday);  </span></li><li><span>-</span><span class="tag">></span><span> end  </span></li><li class="alt"><span>-</span><span class="tag">></span><span> // </span></li></ol><br />

4.视图

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> create view viewuserinfo  </span></span></li><li><span>-</span><span class="tag">></span><span> as select * ,fngetage(userbirthday) as userage from userinfo;  </span></li><li class="alt"><span>mysql</span><span class="tag">></span><span> select * from viewuserinfo; </span></li></ol>

清除日志记录

<ol class="dp-xml"><li class="alt"><span><span>mysql</span><span class="tag">></span><span> truncate table userinfolog;  </span></span></li><li><span>mysql</span><span class="tag">></span><span> delete from userinfolog; </span></li></ol>

以上的相关内容就是对MySQL触发器的介绍，望你能有所收获。

喜欢 (0)赏

[搞代码]

分享 (0)