Data table switching method, device, equipment and storage medium

Abstract

The present invention relates to the field of artificial intelligence, and discloses a data table switching method, apparatus, device, and storage medium. The method includes: receiving a data table reconstruction request initiated by a user, and determining a first data table corresponding to the data table reconstruction request; generating a second data table according to the table format of the first data table; when obtaining a first SQL statement to be executed, determining whether the second data table contains data in the first data table; if so, generating and executing a second SQL statement with the target data table being the second data table according to the first SQL statement, so as to switch the first data table and the second data table; if not, performing data double-writing on the first data table and the second data table according to the first SQL statement. In addition, the present invention also relates to blockchain technology, and the first data table and the second data table can be stored in the blockchain. The present invention can improve the fidelity of data during the switching process after data tables are synchronized.

Description

Technical Field

[0001] The present invention relates to the field of artificial intelligence, and in particular to a data table switching method, device, equipment and storage medium. Background Art

[0002] A data table is the unit for storing data in a database. Over time, the amount of data in a data table increases, even reaching millions or even tens of millions. When a data table is too large, querying and modifying data in the table takes a considerable amount of time, so partitioning and sharding are often used to reduce the load on the table. Traditional partitioning or sharding methods primarily create a table isomorphic to the target data table. Then, using a script or scheduled task, the data from the target data table is added to the isomorphic table. The target and isomorphic tables are then renamed, with the isomorphic table being renamed to the target table, thereby switching the two tables. Finally, any incremental data in the target data table that was not synchronized during the switch is synchronized to the isomorphic table.

[0003] However, when using traditional data table switching methods, due to the time difference between synchronization and switching, data may be missed in the homogeneous table after switching, resulting in data inconsistencies between the two data tables. In addition, when the switch fails, a rollback is performed and the switch is executed again. The incremental data in the homogeneous table during the switch process will be synchronized to the target data table, and the target data table will add data that should not have been added, resulting in data anomalies. Summary of the Invention

[0004] The main purpose of the present invention is to solve the technical problem of poor data consistency in the process of data partitioning or table sharding.

[0005] A first aspect of the present invention provides a data table switching method, which is applied to hot switching of a single data source, comprising:

[0006] receiving a data table reconstruction request initiated by a user, and determining a first data table corresponding to the data table reconstruction request;

[0007] Generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules;

[0008] When obtaining a first SQL statement to be executed, determining whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table;

[0009] If the second data table contains the data in the first data table, generating a second SQL statement with the target data table being the second data table according to the first SQL statement and executing the statement to switch between the first data table and the second data table;

[0010] If the second data table does not contain the data in the first data table, data is double-written in the first data table and the second data table according to the first SQL statement.

[0011] Optionally, in a first implementation of the first aspect of the present invention, if the second data table does not contain data in the first data table, then performing data dual writing on the first data table and the second data table according to the first SQL statement includes:

[0012] If the second data table does not contain the data in the first data table, determining the statement type corresponding to the first SQL statement according to the preset statement type library, where the statement type includes a query type and a data table modification type;

[0013] If the statement type corresponding to the first SQL statement is a query type, executing the first SQL statement;

[0014] If the statement type corresponding to the first SQL statement is a data table modification type, a third SQL statement whose target data table is the second data table is generated based on the first SQL statement, and the first SQL statement and the third SQL statement are executed to perform double writing of data in the first data table and the second data table.

[0015] Optionally, in a second implementation of the first aspect of the present invention, after generating, based on the first SQL statement, a third SQL statement whose target data table is the second data table if the statement type corresponding to the first SQL statement is a data table modification type, and executing the first SQL statement and the third SQL statement to perform data double writing on the first data table and the second data table, the method further includes:

[0016] Recording the first number of affected records corresponding to the first SQL statement and the second number of affected records corresponding to the third SQL statement;

[0017] Determining whether the first impact record number and the second impact record number are equal;

[0018] If the first number of affected records is not equal to the second number of affected records, executing a preset rollback statement to roll back the second data table to a state before executing the third SQL statement;

[0019] The data in the second data table is corrected according to preset correction rules.

[0020] Optionally, in a third implementation of the first aspect of the present invention, the data table modification type includes a data addition and deletion type and a data modification type, and correcting the data in the second data table according to a preset correction rule includes:

[0021] If the statement type corresponding to the first SQL statement is a data addition or deletion type, then executing the third SQL statement and recording the number of re-executions until a preset stop condition is reached and then stopping execution of the third SQL statement, wherein the stop condition includes that the number of re-executions is equal to the retry threshold or the second number of affected records is equal to the first number of affected records;

[0022] If the number of re-executions is equal to the preset retry threshold, the statement type and statement parameters of the third SQL statement are recorded.

[0023] Optionally, in a fourth implementation of the first aspect of the present invention, correcting the data in the second data table according to a preset correction rule includes:

[0024] If the type corresponding to the first SQL statement is a data modification type, extracting the target data condition in the first SQL statement;

[0025] extracting the first data set and the second data set corresponding to the first data table and the second data table respectively according to the target data condition, and determining the difference data between the first data set and the second data set;

[0026] Based on the difference data and preset correction rules, the data in the second data table is corrected.

[0027] Optionally, in a fifth implementation of the first aspect of the present invention, extracting, according to the target data condition, the first data set and the second data set corresponding to the first data table and the second data table, respectively, and determining difference data between the first data set and the second data set includes:

[0028] extracting the first data set and the second data set from the first data table and the second data table respectively according to the target data condition;

[0029] comparing the first data set and the second data set;

[0030] taking data that exists in the first data set but does not exist in the second data set as first difference data;

[0031] The data that exists in the second data set but does not exist in the first data set is used as the second difference data.

[0032] Optionally, in a sixth implementation of the first aspect of the present invention, the correcting the data in the second data table based on the difference data and a preset correction rule includes:

[0033] performing a comparison between the first impact record number, the second impact record number, and zero;

[0034] If the second impact record number is greater than or equal to zero and less than the first impact record number, writing the first difference data into the second data table;

[0035] If the second impact record number is greater than the first impact record number, the data corresponding to the second difference data in the second data table is deleted.

[0036] A second aspect of the present invention provides a data table switching method and apparatus, which is applied to hot switching of a single data source, comprising:

[0037] a receiving module, configured to receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0038] A generating module, configured to generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules;

[0039] A judgment module, configured to, when obtaining a first SQL statement to be executed, judge whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table;

[0040] a switching module configured to generate, based on the first SQL statement, a second SQL statement whose target data table is the second data table and execute the statement, if the second data table contains data in the first data table, so as to switch between the first data table and the second data table;

[0041] A dual writing module is used to perform dual writing of data on the first data table and the second data table according to the first SQL statement if the second data table does not contain the data in the first data table.

[0042] Optionally, in a first implementation of the second aspect of the present invention, the dual-write module includes:

[0043] a judgment unit, configured to determine, if the second data table does not contain the data in the first data table, a statement type corresponding to the first SQL statement based on a preset statement type library, wherein the statement type includes a query type and a data table modification type;

[0044] a first execution unit, configured to execute the first SQL statement if the statement type corresponding to the first SQL statement is a query type;

[0045] The second execution unit is configured to generate, based on the first SQL statement, a third SQL statement whose target data table is the second data table, if the statement type corresponding to the first SQL statement is a data table modification type, and execute the first SQL statement and the third SQL statement to perform double writing of data in the first data table and the second data table.

[0046] Optionally, in a second implementation of the second aspect of the present invention, a correction unit is further connected to the second execution unit, and the correction unit includes:

[0047] a recording subunit, configured to record a first number of impacted records corresponding to the first SQL statement and a second number of impacted records corresponding to the third SQL statement;

[0048] a judging subunit, configured to judge whether the first number of impact records is equal to the second number of impact records;

[0049] a rollback subunit, configured to execute a preset rollback statement if the first number of affected records is not equal to the second number of affected records, so as to roll back the second data table to a state before executing the third SQL statement;

[0050] The first correction subunit is configured to correct the data in the second data table according to a preset correction rule.

[0051] Optionally, in a third implementation of the second aspect of the present invention, the correction unit includes a recording subunit, a judgment subunit, a rollback subunit, a second correction subunit, and a third correction subunit, and the second correction subunit is specifically configured to:

[0052] If the statement type corresponding to the first SQL statement is a data addition or deletion type, then executing the third SQL statement and recording the number of re-executions until a preset stop condition is reached and the execution of the third SQL statement is stopped;

[0053] If the number of re-executions is equal to the preset retry threshold, recording the statement type and statement parameters of the third SQL statement;

[0054] The stopping condition includes that the number of re-executions is equal to the retry threshold or the number of the second impact records is equal to the number of the first impact records.

[0055] Optionally, in a fourth implementation manner of the second aspect of the present invention, the third syndrome unit is further specifically configured to:

[0056] If the type corresponding to the first SQL statement is a data modification type, extracting the target data condition in the first SQL statement;

[0057] extracting the first data set and the second data set corresponding to the first data table and the second data table respectively according to the target data condition, and determining the difference data between the first data set and the second data set;

[0058] Based on the difference data and preset correction rules, the data in the second data table is corrected.

[0059] Optionally, in a fifth implementation manner of the second aspect of the present invention, the third syndrome unit is further configured to:

[0060] extracting the first data set and the second data set from the first data table and the second data table respectively according to the target data condition;

[0061] comparing the first data set and the second data set;

[0062] taking data that exists in the first data set but does not exist in the second data set as first difference data;

[0063] The data that exists in the second data set but does not exist in the first data set is used as the second difference data.

[0064] Optionally, in a sixth implementation manner of the second aspect of the present invention, the third syndrome unit is further configured to:

[0065] performing a comparison between the first impact record number, the second impact record number, and zero;

[0066] If the second impact record number is greater than or equal to zero and less than the first impact record number, writing the first difference data into the second data table;

[0067] If the second impact record number is greater than the first impact record number, the data corresponding to the second difference data in the second data table is deleted.

[0068] The third aspect of the present invention provides a data table switching method device, comprising: a memory and at least one processor, wherein instructions are stored in the memory, and the memory and the at least one processor are interconnected via a line; the at least one processor calls the instructions in the memory so that the data table switching method device executes the above-mentioned data table switching method.

[0069] A fourth aspect of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores instructions, which, when executed on a computer, enable the computer to execute the above-mentioned data table switching method.

[0070] In the technical solution provided by the present invention, after obtaining the first data table to be processed, an isomorphic table of the first data table, i.e., the second data table, is created. The second data table includes partition writing rules or table sub-writing rules. When the target data table is received as the first SQL statement, it is determined whether the second data table contains the data in the first data table. If so, the target data table in the second SQL statement is modified to be the second data table to achieve hot switching between the two data tables, thereby avoiding data inconsistency caused by the switching of the two data tables. Since the second data table contains partitioning or table sub-rules, the second data table is clearer and simpler than the first data table and is easy to operate. If the second data table does not contain the data in the first data table, the two tables are synchronized by double writing. In addition, the present invention also divides SQL statements into query type and data table modification type, and only the data table modification type is double written to reduce the burden on the server. In addition, the present invention also provides a method for determining whether the data in two data tables are consistent by comparing the number of records affected by two SQL statements, and performing corrections, thereby improving the consistency of the data between the two data tables. BRIEF DESCRIPTION OF THE DRAWINGS

[0071] Figure 1 Schematic diagram of a first embodiment of a data table switching method according to an embodiment of the present invention;

[0072] Figure 2 Schematic diagram of a second embodiment of a data table switching method according to an embodiment of the present invention;

[0073] Figure 3 Schematic diagram of a third embodiment of a data table switching method according to an embodiment of the present invention;

[0074] Figure 4 Schematic diagram of a fourth embodiment of a data table switching method according to an embodiment of the present invention;

[0075] Figure 5 Schematic diagram of a fifth embodiment of a data table switching method according to an embodiment of the present invention;

[0076] Figure 6A schematic diagram of an embodiment of a data table switching method and apparatus according to an embodiment of the present invention;

[0077] Figure 7 A schematic diagram of another embodiment of a data table switching method and apparatus according to an embodiment of the present invention;

[0078] Figure 8 Schematic diagram of an embodiment of a data table switching method and device in an embodiment of the present invention. DETAILED DESCRIPTION

[0079] Embodiments of the present invention provide a data table switching method, apparatus, device, and storage medium. In the technical solution provided by the present invention, a second data table is an isomorphic table of the first data table, and the second data table includes partition writing rules or table writing rules. When a SQL statement is received as the target data table, a determination is made as to whether the second data table contains data from the first data table. If so, the target data table in the second SQL statement is modified to be the second data table, thereby achieving hot switching between the two data tables and avoiding data inconsistencies caused by the switching between the two data tables.

[0080] The terms "first," "second," "third," "fourth," and the like (if any) in the description and claims of the present invention and in the accompanying drawings are used to distinguish similar objects and are not necessarily used to describe a particular order or precedence. It should be understood that the terms used in this manner are interchangeable where appropriate so that the embodiments described herein can be implemented in an order other than that illustrated or described herein. In addition, the terms "including" or "having" and any variations thereof are intended to cover non-exclusive inclusions, for example, a process, method, system, product, or apparatus that includes a series of steps or units is not necessarily limited to those steps or units explicitly listed, but may include other steps or units that are not explicitly listed or that are inherent to these processes, methods, products, or apparatus.

[0081] For ease of understanding, the specific process of the embodiment of the present invention is described below. Figure 1 , an embodiment of the data table switching method in an embodiment of the present invention includes:

[0082] 101. Receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0083] It is understandable that the execution subject of the present invention may be a data table switching method device, or a terminal or a server, which is not limited here. The embodiment of the present invention is described by taking a server as the execution subject as an example.

[0084] In this embodiment, the data tables are stored in a MySQL database. Most database operations are performed using SQL statements. When a user initiates a data table reconstruction request, the data table corresponding to the request is determined to be the first data table. In this embodiment, the name of the first data table is table_A. The first data table contains columns such as user ID, registration date, user name, and address. The user ID is the primary key of the data table.

[0085] 102. Generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules;

[0086] Use the SQL statement "CREATE TABLE table_B LIKE table_A" to create a blank table table_B with the same table format as table_A.

[0087] Partitioning divides a table's data into N blocks. Logically, it's a single table, but underlying it consists of N physical blocks. Table sharding breaks a table into N physical tables with independent storage space based on specific rules. The partitioning or sharding rules are pre-written in table_B.

[0088] Common partitioning methods include partitioning by time, such as by month or year. There is also partitioning based on specific numeric or string rules. In this embodiment, in the second data table, two partition functions are included, the first one is partitionpart_01 values less than(to_date('2006-01-01','yyyy-mm-dd')), and the second one is partition part_02 values less than(to_date('2012-01-01','yyyy-mm-dd')). Therefore, when data needs to be written to the second data table, the data is written to the corresponding area according to the time of the data. If the date of the data to be written is 2008-01-01, this data will be written to the second partition. Partitioning methods also include list partitioning and the like. Hash partitioning is to disperse the data containing the field into pre-set partitions based on the hash value of the field, so that the amount of data in each partition is the same.

[0089] 103. When obtaining a first SQL statement to be executed, determine whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table;

[0090] In this embodiment, after obtaining the first SQL statement with table_A as the target data table, it is determined whether table_B contains the data in table_A, so as to determine whether table_B and table_A need to be hot-swapped. The preferred judgment method is to use a binary search method when the first SQL statement is not obtained, and search each data in table_A in table_B to determine whether there is consistent data, and generate a comparison result. If each data has the same data in table_B, it means that the two data tables are consistent, and the comparison result is "table_B contains the data in table_A". When the first SQL statement to be executed is obtained, the server obtains the comparison result obtained before, and determines whether the second data table contains the data in the first data table based on the comparison result.

[0091] 104. If the second data table contains data in the first data table, generate a second SQL statement with the second data table as the target data table according to the first SQL statement and execute the statement to switch between the first data table and the second data table;

[0092] If the second data table contains the data of the first data table, the generated data and the executed actions are consistent, but the target data table is the second SQL statement of the second data table and is executed.

[0093] For example, if the first SQL statement is "UPDATE table_A SET FirstName='Fred'WHERE LastName='Wilson'", the "FirstName" in the data where "LastName" is "Wilson" in table_A is changed to "Fred", then the first data table name in the first SQL statement is extracted, and the second data table name is used to replace the first data table name, and the second SQL statement is "UPDATE table_B SET FirstName='Fred'WHERE LastName='Wilson'".

[0094] Therefore, the operation on the first data table is changed to the operation on the second data table, thereby achieving switching.

[0095] 105. If the second data table does not contain the data in the first data table, perform data double writing on the first data table and the second data table according to the first SQL statement.

[0096] If not, it means that there are still differences between the data in table_B and table_A. Therefore, the data in the first SQL statement needs to be double-written to eventually make the data in the first data table and the second data table consistent.

[0097] This solution provides a seamless way to switch between pre- and post-partition or table sharding data tables, avoiding service interruptions caused by changing partitions or tables, reducing development costs and improving processing efficiency. Furthermore, if a switch fails, it can be quickly rolled back, preventing data anomalies caused by the switch failure.

[0098] See also Figure 2 The second embodiment of the data table switching method in the embodiment of the present invention includes:

[0099] 201. Receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0100] 202. Generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules;

[0101] 203. When obtaining the first SQL statement to be executed, determine whether the second data table contains the data in the first data table;

[0102] 204. If the second data table does not contain the data in the first data table, determine the statement type corresponding to the first SQL statement according to the preset statement type library;

[0103] SQL statements encompass many types. This solution categorizes these statements into table modification and query types, depending on whether they modify data in the target table. For example, clauses such as add, delete, and update, used to modify or add or subtract data in a table, are table modification SQL statements. Keywords in SQL statements are pre-assigned to different statement types to create a pre-built statement type library. Therefore, after obtaining the first SQL statement, the statement type can be determined based on the pre-built statement type library.

[0104] 205. If the statement type corresponding to the first SQL statement is a query type, execute the first SQL statement;

[0105] Since a query-type SQL statement will not modify data in the first data table, when the first SQL statement is a query-type, the first SQL statement is directly executed.

[0106] 206. If the statement type corresponding to the first SQL statement is a data table modification type, generate a third SQL statement whose target data table is the second data table based on the first SQL statement, and execute the first SQL statement and the third SQL statement to perform double writing of data in the first data table and the second data table.

[0107] In another embodiment, the first SQL statement is to insert certain data into the first data table.

[0108] MyBatis allows for interception of calls at certain points during the execution of mapped statements. The intercepted statements are pre-set to SQL statements that modify a table. Based on the first SQL statement, a third SQL statement with the same action and data insertion is executed, creating a second table as the target table. The first and second SQL statements are executed simultaneously, inserting data into both tables simultaneously, achieving double data writes.

[0109] This solution allows you to synchronize data additions, deletions, or modifications in the first data table with the second data table, enabling simultaneous writes between the first and second tables. Query SQL statements are executed only once, reducing server load through this filtering approach.

[0110] See also Figure 3 A third embodiment of the data table switching method in the embodiment of the present invention includes:

[0111] 301. Receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0112] 302. Generate a second data table according to the table format of the first data table;

[0113] 303. When obtaining the first SQL statement to be executed, determine whether the second data table contains the data in the first data table;

[0114] 304. If the second data table does not contain the data in the first data table, determine the statement type corresponding to the first SQL statement according to the preset statement type library;

[0115] 305. If the statement type corresponding to the first SQL statement is a data table modification type, generate a third SQL statement based on the first SQL statement, with the target data table being the second data table, and execute the first and third SQL statements to perform data double writing on the first and second data tables.

[0116] 306. Record the first number of affected records corresponding to the first SQL statement and the second number of affected records corresponding to the third SQL statement;

[0117] After executing the first and third SQL statements, the number of records affected by the first and third SQL statements is obtained through the mySQL_affected_rows() function, which are recorded as the first affected record number and the second affected record number, respectively.

[0118] 307. Determine whether the first impact record number and the second impact record number are equal;

[0119] 308. If the first number of affected records is not equal to the second number of affected records, execute a preset rollback statement to roll back the second data table to a state before executing the third SQL statement;

[0120] If the first and third SQL statements affect different numbers of records, it means that the data affected by the first and third SQL statements are different. A rollback statement is pre-set. If the two statements are different, the second data table is rolled back to the state before the third SQL statement was executed.

[0121] 309. Correct the data in the second data table according to preset correction rules.

[0122] To ensure the consistency of data in the first and second data tables, the data in the second data table is corrected according to preset correction rules, such as inserting data that exists in the first data table but does not exist in the second data table into the second data table.

[0123] This embodiment provides a method for determining whether data in a first data table and a second data table are consistent by comparing the number of records affected, so as to ensure that the data added, subtracted, or modified in the first data table and the second data table are consistent after executing the first and second SQL statements.

[0124] See also Figure 4 The fifth embodiment of the data table switching method in the embodiment of the present invention includes:

[0125] 401. Receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0126] 402. Generate a second data table according to the table format of the first data table;

[0127] 403. When obtaining the first SQL statement to be executed, determine whether the second data table contains data in the first data table;

[0128] 404. If the second data table does not contain the data in the first data table, determine the statement type corresponding to the first SQL statement according to the preset statement type library;

[0129] Table modification types are categorized as data addition and deletion and data modification, depending on whether the SQL statement directly inserts or deletes data or modifies data. For example, SQL statements with the insert or delete action are data addition and deletion, while statements with the update action are data modification.

[0130] 405. If the statement type corresponding to the first SQL statement is a data table modification type, generate a third SQL statement whose target data table is the second data table based on the first SQL statement, and execute the first SQL statement and the third SQL statement.

[0131] 406. Record the first number of affected records corresponding to the first SQL statement and the second number of affected records corresponding to the third SQL statement;

[0132] 407. Determine whether the first impact record number and the second impact record number are equal;

[0133] 408. If the first number of affected records is not equal to the second number of affected records, execute a preset rollback statement to roll back the second data table to a state before executing the third SQL statement;

[0134] 409. If the statement type corresponding to the first SQL statement is a data addition or deletion type, execute the third SQL statement and record the number of re-executions until a preset stop condition is reached and stop executing the third SQL statement.

[0135] In this embodiment, the first SQL statement is a data increment / decrement statement. If the number of affected records for the first and third SQL statements is unequal, the third SQL statement is re-executed and the number of re-executions is recorded. This execution is repeated, and the second number of affected records is recorded and compared with the first number of affected records, until a preset stop condition is met, and execution of the third SQL statement is terminated. One of the preset stop conditions is that the number of affected records for the two SQL statements is the same. The second condition is that the recorded number of re-executions exceeds a preset retry threshold.

[0136] 410. If the number of re-executions is equal to a preset retry threshold, record the statement type and statement parameters of the third SQL statement.

[0137] If the number of re-executions is greater than the preset retry preset, the statement type and statement parameters of the third SQL statement are recorded to prompt the maintainer to find the cause of the error or manually enter the data.

[0138] In this embodiment, when the first SQL statement is of the data addition and deletion type, correction is achieved by re-executing the third SQL statement.

[0139] See also Figure 5 , a fifth embodiment of the data table switching method in the embodiment of the present invention includes:

[0140] 501. Receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0141] 502. Generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules;

[0142] 503. When obtaining a first SQL statement to be executed, determine whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table;

[0143] 504. If the second data table does not contain the data in the first data table, determine the statement type corresponding to the first SQL statement according to the preset statement type library;

[0144] 505. If the statement type corresponding to the first SQL statement is a data table modification type, generate a third SQL statement whose target data table is the second data table based on the first SQL statement, and execute the first SQL statement and the third SQL statement;

[0145] 506. Record the first number of affected records corresponding to the first SQL statement and the second number of affected records corresponding to the third SQL statement;

[0146] 507. Determine whether the first impact record number and the second impact record number are equal;

[0147] 508. If the first number of affected records is not equal to the second number of affected records, execute a preset rollback statement to roll back the second data table to a state before executing the third SQL statement;

[0148] 509. If the type corresponding to the first SQL statement is a data modification type, extract the target data condition in the first SQL statement;

[0149] First, parse the first SQL statement to determine the target data condition in the first SQL statement. If the SQL statement contains a where clause, the target data condition is the content of the where clause; if the SQL statement does not contain a where clause, the target data condition defaults to the entire data table.

[0150] 510. Extract the first data set and the second data set from the first data table and the second data table respectively according to the target data condition, and determine difference data between the first data set and the second data set;

[0151] Furthermore, step 510 specifically includes:

[0152] extracting the first data set and the second data set from the first data table and the second data table respectively according to the target data condition;

[0153] The data sets that meet the target data conditions are determined respectively through the where statement. The data set in the first data table is the first data set, and the data set in the second data table is the second data set.

[0154] comparing the first data set and the second data set;

[0155] Since the first and third SQL statements affect different numbers of records, the number of data in the first and second data sets is also different. First, compare the data in the two data sets.

[0156] In this embodiment, the primary key "not in" is used as the search condition to search for the difference between the first and second data sets. Alternatively, a left join can be used to join two data tables to find the difference between the two data tables.

[0157] taking data that exists in the first data set but does not exist in the second data set as first difference data;

[0158] The data that exists in the second data set but does not exist in the first data set is used as the second difference data.

[0159] 511. Based on the difference data and preset correction rules, correct the data in the second data table.

[0160] Furthermore, step 511 specifically includes:

[0161] performing a comparison between the first impact record number, the second impact record number, and zero;

[0162] If the second impact record number is greater than or equal to zero and less than the first impact record number, writing the first difference data into the second data table;

[0163] If the second number of affected records is greater than or equal to zero and less than the first number of affected records, it means that there is data in the first data table that does not exist in the second data table. Therefore, the first difference data is written into the second data table.

[0164] If the second impact record number is greater than the first impact record number, the data corresponding to the second difference data in the second data table is deleted.

[0165] If the second impact record number is greater than the first impact record number, it indicates that there is abnormal data in the second data table, namely, second difference data, which does not exist in the first data table. Therefore, the second difference data is deleted from the second data table.

[0166] It should be emphasized that in order to further ensure the privacy and security of the above-mentioned first data table and / or second data table, the above-mentioned first data table and / or second data table can also be stored in a node of a blockchain.

[0167] Blockchain, as used in this article, refers to a novel application model for computer technologies such as distributed data storage, peer-to-peer transmission, consensus mechanisms, and encryption algorithms. Blockchain is essentially a decentralized database, a series of data blocks generated using cryptographic methods. Each block contains information about a batch of online transactions, used to verify the validity of this information (to prevent counterfeiting) and generate the next block. Blockchain can include the underlying blockchain platform, the platform product service layer, and the application service layer.

[0168] This embodiment specifically describes the correction method used when the SQL statement corresponding to the statement type is data modification. By applying the merge rule, the difference data between the first and second data sets is inserted or deleted, thereby synchronizing the two data sets and improving the accuracy of the data between the two data tables.

[0169] The above describes the data table switching method in the embodiment of the present invention. The following describes the data table switching method device in the embodiment of the present invention. Figure 6 In one embodiment of the data table switching method of the present invention, the device includes:

[0170] a receiving module, configured to receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request;

[0171] A generating module 601 is configured to generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules;

[0172] A judgment module 602 is configured to, when obtaining a first SQL statement to be executed, judge whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table;

[0173] A switching module 603 is configured to generate, based on the first SQL statement, a second SQL statement whose target data table is the second data table, and execute the statement to switch between the first data table and the second data table if the second data table contains data in the first data table;

[0174] The double writing module 604 is configured to perform double writing of data on the first data table and the second data table according to the first SQL statement if the second data table does not contain the data in the first data table.

[0175] This solution provides a seamless way to switch between pre- and post-partition or table-sharding data tables, avoiding service interruptions caused by changing partitions or tables, reducing development costs and improving processing efficiency. Furthermore, if a switch fails, a quick rollback is possible, preventing data anomalies caused by the switch failure. Because the second data table contains the partition or table-sharding rules, it is clearer and simpler than the first data table, making it easier to operate.

[0176] See also Figure 7 Another embodiment of the data table switching method and apparatus according to the embodiment of the present invention includes:

[0177] The receiving module 701 is configured to receive a data table reconstruction request initiated by a user and determine a first data table corresponding to the data table reconstruction request;

[0178] A generating module 702 is configured to generate a second data table according to the table format of the first data table, wherein the second data table includes a preset partition writing rule or a sub-table writing rule;

[0179] A judgment module 703 is configured to, when obtaining a first SQL statement to be executed, judge whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table;

[0180] A switching module 704 is configured to generate, based on the first SQL statement, a second SQL statement whose target data table is the second data table, and execute the statement to switch between the first data table and the second data table if the second data table contains data in the first data table;

[0181] The dual writing module 705 is configured to perform dual writing of data on the first data table and the second data table according to the first SQL statement if the second data table does not contain the data in the first data table.

[0182] The dual-write module 705 includes:

[0183] The judgment unit 7051 is configured to determine, if the second data table does not contain the data in the first data table, a statement type corresponding to the first SQL statement based on a preset statement type library, where the statement type includes a query type and a data table modification type;

[0184] A first execution unit 7052 is configured to execute the first SQL statement if the statement type corresponding to the first SQL statement is a query type;

[0185] The second execution unit 7053 is used to generate a third SQL statement whose target data table is the second data table based on the first SQL statement if the statement type corresponding to the first SQL statement is a data table modification type, and execute the first SQL statement and the third SQL statement to perform double writing of data in the first data table and the second data table.

[0186] The second execution unit is further connected to a correction unit 7054, and the correction unit 7054 includes:

[0187] a recording subunit, configured to record a first number of impacted records corresponding to the first SQL statement and a second number of impacted records corresponding to the third SQL statement;

[0188] a judging subunit, configured to judge whether the first number of impact records is equal to the second number of impact records;

[0189] a rollback subunit, configured to execute a preset rollback statement if the first number of affected records is not equal to the second number of affected records, so as to roll back the second data table to a state before executing the third SQL statement;

[0190] The first correction subunit is configured to correct the data in the second data table according to a preset correction rule.

[0191] Optionally, the correction unit includes a recording subunit, a judgment subunit, a rollback subunit, a second correction subunit, and a third correction subunit, wherein the second correction subunit is specifically configured to:

[0192] If the statement type corresponding to the first SQL statement is a data addition or deletion type, then executing the third SQL statement and recording the number of re-executions until a preset stop condition is reached and the execution of the third SQL statement is stopped;

[0193] If the number of re-executions is equal to the preset retry threshold, recording the statement type and statement parameters of the third SQL statement;

[0194] The stopping condition includes that the number of re-executions is equal to a preset retry threshold or the number of the second impact records is equal to the number of the first impact records.

[0195] Optionally, the third syndrome subunit is specifically configured to:

[0196] If the type corresponding to the first SQL statement is a data modification type, extracting the target data condition in the first SQL statement;

[0197] extracting the first data set and the second data set corresponding to the first data table and the second data table respectively according to the target data condition, and determining the difference data between the first data set and the second data set;

[0198] Based on the difference data and preset correction rules, the data in the second data table is corrected.

[0199] Optionally, the third syndrome unit is further configured to:

[0200] If the type corresponding to the first SQL statement is a data modification type, extracting the target data condition in the first SQL statement;

[0201] extracting the first data set and the second data set from the first data table and the second data table respectively according to the target data condition;

[0202] comparing the first data set and the second data set;

[0203] taking data that exists in the first data set but does not exist in the second data set as first difference data;

[0204] taking data present in the second data set and not present in the first data set as second difference data;

[0205] Based on the difference data and preset correction rules, the data in the second data table is corrected.

[0206] Optionally, the third syndrome unit is further configured to:

[0207] If the type corresponding to the first SQL statement is a data modification type, extracting the target data condition in the first SQL statement;

[0208] extracting the first data set and the second data set corresponding to the first data table and the second data table respectively according to the target data condition, and determining the difference data between the first data set and the second data set;

[0209] performing a comparison between the first impact record number, the second impact record number, and zero;

[0210] If the second impact record number is greater than or equal to zero and less than the first impact record number, writing the first difference data into the second data table;

[0211] If the second impact record number is greater than the first impact record number, the data corresponding to the second difference data in the second data table is deleted.

[0212] In this embodiment, if the second data table does not contain data from the first data table, a double write approach is used to synchronize the data between the two tables. Furthermore, the present invention provides a method for determining whether the data in the two data tables is consistent by comparing the number of records affected by two SQL statements and performing corrections, thereby improving the consistency of the data between the two data tables.

[0213] above Figure 6 and Figure 7 The data table switching method device in the embodiment of the present invention is described in detail from the perspective of modular functional entities. The data table switching method device in the embodiment of the present invention is described in detail from the perspective of hardware processing.

[0214] Figure 8 : is a structural diagram of a data table switching method device provided in an embodiment of the present invention. The data table switching method device 800 may have relatively large differences due to different configurations or performances, and may include one or more processors (central processing units, CPU) 810 (for example, one or more processors) and a memory 820, and one or more storage media 830 (for example, one or more mass storage devices) storing application programs 833 or data 832. Among them, the memory 820 and the storage medium 830 can be temporary storage or permanent storage. The program stored in the storage medium 830 may include one or more modules (not shown in the figure), and each module may include a series of instruction operations in the data table switching method device 800. Furthermore, the processor 810 can be configured to communicate with the storage medium 830 to execute a series of instruction operations in the storage medium 830 on the data table switching method device 800.

[0215] The data table switching method device 800 may further include one or more power supplies 840, one or more wired or wireless network interfaces 850, one or more input and output interfaces 860, and / or one or more operating systems 831, such as Windows Server, Mac OS X, Unix, Linux, FreeBSD, etc. It will be understood by those skilled in the art that Figure 8The structure of the data table switching method device shown does not constitute a limitation on the data table switching method device, and may include more or fewer components than shown in the figure, or combine certain components, or arrange the components differently.

[0216] The present invention also provides a computer-readable storage medium, which may be a non-volatile computer-readable storage medium or a volatile computer-readable storage medium. The computer-readable storage medium stores instructions, which, when executed on a computer, cause the computer to execute the steps of the data table switching method.

[0217] Those skilled in the art will clearly understand that, for the convenience and brevity of description, the specific working processes of the systems, devices and units described above can refer to the corresponding processes in the aforementioned method embodiments and will not be repeated here.

[0218] If the integrated unit is implemented in the form of a software functional unit and sold or used as an independent product, it can be stored in a computer-readable storage medium. Based on this understanding, the technical solution of the present invention is essentially or the part that contributes to the prior art or all or part of the technical solution can be embodied in the form of a software product. The computer software product is stored in a storage medium and includes several instructions for enabling a computer device (which can be a personal computer, server, or network device, etc.) to perform all or part of the steps of the method described in each embodiment of the present invention. The aforementioned storage medium includes: U disk, mobile hard disk, read-only memory (ROM), random access memory (RAM), magnetic disk or optical disk, etc., various media that can store program code.

[0219] As described above, the above embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit the same. Although the present invention has been described in detail with reference to the above embodiments, those skilled in the art should understand that the technical solutions described in the above embodiments can still be modified, or some of the technical features thereof can be replaced by equivalents. However, these modifications or replacements do not deviate the essence of the corresponding technical solutions from the spirit and scope of the technical solutions of the embodiments of the present invention.

Claims

1. A data table switching method, applied to hot switching of a single data source, characterized in that: The data table switching method includes: Receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request, wherein the data table is stored in a MySQL database; Generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules; When obtaining a first SQL statement to be executed, determining whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table; If the second data table contains the data in the first data table, generating a second SQL statement with the target data table being the second data table according to the first SQL statement and executing the statement to switch between the first data table and the second data table; If the second data table does not contain the data in the first data table, determining the statement type corresponding to the first SQL statement according to the preset statement type library, where the statement type includes a query type and a data table modification type; If the statement type corresponding to the first SQL statement is a query type, executing the first SQL statement; If the statement type corresponding to the first SQL statement is a data table modification type, a third SQL statement whose target data table is the second data table is generated based on the first SQL statement, and the first SQL statement and the third SQL statement are executed to perform double writing of data in the first data table and the second data table.

2. The data table switching method according to claim 1, wherein: If the statement type corresponding to the first SQL statement is a data table modification type, generating, based on the first SQL statement, a third SQL statement whose target data table is the second data table, and executing the first SQL statement and the third SQL statement to perform double writing of data in the first data table and the second data table, further comprising: Recording the first number of affected records corresponding to the first SQL statement and the second number of affected records corresponding to the third SQL statement; Determining whether the first impact record number and the second impact record number are equal; If the first number of affected records is not equal to the second number of affected records, executing a preset rollback statement to roll back the second data table to a state before executing the third SQL statement; The data in the second data table is corrected according to a preset correction rule.

3. The data table switching method according to claim 2, wherein: The data table modification type includes a data addition and deletion type and a data modification type. Correcting the data in the second data table according to the preset correction rule includes: If the statement type corresponding to the first SQL statement is a data addition or deletion type, then executing the third SQL statement and recording the number of re-executions until the preset stop execution condition is met. If the number of re-executions is equal to the preset retry threshold, recording the statement type and statement parameters of the third SQL statement; The preset stop execution condition includes that the number of re-executions is equal to the preset retry threshold or the second impact record number is equal to the first impact record number.

4. The data table switching method according to claim 2 or 3, characterized in that: Correcting the data in the second data table according to the preset correction rule includes: If the type corresponding to the first SQL statement is a data modification type, extracting the target data condition in the first SQL statement; extracting the first data set and the second data set corresponding to the first data table and the second data table respectively according to the target data condition, and determining the difference data between the first data set and the second data set; Based on the difference data and preset correction rules, the data in the second data table is corrected.

5. The data table switching method according to claim 4, characterized in that: The extracting, according to the target data condition, the first data set and the second data set corresponding to the first data table and the second data table, respectively, and determining the difference data between the first data set and the second data set includes: extracting first and second data sets corresponding to the first and second data tables, respectively, according to the target data condition; comparing the first data set and the second data set; taking data that exists in the first data set but does not exist in the second data set as first difference data; The data that exists in the second data set but does not exist in the first data set is used as the second difference data.

6. The data table switching method according to claim 5, characterized in that: The modifying of the data in the second data table based on the difference data and the preset modification rule includes: performing a comparison between the first impact record number, the second impact record number, and zero; If the second impact record number is greater than or equal to zero and less than the first impact record number, writing the first difference data into the second data table; If the second impact record number is greater than the first impact record number, the data corresponding to the second difference data in the second data table is deleted.

7. A data table switching device, applied to hot switching of a single data source, characterized in that: The data table switching device includes: a receiving module, configured to receive a data table reconstruction request initiated by a user, and determine a first data table corresponding to the data table reconstruction request, wherein the data table is stored in a MySQL database; A generating module, configured to generate a second data table according to the table format of the first data table, wherein the second data table includes preset partition writing rules or table writing rules; A judgment module, configured to, when obtaining a first SQL statement to be executed, judge whether the second data table contains data in the first data table, wherein the target data table of the first SQL statement is the first data table; a switching module configured to generate, based on the first SQL statement, a second SQL statement whose target data table is the second data table and execute the statement, if the second data table contains data in the first data table, so as to switch between the first data table and the second data table; A dual-write module is configured to determine, based on a preset statement type library, a statement type corresponding to the first SQL statement if the second data table does not contain data in the first data table, wherein the statement types include query types and data table modification types; if the statement type corresponding to the first SQL statement is a query type, execute the first SQL statement; if the statement type corresponding to the first SQL statement is a data table modification type, generate, based on the first SQL statement, a third SQL statement whose target data table is the second data table, and execute the first and third SQL statements to perform dual-write data on the first and second data tables.

8. A data table switching device, characterized in that: The data table switching device includes: a memory and at least one processor, wherein instructions are stored in the memory, and the memory and the at least one processor are interconnected via a line; The at least one processor calls the instruction in the memory to enable the data table switching device to execute the data table switching method according to any one of claims 1 to 6.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that: When the computer program is executed by a processor, the data table switching method according to any one of claims 1 to 6 is implemented.

Images