当前位置：Gxlcms > 数据库问题 > MYSQL触发器在PHP项目中用来做信息备份、恢复和清空

MYSQL触发器在PHP项目中用来做信息备份、恢复和清空

时间：2021-07-01 10:21:17 帮助过：14人阅读

方法，将备份表中的数据彻底清空，并且释放内存，而且这个方法进行数据删除不会调用触发器。话不多少，直接上干货。

第一步：建表，员工表，员工备份表。

  1. <span style="color: #008080"> 1</span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> `employee` (
  2. </span><span style="color: #008080"> 2</span>   `id` <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000"> AUTO_INCREMENT,
  3. </span><span style="color: #008080"> 3</span>   `denumber` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  4. </span><span style="color: #008080"> 4</span>   `idnumber` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  5. </span><span style="color: #008080"> 5</span>   `worknumber` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  6. </span><span style="color: #008080"> 6</span>   `pwd` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  7. </span><span style="color: #008080"> 7</span>   `emname` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  8. </span><span style="color: #008080"> 8</span>   `tel` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  9. </span><span style="color: #008080"> 9</span>   `salary` <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  10. </span><span style="color: #008080">10</span>   `entrytime` <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">0</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  11. </span><span style="color: #008080">11</span>   `orderpaixu` <span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  12. </span><span style="color: #008080">12</span>   <span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> (`id`)
  13. </span><span style="color: #008080">13</span> ) ENGINE<span style="color: #808080">=</span>MyISAM AUTO_INCREMENT<span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">100</span> <span style="color: #0000ff">DEFAULT</span> CHARSET<span style="color: #808080">=</span><span style="color: #000000">utf8
  14. </span><span style="color: #008080">14</span> 这是员工表
  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> `employeebackup` (
  2.   `id` </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">11</span>) <span style="color: #808080">NOT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  3.   `denumber` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  4.   `idnumber` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  5.   `worknumber` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  6.   `pwd` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  7.   `emname` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  8.   `tel` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  9.   `salary` </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  10.   `entrytime` </span><span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  11.   `orderpaixu` </span><span style="color: #0000ff">int</span>(<span style="color: #800000; font-weight: bold">255</span>) <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  12.   `deletetime` </span><span style="color: #0000ff">datetime</span> <span style="color: #0000ff">DEFAULT</span> <span style="color: #0000ff">NULL</span><span style="color: #000000">,
  13.   </span><span style="color: #0000ff">PRIMARY</span> <span style="color: #0000ff">KEY</span><span style="color: #000000"> (`id`)
  14. ) ENGINE</span><span style="color: #808080">=</span>MyISAM <span style="color: #0000ff">DEFAULT</span> CHARSET<span style="color: #808080">=</span><span style="color: #000000">utf8
  15. 员工备份表，多了一个字段deletetime，为了记录删除时间</span>

第二步：备份，给员工表建触发器（有关触发器的资料可以查看我的另一篇博客 http://www.cnblogs.com/liebagefly/p/7517998.html），在点击删除按钮执行删除功能之前将员工表中的信息导入到备份表中。

触发器sql代码：

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">trigger</span> deletesemployee before <span style="color: #0000ff">delete</span> <span style="color: #0000ff">on</span><span style="color: #000000"> employee
  2. </span><span style="color: #0000ff">for</span><span style="color: #000000"> each ROW
  3. </span><span style="color: #0000ff">begin</span> 
  4.     <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span><span style="color: #000000"> employeebackup (id,denumber,idnumber,worknumber,pwd,emname,tel,salary,entrytime,orderpaixu,deletetime)
  5. </span><span style="color: #0000ff">values</span><span style="color: #000000">(OLD.id,OLD.denumber,OLD.idnumber,OLD.worknumber,OLD.pwd,OLD.emname,OLD.tel,OLD.salary,OLD.entrytime,OLD.orderpaixu,NOW());
  6. </span><span style="color: #0000ff">end</span>

php后台方法，我使用的框架是yii2.

  1.  <span style="color: #0000ff">public</span> <span style="color: #0000ff">function</span> actionEmployeedel(<span style="color: #800080">$id</span><span style="color: #000000">)
  2.     {</span><span style="color: #000000">
  3.         Employee</span>::findOne(<span style="color: #800080">$id</span>)-><span style="color: #000000">delete();
  4.         </span><span style="color: #0000ff">return</span> <span style="color: #800080">$this</span>->redirect([‘employeemanage‘<span style="color: #000000">]);
  5.     }</span>

第三步：恢复，将删除的信息进行恢复，对备份表使用触发器，将备份表中的数据删除，删除的同时将此数据导入到员工表中。

触发器sql代码：

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">trigger</span> deletesemployeebackup before <span style="color: #0000ff">delete</span> <span style="color: #0000ff">on</span><span style="color: #000000"> employeebackup
  2. </span><span style="color: #0000ff">for</span><span style="color: #000000"> each ROW
  3. </span><span style="color: #0000ff">begin</span> 
  4.      <span style="color: #0000ff">insert</span> <span style="color: #0000ff">into</span><span style="color: #000000"> employee (id,denumber,idnumber,worknumber,pwd,emname,tel,salary,entrytime,orderpaixu)
  5. </span><span style="color: #0000ff">values</span><span style="color: #000000">(OLD.id,OLD.denumber,OLD.idnumber,OLD.worknumber,OLD.pwd,OLD.emname,OLD.tel,OLD.salary,OLD.entrytime,OLD.orderpaixu);
  6. </span><span style="color: #0000ff">end</span>

 

php代码

  1.  <span style="color: #0000ff">public</span> <span style="color: #0000ff">function</span> actionRecoveremployeedel(<span style="color: #800080">$id</span><span style="color: #000000">)
  2.     {
  3.         Employeebackup</span>::findOne(<span style="color: #800080">$id</span>)-><span style="color: #000000">delete();
  4.         </span><span style="color: #0000ff">return</span> <span style="color: #800080">$this</span>->redirect([‘recoveremployee‘<span style="color: #000000">]);
  5.     }</span>

除了备份，有时还要做到清空功能，使用truncate方法，将备份表中的数据彻底清空，并且释放内存，而且这个方法进行数据删除不会调用触发器。

yii2的后台中调用sql的原始写法，即将所有已经删除的用户清空。

  1. <span style="color: #000080; font-weight: bold">public function <span>actionDropemployeedel()<br><span>{<br><span>    Yii::<span style="color: #660e7a; font-style: italic">$app<span>-><span style="color: #000080; font-weight: bold">db<span>->createCommand(<span style="color: #008000; font-weight: bold">‘truncate table employeebackup‘<span>)<br><span>        ->execute();<br><span>    <span style="color: #000080; font-weight: bold">return <span style="color: #660000">$this<span>->redirect([<span style="color: #008000; font-weight: bold">‘recoveremployee‘<span>]);<br><span>}</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

以上就是mysql触发器在小项目中的一个简单用法。

原文连接：http://www.cnblogs.com/liebagefly/p/7820324.html

MYSQL触发器在PHP项目中用来做信息备份、恢复和清空

标签：释放   target   redirect   try   class   number   each   name   mys   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！