What are the ways to delete database records?

Introduction

Deleting database records is a very common requirement. When the data loses its value, we will delete it, but if it is not done properly, it will often be deleted. Some valuable data are accidentally deleted, resulting in the loss of important data. Only by using reasonable deletion methods can we make better use of data resources. Here are some commonly used deletion methods.

Deletion method

Physical deletion

Physical deletion is to delete one or more records directly from the database and erase the data from the disk. You can use DELETE FROMSQL statement is implemented. The consequence of this method is that the record is permanently deleted and cannot be retrieved. It is generally suitable for small projects or projects with low data importance, and can improve database resource utilization. The physical deletion method is the simplest and most basic data deletion method. I won’t introduce it here. Let’s mainly look at the logical deletion method.

Logical deletion

The so-called logical deletion is to achieve the effect that the record has been deleted, but in fact the data still exists in the database, but this part of the data is hidden from the user. Some large-scale applications with high data correlation and high data importance often use this deletion method. It can implement practical functions such as the recycle bin, deletion recovery, and viewing historical versions. It has different application scenarios according to business needs.

Application Scenario

For example, in some mailbox applications, when you delete an email, the email will not be deleted directly, but the email will be moved to the recycle bin. You can Recovery, complete deletion and other operations of emails can effectively prevent accidental deletion and other situations.
For another example, blog management platforms generally provide functions such as viewing modification history and comparing historical versions. We can easily view the modification history of articles and restore to a previous version.

Implementation ideas

Mark deletion

The logical deletion function can be easily implemented by deleting the mark. By adding a delete mark field in the table, the normal recorded This field is set to 0, and the field of deleted records is set to 1. When querying, add a where condition to filter and delete records marked with 0, so that the logical deletion function can be realized. At this time, the deletion business only needs You need to modify the record's deletion mark field to 1.

zipper

The zipper method comes from the data warehouse and is defined for the way tables store data in data warehouse design. The so-called zipper is to record history and record a thing from the beginning to the current state. information on all changes. The zipper algorithm is one of the most typical algorithms in the field of data warehouse.
The difference between this table structure and an ordinary table is that there are two more fields (START_DATE&END_DATE) indicating the effective time of the record, respectively adding time and record to the record Maximum validity time.

• The data table adopts the joint primary key method, using id and START_DATE to uniquely represent a certain record, such as:

  
  

  CREATE TABLE `table_name` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  ...,primary key(`id`,`start_date`)
  )ENGINE=MYISAM DEFAULT CHARSET=utf8;

• When adding a record, START_DATE can be set to the current time, END_DATE can be set to null or a time in the future to represent infinity, such as:

  
  

  insert into table_name(start_date,end_date,...) values(当前时间,一百年后,...);

• When querying data, you only need to filter the date to get the currently valid records, such as

  
  

  select * from table_name where id=记录ID and start_date<=当前时间 and end_date>当前时间;

• The way to modify records is different from the traditional way. The modification operation is not to directly modify a record in the database, but to modify the original valid recordEND_DATE is set to the current time, and then a complete and modified record is added, such as:

  
  

  update table_name set end_date=当前时间 where id=原记录ID and end_date=一百年后;insert into table_name(id,start_date,end_date,...) values(原记录ID,当前时间,一百年后,...);

• The deletion operation is very easy Simple, you don’t really remove the record from the data table, you just need to set the END_DATE of the record to the current time, such as:

  
  

  update table_name set end_date=当前时间 where id=删除记录ID;

• In this way, the changes in the data can be completely recorded. Use the following query statement to obtain the complete version list of a record and view the content of a specific version:
  " sql
  -- Get version list
  

• select start_date from table_name where id=记录ID order by start_date;

-- View specific version content

select * from table_name where id=记录ID and start_date=版本日期;

写在最后

不同的业务需要根据其应用场景来选择合适的数据删除方式，一般的应用可以采用物理删除的方式，简单粗暴地将数据擦除，这样可以有效提高数据库地利用率，如果历史数据一点价值都没有或者价值不高，那还留着干什么，这时如果采用逻辑删除地方式反而加重了数据库的负担，浪费了大量宝贵的资源。但是有些项目如金融、交通、能源等领域的历史数据，往往具有很高的利用价值，通过对这些数据进行分析总结，可以更好的了解该领域的发展情况和健康程度，以及对未来的发展规划起到一定指导作用，这时就要采用逻辑删除的方式，虽然数据管理平台为了便于管理，删除了过期的数据，但数据分析系统仍能从数据库中获取到历史数据，通过抽取转换加载的过程，将历史数据转化为高价值的内容，这是目前信息技术发展的主要趋势。

The above is the detailed content of What are the ways to delete database records?. For more information, please follow other related articles on the PHP Chinese website!