实现MySQL触发器的实际操作步骤-mysql教程-PHP中文网

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

php中文网

发布： 2016-06-07 16:13:32

原创

1262人浏览过

以下的文章主要讲述的是实现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触发器的实现：

初阶PHP Apache MySQL网站设计

初阶PHP Apache MySQL网站设计来自作者多年学习、应用和讲授PHP的经验与体会，是专为学习PHP+MySQL数据库编程人员编与的入门教材。在最后二章设计了2个贴近实际应用的典型案例：留言本系统和论坛系统，每个案例先介绍开发思路、步骤，再给出全部源代码，使所学内容与实际应用紧密结合，特别是论坛系统将全书的案例串讲起来，力求使读者学到最贴近应用前沿的知识和技能。

377

查看详情

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