实现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>-</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触发器的介绍，望你能有所收获。