以下的文章主要讲述的是实现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触发器的介绍，望你能有所收获。
