Data synchronization method and apparatus, computer device and storage medium

Abstract

The present application relates to the technical field of databases. Disclosed in the embodiments of the present application are a data synchronization method and apparatus, a computer device and a storage medium. The method comprises: acquiring data to be synchronized of a first data table in a first database (202); when the first data table is a data table without a primary key, generating primary key data on the basis of the data to be synchronized (204); and writing the data to be synchronized and the primary key data into a second data table in a second database, the second data table being a data table having a primary key, and the primary key data being located in a primary key column in the second data table (206). Using the solution provided in the embodiments of the present application can improve the availability of databases and the operation efficiency for data tables in databases.

Description

Data synchronization methods, devices, computer equipment and storage media

[0001] This application claims priority to Chinese Patent Application No. 202510176949.5, filed on February 18, 2025, entitled “Data Synchronization Method, Apparatus, Computer Equipment and Storage Medium”, the entire contents of which are incorporated herein by reference. Technical Field

[0002] This application relates to the field of database technology, and in particular to a data synchronization method, apparatus, computer equipment, and storage medium. Background Technology

[0003] HTAP (Hybrid Transactional and Analytical Processing) databases are databases that can perform both OLTP (On-Line Transaction Processing) and OLAP (On-Line Analytical Processing) simultaneously.

[0004] CDC (Change Data Capture) technology is a technique used to capture data changes (such as insertions, updates, and deletions) in a database and is used to achieve data synchronization between OLTP and OLAP databases.

[0005] In related technologies, because OLTP databases and OLAP databases do not support data synchronization for tables without primary keys, query requests for tables without primary keys in the OLTP database are forwarded to the OLAP database, resulting in query errors and affecting the availability of the OLAP database. Summary of the Invention

[0006] This application provides a data synchronization method, apparatus, computer device, and storage medium. The technical solution is as follows:

[0007] On one hand, embodiments of this application provide a data synchronization method, which is executed by a computer device, and the method includes:

[0008] Retrieve the data to be synchronized from the first table in the first database;

[0009] If the first data table is a data table without a primary key, primary key data is generated based on the data to be synchronized;

[0010] The data to be synchronized and the primary key data are written into a second data table in a second database. The second data table is a primary key data table, and the primary key data is located in the primary key column of the second data table.

[0011] On the other hand, embodiments of this application provide a data synchronization device, the device comprising:

[0012] The reading module is used to retrieve the data to be synchronized from the first data table in the first database.

[0013] The processing module is used to generate primary key data based on the data to be synchronized when the first data table is a data table without a primary key.

[0014] The writing module is used to write the data to be synchronized and the primary key data into a second data table in a second database. The second data table is a primary key data table, and the primary key data is located in the primary key column of the second data table.

[0015] On the other hand, embodiments of this application provide a computer device including a processor and a memory, wherein the memory stores at least one computer instruction, which is loaded and executed by the processor to implement the data synchronization method as described above.

[0016] On the other hand, embodiments of this application provide a computer-readable storage medium storing at least one computer instruction, which is loaded and executed by a processor to implement the data synchronization method as described above.

[0017] On the other hand, embodiments of this application provide a computer program product including computer instructions stored in a computer-readable storage medium, wherein a processor retrieves the computer instructions from the computer-readable storage medium and executes the computer instructions to implement the data synchronization method as described above.

[0018] In this embodiment, when the data synchronization device synchronizes data between the first database and the second database, if the data to be synchronized belongs to a table without a primary key in the first database, the data synchronization device automatically generates primary key data based on the data to be synchronized and writes the data to be synchronized and the primary key data together into a table with a primary key in the second database. This achieves data synchronization of tables without primary keys without changing the table structure of the tables without primary keys in the first database. It satisfies the storage requirements of the tables with primary keys in the second database while avoiding data intrusion caused by modifying the tables in the first database. This allows subsequent queries to tables without primary keys in the first database within the second database, improving database availability. Furthermore, since the tables in the second database are tables with primary keys, subsequent operations on these tables can be performed using the primary keys to improve efficiency. Attached Figure Description

[0019] Figure 1 shows a schematic diagram of a database system provided in an exemplary embodiment of this application;

[0020] Figure 2 shows a flowchart of a data synchronization method provided in an exemplary embodiment of this application;

[0021] Figure 3 is a schematic diagram illustrating an exemplary embodiment of the data synchronization process of this application;

[0022] Figure 4 is a flowchart illustrating the process of determining composite primary key data in an exemplary embodiment of this application;

[0023] Figure 5 is a schematic diagram illustrating an exemplary embodiment of the process of adding composite primary key data according to this application;

[0024] Figure 6 is a flowchart illustrating the full data synchronization phase of an exemplary embodiment of this application;

[0025] Figure 7 is a flowchart illustrating a row data update process in an exemplary embodiment of this application;

[0026] Figure 8 is a schematic diagram illustrating an implementation of the row data update process in an exemplary embodiment of this application;

[0027] Figure 9 is a flowchart illustrating a primary key replacement process in an exemplary embodiment of this application;

[0028] Figure 10 is a schematic diagram illustrating an exemplary embodiment of the primary key replacement process of this application;

[0029] Figure 11 is a flowchart illustrating a primary key update process in an exemplary embodiment of this application;

[0030] Figure 12 is a schematic diagram illustrating an exemplary embodiment of the primary key update process of this application;

[0031] Figure 13 is a flowchart illustrating a primary key deletion process in an exemplary embodiment of this application;

[0032] Figure 14 is a schematic diagram illustrating an exemplary embodiment of the primary key deletion process of this application;

[0033] Figure 15 is a schematic diagram illustrating the overlap of full data synchronization and incremental data synchronization time periods in an exemplary embodiment of this application;

[0034] Figure 16 is a flowchart illustrating the incremental data synchronization phase of an exemplary embodiment of this application;

[0035] Figure 17 is a structural block diagram of a data synchronization device provided in an exemplary embodiment of this application;

[0036] Figure 18 shows a schematic diagram of the structure of a computer device provided in an exemplary embodiment of this application. Detailed Implementation

[0037] To make the objectives, technical solutions, and advantages of this application clearer, the embodiments of this application will be described in further detail below with reference to the accompanying drawings.

[0038] For ease of understanding, the terms used in the embodiments of this application will be explained below.

[0039] Primary key: Short for primary key, it refers to one or more fields in a database. The value of the primary key uniquely identifies a record in a data table; that is, every record in the data table can be uniquely identified by the primary key. A primary key can be composed of multiple fields (called a composite primary key). The column containing the primary key in the data table is called the primary key column (one or more columns), and the primary key column cannot contain null values. The data located in the primary key column of a data table with a primary key is called primary key data. Whether a data table has a primary key is optional and can be defined in the CREATE TABLE or ALTER TABLE statements. In this embodiment, a data table with a primary key is called a primary key table, and a data table without a primary key is called a no-primary-key table. When creating a data table with a primary key, the primary key column must be specified.

[0040] Database log (binlog): A log used to record data changes in a database, typically stored in binary format. During database synchronization, the synchronization component reads the source database's database log and parses it into database commands that the destination database can recognize and process. Synchronization is then achieved by replaying these commands at the destination database.

[0041] Database commands, also known as database statements, are command statements used to perform operations on data tables in a database. Database commands can be divided into Data Manipulation Language (DML) commands and Data Definition Language (DDL) commands. DML commands are used to implement basic database operations, including basic CRUD operations (Create, Read, Update, Delete), and command keywords can include select, update, delete, insert, merge, etc. DDL commands are used to define and manage objects in the database (e.g., database, table), and command keywords can include create, alter, drop, truncate (delete all data in a table while preserving the table structure), comment, grant, revoke, etc. In this embodiment, the data synchronization component obtains database commands by parsing the database logs, and then achieves data synchronization by replaying the database commands.

[0042] Full data synchronization & incremental data synchronization: Full synchronization, also known as complete synchronization, refers to the complete synchronization of data from the source database to the destination database. It is typically used when there are significant differences between the source and destination databases to ensure data consistency. Incremental data synchronization, also known as difference synchronization, refers to the transmission of data that has changed in the database since the last synchronization. This can significantly reduce the amount of data to be synchronized and speed up the synchronization process. Incremental data synchronization can be performed in parallel during full data synchronization.

[0043] Figure 1 shows a schematic diagram of a database system provided in an exemplary embodiment of this application. The system includes a first server 110, a data synchronization device 120, a second server 130, and a front-end agent engine 140.

[0044] Both the first server 110 and the second server 130 are database servers, with the first server 110 maintaining a first database and the second server 130 maintaining a second database. The data synchronization device 120 is a computer device used to synchronize data between the first server 110 and the second server 130.

[0045] The first server 110 and the data synchronization device 120, and the second server 130 and the data synchronization device 120, are connected via wired or wireless networks.

[0046] For example, the data synchronization device 120 is a data synchronization server used for data synchronization between database servers, and it establishes communication connections with the first server 110 and the second server 120 respectively. As another example, the data synchronization device 120 is a personal calculator used for data synchronization between database servers, and it establishes communication connections with the first server 110 and the second server 120 respectively. This application does not limit the specific device type of the data synchronization device 120.

[0047] In some embodiments, the first database is the primary database (or source database), and the second database is the secondary database (or destination database). The second database is essentially a read-only copy of the first database, and the data in the first database needs to be synchronized to the second database via data synchronization device 120. When the proxy engine 140, located between the first server 110 and the second server 130, receives a database command, it forwards the database command to the appropriate server for execution based on the command and the respective advantages of the two databases.

[0048] For illustrative purposes, the first database is an OLTP database, and the second database is an OLAP database; together, the first and second databases constitute an HTAP database. When a database statement is received, the front-end agent forwards the most important execution statements from the OLTP database (such as transactional statements with a high proportion of insert, delete, and update operations) to the first server 110; and forwards the most important execution statements from the OLAP database (such as analytical statements like large or complex queries) to the second server 130.

[0049] In one possible design, when a data table in the first database is modified, the first server 110 stores the data table modification information in the database log. When data synchronization is required, the data synchronization device 120 reads the database log from the first server 110 and determines the data to be synchronized by parsing the database log.

[0050] When the data to be synchronized belongs to a table with a primary key, the data synchronization device 120 synchronizes the data to the second server 130 according to the conventional data synchronization method. If it is detected that the data to be synchronized belongs to a table without a primary key, in order to achieve synchronization of the non-primary key table without affecting the first database, the data synchronization device 120 generates primary key data based on the data to be synchronized and writes both the data to be synchronized and the primary key data into the second database of the second server 130. Specifically, the data to be synchronized and the primary key data are written to the second database using database command replay.

[0051] Since it can synchronize data tables without primary keys in the first database, when the front-end proxy engine 140 forwards query commands for data tables without primary keys in the first database to the second server 130, the second server 130 can perform fast queries based on the primary keys in the data tables with primary keys, which ensures the availability of the second database and improves data query efficiency.

[0052] Figure 2 shows a flowchart of a data synchronization method provided in an exemplary embodiment of this application. This embodiment illustrates the method as being executed by the data synchronization device 120 shown in Figure 1, and the method includes the following steps.

[0053] Step 202: Obtain the data to be synchronized from the first data table in the first database.

[0054] The data to be synchronized can be data from the full synchronization phase or the incremental synchronization phase.

[0055] In this embodiment, the first server maintains a database log that records data changes in the database. During data synchronization, the data synchronization device reads the database log from the first server and determines the data to be synchronized to the first data table in the second database by parsing the database log.

[0056] The first data table can be a single data table or multiple data tables. In this embodiment, any data table belonging to the first database and requiring data synchronization is referred to as the first data table.

[0057] The first database is a database that supports both tables with primary keys and tables without primary keys. Correspondingly, the first data table can be a table with a primary key or a table without a primary key.

[0058] In order to determine whether special processing is required during data synchronization, the data synchronization device in this application embodiment has the ability to identify whether the first data table belongs to a primary key data table or a non-primary key data table.

[0059] When the first data table is identified as a table with a primary key, the data synchronization device will synchronize the data to be synchronized to the second database; when the first data table is identified as a table without a primary key, the data synchronization device will execute the following step 204.

[0060] The data synchronization device can determine whether the first data table is a table with a primary key by detecting whether the table creation statement of the first data table contains a primary key definition (the table creation statement for a table with a primary key contains a primary key definition, while the table creation statement for a table without a primary key does not contain a primary key definition), or by querying the table structure of the first data table (a table with a primary key has a primary key column, while a table without a primary key does not contain a primary key column), or by querying the primary key index of the first data table (a table with a primary key has a primary key index, while a table without a primary key does not contain a primary key index). This application embodiment does not limit the specific method of distinguishing between tables with and without primary keys.

[0061] As illustrated in Figure 3, the data synchronization device concurrently extracts data to be synchronized from the first database 31, which belongs to the first data table 311.

[0062] Step 204: If the first data table is a data table without a primary key, generate primary key data based on the data to be synchronized.

[0063] In this embodiment, the second database is a database that supports tables with primary keys. When the first table is identified as having no primary key, to ensure normal data synchronization, the data synchronization device generates primary key data based on the data to be synchronized, so that the primary key data can be subsequently added to the primary key column of the second database containing the primary key.

[0064] In the case where the first data table in the first database is a data table without a primary key, the second data table in the second database corresponding to the first data table is a data table with a primary key, and the primary key column is specified as the primary key when the second data table is created.

[0065] Since the first data table may not contain data located in the primary key column of the second data table, the data synchronization device needs to generate the data to be populated into the primary key column of the second data table before data synchronization; that is, to generate primary key data. The method for generating the primary key data and its column position in the second data table are determined when the second data table is created.

[0066] Since the value of a primary key is used to uniquely identify a record in a data table, the data synchronization device needs to ensure the uniqueness of the primary key data when generating primary key data based on the data to be synchronized.

[0067] The primary key data can be generated based on all or part of the data to be synchronized. For example, it can be generated based on column data from some columns in the data to be synchronized. Furthermore, the primary key data can be either a single primary key or a composite primary key.

[0068] In some embodiments, if there is no second data table corresponding to the first data table in the second database (such as during the full synchronization phase), the data synchronization device needs to define a method for determining the primary key data, such as defining which part of the data to be synchronized is used to generate the primary key data, and then determining the primary key data based on the defined method and the data to be synchronized.

[0069] If a second data table corresponding to the first data table exists in the second database (e.g., during incremental synchronization), the data synchronization device directly determines the primary key data based on the primary key data determination method already defined in the second data table and the data to be synchronized.

[0070] As illustrated in Figure 3, when the first data table 311 is identified as a data table without a primary key, the data synchronization device needs to generate primary key data based on the data to be synchronized in the first data table 311. This primary key data is generated based on the "group" and "user" columns in the first data table 311, as well as the row identifier.

[0071] Step 206: Write the data to be synchronized and the primary key data into the second data table in the second database. The second data table is a primary key table, and the primary key data is located in the primary key column of the second data table.

[0072] The second data table corresponds to the first data table in the first database, and the first data table and the second data table have the same table name.

[0073] In this embodiment, the data synchronization device generates database commands, and then writes the data to be synchronized and the primary key data into a second data table in a second database through database command replay. Specifically, the data to be synchronized is written to a non-primary key column of the second data table, while the primary key data is written to the primary key column of the second data table.

[0074] In addition to the database commands for adding data to be synchronized, the database commands also include database commands related to adding primary key data.

[0075] It should be noted that if a second data table corresponding to the first data table does not exist in the second database, the data synchronization device first needs to create the corresponding second data table in the second database using database commands before performing data synchronization. If a second data table corresponding to the first data table exists in the second database, the data synchronization device can directly perform data synchronization. Specifically, if a second data table exists in the second database, the data synchronization device needs to compare the table structure of the second data table with that of the first data table. If they do not match, the second data table in the second database needs to be deleted first, and then a second data table with the same table structure as the first data table needs to be created.

[0076] As illustrated in Figure 3, the data synchronization device adds primary key-related database commands to the database commands related to the data to be synchronized, thereby writing the data to be synchronized in the first data table 311 and the generated primary key data 322 into the second data table 321 of the second database 32. Compared with the first data table 311, the second data table 321 adds a primary key column "Hash(group, user)" and a "row identifier".

[0077] Since the first database is typically a user database, modifying the table structure of tables without primary keys in the first database usually impacts existing user services, and adding primary keys to large-scale tables is very time-consuming. In this embodiment, without changing the table structure of the first table in the first database, the data synchronization device automatically generates primary key data, thereby synchronizing the data to be synchronized (without primary keys) and the primary key data to the second database. This satisfies the storage requirements of the second database for tables with primary keys while avoiding intrusion into user services. Subsequently, the second database can also query tables without primary keys in the first database, ensuring the availability of the second database.

[0078] In summary, in this embodiment, when the data synchronization device synchronizes data between the first database and the second database, if the data to be synchronized belongs to a table without a primary key in the first database, the data synchronization device automatically generates primary key data based on the data to be synchronized and writes the data to be synchronized and the primary key data together into a table with a primary key in the second database. This achieves data synchronization of tables without primary keys without changing the table structure of the tables without primary keys in the first database, enabling subsequent queries on tables without primary keys in the first database to be performed in the second database, thus improving database availability. Furthermore, since the tables in the second database are tables with primary keys, subsequent operations on the tables can be performed using the primary keys to improve efficiency.

[0079] Primary key column settings

[0080] In one possible implementation, the data synchronization device uses an auto-incrementing column as the primary key column.

[0081] When using a single auto-incrementing column as the primary key, database commands for the first table in the first database (which has no primary key) do not carry the auto-incrementing data of the column. Therefore, during subsequent incremental data synchronization and queries, the second database needs to perform a full scan of the second table based on the database commands, resulting in low efficiency. Specifically, during the full scan of the second table, the data synchronization device needs to match the rows before the change with the rows in the second table to identify the rows in the second table that match the rows before the change, and then update the row data based on the rows after the change.

[0082] To ensure the uniqueness of primary key data and improve the efficiency of subsequent operations based on primary key data in the second data table, in another possible implementation, the data synchronization device uses a composite primary key scheme to generate composite primary key data.

[0083] In the composite primary key scheme, the second data table contains a composite primary key column, which is composed of at least two primary key columns from the second data table. Accordingly, the generated composite primary key data will be populated into the composite primary key column of the second data table. The method for determining the primary key data in different composite primary key columns differs.

[0084] When a composite primary key column contains n primary key columns, for a second data table that uses a composite primary key column, each record in the second data table can be uniquely identified by a combination of n data located in n (n≥2) primary key columns. That is, based on the n data located in the n data columns, a record can be uniquely located in the second data table, where the record can be called row data.

[0085] In one possible design, the composite primary key data generated by the data synchronization device based on the data to be synchronized includes a first primary key and a second primary key. The first primary key is the hash data located in the hash column of the second data table, and the second primary key is the auto-incrementing data located in the auto-incrementing column of the second data table. The hash column and the auto-incrementing column together constitute the composite primary key column of the second data table.

[0086] The hash data is the hash result of at least one column of the data to be synchronized. Since the row data of different rows under the same column in a table without a primary key may be the same, the hash data corresponding to different rows in the hash column may be the same, that is, the hash data is not unique.

[0087] Since the auto-incrementing data is the auto-value corresponding to each row in the data table, the auto-incrementing data for different rows in the auto-incrementing column is different. Accordingly, the combination of hash data and auto-incrementing data in the hash column and auto-incrementing column is unique to meet the uniqueness requirement of the primary key column value.

[0088] When a database command is subsequently received for a first data table in the first database that has no primary key, the second database can determine the hash data to be located based on the data contained in the database command. Then, based on the hash data to be located and the hash data in the hash column, the row to be operated on can be located in the second data table without having to perform a full scan of the second data table, thus improving efficiency.

[0089] In one possible implementation, as shown in Figure 4, generating composite primary key data based on the data to be synchronized may include the following steps:

[0090] Step 204A: For each row of data in the data to be synchronized, the hash operation result of the data in the row that is located in at least one candidate column is determined as the first primary key data corresponding to the row data. The at least one candidate column is determined from the columns contained in the first data table.

[0091] For a first data table without a primary key, the data synchronization device selects data from at least one candidate column from the columns of the first data table to generate hash data (i.e., the first primary key data) for the hash column in the second data table.

[0092] In some embodiments, at least one candidate column may be determined by the data synchronization device from the first data table during the initial synchronization of the first data table.

[0093] When the data to be synchronized includes at least one row of data from the first data table, for each row of data, the data synchronization device extracts data from at least one candidate column, performs a hash operation on the extracted data based on a hash function, and determines the first primary key data corresponding to that row of data based on the hash operation result. The length of the first primary key data corresponding to each row of data is the same.

[0094] Optionally, the hash function may include various functions such as MD2, MD4, and MD5 that output a fixed-length hash value based on the input data, and this application embodiment does not limit this.

[0095] Optionally, for different non-key data tables in the first database, the number of candidate columns used to determine the first primary key data in the non-key data tables can be the same or different. For example, different non-key data tables in the first database may all determine the first primary key data based on the data in 2 candidate columns, or the number of candidate columns used to determine the first primary key data in the non-key data tables may be 1, 2, 3, 4, etc. The embodiments of this application do not limit the number of candidate columns.

[0096] Optionally, for different keyless tables in the first database, the hash function used when performing hash operations on the candidate columns can be the same or different. For example, for different keyless tables, the hash function can be selected based on the data of the candidate columns in the keyless tables, considering both efficiency (improving computational efficiency) and uniformity (distributing data evenly across the hash table to reduce collisions). This application does not limit the specific method for selecting the hash function.

[0097] As illustrated in Figure 5, in the first data table 51 without a primary key, the candidate columns used to calculate the first primary key data are group column 511 and user column 512. For the first row of data in the first data table 51, the data synchronization device determines the hash operation result as 123456789 based on the data "1" in group column 511 and the data "Max" in user column 512; for the second row of data in the first data table 51, the data synchronization device determines the hash operation result as 223456789 based on the data "2" in group column 511 and the data "Max" in user column 512; for the third and fourth rows of data in the first data table 51, since the data in the third and fourth rows are completely identical, the data synchronization device determines the hash operation result as 143256789 based on the data "1" in group column 511 and the data "Vic" in user column 512.

[0098] Step 204B: Determine the auto-incrementing row number corresponding to the row data as the second primary key data corresponding to the row data.

[0099] For each row of data in the data to be synchronized, the data synchronization device assigns an auto-incrementing row number to each row of data and determines the auto-incrementing data (i.e., the second primary key data) of the auto-incrementing column corresponding to each row of data.

[0100] Since data synchronization may be interrupted due to network issues, the data synchronization process between databases needs to support resuming interrupted data transfers. Accordingly, before resuming interrupted data transfers, the data synchronization device needs to determine the starting point of the auto-incrementing row number to ensure the uniqueness of the auto-incrementing row number for each row of data in the same data table.

[0101] Optionally, in the case of non-disconnection resume mode, the data synchronization device determines the auto-incrementing row number corresponding to each row of data in the data to be synchronized, starting from the initial row number. This initial row number can be 1.

[0102] As illustrated in Figure 5, in the non-disconnection resume mode, the data synchronization device assigns auto-incrementing row numbers to the four rows of data in the first data table 51, which are 1, 2, 3, and 4 in sequence.

[0103] When in the breakpoint resume state, the data synchronization device obtains the maximum row number of the auto-incrementing column in the second data table of the second database, and then uses the maximum row number as the starting point to determine the auto-incrementing row number corresponding to each row of data in the data to be synchronized.

[0104] The data synchronization device can connect to the second database and, based on the table name and the name of the auto-increment column of the second data table, read the data of the auto-increment column in the second data table, thereby determining the maximum row number of the auto-increment column.

[0105] For example, when the maximum number of rows in the auto-incrementing column needs to be 3, the data synchronization device will start from row number 4 to determine the auto-incrementing row number of each row in the data to be synchronized.

[0106] It should be noted that there is no strict order between the above steps 204A and 204B, that is, they can be executed synchronously or asynchronously, and this application embodiment does not limit this.

[0107] Step 204C: Determine the first primary key data and the second primary key data as the composite primary key data of the row data.

[0108] Furthermore, the data synchronization device determines the first primary key data and the second primary key data as composite primary key data that are unique to each row of data.

[0109] As illustrated in Figure 5, the data synchronization device determines the first primary key data of hash column 521 and the second primary key data of auto-increment column 522 as the composite primary key data of the second data table 52.

[0110] When writing the data to be synchronized and the primary key data into the second data table, since the table structure of the second data table is different from that of the first data table, the table creation statement for the second data table needs to be rewritten. The rewritten table creation statement includes database commands for creating hash columns and auto-increment columns.

[0111] As an illustration, when the first data table includes an id column and a value column, the database command to create the first data table in the first database can be:

[0112] When it is necessary to synchronize the first data table to the second database, the database command for creating the second data table in the second database is rewritten as follows:

[0113] In the second data table, the hash data of the hash column is the hash result of the data in the id column and the value column, and the auto-increment data of the auto-increment column is the auto-increment row number.

[0114] In one possible implementation, during the full synchronization phase, the data synchronization process of the data synchronization device is shown in Figure 6. When starting full data synchronization, the data synchronization device records the start checkpoint (601) and determines whether the current data is a resumed interrupted transmission (602). If it is not a resumed interrupted transmission, the data synchronization device connects to the first database to obtain the table structure of the first data table to be synchronized (603); if it is a resumed interrupted transmission, the data synchronization device connects to the second database to restore the latest value of the auto-increment column of the second data table corresponding to the first data table (604).

[0115] The data synchronization device parses the table creation statement of the first data table (605) using a syntax parser and determines whether the first data table has a primary key (606). If a primary key exists, full data synchronization is performed according to the normal process for a data table with a primary key (607); if no primary key exists, candidate columns for hash calculation are selected from the first data table, and the latest value of the auto-increment column is obtained (608). Based on the candidate columns and the auto-increment column, the table creation statement of the second data table is modified (609) so that the created second data table has a composite primary key column consisting of an auto-increment column and a hash column. After completing the full data synchronization, the data synchronization device records the end checkpoint (610).

[0116] In this embodiment, the data synchronization device uses a hash column and an auto-incrementing column as the composite primary key column of the second data table. While ensuring the uniqueness of the data in the composite primary key column, the hash data of the hash column can be used to improve the data location speed. There is no need to perform a full scan and traversal of the second data table, which helps to improve the operation efficiency of the data table.

[0117] Selection of candidate columns

[0118] As described in the above embodiments, since the first data table without a primary key is allowed to contain two identical rows of data, hash collisions may occur when hash data is calculated based on data from at least one candidate column. Hash collisions can affect the operational efficiency of the data table. To further improve the operational efficiency of the data table, the data synchronization device selects candidate columns for calculating hash data with the goal of reducing hash collisions.

[0119] In one possible implementation, the data synchronization device determines the data duplication rate of each column in the first data table, and based on the data duplication rate, determines at least one candidate column from the columns contained in the first data table.

[0120] Specifically, the data duplication rate in at least one candidate column is lower than the data duplication rate in other columns besides at least one candidate column in the first data table. In other words, the data synchronization device selects columns with lower data duplication rates as candidate columns to reduce the probability of hash collisions.

[0121] In some embodiments, when the first data table is synchronized for the first time, i.e., when there is no second data table in the second database corresponding to the first data table, the data synchronization device performs the step of determining at least two candidate columns, and subsequent synchronization of the first data table uses the candidate columns determined during the first synchronization.

[0122] Optionally, the data duplication rate is the proportion of duplicate data to the total amount of data.

[0123] Optionally, the data synchronization device identifies data in the same column within the data to be synchronized and calculates the proportion of duplicate data in each column relative to the total data volume, obtaining the data duplication rate for each column in the first data table. The data synchronization device sorts the data according to the duplication rate in ascending order and selects the columns corresponding to the top n data duplication rates as candidate columns, where n is a positive integer. In other words, the data synchronization device selects the top n columns with the lowest data duplication rates as candidate columns.

[0124] As illustrated in Figure 5, the data synchronization device determines that the data repetition rate of the group column is 75%, the data repetition rate of the user column is 50%, and the data repetition rate of the fraction column is 100%, thus identifying the group column and the user column as candidate columns.

[0125] In other possible implementations, the data synchronization device may also arrange and combine different data columns in the first data table, and perform simulated hash operations on the data columns in different arrangement and combination methods, thereby selecting the arrangement and combination method with a lower hash collision rate based on the simulated hash operation results.

[0126] For example, data synchronization devices can perform simulated hash operations in permutations and combinations such as "group column + user column", "group column + score column" and "user column + score column" to obtain the hash collision rate of each of the three permutations and combinations, and then select the permutation and combination with the lowest hash collision rate.

[0127] As illustrated in Figure 5, since the hash collision rate is lowest under the combination of "group column + user column", the data synchronization device determines the group column and user column as candidate columns.

[0128] Of course, in other possible implementations, the data synchronization device may also determine candidate columns from the columns contained in the first data table according to other rules, such as randomly selecting or selecting specified columns (such as the first column and the second column), etc., and this application embodiment does not limit this.

[0129] In this embodiment, the data synchronization device determines at least one candidate column for calculating the hash column based on the data duplication rate of each column in the first data table, so as to reduce the hash collision rate of the hash data in the hash column and improve the efficiency of subsequent data table operations based on the hash column.

[0130] Accelerating DML command execution

[0131] When performing operations such as querying, updating, and deleting on the second data table in the second database (DML commands), the command statement carries information about the rows to be queried, updated, or deleted. Since the second data table contains a hash column, the hash data corresponding to the row data to be operated on can be determined. By comparing it with the hash data of the hash column in the second data table, the position of the row data to be operated on in the second data table can be quickly located.

[0132] In one possible scenario, when it is necessary to synchronize changes in row data in the first data table to the second data table, as shown in Figure 7, the process may include the following steps:

[0133] Step 701: Read row change data from the database log of the first database. The row change data is used to characterize the row data changes of the first changed row in the first data table.

[0134] In one possible implementation, when row data in the first data table of the first database changes (including adding, deleting, and modifying rows), the first server saves the row data change information in the database log. During data synchronization, the data synchronization device reads the row change data from the database log.

[0135] Among them, the data synchronization device can start from the last recorded checkpoint and read row change data from the database log to avoid repeated reading.

[0136] In some embodiments, row change data in the database log is recorded using row-based replication (RBR). For example, when a row is updated, the database log contains the original row data before the update and the updated row data; when a row is deleted, the database log contains the original row data and the empty row data after deletion.

[0137] Correspondingly, the data synchronization device reads the row change data of the first changed row in the first data table, including the row data change method (such as update or delete), as well as the row data before and after the change of the first changed row (which can be empty).

[0138] As illustrated in Figure 8, the data synchronization device reads the row change data 81 of the first changed row, including the change method of the first changed row, update, and the row data before the change, "Group:2, User:Max, Score:100", and the row data after the change, "Group:2, User:Max, Score:95". This row change data indicates that the score of user "Max" in group 2 has been updated from 100 to 95.

[0139] The data synchronization device reads the row change data 83 of the first changed row, including the change method of the first changed row, update, and the row data before the change, "Group:1, User:Vic, Score: 95", and the row data after the change, "Group:1, User:Vic, Score: 100". This row change data indicates that the score of user "Vic" in group 1 has been updated from 95 to 100.

[0140] Step 702: Based on the row change data and the first primary key data in the second data table, determine the second change row corresponding to the first change row in the second data table.

[0141] Since the first data table does not have a primary key, the row change data in the database log also does not contain a primary key. To avoid scanning and traversing the second data table, in this embodiment, the data synchronization device can determine the second changed row in the second data table corresponding to the first changed row based on the row change data and the primary key data in the second data table.

[0142] In some embodiments, since the first changed row cannot be mapped to its corresponding row number based on the row change data, but the hash data corresponding to the first changed row can be determined based on the row change data, the data synchronization device determines the second changed row corresponding to the first changed row in the second data table based on the row change data and the first primary key data. Optionally, this process may include the following sub-steps:

[0143] Step 702A: Determine the row preceding the change in the first changed row of the row change data.

[0144] Since the second data table records unchanged row data, the data synchronization device needs to locate the second row to be changed in the second data table based on the row data before the change of the first changed row.

[0145] As illustrated in Figure 8, the data synchronization device determines that the preceding row data of the first changed row in the row change data 81 is "Group:2, User:Max, Score:100".

[0146] Step 702B: Based on the hash operation result of the data located in at least one candidate column in the previous row data and the matching result of the first primary key data in the second data table, candidate rows are determined from the second data table, and the first primary key data of the candidate rows matches the hash operation result.

[0147] For the acquired data before the change, the data synchronization device extracts the data located in the candidate columns from the data before the change and performs a hash operation on the extracted data to obtain the hash operation result. Further, the data synchronization device matches the hash operation result with the first primary key data in the second data table, and determines the row to which the first primary key data that matches the hash operation result (i.e., the hash operation result is the same as the first primary key data) belongs as the candidate row.

[0148] Because hash collisions may occur, one or at least two candidate rows may be identified. Although hash collisions are possible, the hash calculation result can filter out the vast majority of rows in the second data table. Therefore, locating the second changed row based on the hash calculation result is significantly more efficient than performing a full match on the data table directly based on the data before the change.

[0149] Furthermore, the data synchronization device determines the second changed row from the candidate rows.

[0150] Optionally, if there is one candidate row, the data synchronization device determines the candidate row as the second changed row; if there are at least two candidate rows, the data synchronization device further determines the second changed row from the candidate rows through the following step 702C.

[0151] As illustrated in Figure 8, the data synchronization device performs a hash operation on the data "2" in the group column and the data "Max" in the user column, obtaining a hash result of 223456789. This hash result is then matched with the first primary key data in the hash column of the second data table 82 to determine the second row in the second data table 82 as a candidate row.

[0152] Step 702C: If there are at least two candidate rows, determine the second changed row from the at least two candidate rows based on the row data before the change and the row data of the candidate rows, wherein the row data of the second changed row matches the row data before the change.

[0153] When there are at least two candidate rows, in order to accurately locate the second changed row, the data synchronization device matches the row data of the candidate rows in the second data table with the row data of the row before the change, and determines the candidate row that completely matches the row data before the change as the second changed row.

[0154] As illustrated in Figure 8, the data synchronization device determines that the preceding row data of the first changed row in row change data 83 is "Group:1, User:Vic, Score:95". A hash operation is performed on the data "1" in the group column and the data "Vic" in the user column, yielding a hash result of 143256789. This hash result is then matched with the first primary key data in the hash column of the second data table 82, determining the third and fourth rows in the second data table 82 as candidate rows.

[0155] The data synchronization device further matches the data before the change with the third and fourth rows of the second data table 82, thereby identifying the third row as the second changed row.

[0156] Step 703: Update the row data of the second changed row in the second data table based on the row change data.

[0157] After identifying the second changed row, the data synchronization device updates the original row data of the second changed row in the second data table based on the row change data, so that it is consistent with the row data of the first changed row in the first data table (row data other than the primary key column).

[0158] In one possible implementation, the data synchronization device determines the changed row data of the first changed row in the row change data, and then updates the row data of the second changed row in the second data table based on the changed row data, that is, replaces the original row data of the second changed row with the changed row data.

[0159] Optionally, the data synchronization device generates a database command based on the determined auto-incrementing row number of the second changed row and the row change data, and then updates the row data of the second changed row in the second data table by means of database command playback.

[0160] It should be noted that the data synchronization device also needs to update the first primary key data in the hash column based on the data in at least one candidate column in the updated second changed row, in order to ensure the accuracy of the first primary key data in the hash column before and after the update.

[0161] As illustrated in Figure 8, the data synchronization device modifies the score of the second row in the second data table 82 from 100 to 95 based on the changed row data in row change data 81; the data synchronization device also modifies the score of the third row in the second data table 82 from 95 to 100 based on the changed row data in row change data 82. Since the above changes are applied to the score column data, and the score column is not a candidate column used to determine the hash value, the first primary key data of the hash column in the second data table 82 remains unchanged.

[0162] It should be noted that the above embodiment uses updating row data as an example for explanation. In other possible scenarios, when the row change data indicates the deletion of the row data of the first changed row, the data synchronization device locates the second changed row in the second data table and deletes the row data of the second changed row; when the row change data indicates the addition of row data, the data synchronization device does not need to locate the second changed row in the second data table, but adds row data to the second data table based on the row change data, which will not be elaborated here.

[0163] In this embodiment, when the row data of the first changed row in the first data table changes, the data synchronization device locates the second changed row in the second data table corresponding to the first changed row based on the row change data in the database log and the hash data in the second data table. This eliminates the need to traverse the second data table row by row based on the row change data, thereby accelerating DML commands such as query, update, and delete, and improving the operational efficiency of the database.

[0164] The above embodiments only illustrate the example of a data synchronization device accelerating DML commands based on primary key data during data synchronization. In query scenarios, the second database can also accelerate its own data query process based on query commands and primary key data. This process can be referred to as the DML command acceleration process, and will not be elaborated here.

[0165] Special handling during DDL command execution

[0166] Besides manipulating the data in the data table, in some application scenarios, users may modify the table structure of the first data table in the first database. For example, deleting a column in the first data table, designating a column as the primary key column, or removing the primary key column.

[0167] In order to synchronize the table structure modification of the first data table in the first database to the second data table in the second database, in one possible implementation, in a data synchronization scenario involving the modification of the data table structure, the data synchronization device reads the table structure update data from the database log of the first database. This table structure update data is used to characterize the changes in the table structure of the first data table, and based on the table structure update data, updates the table structure of the second data table in the second database.

[0168] The table structure update data represents changes in the table structure, including at least one of the following: adding columns, deleting columns, adding primary keys, and deleting primary keys.

[0169] In some embodiments, when the table structure update represents adding or deleting columns, the updated second data table adds or deletes the columns added or deleted in the first data table accordingly; when the table structure update represents adding a primary key (the first data table changes from a table without a primary key to a table with a primary key), the updated second data table uses the same primary key as the first data table; when the table structure update represents deleting a primary key (the first data table changes from a table with a primary key to a table without a primary key), the updated second data table still has a primary key.

[0170] In one possible implementation, the data synchronization device generates DDL commands based on the table structure update data, thereby updating the table structure of the second data table in the second database through database command replay.

[0171] Because the solution provided in this application automatically generates a primary key column when creating a primary key-based table corresponding to a primary key-less table, the data synchronization device needs to perform special processing for certain DDL commands during the data synchronization process between databases to ensure the correctness of the table structure of the second table. The following describes several special DDL operation scenarios:

[0172] Case 1: Add a primary key to the first data table that does not have a primary key.

[0173] When the first data table changes from a table without a primary key to a table with a primary key (i.e., the user specifies a primary key in the original table without a primary key), and since the second data table in the second database already has a primary key, to avoid inconsistencies in primary keys between the tables, the data synchronization device needs to remove the original primary key in the second data table and add the specified primary key added in the first data table (i.e., perform primary key replacement). As shown in Figure 9, the table structure update process for the second data table may include the following steps:

[0174] Step 901: If a specified primary key is added to the first data table representing the table structure update, a temporary data table is created in the second database. The temporary data table does not contain hash columns or auto-increment columns.

[0175] The specified primary key can be a single primary key or a composite primary key consisting of at least two columns in the first data table.

[0176] Since the second data table in the second database already contains an automatically added composite primary key column (hash column and auto-increment column), when updating the table structure of the second data table, the data synchronization device instructs the second database to create a temporary data table that does not contain the hash column and auto-increment column, so that data other than the primary key data in the second data table can be imported into the temporary data table later.

[0177] The temporary data table has a different name than the second data table. For example, if the second data table is named t1, the temporary data table is named t1_temp.

[0178] Optionally, the data synchronization device instructs the second server to create a temporary data table in the second database by replaying database commands.

[0179] As illustrated in Figure 10, the first data table 1010 is initially a data table without a primary key. During the synchronization of the first data table 1010, the data synchronization device creates a second data table 1020 in the second database. This second data table 1020 includes a composite primary key column 1021 composed of a hash column and an auto-incrementing column. When the user specifies the group and user columns in the first data table 1010 as primary key columns, the data synchronization device creates a temporary data table in the second database containing only group, user, and score columns.

[0180] Step 902: Obtain the original table data of the second data table.

[0181] Step 903: Delete the first primary key data and the second primary key data from the original table data to obtain the cleaned table data.

[0182] After creating the temporary data table, the data synchronization device connects to the second database, retrieves the original table data from the second data table, and deletes the composite primary key data (the first primary key data in the hash column and the second primary key data in the auto-increment column) from the original table data, obtaining the cleaned table data. This cleaned table data is identical to the table data in the first data table.

[0183] It should be noted that if the second data table uses a single primary key, the data synchronization device will delete the single primary key data corresponding to that single primary key to obtain the cleaned table data.

[0184] As illustrated in Figure 10, after obtaining the original table data of the second data table 1020, the data synchronization device deletes the composite primary key data of the composite primary key column 1021 to obtain cleaned table data containing data of groups, users, and score columns.

[0185] Step 904: Write the cleaned table data into a temporary data table.

[0186] Furthermore, the data synchronization device writes the cleaned table data into a temporary data table via database command replay. The data contained in this temporary data table is identical to the data contained in the first data table.

[0187] Step 905: Delete the second data table and rename the temporary data table to the second data table.

[0188] After the data writing is completed, the data synchronization device instructs the second database to delete the second data table and rename the temporary data table to the second data table, that is, update the table name of the temporary data table to the table name of the second data table.

[0189] Step 906: Set a specified primary key for the second data table.

[0190] To ensure the consistency of primary keys in the primary key tables of the first and second databases, the data synchronization device instructs the second data table to set a specified primary key that is consistent with that of the first data table through database command replay. Subsequent operations on the second data table can then be performed based on this specified primary key.

[0191] As illustrated in Figure 10, in the updated first data table 1010, groups and users are set to the specified primary key 1011, and in the updated second data table 1020, groups and users are set to the specified primary key 1022.

[0192] Case 2: Some columns in the first data table without a primary key are deleted, and the deleted columns overlap with the candidate columns, or the data type of the candidate columns changes.

[0193] Since the hash data in the hash column of the second data table is calculated based on the data in the candidate column, the deletion of the candidate column, or the change of the data type of the candidate column (such as from int to string), will directly affect the hash column (the change of data type will affect the generated hash value), and thus affect the availability of the primary key of the second data table.

[0194] To ensure the availability of the primary key in the second data table after the data table structure is updated, if some columns in the first data table are deleted during the structure update, the data synchronization device needs to determine whether the deleted columns include the column used to determine the composite primary key. If not, the table structure of the second data table is updated according to the normal procedure; if it does, primary key replacement is required.

[0195] Alternatively, if the data type of some columns in the first data table changes, the data synchronization device determines whether the columns whose data type has changed include candidate columns. If not, the data type of the columns is updated according to the normal process; if so, the primary key needs to be replaced.

[0196] As shown in Figure 11, the table structure update process for the second data table may include the following steps:

[0197] Step 1101: If at least one candidate column is deleted in whole or in part, or if the data type of at least one candidate column changes, then at least one candidate column is re-determined from the columns contained in the first data table.

[0198] If all or part of the candidate columns used to determine hash data are deleted, or if the data type of at least one candidate column changes, the hash data of the hash column will be invalid. Therefore, the data synchronization device needs to re-determine at least one candidate column corresponding to the first data table.

[0199] In one possible implementation, the data synchronization device maintains candidate columns for determining hash data in each keyless data table. When table structure update data for a keyless data table is read, and this update indicates a deleted column, or a change in the data type of a column, the data synchronization device further checks whether the deleted column, or the column with the changed data type, intersects with the corresponding candidate columns of the keyless data table. If an intersection exists, the candidate columns for the keyless data table are re-determined. Further, based on the data from the re-determined candidate columns, the data synchronization device determines the first primary key data.

[0200] The process of re-determining candidate columns can refer to the above-mentioned embodiment for selecting candidate columns, which will not be elaborated here.

[0201] As illustrated in Figure 12, the first data table 1210 is initially a data table without a primary key. During the synchronization of the first data table 1210, the data synchronization device creates a second data table 1220 in the second database. This second data table 1220 includes a composite primary key column consisting of a hash column 1221 and an auto-incrementing column 1222. The hash data in the hash column 1221 is the result of hash operations on the group column and the user column data. When the group column in the first data table 1210 is deleted, the data synchronization device determines that the hash column 1221 is affected, and therefore re-selects the user column and the score column as candidate columns.

[0202] It should be noted that if the data type of a candidate column changes, the newly determined candidate column may be the same as the original candidate column, meaning that the primary key of the second data table did not change before and after the data type change.

[0203] Step 1102: Create a temporary data table in the second database. The temporary data table does not contain the columns deleted from the first data table, but contains hash columns and auto-increment columns.

[0204] Since the second data table in the second database contains the original composite primary key column and the column that was deleted from the first data table, when updating the table structure of the second data table, the data synchronization device instructs the second database to create a temporary data table containing hash columns and auto-increment columns, but not containing the deleted columns, so that data can be imported into the temporary data table later.

[0205] The temporary data table has a different name than the second data table. For example, if the second data table is named t1, the temporary data table is named t1_temp.

[0206] Optionally, the data synchronization device instructs the second server to create a temporary data table in the second database by replaying database commands.

[0207] As illustrated in Figure 12, when the table structure update data characterizes the deletion of the group column in the first data table 1210, the data synchronization device creates a temporary data table in the second database containing a user column, a score column, a hash column, and an auto-increment column.

[0208] Step 1103: Obtain the original table data of the second data table.

[0209] Step 1104: Delete the first primary key data in the original table data, and add the newly determined first primary key data based on the original table data and at least one newly determined candidate column to obtain the cleaned table data.

[0210] After the temporary data table is created, the data synchronization device connects to the second database, retrieves the original table data from the second data table, deletes the composite primary key data (the first primary key data of the hash column and the second primary key data of the auto-increment column) from the original table data, and deletes the data corresponding to the deleted columns from the first data table.

[0211] In addition to deleting some columns from the original table data, the data synchronization device also needs to redetermine the first primary key data of the hash column based on the data of the newly selected candidate columns so that it can be added to the hash column later. Since deleting some columns does not affect the auto-incrementing row sequence number, the second primary key data in the original table data can remain unchanged.

[0212] As illustrated in Figure 12, after obtaining the original table data of the second data table 1220, the data synchronization device deletes the data in the hash column 1221 and the data in the group column; the data synchronization device determines the hash operation result of the user column and the score column as the first primary key data, and obtains the cleaned table data containing the user column, the score column, the hash column and the auto-increment column.

[0213] Step 1105: Write the cleaned table data into a temporary data table.

[0214] Furthermore, the data synchronization device writes the cleaned table data into a temporary data table by replaying database commands.

[0215] Step 1106: Delete the second data table and rename the temporary data table to the second data table.

[0216] After the data writing is completed, the data synchronization device instructs the second database to delete the second data table and rename the temporary data table to the second data table, that is, update the table name of the temporary data table to the table name of the second data table.

[0217] As illustrated in Figure 12, after the column deletion and primary key replacement are completed, the second data table 1220 still contains the hash column 1221 and the auto-increment column 1222, but the first primary key data contained in the hash column 1221 is different from the first primary key data contained in the hash column 1221 before the column deletion.

[0218] Case 3: Delete the primary key from the first data table that has a primary key.

[0219] The scenarios 1 and 2 described above are special handling procedures when the first data table is initially a data table without a primary key. When the first data table is initially a data table with a primary key, during data synchronization, the data synchronization device does not need to generate additional primary key data. Instead, it writes the data to be synchronized into a third data table in the second database. The third data table has the same primary key as the first data table; that is, the third data table is completely identical to the first data table.

[0220] When a user deletes the primary key of the first data table during use, in addition to deleting the primary key of the third data table, the data synchronization device needs to re-add the primary key to the third data table to ensure its availability. As shown in Figure 13, this process may include the following steps:

[0221] Step 1301: If the primary key of the first data table representing the table structure update is deleted, a temporary data table is created in the second database. The temporary data table contains a hash column and an auto-increment column.

[0222] Specifically, if the primary key column of the first data table is deleted, the primary key of the first data table is deleted; or, if a drop primary key command is received for the primary key of the first data table (the data in the primary key column is still retained), the primary key of the first data table is deleted.

[0223] In one possible implementation, in order to ensure that the third data table still has a primary key after the table structure is updated, the data synchronization device needs to create a temporary data table containing a composite primary key column (hash column and auto-increment column) so that the original table data and composite primary key data in the third data table can be imported into the temporary data table later.

[0224] The temporary data table has a different name than the third data table. For example, the third data table is named t1, and the temporary data table is named t1_temp.

[0225] Optionally, the data synchronization device instructs the second server to create a temporary data table in the second database by replaying database commands.

[0226] As illustrated in Figure 14, the initial primary key column of the first data table 1410 is the ID column. Correspondingly, after data synchronization, the initial primary key column of the third data table 1420 in the second database is also the ID column. When a user deletes the primary key in the ID column of the first data table 1410, the data synchronization device reads the table structure update data of the first data table from the database log and determines that the primary key of the first data table has been deleted, thereby creating a temporary data table containing the ID column, group column, user column, score column, hash column, and auto-increment column.

[0227] It should be noted that in other possible implementations, the data synchronization device may also create a single primary key column in the third data table and add primary key data to the single primary key column; this embodiment does not limit this.

[0228] Step 1302: Obtain the original table data of the third data table, and determine at least one candidate column from the columns contained in the third data table.

[0229] Similar to the synchronization process for a data table without a primary key, the data synchronization device acquires the original table data of the third data table and determines at least one candidate column for calculating the hash data. The process of determining the candidate column can be referred to in the above embodiment, and will not be repeated here.

[0230] As illustrated in Figure 14, the data synchronization device identifies the group column and user column in the third data table 1420 as candidate columns for calculating hash data. Of course, in other possible ways, the data synchronization device could also identify the original primary key column (ID column) as a candidate column; this embodiment does not limit this approach.

[0231] Step 1303: For each row of data in the original table, determine the hash result of the data in at least one candidate column in the row as the first primary key data corresponding to the row.

[0232] Step 1304: Determine the auto-incrementing row number corresponding to the row data as the second primary key data corresponding to the row data.

[0233] Furthermore, the data synchronization device extracts data from candidate columns from the row data and performs a hash operation on the extracted data, thereby determining the hash operation result as the first primary key data corresponding to the row data. In addition, to ensure the uniqueness of the primary key data, the data synchronization device assigns an auto-incrementing row number to each row data, and determines the auto-incrementing row number as the second primary key data corresponding to the row data.

[0234] The specific process of calculating the first primary key data and determining the second primary key data can be referred to in the above embodiments, and will not be repeated here.

[0235] As illustrated in Figure 14, the data synchronization device determines the first primary key data based on the hash results of the data in the group and user columns, and determines the auto-incrementing row number of each row as the second primary key data.

[0236] Step 1305: Write the original table data, the first primary key data, and the second primary key data into a temporary data table.

[0237] After determining the composite primary key data, the data synchronization device writes the original table data, the first primary key data, and the second primary key data into a temporary data table by replaying database commands.

[0238] It should be noted that if the primary key column in the first data table is deleted, the data synchronization device still needs to perform data cleaning on the original table data, that is, delete the data in the primary key column. This will not be elaborated on in this embodiment.

[0239] Step 1306: Delete the third data table and rename the temporary data table to the third data table.

[0240] After the data writing is completed, the data synchronization device instructs the second database to delete the third data table and rename the temporary data table to the third data table, that is, update the name of the temporary data table to the name of the third data table.

[0241] As illustrated in Figure 14, after the table structure update is completed, the hash column 1421 and the auto-increment column 1422 in the third data table 1420 are composite primary key columns of the third data table 1420.

[0242] In this embodiment, when a primary key is specified in the first data table that originally had no primary key, the data synchronization device ensures the consistency of primary keys in the primary key data tables between different databases by canceling the original primary key in the second data table and adding the specified primary key in the first data table (i.e., performing primary key replacement).

[0243] Furthermore, when deleting some columns in the first data table without a primary key, and the deleted columns affect the hash data calculation of the hash column in the second data table, the data synchronization device re-determines the hash column used to calculate the hash data, ensuring the validity of the hash data in the hash column, and thus ensuring the availability of the composite primary key in the second data table after the table structure is updated.

[0244] In addition, when the primary key of the first data table with a primary key is deleted, the data synchronization device automatically generates a composite primary key column consisting of a hash column and an auto-increment column for the third data table, so that the primary key can still be used to speed up the operation of the third data table.

[0245] Data consistency of tables without primary keys during hot migration

[0246] During the full or incremental data synchronization phase, when writing the data to be synchronized and the primary key data using the data command playback method, the data synchronization device generates database commands based on the data to be synchronized and the primary key data. Based on these database commands, the data to be synchronized and the primary key data are then written to the second data table in the second database. Each row in the second data table contains the corresponding database command containing the data to be synchronized for that row, as well as the primary key data located in the primary key column.

[0247] In the hot migration scenario, as shown in Figure 15, since incremental data synchronization is also performed during the full data synchronization process, there will be overlap between the full data and incremental data synchronization.

[0248] For tables with primary keys, the database can use the primary key to ensure the idempotency of adding, deleting, modifying, and querying data. Therefore, repeatedly executing database commands within overlapping time periods can also ensure data consistency between the destination database and the source database.

[0249] However, for tables without primary keys, the database cannot guarantee the idempotency of CRUD operations using primary keys, and it cannot determine whether a database statement has been executed. Therefore, it is difficult to guarantee the consistency of data between databases.

[0250] For example, during the full data synchronization phase, a row of data has already been added to the second data table based on a database command; however, during the incremental data synchronization phase, which overlaps with the full data synchronization, if the database command is executed again, it will result in two duplicate rows of data in the second data table.

[0251] To ensure data consistency in non-keyed tables during hot migration, the data synchronization device needs to add time point and database command type checks during the incremental data synchronization phase. The process of generating database commands during the incremental data synchronization phase may include the following steps.

[0252] Step 1: In the incremental data synchronization phase, if the data to be synchronized is the data to be inserted, determine the insertion time point corresponding to the data to be synchronized.

[0253] During the incremental data synchronization phase, the data synchronization device identifies the type of data to be synchronized. If the data to be synchronized is data to be inserted, to avoid the same data being repeatedly inserted into the second data table, the data synchronization device determines the insertion point corresponding to that data.

[0254] Among them, the data synchronization device can determine the timestamp of the log record corresponding to the data to be synchronized in the database log as the insertion time point.

[0255] Optionally, when the type of data to be synchronized in the database log is Insert, the data synchronization device determines that the data to be synchronized is data to be inserted.

[0256] In one possible implementation, during the full data synchronization phase, the data synchronization device records the start and end times of the full data synchronization. If the insertion time point is between the start and end times, it indicates that the insertion of the data to be synchronized occurred within the synchronization overlap period, posing a risk of duplicate insertion; if the insertion time point is not between the start and end times, it indicates that the insertion of the data to be synchronized occurred outside the synchronization overlap period, posing no risk of duplicate insertion.

[0257] Step 2: If the insertion time point is between the start and end time points of the full data synchronization phase, generate an update command based on the data to be synchronized and the primary key data.

[0258] When the insertion time point falls between the start and end time points of the full data synchronization phase, in order to avoid inserting duplicate data to be synchronized into the second database during the full and incremental data synchronization phases, the data synchronization device will not generate an insert command based on the data to be synchronized and the primary key data, but will instead generate an update command.

[0259] In some embodiments, the update command is an upsert (update insert) command.

[0260] When the second database executes the update command, if the data to be synchronized has not been inserted during the full data synchronization phase (at this time, the data to be synchronized does not exist in the second database), then the data to be synchronized is inserted into the second data table based on the update command; if the data to be synchronized has been inserted during the full data synchronization phase (at this time, the data to be synchronized exists in the second database), then the row data is updated based on the update command (since the data is the same, the row data will not change).

[0261] Step 3: If the insertion time point is not between the start and end time points of the full data synchronization phase, generate an insertion command based on the data to be synchronized and the primary key data.

[0262] If the insertion time point is not between the start and end time points of the full data synchronization phase, the data synchronization device generates an insertion command based on the data to be synchronized and the primary key data, so that the second database inserts the data to be synchronized into the second data table based on the insertion command.

[0263] It should be noted that for DML commands of types other than data insertion, the data synchronization device does not need to change the command type. For example, in the incremental data synchronization phase, if the data to be synchronized is data to be deleted, even if the deletion time of the data to be deleted is between the start and end time of the full synchronization phase, the data synchronization device will still generate a deletion command based on the data to be synchronized and the primary key data (even if it has already been deleted in the full synchronization phase, repeatedly executing the deletion command will not affect data consistency).

[0264] In one possible implementation, as shown in Figure 16, during the incremental data synchronization phase, the data synchronization device first obtains the database log, determines the synchronization time point of incremental data synchronization (1601), and parses the log through a syntax parser (1602).

[0265] Based on the log parsing results, the data synchronization device determines whether the data table to be synchronized belongs to a primary key table (1603). If it is a primary key table, the data synchronization is performed according to the standard primary key table synchronization process.

[0266] If the data table has no primary key and it is a table creation command (1604), the data synchronization device selects the candidate column for calculating the hash data and obtains the latest value of the auto-increment column (1605). Based on the hash calculation result of the candidate column and the latest value of the auto-increment column, the table creation command (1606) is modified to create a data table with the auto-increment column and the hash column as composite primary key columns.

[0267] If the data table has no primary key and the command is a DDL command (1607), the data synchronization device further determines whether the DDL command is a special DDL command (1608) (special DDL commands include adding or deleting primary keys, deleting candidate columns, etc.). If it is a special DDL command, the data table reconstruction logic is executed (1609) (the three cases in the above embodiments); if it is not a special DDL command, the data table structure is updated according to the conventional data table structure update process.

[0268] If the data table has no primary key and the command is a DML command (1610), the data synchronization device determines whether the synchronization time point is within the synchronization overlap period (1611) (the overlap period for full and incremental data synchronization). If it is outside the synchronization overlap period, the data in the data table is updated according to the regular data update process based on the DML command.

[0269] If the data is within a synchronization overlap period, the data synchronization device further determines whether the DML command is an insert command (1612). If it is an insert command, it is converted into an update command and executed (1613); if it is not an insert command, the data in the data table is updated according to the regular data update process based on the DML command.

[0270] In this embodiment, during the incremental data synchronization phase, the data synchronization device determines the data insertion instruction located in the full data synchronization phase by adding time points and database command type judgment strategies, and modifies the data insertion instruction into a data update instruction to avoid duplicate insertion of the same row of data in both the full data and incremental data synchronization phases, thus ensuring data consistency of the non-primary key data table in the hot migration scenario.

[0271] Figure 17 is a structural block diagram of a data synchronization device provided in an exemplary embodiment of this application. The device includes:

[0272] The reading module 1701 is used to obtain the data to be synchronized from the first data table in the first database;

[0273] Processing module 1702 is used to generate primary key data based on the data to be synchronized when the first data table is a data table without a primary key.

[0274] The writing module 1703 is used to write the data to be synchronized and the primary key data into a second data table in a second database. The second data table is a primary key data table, and the primary key data is located in the primary key column of the second data table.

[0275] Optionally, processing module 1702 is used for:

[0276] A composite primary key is generated based on the data to be synchronized. The composite primary key includes a first primary key and a second primary key. The first primary key is hash data located in the hash column of the second data table, and the second primary key is auto-increment data located in the auto-increment column of the second data table.

[0277] Optionally, processing module 1702 is used for:

[0278] For each row of data in the data to be synchronized, the hash operation result of the data in the row that is located in at least one candidate column is determined as the first primary key data corresponding to the row. The at least one candidate column is determined from the columns contained in the first data table.

[0279] The auto-incrementing row number corresponding to the row data is determined as the second primary key data corresponding to the row data;

[0280] The first primary key data and the second primary key data are determined as the composite primary key data of the row data.

[0281] Optionally, the processing module 1702 is also used for:

[0282] Determine the data duplication rate of each column in the first data table;

[0283] Based on the data duplication rate, at least one candidate column is determined from the columns contained in the first data table, wherein the data duplication rate of the data in the at least one candidate column is lower than the data duplication rate of the data in other columns besides the at least one candidate column in the first data table.

[0284] Optionally, the device further includes a data update module for:

[0285] Read row change data from the database log of the first database, the row change data being used to characterize the row data changes of the first changed row in the first data table;

[0286] Based on the row change data and the first primary key data in the second data table, determine the second change row corresponding to the first change row in the second data table;

[0287] Update the row data of the second changed row in the second data table based on the row change data.

[0288] Optional, a data update module is used for:

[0289] Determine the row preceding the change in the first changed row of the row change data;

[0290] Based on the hash operation result of the data located in the at least one candidate column in the previous row data, and the matching result of the first primary key data in the second data table, candidate rows are determined from the second data table, and the first primary key data of the candidate rows matches the hash operation result;

[0291] The second changed row is determined from the candidate rows.

[0292] Optional, a data update module, used for:

[0293] If a candidate row exists, the candidate row is determined as the second changed row;

[0294] In the presence of at least two candidate rows, the second changed row is determined from the at least two candidate rows based on the row data before the change and the row data of the candidate rows, wherein the row data of the second changed row matches the row data before the change.

[0295] Optional, a data update module, used for:

[0296] Determine the changed row data of the first changed row in the row change data;

[0297] The row data of the second changed row in the second data table is updated based on the changed row data.

[0298] Optionally, the device further includes a structural update module for:

[0299] Read table structure update data from the database log of the first database, the table structure update data being used to characterize the changes in the table structure of the first data table;

[0300] Update the data based on the table structure, and update the table structure of the second data table in the second database.

[0301] Optionally, if the first data table is a data table without a primary key, the structure update module is used to:

[0302] When the table structure update data indicates that a specified primary key has been added to the first data table, a temporary data table is created in the second database. The temporary data table does not contain the hash column and the auto-increment column.

[0303] Obtain the original table data of the second data table;

[0304] Delete the first primary key data and the second primary key data from the original table data to obtain the cleaned table data;

[0305] Write the cleaned table data into the temporary data table;

[0306] Delete the second data table and rename the temporary data table to the second data table;

[0307] Set the specified primary key for the second data table.

[0308] Optionally, if the first data table is a data table without a primary key, the structure update module is further used for:

[0309] If the table structure update data indicates that at least one candidate column has been completely or partially deleted, or if the data type of at least one candidate column has changed, the at least one candidate column shall be re-determined from the columns contained in the first data table;

[0310] A temporary data table is created in the second database. The temporary data table does not contain the columns deleted from the first data table, but contains the hash column and the auto-increment column.

[0311] Obtain the original table data of the second data table;

[0312] Delete the first primary key data from the original table data, and add the re-determined first primary key data based on the original table data and the re-determined at least one candidate column to obtain the cleaned table data;

[0313] Write the cleaned table data into the temporary data table;

[0314] Delete the second data table and rename the temporary data table to the second data table.

[0315] Optionally, if the first data table is a primary key data table, the write module 1703 is further configured to:

[0316] The data to be synchronized is written into a third data table in the second database, and the third data table has the same primary key as the first data table.

[0317] The structure update module is also used for:

[0318] In the event that the primary key of the first data table is deleted in the table structure update data representation, a temporary data table is created in the second database, the temporary data table containing the hash column and the auto-increment column;

[0319] Obtain the original table data of the third data table, and determine the at least one candidate column from the columns contained in the third data table;

[0320] For each row of data in the original table data, the hash operation result of the data in the row data located in at least one candidate column is determined as the first primary key data corresponding to the row data;

[0321] The auto-incrementing row number corresponding to the row data is determined as the second primary key data corresponding to the row data;

[0322] Write the original table data, the first primary key data, and the second primary key data into the temporary data table;

[0323] Delete the third data table and rename the temporary data table to the third data table.

[0324] Optionally, the processing module 1702 is also used for:

[0325] When in the breakpoint resume state, obtain the maximum row number of the auto-incrementing column in the second data table; using the maximum row number as the starting point, determine the auto-incrementing row number corresponding to each row of data in the data to be synchronized.

[0326] The processing module 1702 is also used for:

[0327] In the case of non-breakpoint resume transmission, the auto-incrementing row number corresponding to each row of data in the data to be synchronized is determined, starting from the initial row number.

[0328] Optionally, the write module 1703 is used for:

[0329] Generate database commands based on the data to be synchronized and the primary key data;

[0330] Based on the database command, the data to be synchronized and the primary key data are written into the second data table in the second database.

[0331] Optionally, the write module 1703 is used for:

[0332] During the incremental data synchronization phase, if the data to be synchronized is data to be inserted, the insertion time point corresponding to the data to be synchronized is determined.

[0333] If the insertion time point is between the start and end time points of the full data synchronization phase, an update command is generated based on the data to be synchronized and the primary key data.

[0334] If the insertion time point is not located between the start and end time points of the full data synchronization phase, an insertion command is generated based on the data to be synchronized and the primary key data.

[0335] Optionally, the first database is an OLTP database, and the second database is an OLAP database.

[0336] In summary, in this embodiment, when the data synchronization device synchronizes data between the first database and the second database, if the data to be synchronized belongs to a table without a primary key in the first database, the data synchronization device automatically generates primary key data based on the data to be synchronized and writes the data to be synchronized and the primary key data together into a table with a primary key in the second database. This achieves data synchronization of tables without primary keys without changing the table structure of the tables without primary keys in the first database, enabling subsequent queries on tables without primary keys in the first database within the second database, thus improving database availability. Furthermore, since the tables in the second database are tables with primary keys, subsequent operations on the tables can be performed using the primary keys to improve efficiency.

[0337] Please refer to Figure 18, which shows a schematic diagram of the structure of a computer device provided in an exemplary embodiment of this application. This computer device can be implemented as a data synchronization device in the various embodiments described above.

[0338] Computer device 1800 includes a processing unit 1801, a system memory 1804 including random access memory 1802 and read-only memory 1803, and a system bus 1805 connecting the system memory 1804 and the processing unit 1801. Computer device 1800 also includes a basic input / output system (I / O system) 1806 to facilitate information transfer between various devices within the computer, and a mass storage device 1807 for storing the operating system 1813, application programs 1814, and other program modules 1815. The processing unit 1801 may include a CPU and a GPU.

[0339] The basic input / output system 1806 includes a display 1808 for displaying information and an input device 1809 for user input, such as a mouse or keyboard. Both the display 1808 and the input device 1809 are connected to the processing unit 1801 via an input / output controller 1810 connected to the system bus 1805. The basic input / output system 1806 may also include the input / output controller 1810 for receiving and processing input from multiple other devices such as a keyboard, mouse, or electronic stylus. Similarly, the input / output controller 1810 also provides output to a display screen, printer, or other types of output devices.

[0340] The mass storage device 1807 is connected to the processing unit 1801 via a mass storage controller (not shown) connected to the system bus 1805. The mass storage device 1807 and its associated computer-readable media provide non-volatile storage for the computer device 1800. That is, the mass storage device 1807 may include computer-readable media (not shown) such as a hard disk or drive.

[0341] Without loss of generality, the computer-readable medium may include computer storage media and communication media. Computer storage media include volatile and non-volatile, removable and non-removable media implemented using any method or technology for storing information such as computer-readable instructions, data structures, program modules, or other data. Computer storage media include random access memory (RAM), read-only memory (ROM), flash memory or other solid-state storage technologies, compact disc read-only memory (CD-ROM), digital versatile disc (DVD) or other optical storage, magnetic tape cassettes, magnetic tape, disk storage, or other magnetic storage devices. Of course, those skilled in the art will recognize that the computer storage media are not limited to the above-mentioned types. The system memory 1804 and mass storage device 1807 described above can be collectively referred to as memory.

[0342] The memory stores one or more programs, which are configured to be executed by one or more processing units 1801. The one or more programs contain instructions for implementing the methods described above, and the processing unit 1801 executes the one or more programs to implement the methods provided in the various method embodiments described above.

[0343] According to various embodiments of this application, the computer device 1800 can also be connected to a remote computer on a network, such as the Internet. That is, the computer device 1800 can be connected to the network 1812 via the network interface unit 1811 connected to the system bus 1805, or the network interface unit 1811 can be used to connect to other types of networks or remote computer systems (not shown).

[0344] This application also provides a computer-readable storage medium storing at least one instruction, which is loaded and executed by a processor to implement the data synchronization method provided in the above embodiments.

[0345] Optionally, the computer-readable storage medium may include ROM, RAM, solid-state drives (SSDs), or optical discs, etc. The RAM may include resistive random access memory (ReRAM) and dynamic random access memory (DRAM).

[0346] This application provides a computer program product including computer instructions stored in a computer-readable storage medium. A processor of a computer device reads the computer instructions from the computer-readable storage medium and executes the computer instructions, causing the computer device to perform the data synchronization method described in the above embodiments.

[0347] Those skilled in the art will understand that all or part of the steps of the above embodiments can be implemented by hardware or by a program instructing related hardware. The program can be stored in a computer-readable storage medium, such as a read-only memory, a disk, or an optical disk.

[0348] The above description is merely an optional embodiment of this application and is not intended to limit this application. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of this application should be included within the protection scope of this application.

Claims

1. A data synchronization method, the method being executed by a computer device, the method comprising: Retrieve the data to be synchronized from the first table in the first database; If the first data table is a data table without a primary key, primary key data is generated based on the data to be synchronized; The data to be synchronized and the primary key data are written into a second data table in a second database. The second data table is a primary key data table, and the primary key data is located in the primary key column of the second data table.

2. The method of claim 1, wherein, The generation of primary key data based on the data to be synchronized includes: A composite primary key is generated based on the data to be synchronized. The composite primary key includes a first primary key and a second primary key. The first primary key is hash data located in the hash column of the second data table, and the second primary key is auto-increment data located in the auto-increment column of the second data table.

3. The method of claim 2, wherein, The generation of composite primary key data based on the data to be synchronized includes: For each row of data in the data to be synchronized, the hash operation result of the data in the row that is located in at least one candidate column is determined as the first primary key data corresponding to the row. The at least one candidate column is determined from the columns contained in the first data table. The auto-incrementing row number corresponding to the row data is determined as the second primary key data corresponding to the row data; The first primary key data and the second primary key data are determined as the composite primary key data of the row data.

4. The method of claim 3, wherein, The method further includes: Determine the data duplication rate of each column in the first data table; Based on the data duplication rate, at least one candidate column is determined from the columns contained in the first data table, wherein the data duplication rate of the data in the at least one candidate column is lower than the data duplication rate of the data in other columns besides the at least one candidate column in the first data table.

5. The method of claim 3, wherein, The method further includes: Read row change data from the database log of the first database, the row change data being used to characterize the row data changes of the first changed row in the first data table; Based on the row change data and the first primary key data in the second data table, determine the second change row corresponding to the first change row in the second data table; Update the row data of the second changed row in the second data table based on the row change data.

6. The method of claim 5, wherein, Determining the second changed row in the second table corresponding to the first changed row based on the row change data and the first primary key data in the second data table includes: Determine the row preceding the change in the first changed row of the row change data; Based on the hash operation result of the data located in the at least one candidate column in the previous row data, and the matching result of the first primary key data in the second data table, candidate rows are determined from the second data table, and the first primary key data of the candidate rows matches the hash operation result; The second changed row is determined from the candidate rows.

7. The method of claim 6, wherein determining the second changed row from the candidate rows comprises: If a candidate row exists, the candidate row is determined as the second changed row; In the presence of at least two candidate rows, the second changed row is determined from the at least two candidate rows based on the row data before the change and the row data of the candidate rows, wherein the row data of the second changed row matches the row data before the change.

8. The method of claim 5, wherein, The step of updating the row data of the second changed row in the second data table based on the row change data includes: Determine the changed row data of the first changed row in the row change data; The row data of the second changed row in the second data table is updated based on the changed row data.

9. The method according to any one of claims 3 to 8, wherein, The method further includes: Read table structure update data from the database log of the first database, the table structure update data being used to characterize the changes in the table structure of the first data table; Update the data based on the table structure, and update the table structure of the second data table in the second database.

10. The method of claim 9, wherein, When the first data table is a data table without a primary key, updating the data based on the table structure and updating the table structure of the second data table in the second database includes: When the table structure update data indicates that a specified primary key has been added to the first data table, a temporary data table is created in the second database. The temporary data table does not contain the hash column and the auto-increment column. Obtain the original table data of the second data table; Delete the first primary key data and the second primary key data from the original table data to obtain the cleaned table data; Write the cleaned table data into the temporary data table; Delete the second data table and rename the temporary data table to the second data table; Set the specified primary key for the second data table.

11. The method of claim 9, wherein, When the first data table is a data table without a primary key, updating the data based on the table structure and updating the table structure of the second data table in the second database includes: If the table structure update data indicates that at least one candidate column has been completely or partially deleted, or if the data type of at least one candidate column has changed, the at least one candidate column shall be re-determined from the columns contained in the first data table; A temporary data table is created in the second database. The temporary data table does not contain the columns deleted from the first data table, but contains the hash column and the auto-increment column. Obtain the original table data of the second data table; Delete the first primary key data from the original table data, and add the re-determined first primary key data based on the original table data and the re-determined at least one candidate column to obtain the cleaned table data; Write the cleaned table data into the temporary data table; Delete the second data table and rename the temporary data table to the second data table.

12. The method of claim 9, wherein, The method further includes: If the first data table is a data table with a primary key, the data to be synchronized is written into a third data table in the second database, and the third data table has the same primary key as the first data table. The step of updating the data based on the table structure, and updating the table structure of the second data table in the second database, includes: In the event that the primary key of the first data table is deleted in the table structure update data representation, a temporary data table is created in the second database, the temporary data table containing the hash column and the auto-increment column; Obtain the original table data of the third data table, and determine the at least one candidate column from the columns contained in the third data table; For each row of data in the original table data, the hash operation result of the data in the row data located in at least one candidate column is determined as the first primary key data corresponding to the row data; The auto-incrementing row number corresponding to the row data is determined as the second primary key data corresponding to the row data; Write the original table data, the first primary key data, and the second primary key data into the temporary data table; Delete the third data table and rename the temporary data table to the third data table.

13. The method of any one of claims 3 to 12, wherein, The method further includes: When in the interrupted resume state, obtain the maximum row number of the auto-incrementing column in the second data table; Starting from the maximum row number, determine the auto-incrementing row number corresponding to each row of data in the data to be synchronized.

14. The method of any one of claims 3 to 12, wherein, The method further includes: In the case of non-breakpoint resume transmission, the auto-incrementing row number corresponding to each row of data in the data to be synchronized is determined, starting from the initial row number.

15. The method of any one of claims 1 to 14, wherein, The step of writing the data to be synchronized and the primary key data into the second data table of the second database includes: Generate database commands based on the data to be synchronized and the primary key data; Based on the database command, the data to be synchronized and the primary key data are written into the second data table in the second database.

16. The method of claim 15, wherein, The process of generating database commands based on the data to be synchronized and the primary key data includes: During the incremental data synchronization phase, if the data to be synchronized is data to be inserted, the insertion time point corresponding to the data to be synchronized is determined. If the insertion time point is between the start and end time points of the full data synchronization phase, an update command is generated based on the data to be synchronized and the primary key data. If the insertion time point is not located between the start and end time points of the full data synchronization phase, an insertion command is generated based on the data to be synchronized and the primary key data.

17. The method of any one of claims 1 to 16, wherein, The first database is an OLTP database, and the second database is an OLAP database.

18. A data synchronization device, the device comprising: The reading module is used to obtain the data to be synchronized from the first data table in the first database; The processing module is used to generate primary key data based on the data to be synchronized when the first data table is a data table without a primary key. The writing module is used to write the data to be synchronized and the primary key data into a second data table in a second database. The second data table is a primary key data table, and the primary key data is located in the primary key column of the second data table.

19. A computer device comprising a processor and a memory, the memory storing at least one computer instruction, the at least one computer instruction being loaded and executed by the processor to implement the data synchronization method as claimed in any one of claims 1 to 17.

20. A computer-readable storage medium storing at least one computer instruction, the at least one computer instruction being loaded and executed by a processor to implement the data synchronization method as described in any one of claims 1 to 17.

21. A computer program product comprising computer instructions stored in a computer-readable storage medium, wherein a processor retrieves the computer instructions from the computer-readable storage medium and executes the computer instructions to implement the data synchronization method as described in any one of claims 1 to 17.

Images