Data maintenance method and device for database

Abstract

Disclosed in the present invention is a data maintenance method and device for a database. The method comprises: creating an automated database maintenance task, wherein the automated database maintenance task comprises at least one sub-task, and information comprised in each sub-task at least comprises: a table name, the number of keepdays KeepDays, the minimum number of keeprows KeepRowsMin, the maximum number of deleterows at a time DeleteRowsMax, and an expected next delete time NextDelTime; acquiring information comprised in a sub-task with a minimum expected next delete time in the automated database maintenance task; and when the expected next delete time NextDelTime comprised in the sub-task is less than or equal to a current system time DtNow, executing a data maintenance task on a corresponding data table in a database according to the information comprised in the sub-task, and during the execution of the data maintenance task, automatically deleting data in the data table KeepDays ago on the basis of a time field, and ensuring that at least KeepRowsMin rows of data are left in the data table, no more than DeleteRowsMax rows of data being deleted at a time. By means of the method of the present invention, database maintenance of a process control system can be completed in a full-automated mode, and the burden of manual maintenance is reduced.

Description

Method and device for maintaining database data Technical Field

[0001] The present invention relates to the technical field of database management, and in particular to a data maintenance method and device for a process control system database. Background Art

[0002] The process control system is a bridging management system in the steel industry. It obtains master data from the upper-level manufacturing execution system (MES), calculates production setpoints, and transmits these to the lower-level basic automation system for execution. During production, the process control system acquires measured process data from the lower-level basic automation system, generates actual production results, and transmits these results to the upper-level MES.

[0003] Essentially, a process control system involves data exchange, management, and display. It includes master data tables, performance data tables, real-time production process data tables, log tables, process parameter configuration tables, and process parameter history tables, among others. As the amount of data in these tables increases, program query and write efficiency may be affected. Generally, real-time data should be retained for at least six months, and material master data and performance data should be retained for at least two years.

[0004] To ensure the stable and efficient operation of the system, maintenance personnel usually delete early historical data on a regular basis. However, this operation not only increases manual workload but also may lead to the risk of misoperation.

[0005] Some process control systems use a bat command to periodically delete historical data. However, this method has the following issues: 1. Deleting large amounts of data can cause the database to lock temporarily, impacting normal program operation. 2. If a unit is out of service for an extended period, the data table may be completely cleared. 3. Deleted data cannot be accessed again.

[0006] Summary of the Invention

[0007] The purpose of the present invention is to overcome at least one defect in the prior art and to provide a method and device for maintaining database data.

[0008] To achieve the above object, the present invention provides a method for maintaining data in a database, comprising the following steps:

[0009] S1: Create a database automatic maintenance task. The database automatic maintenance task includes at least one subtask. Each subtask contains at least the following information: table name, retention days (KeepDays), minimum number of retained rows (KeepRowsMin), maximum number of rows to be deleted at a time (DeleteRowsMax), and expected next deletion time (NextDelTime).

[0010] S2: Obtain information contained in the subtask with the smallest expected next deletion time in the database automatic maintenance task. If the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, execute step S2 again. If the expected next deletion time NextDelTime contained in the subtask is less than or equal to the current system time DtNow, execute step S3.

[0011] S3: Perform data maintenance tasks on the corresponding data table in the database according to the information contained in this subtask. When performing data maintenance tasks, automatically delete the data in the data table older than KeepDays days based on the time field to ensure that at least KeepRowsMin rows of data remain in the data table, and no more than DeleteRowsMax rows of data can be deleted at a single time.

[0012] In some embodiments, establishing a database automatic maintenance task specifically includes: establishing a database automatic maintenance table, wherein the fields of the database automatic maintenance table include at least: table name, retention days KeepDays, minimum number of retained rows KeepRowsMin, maximum number of rows deleted at a time DeleteRowsMax, and expected next deletion time NextDelTime;

[0013] Each row of the database automatic maintenance table corresponds to a subtask.

[0014] In some embodiments, in step S2, when the expected next deletion time NextDelTime included in the subtask is greater than the current system time DtNow, the waiting time T0 or T1 is repeated, and then step S2 is executed again.

[0015] In some embodiments, T0 is equal to the total number of seconds of NextDelTime minus DtNow and rounded up.

[0016] T1 is the set time.

[0017] In some embodiments, after each execution of the data maintenance task in S3, the expected next deletion time NextDelTime of the subtask is updated, the waiting time T1 is waited, and then step S2 is entered.

[0018] In some embodiments, before executing the data maintenance task, it also includes: determining whether there is a data table corresponding to the table name contained in the subtask in the database. If there is a corresponding data table in the database, the data maintenance task is executed on the data table. Otherwise, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and then wait for T1 seconds to enter step S2, where T2 is greater than T1.

[0019] In some embodiments, there is only one time field in the data table. When executing the data maintenance task, the data in the data table older than KeepDays days is automatically deleted according to the time field TimeFieldName.

[0020] or,

[0021] There are one or more time fields in the data table. The information contained in each subtask also includes a time field TimeFieldName. The time field TimeFieldName is one of the time fields in the data table. When executing the data maintenance task, the data in the data table before KeepDays days is automatically deleted according to the time field TimeFieldName.

[0022] In some embodiments, step S3 specifically includes:

[0023] S31: Obtain the total number of rows r1 in the data table whose time field TimeFieldName is less than deadlineLineTime, where deadlineLineTime is equal to the current system time DtNow minus the number of retention days KeepDays. If r1 is greater than 0, proceed to step S32;

[0024] S32: Get the total number of rows r2 in the data table, and get the minimum value delTarget among r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is greater than 0, proceed to step S33;

[0025] S33: Obtaining delTime, including: searching the data table in ascending order according to the time field TimeFieldName, skipping the delTarget row, and obtaining the value of the time field TimeFieldName in the first row, namely delTime;

[0026] S34: Delete all rows in the data table whose time field TimeFieldName is less than delTime, and obtain the actual number of deleted rows delCnt. If delCnt is greater than or equal to DeleteRowsMax, update the NextDelTime of the subtask to the current system time DtNow plus T1 seconds, wait for T1 seconds and enter step S2. If delCnt is less than DeleteRowsMax, update the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, wait for T1 second and enter step S2, where T2 is greater than T1.

[0027] In some embodiments, after each execution of a data maintenance task, when it is found that the data maintenance task has not been completed (i.e., when r1 and r2-KeepRowsMin are both greater than DeleteRowsMax), the NextDelTime of the subtask is updated to the current system time DtNow plus T1 seconds, and step S2 is entered after waiting for T1 seconds; when it is found that the data maintenance task has been completed (i.e., when any one of r1 and r2-KeepRowsMin is less than or equal to DeleteRowsMax), the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and step S2 is entered after waiting for T1 seconds, and T2 is greater than T1.

[0028] In some embodiments, if r1 is equal to 0 in step S31, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S2 after waiting T1 seconds.

[0029] and / or,

[0030] If delTarget is less than or equal to 0 in step S32, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S5 after waiting for T1 seconds.

[0031] In some embodiments, the information included in the subtask further includes: information indicating whether to perform a backup, and between step S33 and step S34, the following step is further included: determining whether a backup is required based on the information included in the subtask, and if so, executing the data backup step and then proceeding to step S34; if not, skipping the data backup step and proceeding to step S34;

[0032] The data backup step is performed, specifically including: obtaining all rows in the data table where the time field TimeFieldName is less than delTime, and performing a different database backup after generating backup data.

[0033] In some embodiments, performing the data backup step specifically includes: obtaining all rows in the data table where the time field TimeFieldName is less than delTime, generating multiple rows of backup table data, and storing the data in the backup table;

[0034] The fields of the backup table include at least: a unique ID, the time of the original table, the name of the original table, a row of records in the original table, and the current time of the backup.

[0035] The present invention also discloses a database data maintenance device, comprising: a memory, a processor, and a database data maintenance program stored in the memory and executable on the processor, wherein the database data maintenance program is configured to implement the database data maintenance method described above.

[0036] The present invention has at least the following beneficial effects:

[0037] 1) The present invention can fully automatically complete the maintenance of the process control system database, reducing the burden of manual maintenance.

[0038] 2) Before deleting a data table, the present invention first performs a different database backup, which can prevent accidental deletion of data on the one hand, and also provides the possibility of later data backtracking on the other hand.

[0039] 3) The present invention performs current limiting operations during the data table deletion process, thereby avoiding table deadlock and ensuring the normal business logic operation of the process control system.

[0040] 4) The data table of the present invention retains at least a certain amount of data to ensure that the HMI interface of the process control system will not be empty. BRIEF DESCRIPTION OF THE DRAWINGS

[0041] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are only some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0042] FIG1 is a flow chart of a data maintenance method for a process control system database provided by an embodiment of the present invention. DETAILED DESCRIPTION

[0043] The following will clearly and completely describe the technical solutions in the embodiments of the present invention in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative efforts are within the scope of protection of the present invention.

[0044] To address the problem of database bloat caused by long-term operation in process control systems, which in turn affects operational efficiency, this paper proposes a method for maintaining historical database data. This method ensures that a table contains at least a certain number of rows, optimizes delete operations to avoid deadlocks, and backs up deleted data to improve database operational stability.

[0045] 1 , an embodiment of the present invention provides a data maintenance method for a process control system database, comprising the following steps:

[0046] S1: Create a database automatic maintenance task. The database automatic maintenance task includes at least one subtask. Each subtask contains at least the following information: table name, retention days (KeepDays), minimum number of retained rows (KeepRowsMin), maximum number of rows to be deleted at a time (DeleteRowsMax), and expected next deletion time (NextDelTime).

[0047] S2: Obtain information contained in the subtask with the smallest expected next deletion time in the database automatic maintenance task. If the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, wait for time T0 or T1 and then execute step S2 again. If the expected next deletion time NextDelTime contained in the subtask is less than or equal to the current system time DtNow, execute step S3.

[0048] S3: Automatic data table maintenance, including executing data maintenance tasks on the data table corresponding to the table name contained in the subtask in the database according to the information contained in the subtask. When executing the data maintenance task, the data in the data table older than KeepDays days is automatically deleted according to the time field to ensure that at least KeepRowsMin rows of data remain in the data table, and no more than DeleteRowsMax rows of data can be deleted at a single time.

[0049] In some embodiments, T0 is equal to the total number of seconds of NextDelTime minus DtNow and rounded up.

[0050] In some embodiments, after each execution of the data maintenance task in S3, the expected next deletion time NextDelTime of the subtask is updated, the waiting time T1 is waited, and then step S2 is entered.

[0051] T1 is the set time, which is generally a short time, such as 1 second.

[0052] The present invention can execute steps S2 and S3 regularly or periodically (for example, at a time interval T1) to maintain the data in the database.

[0053] The present invention can be configured so that the program starts running when the device is turned on, i.e., starts executing steps S2 and S3, and automatically ends running the program when the device is turned off.

[0054] In some embodiments, establishing a database automatic maintenance task specifically includes: establishing a database automatic maintenance table, the fields of the database automatic maintenance table include at least: table name, retention days KeepDays, minimum number of retained rows KeepRowsMin, maximum number of rows deleted at a single time DeleteRowsMax, expected next deletion time NextDelTime; each row of the database automatic maintenance table corresponds to a subtask one by one.

[0055] In some embodiments, there is only one time field in the data table. When a data maintenance task is executed, data in the data table older than KeepDays days is automatically deleted based on the time field TimeFieldName.

[0056] In other embodiments, there are one or more time fields in the data table, and the information contained in each subtask also includes a time field TimeFieldName, which is one of the time fields in the data table. When executing the data maintenance task, the data in the data table before KeepDays days is automatically deleted according to the time field TimeFieldName.

[0057] In some embodiments, the fields for establishing a database automatic maintenance table include at least: table name, which is a string type and is the primary key; number of retention days, which is a single-precision floating point type; minimum number of retained rows, which is an integer type; maximum number of deleted rows, which is an integer type; time field, which is a string type; expected next deletion time, which is a date and time type.

[0058] This database automatic maintenance table is generally built in the business database of the process control system. Of course, it can also be built in an independent database or even a text file. In one embodiment, taking the MySQL database as an example, a database automatic maintenance table is established, named cfg_db_maintenance, and the field details of the table are as follows:

[0059] Among them, one line contains the following:

[0060] TableName=mill_operation_log

[0061] KeepDays=30

[0062] KeepRowsMin=100

[0063] DeleteRowsMax=1000

[0064] TimeFieldName=OperationTime

[0065] NextDelTime=2023-08-2920:28:03

[0066] It indicates that the program needs to automatically maintain the mill_operation_log table and automatically delete data older than 30 days based on the OperationTime field. However, it must ensure that there are at least 100 rows of data remaining in the mill_operation_log table, and no more than 1,000 rows can be deleted at a time. The next time this maintenance task will be executed is 2023-08-29 20:28:03.

[0067] In some embodiments, step S1 further includes establishing a backup table, the fields of which include: a primary key field, which ensures its uniqueness; the time of the original table, which is of type date and time; the table name of the original table, which is of type string; a row of records in the original table, which is of type text; and the backup time, which is of type date and time.

[0068] The primary key field can use a long integer as an auto-incrementing ID, or a string generated from a GUID. Preferably, use a Bigint generated by the SnowFlake component as the primary key field to ensure its incrementing and uniqueness. Compared to a string GUID, the SnowFlake component is more efficient to read and write, and compared to an auto-incrementing ID, it does not expose the progress of business data.

[0069] It should be noted that the backup table should not be placed in the business database of the process control system, but should be stored in a third-party database. Taking a certain embodiment as an example, it is stored in the local SQLite database for easy backup and management. The name of this historical data backup table is HisDbBackup, and the field details of the table are as follows:

[0070] RowJson will undoubtedly take up a significant amount of space in the future. It is the true location for historical data backups in the process control system's business database. This table design is very versatile and can be used to back up various types of tables.

[0071] In some embodiments, T0 is equal to the total number of seconds of NextDelTime minus DtNow and rounded up.

[0072] In some embodiments, before executing the data maintenance task, it also includes: determining whether there is a data table corresponding to the table name included in the subtask in the database; if there is a data table corresponding to the table name included in the subtask in the database, then executing the data maintenance task on the data table; otherwise, updating the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, and entering step S2 after waiting T1 seconds, where T2 is greater than T1.

[0073] In some embodiments, step S3 specifically includes:

[0074] S31: Obtain the total number of rows r1 in the data table whose time field TimeFieldName is less than deadlineLineTime, where deadlineLineTime is equal to the current system time DtNow minus the number of retention days KeepDays. If r1 is greater than 0, proceed to step S32;

[0075] S32: Get the total number of rows r2 in the data table, and get the minimum value delTarget among r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is greater than 0, proceed to step S33;

[0076] S33: Obtaining delTime, including: searching the data table in ascending order according to the time field TimeFieldName, skipping the delTarget row, and obtaining the value of the time field TimeFieldName in the first row, namely delTime;

[0077] S34: Delete all rows in the data table where the time field TimeFieldName is smaller than delTime.

[0078] In some embodiments, after deleting all rows in the data table whose time field TimeFieldName is less than delTime in step S34, the actual number of deleted rows delCnt is obtained. If delCnt is greater than or equal to DeleteRowsMax, the NextDelTime of the subtask is updated to the current system time DtNow plus T1 seconds, and step S2 is entered after waiting for T1 seconds. If delCnt is less than DeleteRowsMax, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and step S2 is entered after waiting for T1 seconds, and T2 is greater than T1.

[0079] In some embodiments, after deleting all rows in the data table whose time field TimeFieldName is less than delTime in step S34, when it is found that the data maintenance task has not been completed (that is, when r1 and r2-KeepRowsMin are both greater than DeleteRowsMax), the NextDelTime of the subtask is updated to the current system time DtNow plus T1 seconds, and step S2 is entered after waiting for T1 seconds; when it is found that the data maintenance task has been completed (that is, when any one of r1 and r2-KeepRowsMin is less than or equal to DeleteRowsMax), the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and step S2 is entered after waiting for T1 seconds, and T2 is greater than T1.

[0080] In some embodiments, if r1 is equal to 0 in step S31, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and step S2 is entered after waiting for T1 seconds.

[0081] In some embodiments, if delTarget is less than or equal to 0 in step S32 , the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and the process proceeds to step S5 after waiting for T1 seconds.

[0082] In some embodiments, the information included in the subtask further includes: information indicating whether to perform a backup, and between step S33 and step S34, the following step is further included: determining whether a backup is required based on the information included in the subtask, and if so, executing the data backup step and then proceeding to step S34; if not, skipping the data backup step and proceeding to step S34;

[0083] The data backup step is performed, specifically including: obtaining all rows in the data table where the time field TimeFieldName is less than delTime, and performing a different database backup after generating backup data.

[0084] In some embodiments, performing the data backup step specifically includes: obtaining all rows in the data table where the time field TimeFieldName is less than delTime, generating multiple rows of backup table data, and storing the data in the backup table;

[0085] The fields of the backup table include at least: a unique ID, the time of the original table, the name of the original table, a row of records in the original table, and the current time of the backup.

[0086] In some embodiments, when establishing a database automatic maintenance task in step S1 by establishing a database automatic maintenance table, step S2 specifically includes: obtaining the row Row in the automatic maintenance table with the minimum expected next deletion time, which row at least includes the table name TableName, the number of retention days KeepDays, the minimum number of retained rows KeepRowsMin, the maximum number of deleted rows DeleteRowsMax, the time field TimeFieldName, and the expected next deletion time NextDelTime.

[0087] When NextDelTime is greater than the current system time DtNow, the process sleeps for T0 seconds and then re-executes step S2, where T0 represents the total number of seconds elapsed by (NextDelTime-DtNow) and is rounded up to an integer.

[0088] When NextDelTime is less than or equal to the current system time DtNow, step S3 is executed;

[0089] Step S3 specifically includes:

[0090] Step S31: If the table TableName does not exist in the database, update the NextDelTime of Row to the current system time DtNow plus T2 seconds, and go to step S4; otherwise, go to step S32;

[0091] Step S32: Get the total number of rows r1 in table TableName whose time field TimeFieldName is less than deadlineLineTime, where deadlineLineTime is equal to DtNow minus KeepDays. If r1 is 0, update Row's NextDelTime to DtNow plus T2 seconds and proceed to step S4. If r1 is greater than 0, proceed to step S33.

[0092] Step S33: Get the total number of rows in table TableName, r2. Then, get the minimum value delTarget among r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is less than or equal to 0, update the NextDelTime of the row to DtNow plus T2 seconds, and proceed to step S4. If delTarget is greater than 0, proceed to step S34.

[0093] Step S34, obtaining delTime, that is, searching the table TableName in ascending order according to TimeFieldName, skipping the delTarget row and selecting the first row whose TimeFieldName value is delTime.

[0094] Step S35, backup data: obtain all rows in table TableName whose TimeFieldName is less than delTime, and generate multiple rows of backup table data, including the unique ID, the time of the original table (the time corresponding to the TimeFieldName of the row to be backed up in the original table), the table name of the original table, and the string after the entire JSON serialization of the current row, back up the current time, and store these data in the backup table.

[0095] Step S36, deleting data: deleting all rows in the table TableName where TimeFieldName is less than delTime, and returning the actual number of deleted rows delCnt.

[0096] Step S37: If delCnt is greater than or equal to DeleteRowsMax, update the NextDelTime of Row to DtNow plus T1 seconds, and go to step S4; otherwise, update the NextDelTime of Row to DtNow plus T2 seconds, and go to step S4.

[0097] Step S4, sleep for T1 seconds, and then go to step S2.

[0098] T1 is a shorter time, such as 1 second, and T2 is a longer time, such as 300 seconds. Specifically, if maintenance is found to be complete, the system will sleep for a longer time, T2; if maintenance is not completed, the system will sleep for a shorter time, T1, and then continue with maintenance.

[0099] It should be noted that for some tables, if the historical data is not important, you can choose not to back up. In this case, you need to add an additional field in the automatic maintenance table to indicate whether to back up. If no backup is required, skip step S35.

[0100] Based on the same inventive concept, an embodiment of the present invention also discloses a database data maintenance device, including: a memory, a processor, and a database data maintenance program stored on the memory and runnable on the processor, wherein the database data maintenance program is configured to implement the database data maintenance method as described above.

[0101] In some embodiments, the processor may be a central processing unit (CPU), a controller, a microcontroller, a microprocessor, or other data processing chip. The processor is generally used to control the overall operation of the electronic device. In this embodiment, the processor is used to run program code stored in the memory or process data, such as program code of a data maintenance method for a database.

[0102] The memory includes at least one type of readable storage medium, including flash memory, hard disk, multimedia card, card-type memory (e.g., SD or DX memory), random access memory (RAM), static random access memory (SRAM), read-only memory (ROM), electrically erasable programmable read-only memory (EEPROM), programmable read-only memory (PROM), magnetic memory, magnetic disk, optical disk, etc. In some embodiments, the memory can be an internal storage unit of the electronic device, such as the hard disk or memory of the electronic device. In other embodiments, the memory can also be an external storage device of the electronic device, such as a plug-in hard disk equipped on the electronic device, a smart memory card (SMC), a secure digital (SD) card, a flash memory card, etc. Of course, the memory can also include both the internal storage unit of the electronic device and its external storage device. In this embodiment, the memory is generally used to store operating methods and various application software installed in the electronic device, such as program code of a database data maintenance method. In addition, the memory can also be used to temporarily store various types of data that have been output or are about to be output.

[0103] Based on the same inventive concept, an embodiment of the present invention further discloses a storage medium, which stores a database data maintenance program, and the database data maintenance program is used to enable a processor to implement the database data maintenance method described above when executed.

[0104] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Thus, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment, or an embodiment combining software and hardware. Furthermore, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to magnetic disk storage and optical storage, etc.) containing computer-usable program code.

[0105] The present invention is described with reference to the flowcharts and / or block diagrams of the methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or box in the flowchart and / or block diagram, as well as the combination of processes and / or boxes in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing device to produce a machine, so that the instructions executed by the processor of the computer or other programmable data processing device produce a device for implementing the functions specified in one or more processes in the flowchart and / or one or more boxes in the block diagram.

[0106] These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce a product including an instruction device that implements the functions specified in one or more processes in the flowchart and / or one or more boxes in the block diagram.

[0107] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, so that the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one or more processes in the flowchart and / or one or more boxes in the block diagram.

Claims

1. A method for maintaining data in a database, characterized in that: The steps include: S1: Create a database automatic maintenance task, which includes at least one subtask. Each subtask contains at least the following information: table name, number of retention days KeepDays, minimum number of retained rows KeepRowsMin, maximum number of rows to be deleted at a time DeleteRowsMax, and expected next deletion time NextDelTime; S2: Obtain information contained in the subtask with the smallest expected next deletion time in the database automatic maintenance task. When the expected next deletion time NextDelTime contained in the subtask is greater than the current system time DtNow, re-execute step S2; when the expected next deletion time NextDelTime contained in the subtask is less than or equal to the current system time DtNow, execute step S3; S3: Perform data maintenance tasks on the corresponding data table in the database according to the information contained in the subtask. When performing the data maintenance task, automatically delete the data in the data table before KeepDays days according to the time field to ensure that at least KeepRowsMin rows of data remain in the data table, and no more than DeleteRowsMax rows of data can be deleted at a single time.

2. The method for maintaining a database according to claim 1, wherein: Establishing a database automatic maintenance task, specifically including: establishing a database automatic maintenance table, the fields of which at least include: table name, number of retention days KeepDays, minimum number of retained rows KeepRowsMin, maximum number of rows deleted at a time DeleteRowsMax, and expected next deletion time NextDelTime; Each row of the database automatic maintenance table corresponds to a subtask.

3. The method for maintaining a database according to claim 1, wherein: In step S2, when the expected next deletion time NextDelTime included in the subtask is greater than the current system time DtNow, wait for time T0 or T1, and then re-execute step S2; T0 is equal to the total number of seconds of NextDelTime minus DtNow and rounded up, and T1 is the set time; After each data maintenance task is executed in S3, the expected next deletion time NextDelTime of the subtask is updated, the waiting time T1 is waited, and then step S2 is entered.

4. The method for maintaining a database according to claim 1, wherein: Before executing the data maintenance task, it also includes: determining whether there is a data table corresponding to the table name contained in the subtask in the database. If there is a corresponding data table in the database, the data maintenance task is executed on the data table; otherwise, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and then wait for T1 seconds to enter step S2, where T2 is greater than T1.

5. The method for maintaining a database according to claim 1, wherein: There is one time field in the data table. When executing the data maintenance task, the data in the data table before KeepDays days is automatically deleted according to the time field TimeFieldName. or, There are one or more time fields in the data table. The information contained in each subtask also includes a time field TimeFieldName. The time field TimeFieldName is one of the time fields in the data table. When executing the data maintenance task, the data in the data table before KeepDays days is automatically deleted according to the time field TimeFieldName.

6. The method for maintaining a database according to claim 5, wherein: Step S3 specifically includes: S31: Obtain the total number of rows r1 in the data table whose time field TimeFieldName is less than deadlineLineTime, where deadlineLineTime is equal to the current system time DtNow minus the number of retention days KeepDays. If r1 is greater than 0, proceed to step S32; S32: Get the total number of rows r2 in the data table, and get the minimum value delTarget among the three data r1, r2-KeepRowsMin, and DeleteRowsMax. If delTarget is greater than 0, go to step S33; S33: obtaining delTime, including: searching the data table in ascending order according to the time field TimeFieldName, skipping the delTarget row, and obtaining the value of the time field TimeFieldName in the first row, namely delTime; S34: Delete all rows in the data table whose time field TimeFieldName is less than delTime, and obtain the actual number of deleted rows delCnt. If delCnt is greater than or equal to DeleteRowsMax, update the NextDelTime of the subtask to the current system time DtNow plus T1 seconds, wait for T1 seconds and enter step S2. If delCnt is less than DeleteRowsMax, update the expected next deletion time NextDelTime of the subtask to the current system time DtNow plus T2 seconds, wait for T1 seconds and enter step S2, where T2 is greater than T1.

7. The method for maintaining a database according to claim 6, wherein: In step S31, if r1 is equal to 0, then the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and then enter step S2 after waiting for T1 seconds; and / or, If delTarget is less than or equal to 0 in step S32, the expected next deletion time NextDelTime of the subtask is updated to the current system time DtNow plus T2 seconds, and then the process goes to step S5 after waiting for T1 seconds.

8. The method for maintaining a database according to claim 6, wherein: The information included in the subtask also includes: information for indicating whether to back up, and between step S33 and step S34, the following step is also included: judging whether to back up according to the information included in the subtask, if so, executing the data backup step and then entering step S34, if not, skipping the data backup step and entering step S34; The data backup step is performed, specifically including: obtaining all rows in the data table where the time field TimeFieldName is less than delTime, and performing a different database backup after generating backup data.

9. The method for maintaining a database according to claim 8, wherein: Executing a data backup step specifically includes: obtaining all rows in the data table where the time field TimeFieldName is less than delTime, generating multiple rows of backup table data and storing them in the backup table; The fields of the backup table include at least: a unique ID, the time of the original table, the name of the original table, a row of records in the original table, and the current time of the backup.

10. A data maintenance device for a database, characterized in that: include: A memory, a processor, and a database data maintenance program stored in the memory and executable on the processor, wherein the database data maintenance program is configured to implement the database data maintenance method according to any one of claims 1 to 9.

Images