Mysql triggers are used in PHP projects for information backup, recovery and clearing

Case:

Through the PHP background code, employee information can be deleted and the deleted employee information can be restored (similar to restoring employee information from the recycle bin), and it can also Clear deleted employees (similar to the function of clearing the reply station). This article mainly introduces the relevant information about Mysql triggers used for information backup, recovery and clearing in PHP projects. Friends in need can refer to it.

Idea:

There must be an employee table and an employee backup table; for backup, use triggers to delete employees before clicking the delete button to perform the delete function. The information in the table is imported into the backup table, thus achieving the backup effect; to restore, use a trigger on the backup table to delete the data in the backup table, and import this data into the employee table while deleting; to clear, use The truncate method completely clears the data in the backup table and releases the memory, and this method does not call triggers for data deletion. Not much to say, just get down to the practical stuff.

Step one: Create tables, employee tables, and employee backup tables.

CREATE TABLE `employee` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `denumber` varchar(255) DEFAULT '0',
 `idnumber` varchar(255) DEFAULT '0',
 `worknumber` varchar(255) DEFAULT '1',
 `pwd` varchar(255) DEFAULT NULL,
 `emname` varchar(255) DEFAULT '0',
 `tel` varchar(255) DEFAULT '0',
 `salary` int(255) DEFAULT '0',
 `entrytime` varchar(255) DEFAULT '0',
 `orderpaixu` int(255) DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8

This is the employee table

CREATE TABLE `employeebackup` (
 `id` int(11) NOT NULL,
 `denumber` varchar(255) DEFAULT NULL,
 `idnumber` varchar(255) DEFAULT NULL,
 `worknumber` varchar(255) DEFAULT NULL,
 `pwd` varchar(255) DEFAULT NULL,
 `emname` varchar(255) DEFAULT NULL,
 `tel` varchar(255) DEFAULT NULL,
 `salary` int(255) DEFAULT NULL,
 `entrytime` varchar(255) DEFAULT NULL,
 `orderpaixu` int(255) DEFAULT NULL,
 `deletetime` datetime DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The employee backup table has an additional field deletetime, in order to record the deletion time

Second step: Backup, create a trigger for the employee table (for information about triggers, you can check my other blog http://www.cnblogs.com/liebagefly/p/7517998.html), import the information in the employee table into the backup table before clicking the delete button to perform the delete function.

Trigger sql code:

CREATE trigger deletesemployee before delete on employee
for each ROW
begin 
 insert into employeebackup (id,denumber,idnumber,worknumber,pwd,emname,tel,salary,entrytime,orderpaixu,deletetime)
values(OLD.id,OLD.denumber,OLD.idnumber,OLD.worknumber,OLD.pwd,OLD.emname,OLD.tel,OLD.salary,OLD.entrytime,OLD.orderpaixu,NOW());
end

php background method, the framework I use is yii2.

 public function actionEmployeedel($id)
 {
  Employee::findOne($id)->delete();
  return $this->redirect(['employeemanage']);
 }

The third step: Recovery, restore the deleted information, use triggers on the backup table, delete the data in the backup table, While deleting, import this data into the employee table.

Trigger sql code:

CREATE trigger deletesemployeebackup before delete on employeebackup
for each ROW
begin 
  insert into employee (id,denumber,idnumber,worknumber,pwd,emname,tel,salary,entrytime,orderpaixu)
values(OLD.id,OLD.denumber,OLD.idnumber,OLD.worknumber,OLD.pwd,OLD.emname,OLD.tel,OLD.salary,OLD.entrytime,OLD.orderpaixu);
end

php code

public function actionRecoveremployeedel($id)
 {
  Employeebackup::findOne($id)->delete();
  return $this->redirect(['recoveremployee']);
 }

In addition to backup, sometimes the clearing function is also required. Use the truncate method to completely clear the data in the backup table and release the memory. This method also deletes data. The trigger will not be called.

The original way of calling sql in the background of yii2 is to clear all deleted users.

public function actionDropemployeedel()
{
 Yii::$app->db->createCommand('truncate table employeebackup')
  ->execute();
 return $this->redirect(['recoveremployee']);
}

How did you learn it? If it is useful, collect it quickly.

Related recommendations:

Mysql trigger details

Mysql trigger introduction and how to create and delete triggers

[Original] Use MySQL triggers to create data with high performance

The above is the detailed content of Mysql triggers are used in PHP projects for information backup, recovery and clearing. For more information, please follow other related articles on the PHP Chinese website!