SQL-based soft deletion methods, systems, and media for storing data.
By adding a soft delete field to the SQL database and using a proxy data source to intercept modified SQL statements, the problem of long recovery times for large-scale data was solved, achieving efficient data recovery and performance protection.
Patent Information
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- SHANGHAI SHUHE INFORMATION TECH CO LTD
- Filing Date
- 2025-12-24
- Publication Date
- 2026-05-26
AI Technical Summary
In existing technologies, large-scale data recovery is time-consuming, and soft deletion causes the table data to grow larger and larger, affecting operational efficiency.
By adding a soft delete field to the SQL database, the proxy data source intercepts and modifies the SQL statement, generates the target SQL statement, and sends it to the database, thus realizing the soft delete process of data.
This effectively prevents excessive data volume in the original table from impacting performance, reduces intrusion into business code, and improves data recovery efficiency.
Smart Images
Figure CN122086868A_ABST
Abstract
Description
Technical Field
[0001] This application belongs to the field of data processing technology, and relates to a data storage method, particularly to a soft deletion method, system and medium for storing data based on SQL. Background Technology
[0002] In most enterprise applications, SQL is used to store data in relational databases, and data deletion is done directly on the physical media (disk). Once data is mistakenly deleted due to user error, application bugs, or other factors, it is very difficult to recover.
[0003] Existing technologies typically employ database rollback solutions or add an is_delete field to the table to distinguish whether data has been deleted. However, for large amounts of data, there are issues such as long recovery times and soft deletions causing the table to grow larger and larger. Summary of the Invention
[0004] The purpose of this application is to provide a soft deletion method, system, and medium based on SQL-stored data, in order to solve the problem that the recovery of large amounts of data in the prior art is time-consuming and affects the operating efficiency.
[0005] Firstly, this application provides a soft deletion method for stored data based on SQL, the method comprising: Obtain the table structure of the data in the SQL database, modify the table structure, and add a soft delete field; When the application performs a modification operation on the target data, it obtains the proxy link corresponding to the target data from the data source through the proxy data source and returns the proxy link to the application. The proxy data source and the data source have the same structure. After the application sends an SQL statement based on the modified table structure and the proxy link, the proxy data source intercepts and modifies the SQL statement to obtain the target SQL statement, and then sends the target SQL statement to the corresponding database.
[0006] In one implementation of the first aspect, when the application performs a modification operation on the target data, obtaining the proxy link corresponding to the target data from the data source through the proxy data source and returning the proxy link to the application includes: When the application retrieves a link from the data source based on the target data, the data source creates a database link corresponding to the target data; The proxy data source generates a proxy link based on the database link, and the proxy data source sends the proxy link to the application.
[0007] In one implementation of the first aspect, the step of intercepting and modifying the SQL statement through the proxy data source to obtain the target SQL statement includes: Determine the type of the SQL statement obtained; When it is determined that the type of the SQL statement is a database definition statement type (DDL), there is no need to modify the SQL statement; When it is determined that the type of the SQL statement is a Data Manipulation Statement (DML), the beginning of the SQL statement is obtained to determine the corresponding sub-type; The SQL statement is processed according to the specific sub-type.
[0008] In one implementation of the first aspect, processing the SQL statement according to the subdivision type includes: When it is determined that the SQL statement is an add type, a soft delete field is added to the original SQL statement, and the field value of the soft delete field is configured to a first initial value; When it is determined that the SQL statement is of the modification type, a soft delete field and a second initial value are added to the filter condition part of the original SQL statement; When it is determined that the SQL statement is of the deletion type, the delete statement in the original SQL statement is modified into an update statement, the soft delete field in the SQL statement is modified to the current timestamp, and a soft delete condition is added to the filter condition part of the SQL statement. When the SQL statement is determined to be a query type, a soft delete condition is added to the filter condition section of the SQL statement.
[0009] In one implementation of the first aspect, when it is determined that the SQL statement is a query type, if the SQL statement contains a target keyword, a soft delete condition is added to the ON condition to which the keyword belongs.
[0010] In one implementation of the first aspect, the step of intercepting and modifying the SQL statement through the proxy data source to obtain the target SQL statement further includes: Determine if the SQL statement contains a substatement; If it exists, modify the statements in each level of the sub-statement.
[0011] In one implementation of the first aspect, modifying the table structure and adding a soft delete field includes: Determine whether a soft delete field exists in the table structure; If it does not exist, add the soft delete field to the table structure and determine whether the unique index of the modified table structure has the soft delete field. If the soft delete field is not present in the unique index of the modified table structure, then the soft delete field is added to the unique index.
[0012] In one implementation of the first aspect, the method further includes: When the database is in a low-business period, perform data migration to migrate the pending data with a soft delete field of 0 in the database to the archive table, and delete the data with a soft delete field of 0 from the database.
[0013] Secondly, the present invention also provides a soft deletion system for storing data based on SQL, comprising: The structure modification module is used to obtain the table structure of data in the SQL database, modify the table structure, and add soft delete fields. The link generation module is used to obtain the proxy link corresponding to the target data from the data source through the proxy data source when the application performs a modification operation on the target data, and return the proxy link to the application, wherein the proxy data source and the data source have the same structure; The soft delete module is used to intercept and modify the SQL statement through the proxy data source to obtain the target SQL statement after the application sends the SQL statement according to the modified table structure and the proxy link, and then send the target SQL statement to the corresponding database.
[0014] Thirdly, the present invention also discloses a computer-readable storage medium having a computer program stored thereon, which, when executed, implements the aforementioned soft deletion method for storing data based on SQL.
[0015] As described above, the soft deletion method, system, and medium based on SQL-stored data described in this application have the following beneficial effects: This application's solution avoids the invalidation of unique indexes by automatically modifying the table structure. Then, it uses a proxy data source to generate a proxy link corresponding to the target data and sends it to the application. After the application sends the SQL statement, the proxy data source intercepts and modifies the SQL statement to obtain the target SQL statement. The target SQL statement is then sent to the corresponding database, thereby realizing the soft deletion process of data. This effectively prevents the performance impact of excessive data volume in the original table, and it does not require manual modification of the SQL statement, thus having less intrusion into the business code. Attached Figure Description
[0016] Figure 1The flowchart shown is a soft deletion method for storing data based on SQL, as described in an embodiment of this application.
[0017] Figure 2 This diagram illustrates the process of modifying the table structure and adding a soft-delete field in the SQL-based soft-delete method described in this application embodiment.
[0018] Figure 3 This diagram illustrates the process of intercepting and modifying SQL statements in the soft deletion method for storing data based on SQL, as described in this application embodiment.
[0019] Figure 4 The diagram shown is a structural block diagram of the soft deletion system based on SQL storage data as described in the embodiments of this application. Detailed Implementation
[0020] The following specific examples illustrate the implementation of this application. Those skilled in the art can easily understand other advantages and effects of this application from the content disclosed in this specification. This application can also be implemented or applied through other different specific embodiments, and various details in this specification can also be modified or changed based on different viewpoints and applications without departing from the spirit of this application. It should be noted that, unless otherwise specified, the following embodiments and features in the embodiments can be combined with each other.
[0021] It should be noted that the illustrations provided in the following embodiments are only schematic representations of the basic concept of this application. Therefore, the drawings only show the components related to this application and are not drawn according to the actual number, shape and size of the components in the actual implementation. In the actual implementation, the form, quantity and proportion of each component can be arbitrarily changed, and the layout of the components may also be more complex.
[0022] See Figures 1 to 4 The following embodiments of this application provide a method, system, and medium for soft deletion of data stored in SQL. This method avoids the invalidation of unique indexes by automatically modifying the table structure. Then, a proxy link corresponding to the target data is generated using a proxy data source and sent to the application. After the application sends the SQL statement, the proxy data source intercepts and modifies the SQL statement to obtain the target SQL statement, which is then sent to the corresponding database. This achieves the soft deletion process, effectively preventing performance impact from excessive original table data volume. Furthermore, it eliminates the need for manual modification of the SQL statement, resulting in less intrusion into business code.
[0023] The technical solutions in the embodiments of this application will be described in detail below with reference to the accompanying drawings.
[0024] like Figure 1As shown, this embodiment provides a soft deletion method for data stored using SQL, the method comprising the following steps: S100. Obtain the table structure of the data in the SQL database, modify the table structure and add a soft delete field.
[0025] In some embodiments, modifying the table structure and adding a soft delete field includes: Determine whether a soft delete field exists in the table structure; If it does not exist, add the soft delete field to the table structure and determine whether the unique index of the modified table structure has the soft delete field. If the soft delete field is not present in the unique index of the modified table structure, then the soft delete field is added to the unique index.
[0026] In this embodiment, after obtaining the table structure of the data in the SQL database, the table structure is modified and a soft delete field is added so that the timestamp of the soft delete data can be stored in the soft delete field to facilitate subsequent data recovery.
[0027] For details, please refer to Figure 2 During the modification of the table structure, since the table structure contains multiple field names, the process first checks whether a soft-delete field exists in the table structure. If it does not exist, the soft-delete field is added to the table structure; otherwise, the process continues to the next step. This involves further checking whether a soft-delete field exists in the unique index of the table structure. If it does not exist, the unique index needs to include the soft-delete field to avoid the problem of duplicate values in the unique index being unable to be written to the database due to soft deletion.
[0028] For example, taking the soft delete field as deleted_at, refer to... Figure 2 The table structure before modification is shown in the table below. During the modification of the table structure, since the soft delete field `deleted_at` was not present in the table structure, the table structure was modified to add the soft delete field. The modified table structure is shown in the table below. S200. When the application performs a modification operation on the target data, the proxy link corresponding to the target data is obtained from the data source through the proxy data source, and the proxy link is returned to the application. The proxy data source and the data source have the same structure.
[0029] In some embodiments, when the application performs a modification operation on the target data, obtaining the proxy link corresponding to the target data from the data source through the proxy data source and returning the proxy link to the application includes: When the application retrieves a link from the data source based on the target data, the data source creates a database link corresponding to the target data; The proxy data source generates a proxy link based on the database link, and the proxy data source sends the proxy link to the application.
[0030] In existing technologies, when an application performs modification operations on a database, it typically obtains a database connection from a data source, sends SQL statements to the database through this connection, and executes the corresponding CRUD operations. However, in this application's solution, reference... Figure 3 When the application retrieves data from the data source, it directly obtains the database connection created by the data source. Then, the proxy data source generates a proxy connection based on the database connection and sends the proxy connection to the application. This facilitates the subsequent interception and modification of SQL statements issued by the application by the proxy data source, thereby achieving soft deletion of the target data and meeting the soft deletion requirements of the database.
[0031] S300: After the application sends the SQL statement according to the modified table structure and the proxy link, the SQL statement is intercepted and modified through the proxy data source to obtain the target SQL statement, and the target SQL statement is sent to the corresponding database.
[0032] In this embodiment, when it is necessary to modify target data in the database, the application first generates the corresponding SQL statement based on the table structure and proxy link, and sends it to the database. The proxy data source then intercepts and modifies the SQL statement to obtain the target SQL statement, and then sends the target SQL statement to the database, thereby completing the soft deletion process of the corresponding target data in the database.
[0033] In some other embodiments, the step of intercepting and modifying the SQL statement through the proxy data source to obtain the target SQL statement includes: Determine the type of the SQL statement obtained; When it is determined that the type of the SQL statement is a database definition statement type (DDL), there is no need to modify the SQL statement; When it is determined that the type of the SQL statement is a Data Manipulation Statement (DML), the beginning of the SQL statement is obtained to determine the corresponding sub-type; The SQL statement is processed according to the specific sub-type.
[0034] In this embodiment, during the process of intercepting and modifying SQL statements by the proxy data source, the type of the SQL statement is first determined. Generally, SQL statements sent to the database are divided into two categories: the first is Database Definition Language (DDL), which only modifies the table structure and does not affect the data, so no modification is needed; the second is Data Manipulation Language (DML), which affects the data and needs to be rewritten to adapt to the soft delete function. Therefore, when it is determined that the SQL statement type is a Data Manipulation Language (DML), the beginning of the SQL statement is obtained to further determine the corresponding sub-type, facilitating further subdivision and processing of the SQL statement based on the sub-type.
[0035] Specifically, Data Manipulation (DML) statements are typically categorized by their prefix: Insert, Update, Delete, and SELECT. Furthermore, the step of processing the SQL statement according to the subdivision type includes: When it is determined that the SQL statement is an add type, a soft delete field is added to the original SQL statement, and the field value of the soft delete field is configured to a first initial value; When it is determined that the SQL statement is of the modification type, a soft delete field and a second initial value are added to the filter condition part of the original SQL statement; When it is determined that the SQL statement is of the deletion type, the delete statement in the original SQL statement is modified into an update statement, the soft delete field in the SQL statement is modified to the current timestamp, and a soft delete condition is added to the filter condition part of the SQL statement. When it is determined that the SQL statement is a query type, a soft delete condition is added to the filter condition part of the SQL statement; Wherein, both the first initial value and the second initial value are 0.
[0036] For example, taking the soft delete field (deleted_at) as an example, when it is determined that the SQL statement starts with insert and is a new type, a soft delete field is directly added to the end of the original SQL statement's fields, and the first initial value of the soft delete field is configured to be 0. That is, the words after the keyword insert into in the SQL statement are identified as table names; the content in parentheses after the table name is separated by commas as field names, and the deleted_at field is appended to the end of the field names; the content in parentheses after the keyword values is separated by commas as field values, and 0 is appended to the end of the field values.
[0037] For example: the original SQL is insert into table (field1,field2,field3) values(value1,value2,value3); the modified SQL is insert into table (field1,field2,field3,deleted_at) values (value1,value2,value3,0).
[0038] When the SQL statement is determined to be an update type, since no data deletion is required, a soft delete field and a second initial value are directly added to the filter condition part of the original SQL statement. This means appending the `deleted_at = 0` condition to the original SQL statement's filter condition part (i.e., the `WHERE` clause). This is implemented by parsing it into an abstract syntax tree. For example: the original SQL is `update table set field1=value1 where field2=value2;` and the modified SQL is `update table set field1=value1 where field2=value2 and deleted_at=0;`
[0039] When the SQL statement is determined to be a deletion type, the `delete` statement in the original SQL statement is modified to an `update` statement, and the soft-delete field in the SQL statement is modified to the current timestamp to facilitate subsequent determination of the data deletion date. Since the filtering condition part of the SQL statement remains unchanged, a soft-delete condition `deleted_at=0` is added to the filtering condition part of the SQL statement. For example, the original SQL statement is `delete from table where field1=value1;`, which is modified to `update table set deleted_at = N where field1=value1 and deleted_at=0;`, where N is the current timestamp. By using the deletion timestamp to mark the soft-deleted data, compared to a single fixed value scheme, it does not cause the database unique index to become invalid.
[0040] When determining that the SQL statement is a query type, add the soft delete condition `deleted_at=0` directly to the filter condition part of the original SQL statement. If the `join` keyword exists, the soft delete condition `deleted_at=0` needs to be appended to the `on` condition of the `join` statement. For example, the original SQL is `select field1,field2 from table1 join table2on table1.field3=table2.field4 where table1.field3 = value3;`, which can be modified to `select field1,field2 from table1 join table2 on table1.field3=table2.field4 and table2.deleted_at = 0 where table1.field3 = value3 and table1.deleted_at =0;`
[0041] It should be noted that the process of modifying the SQL statement according to its type in the above process is implemented using the abstract syntax tree in existing technology. This solution does not involve any improvement to the modification technology itself, and will not be elaborated here.
[0042] In some embodiments, when it is determined that the SQL statement is a query type, if the SQL statement has a target keyword, a soft delete condition deleted_at = 0 is added to the ON condition to which the keyword belongs.
[0043] In some embodiments, the step of intercepting and modifying the SQL statement through the proxy data source to obtain the target SQL statement further includes: Determine if the SQL statement contains a substatement; If it exists, modify the statements in each level of the sub-statement.
[0044] In this embodiment, since the SQL statement may contain substatements, when modifying the table structure, it is first determined whether the SQL statement contains substatements. If it does, the above modification process is performed on each level of substatement to ensure that each substatement is modified once and to avoid omissions.
[0045] In some other embodiments, the method further includes: When the database is in a low-business period, perform data migration to migrate the pending data with a soft delete field of 0 in the database to the archive table, and delete the data with a soft delete field of 0 from the database.
[0046] This invention also discloses a soft deletion system for storing data based on SQL, see reference. Figure 4 ,include: The structure modification module 401 is used to obtain the table structure of data in the SQL database, modify the table structure, and add a soft delete field. The link generation module 402 is used to obtain the proxy link corresponding to the target data from the data source through the proxy data source when the application performs a modification operation on the target data, and return the proxy link to the application, wherein the proxy data source and the data source have the same structure; The soft delete module 403 is used to intercept and modify the SQL statement through the proxy data source to obtain the target SQL statement after the application sends the SQL statement according to the modified table structure and the proxy link, and then send the target SQL statement to the corresponding database.
[0047] The present invention also provides a computer-readable storage medium having a computer program stored thereon, which, when executed, implements the above-described soft deletion method for storing data based on SQL.
[0048] The scope of protection of the soft deletion method based on SQL storage data described in this application is not limited to the execution order of the steps listed in this embodiment. Any solution implemented by adding, subtracting, or replacing steps in the prior art based on the principles of this application is included within the scope of protection of this application.
[0049] This invention also provides a computer-readable storage medium storing a computer program thereon, which, when executed by an electronic device, implements the aforementioned soft deletion method for storing data based on SQL.
[0050] Those skilled in the art will understand that all or part of the steps in the methods of the above embodiments can be implemented by a program instructing a processor. The program can be stored in a computer-readable storage medium, which is a non-transitory medium, such as random access memory, read-only memory, flash memory, hard disk, solid-state drive, magnetic tape, floppy disk, optical disk, and any combination thereof. The storage medium can be any available medium accessible to a computer or a data storage device such as a server or data center that integrates one or more available media. This available medium can be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., digital video disc (DVD)), or a semiconductor medium (e.g., solid-state drive (SSD)).
[0051] This application also provides a soft deletion system based on SQL-stored data. The soft deletion system based on SQL-stored data can implement the soft deletion method based on SQL-stored data described in this application. However, the implementation device of the soft deletion method based on SQL-stored data described in this application includes, but is not limited to, the structure of the soft deletion system based on SQL-stored data listed in this embodiment. All structural modifications and substitutions of the prior art made in accordance with the principles of this application are included within the protection scope of this application.
[0052] It should be noted that the specific working principles of each of the above modules correspond one-to-one with the steps of the aforementioned soft deletion method based on SQL-stored data, so they will not be repeated here.
[0053] In the embodiments provided in this application, it should be understood that the disclosed systems, apparatuses, or methods can be implemented in other ways. For example, the apparatus embodiments described above are merely illustrative. For instance, the division of modules / units is only a logical functional division, and in actual implementation, there may be other division methods. For example, multiple modules or units may be combined or integrated into another system, or some features may be ignored or not executed. Furthermore, the coupling or direct coupling or communication connection shown or discussed may be through some interfaces; the indirect coupling or communication connection of apparatuses or modules or units may be electrical, mechanical, or other forms.
[0054] The modules / units described as separate components may or may not be physically separate. The components shown as modules / units may or may not be physical modules; that is, they may be located in one place or distributed across multiple network units. Some or all of the modules / units can be selected to achieve the objectives of the embodiments of this application, depending on actual needs. For example, the functional modules / units in the various embodiments of this application may be integrated into one processing module, or each module / unit may exist physically separately, or two or more modules / units may be integrated into one module / unit.
[0055] Those skilled in the art will further recognize that the units and algorithm steps of the various examples described in conjunction with the embodiments disclosed herein can be implemented in electronic hardware, computer software, or a combination of both. To clearly illustrate the interchangeability of hardware and software, the components and steps of the various examples have been generally described in terms of functionality in the foregoing description. Whether these functions are implemented in hardware or software depends on the specific application and design constraints of the technical solution. Those skilled in the art can use different methods to implement the described functions for each specific application, but such implementation should not be considered beyond the scope of this application.
[0056] The descriptions of the processes or structures corresponding to the above figures each have their own emphasis. For parts of a process or structure that are not described in detail, please refer to the relevant descriptions of other processes or structures.
[0057] The above embodiments are merely illustrative of the principles and effects of this application and are not intended to limit this application. Any person skilled in the art can modify or alter the above embodiments without departing from the spirit and scope of this application. Therefore, all equivalent modifications or alterations made by those skilled in the art without departing from the spirit and technical concept disclosed in this application should still be covered by the claims of this application.
Claims
1. A soft deletion method for storing data based on SQL, characterized in that, The method includes: Obtain the table structure of the data in the SQL database, modify the table structure, and add a soft delete field; When the application performs a modification operation on the target data, the proxy link corresponding to the target data is obtained from the data source through the proxy data source, and the proxy link is returned to the application. The proxy data source and the data source have the same structure. After the application sends an SQL statement based on the modified table structure and the proxy link, the proxy data source intercepts and modifies the SQL statement to obtain the target SQL statement, and then sends the target SQL statement to the corresponding database.
2. The soft deletion method for storing data based on SQL according to claim 1, characterized in that, When the application performs a modification operation on the target data, obtaining the proxy link corresponding to the target data from the data source through the proxy data source and returning the proxy link to the application includes: When the application obtains a link from the data source based on the target data, the data source creates a database link corresponding to the target data; The proxy data source generates a proxy link based on the database link, and the proxy data source sends the proxy link to the application.
3. The soft deletion method for storing data based on SQL according to claim 1, characterized in that, The step of intercepting and modifying the SQL statement through the proxy data source to obtain the target SQL statement includes: Determine the type of the SQL statement obtained; When it is determined that the type of the SQL statement is a database definition statement type (DDL), there is no need to modify the SQL statement; When it is determined that the type of the SQL statement is a Data Manipulation Statement (DML), the beginning of the SQL statement is obtained to determine the corresponding sub-type; The SQL statement is processed according to the specific sub-type.
4. The soft deletion method for storing data based on SQL according to claim 3, characterized in that, The step of processing the SQL statement according to the subdivision type includes: When it is determined that the SQL statement is an add type, a soft delete field is added to the original SQL statement, and the field value of the soft delete field is configured to a first initial value; When it is determined that the SQL statement is of the modification type, a soft delete field and a second initial value are added to the filter condition part of the original SQL statement; When it is determined that the SQL statement is of the deletion type, the delete statement in the original SQL statement is modified into an update statement, the soft delete field in the SQL statement is modified to the current timestamp, and a soft delete condition is added to the filter condition part of the SQL statement. When the SQL statement is determined to be a query type, a soft delete condition is added to the filter condition section of the SQL statement.
5. The soft deletion method for storing data based on SQL according to claim 4, characterized in that, When determining that the SQL statement is a query type, if the SQL statement contains a target keyword, a soft delete condition is added to the ON condition to which the keyword belongs.
6. The soft deletion method for storing data based on SQL according to claim 3, characterized in that, The step of intercepting and modifying the SQL statement through the proxy data source to obtain the target SQL statement also includes: Determine if the SQL statement contains a substatement; If it exists, modify the statements in each level of the sub-statement.
7. The soft deletion method for storing data based on SQL according to claim 1, characterized in that, The modification of the table structure and the addition of a soft delete field include: Determine whether a soft delete field exists in the table structure; If it does not exist, add the soft delete field to the table structure and determine whether the unique index of the modified table structure has the soft delete field. If the soft delete field is not present in the unique index of the modified table structure, then the soft delete field is added to the unique index.
8. The soft deletion method for SQL-based stored data according to any one of claims 1 to 7, characterized in that, The method further includes: When the database is in a low-business period, perform data migration to migrate the pending data with a soft delete field of 0 in the database to the archive table, and delete the data with a soft delete field of 0 from the database.
9. A soft deletion system for storing data based on SQL, characterized in that... include: The structure modification module is used to obtain the table structure of data in the SQL database, modify the table structure, and add soft delete fields. The link generation module is used to obtain the proxy link corresponding to the target data from the data source through the proxy data source when the application performs a modification operation on the target data, and return the proxy link to the application, wherein the proxy data source and the data source have the same structure; The soft delete module is used to intercept and modify the SQL statement through the proxy data source to obtain the target SQL statement after the application sends the SQL statement according to the modified table structure and the proxy link, and then send the target SQL statement to the corresponding database.
10. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the program is executed, it implements the soft deletion method for storing data based on SQL, as described in any one of claims 1 to 8.