CN113590695A - Data transmission method, system and terminal based on trigger and main key - Google Patents
Data transmission method, system and terminal based on trigger and main key Download PDFInfo
- Publication number
- CN113590695A CN113590695A CN202110590666.7A CN202110590666A CN113590695A CN 113590695 A CN113590695 A CN 113590695A CN 202110590666 A CN202110590666 A CN 202110590666A CN 113590695 A CN113590695 A CN 113590695A
- Authority
- CN
- China
- Prior art keywords
- data
- transmission
- transmitted
- current data
- trigger
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
- 230000005540 biological transmission Effects 0.000 title claims abstract description 346
- 238000000034 method Methods 0.000 title claims abstract description 49
- 238000012856 packing Methods 0.000 claims abstract description 18
- 238000000605 extraction Methods 0.000 claims description 18
- 238000004806 packaging method and process Methods 0.000 claims description 17
- 238000012546 transfer Methods 0.000 claims description 4
- 239000000284 extract Substances 0.000 claims description 3
- 238000012545 processing Methods 0.000 description 4
- 230000009286 beneficial effect Effects 0.000 description 3
- 238000010586 diagram Methods 0.000 description 2
- 238000012986 modification Methods 0.000 description 2
- 230000004048 modification Effects 0.000 description 2
- 238000004891 communication Methods 0.000 description 1
- 238000012217 deletion Methods 0.000 description 1
- 230000037430 deletion Effects 0.000 description 1
- 230000000694 effects Effects 0.000 description 1
- 238000005516 engineering process Methods 0.000 description 1
- 230000006870 function Effects 0.000 description 1
- 238000000926 separation method Methods 0.000 description 1
- 238000012163 sequencing technique Methods 0.000 description 1
- 238000012795 verification Methods 0.000 description 1
Images
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/25—Integrating or interfacing systems involving database management systems
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Databases & Information Systems (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The application discloses a data transmission method, a system and a terminal based on a trigger and a main key, wherein the method comprises the following steps: establishing a trigger table and a transmission table; respectively establishing triggers in each data table; storing the data transmission information of each data table into a trigger table; in the packing thread, extracting the whole data of the current data to be transmitted from a data table to form a database statement according to the table name and the main key of the current data to be transmitted; storing database statements, table names of current data to be transmitted, main keys and operation time into a transmission table, and setting transmission marks according to the table names and the main keys; and in the sending thread, extracting the database statement from the transmission table and writing the database statement into a target database. The system comprises: the device comprises a first establishing module, a second establishing module, a first storing module, an extracting module, a second storing module and a writing module. The terminal includes: a processor and a memory communicatively coupled to the processor. By the method and the device, the accuracy and the fault tolerance of data transmission can be effectively improved.
Description
Technical Field
The present application relates to the field of data transmission technologies, and in particular, to a data transmission method, system and terminal based on a trigger and a primary key.
Background
In order to ensure the security of data or meet the requirements of data read-write separation, etc., it is a common operation to transmit data in a database.
The currently common data transmission methods mainly include two types: one is a data transmission method based on the redo log, and the other is a data transmission method based on the trigger. Because the database redo log is binary information, the analysis difficulty is higher, and the data transmission method based on the trigger is widely applied in China.
The data transmission method based on the trigger generally performs data transmission based on data operation, and is data modification operation of capturing and transmitting through the trigger. Specifically, the operation of each piece of data is executed according to the capture of the trigger and is transmitted to the destination library, and the operational relationship between the front data and the back data is not concerned. A trigger triggers an insert operation if the current data needs to perform an insert operation, i.e. insert, and a delete operation if the current data needs to perform a delete operation, i.e. delete.
However, in the current data transmission method based on the trigger, because data transmission is performed based on data operation, it is set that a piece of data first executes insert and then executes delete, and if the insert operation fails to be transmitted in the destination library because a certain field in the destination library cannot be empty or the character length is insufficient, but the delete operation is successfully executed in the destination library, the current piece of data is transmitted by the data, and then the insert operation is executed first, which finally causes the data of the destination library and the data of the source library to be different, thereby reducing the accuracy of data transmission.
Disclosure of Invention
The application provides a data transmission method, a system and a terminal based on a trigger and a main key, which aim to solve the problem that the data transmission accuracy is low due to the data transmission method based on the trigger in the prior art.
In order to solve the technical problem, the embodiment of the application discloses the following technical scheme:
a method of data transmission based on a trigger and a primary key, the method comprising:
establishing a trigger table and a transmission table in a source database, wherein the trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission;
respectively establishing triggers in each data table of a service system, wherein a plurality of data to be transmitted are stored in any data table;
storing data transmission information of each data table into a trigger table, wherein the data transmission information comprises: the table name, the primary key for adding, deleting and modifying data, the operation mark for adding, deleting and modifying operation and the operation time;
in a packing thread, when the current data to be transmitted exists in a trigger table, extracting the whole data of the current data to be transmitted from the data table according to the table name and the main key of the current data to be transmitted, and forming a database statement according to an operation mark;
storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table;
and in the sending thread, extracting a database statement from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statement into a target database.
Optionally, when it is found that there is data to be transmitted currently in the trigger table, according to the table name and the primary key of the data to be transmitted currently, extracting the whole data of the data to be transmitted currently from the data table, and forming a database statement according to the operation flag, including:
judging whether the trigger table has data to be transmitted currently or not through query;
if so, the packing thread extracts the data transmission information from the trigger table according to the operation time sequence;
extracting the whole data of the current data to be transmitted from the data table according to the table name and the primary key of the current data to be transmitted;
and forming a database statement according to the whole data of the current data to be transmitted and the operation mark.
Optionally, the method for storing the database statement, the table name of the current data to be transmitted, the primary key, and the operation time in a transmission table, and setting the transmission flag of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table includes:
judging whether the transmission table has the same data or not through query, wherein the same data is as follows: transmitting data which is the same as the table name and the primary key of the current data to be transmitted in the table;
if the same data exists in the transmission table, judging that the table name and the primary key of the current data to be transmitted have transmission failure records;
storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted as a transmission mark of the same data;
if the same data does not exist in the transmission table, judging that the table name and the primary key of the current data to be transmitted have no transmission failure record;
and storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into a transmission table, and setting the transmission mark of the current data to be transmitted to be 0.
Optionally, after saving the database statement, the table name of the current data to be transmitted, the primary key, and the operation time in a transmission table, and setting a transmission flag of the current data to be transmitted as a transmission flag of the same data, the method further includes:
the data currently to be transmitted is deleted from the trigger table.
Optionally, in the sending thread, according to the transmission flag and the operation time of the current data to be transmitted, extracting a database statement from the transmission table and writing the database statement into the destination database, including:
judging whether the transmission table has current data to be transmitted or not through inquiry;
if so, extracting a database statement from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statement into a target database;
judging whether the write operation of the current data to be transmitted is successful;
if the write operation fails, adding 1 to the transmission marks of all data in the transmission table, which are the same as the table name and the primary key of the current data to be transmitted;
if the write operation is successful, deleting the current data to be transmitted from the transmission table;
judging whether a transmission mark of the current data to be transmitted is greater than 0;
if the transmission mark is larger than 0, judging that the table name and the primary key of the current data to be transmitted have transmission failure records and the data repair is finished;
and setting the transmission marks of all data in the transmission table, which are the same as the table name and the primary key of the current data to be transmitted, to be 0.
Optionally, the packing thread and the sending thread perform data transmission in parallel, asynchronously, and circularly.
A trigger and primary key based data transmission system, the system comprising:
the device comprises a first establishing module, a second establishing module and a third establishing module, wherein the first establishing module is used for establishing a trigger table and a transmission table in a source database, the trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission;
the second establishing module is used for respectively establishing triggers in all data tables of the service system, wherein a plurality of data to be transmitted are stored in any one data table;
the first storage module is configured to store data transmission information of each data table into a trigger table, where the data transmission information includes: the table name, the primary key for adding, deleting and modifying data, the operation mark for adding, deleting and modifying operation and the operation time;
the extraction module is used for extracting the whole data of the current data to be transmitted from the data table according to the table name and the main key of the current data to be transmitted when the current data to be transmitted exists in the trigger table in the packing thread, and forming a database statement according to the operation mark;
the second storage module is used for storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table;
and the writing module is used for extracting the database statement from the transmission table and writing the database statement into the target database according to the transmission mark and the operation time of the current data to be transmitted in the sending thread.
Optionally, the extraction module comprises:
the first judgment unit is used for judging whether the trigger table has current data to be transmitted or not through inquiry;
the first extraction unit is used for extracting the data transmission information from the trigger table by the packaging thread according to the operation time sequence when the current data to be transmitted exists in the trigger table;
the second extraction unit is used for extracting the whole piece of data of the current data to be transmitted from the data table according to the table name and the primary key of the current data to be transmitted;
and the database statement forming unit is used for forming database statements according to the whole piece of data of the current data to be transmitted and the operation marks.
Optionally, the writing module includes:
the second judging unit is used for judging whether the current data to be transmitted exists in the transmission table or not through inquiry;
the third extraction unit is used for extracting database statements from the transmission table and writing the database statements into a target database according to the transmission mark and the operation time of the current data to be transmitted when the current data to be transmitted exists in the transmission table;
the third judging unit is used for judging whether the write-in operation of the current data to be transmitted is successful or not;
the first transmission mark updating unit is used for adding 1 to the transmission marks of all data which are identical to the table name and the primary key of the current data to be transmitted in the transmission table when the writing operation fails;
the first deleting unit is used for deleting the current data to be transmitted from the transmission table when the writing operation is successful;
the fourth judging unit is used for judging whether the transmission mark of the current data to be transmitted is larger than 0;
and the second transmission mark updating unit is used for judging that the table name and the primary key of the current data to be transmitted have transmission failure records and finish data repair when the transmission mark is larger than 0, and setting the transmission marks of all data, which are identical to the table name and the primary key of the current data to be transmitted, in the transmission table to be 0.
A terminal, the terminal comprising: a processor, and a memory communicatively coupled to the processor, wherein,
the memory has stored therein instructions executable by the processor to enable the processor to perform a trigger and primary key based data transfer method as described in any one of the above.
The technical scheme provided by the embodiment of the application can have the following beneficial effects:
the method comprises the steps of firstly establishing a trigger table and a transmission table in a source database, secondly respectively establishing triggers in each data table of a service system, then storing data transmission information of each data table into the trigger table, and then executing a data packing thread and a sending thread. When the trigger table is inquired to have the current data to be transmitted in the packaging thread, extracting the whole data from the data table according to the table name and the main key of the current data to be transmitted, and forming a database statement according to the operation mark; and similarly, storing the database statement, the table name of the current data to be transmitted, the main key and the operation time in the packing thread into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the main key of the existing data in the transmission table. And finally, in the sending thread, according to the transmission mark and the operation time of the current data to be transmitted, extracting a database statement from the transmission table and writing the database statement into a target database, thereby completing data transmission.
In this embodiment, to implement data transmission in different data tables of the service system, a trigger table and a transmission table respectively applied to the data packing thread and the sending thread are established. The data transmission information of the embodiment includes the table name and the main key of the data to be transmitted, the data transmission information of each data table is stored in the trigger table, and in a subsequent packaging thread, the corresponding whole data can be extracted from the data table according to the table name and the main key of the current data to be transmitted, and finally a database statement is formed, so that all operations of the current data to be transmitted can be serially connected according to a time sequence by taking the data as a core, the consistency of the data written into a target database in a writing thread and the data of a source database is ensured, and the data transmission accuracy is greatly improved.
In addition, in the embodiment, corresponding table names and main keys are set for any data table, a transmission mark of the current data to be transmitted is set in the packaging thread according to the table names and the main keys of the existing data in the transmission table, and different transmission marks can be set for transmission failure and transmission success by using the main keys, so that a verification function is realized, and an effective basis is provided for subsequent users to perform data processing. When transmission fails, because any data table is provided with an independent table name and a main key, the transmission process of other data cannot be influenced due to the failure of one data table, the transmission condition of the whole database cannot be influenced, and the fault tolerance of data transmission is high.
The present application further provides a data transmission system based on a trigger and a primary key, the system mainly comprising: the device comprises a first establishing module, a second establishing module, a first storing module, an extracting module, a second storing module and a writing module. The trigger table and the transmission table are established in the source database through the first establishing module and are respectively applied to a packaging thread and a sending thread of data transmission, the data transmission can be controlled in a targeted manner according to different threads, and the data transmission accuracy is improved. And respectively establishing triggers in each data table of the service system through a second establishing module. The data transmission information of each data table is stored in the trigger table through the first storage module, so that the table name and the main key of the data to be transmitted can be acquired in time when the data is inquired in the trigger. The extraction module and the second storage module run in the packing thread, and the whole data can be extracted from the data table according to the table name and the main key of the current data to be transmitted through the extraction module, so that all operations of the current data to be transmitted can be serially connected according to the time sequence by taking the data as a core, and the data operation is not taken as the core. The method can effectively ensure that the data written into the target database is consistent with the data of the source database, thereby greatly improving the accuracy of data transmission. The writing module runs in the data sending thread, checks the writing operation in the process of extracting the database statement from the transmission table and writing the database statement into the target database, and updates the transmission mark, thereby being beneficial to further improving the accuracy of data transmission.
The application also provides a terminal, and the terminal also has the corresponding technical effects of the data transmission method and the data transmission system based on the trigger and the main key, and the details are not repeated herein.
It is to be understood that both the foregoing general description and the following detailed description are exemplary and explanatory only and are not restrictive of the application.
Drawings
The accompanying drawings, which are incorporated in and constitute a part of this specification, illustrate embodiments consistent with the present application and together with the description, serve to explain the principles of the application.
In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings used in the description of the embodiments or the prior art will be briefly described below, and it is obvious for those skilled in the art that other drawings can be obtained according to the drawings without creative efforts.
Fig. 1 is a schematic flowchart of a data transmission method based on a trigger and a primary key according to an embodiment of the present application;
fig. 2 is a schematic structural diagram of a data transmission system based on a trigger and a primary key according to an embodiment of the present application.
Detailed Description
In order to make those skilled in the art better understand the technical solutions in the present application, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it is obvious that the described embodiments are only a part of the embodiments of the present application, and not all of the embodiments. All other embodiments, which can be derived by a person skilled in the art from the embodiments given herein without making any creative effort, shall fall within the protection scope of the present application.
For a better understanding of the present application, embodiments of the present application are explained in detail below with reference to the accompanying drawings.
Example one
Referring to fig. 1, fig. 1 is a schematic flowchart of a data transmission method based on a trigger and a primary key according to an embodiment of the present application. As shown in fig. 1, the data transmission method based on the trigger and the primary key in this embodiment mainly includes the following steps:
s1: and establishing a trigger table and a transmission table in the source database.
The trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission.
S2: and respectively establishing triggers in each data table of the service system.
Wherein, any one data table stores a plurality of data to be transmitted. The trigger table and the transmission table in this embodiment are two tables that are specially created in the source database to implement data transmission, and each data table is a table used for summarizing data to be transmitted in the service system, which is also called a service table. The trigger table and the transfer table are provided for transferring data in the data table.
S3: and storing the data transmission information of each data table into a trigger table.
Wherein the data transmission information includes: the table name, the primary key for adding and deleting the data, the operation mark for adding and deleting the operation and the operation time. And establishing a relation between the data to be transmitted and the trigger table by storing the data transmission information into the trigger table.
Through the steps S1-S3, the data transmission environment is built, and a foundation can be provided for subsequent data transmission.
With continued reference to fig. 1, after the data transmission information of each data table is saved in the trigger table, step S4 is executed: in the packing thread, when the trigger table is inquired that the current data to be transmitted exists, the whole data of the current data to be transmitted is extracted from the data table according to the table name and the main key of the current data to be transmitted, and a database statement is formed according to the operation mark.
The database statement, namely the sql statement, includes: insert statement, update statement, and delete statement. insert statement, update statement, upload statement, delete statement.
Specifically, step S4 includes:
s41: and judging whether the trigger table has the current data to be transmitted or not through inquiry.
If the trigger table has the current data to be transmitted, execute step S42: the packing thread extracts data transfer information from the trigger table in order of operation time.
S43: and extracting the whole data of the current data to be transmitted from the data table according to the table name and the primary key of the current data to be transmitted.
The fields in each data table are relatively fixed, the current data to be transmitted and the fields form a whole piece of data, and the whole piece of data can form a database statement according to the operation mark. For example: in the whole data insert values, insert is a table name, also called a field name, and values are data; in the entire data update set, update is a table name, and set is data.
According to the method for extracting the whole data through the data main key, all operations of the current data to be transmitted can be serially connected according to the time sequence by taking the data as a core, and compared with a trigger grabbing mode taking data operations as a center, the method can avoid that some operations are missed due to different data transmission requirements in a target database, so that the data written into the target database is consistent with the data in a source database, and the accuracy of data transmission is greatly improved.
S44: and forming a database statement according to the whole data of the current data to be transmitted and the operation mark.
With continued reference to FIG. 1, the packing thread further includes step S5: and storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table.
Specifically, step S5 includes the following processes:
s51: and judging whether the transmission table has the same data or not through inquiry.
The same data in this embodiment are defined as: and transmitting the data which is identical to the table name and the primary key of the current data to be transmitted in the table.
If the transmission table has the same data, go to step S52: and judging that the table name and the primary key of the current data to be transmitted have transmission failure records. That is, if the transmission table has the same data, it indicates that the current data to be transmitted has a fault, and there is a backlog of database statements of transmission failure, and the current data to be transmitted needs to be marked for subsequent processing.
S53: and storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into a transmission table, and setting the transmission mark of the current data to be transmitted as the transmission mark of the same data.
The transmission mark of the current data to be transmitted is set to be the transmission mark of the same data, so that the current data to be transmitted can be marked and filed, a basis is provided for subsequent data processing, and the data transmission accuracy is improved.
Further, after setting the transmission flag of the current data to be transmitted as the transmission flag of the same data, the method further includes step S56: the data currently to be transmitted is deleted from the trigger table.
When the data to be transmitted is judged to have a fault, the data to be transmitted is transmitted to the transmission table and the transmission mark is marked, and then the data to be transmitted is deleted in the trigger table, so that the trigger table can be cleaned in time, the resource utilization rate of the trigger table is improved, and the data transmission efficiency is improved.
If there is no identical data in the transmission table, step S54 is executed: and judging that the table name and the primary key of the current data to be transmitted have no transmission failure record. The current data to be transmitted is safe data, and no backlog of transmission failure database statements exist, so that subsequent data transmission can be carried out.
S55: and storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into a transmission table, and setting the transmission mark of the current data to be transmitted to be 0.
As can be seen from fig. 1, after the database statement, the table name of the current data to be transmitted, the primary key, and the operation time are stored in the transmission table, and the transmission flag of the current data to be transmitted is set according to the table name and the primary key of the existing data in the transmission table, step S6 is executed: and in the sending thread, extracting a database statement from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statement into a target database.
Specifically, step S6 includes the following processes:
s61: and judging whether the transmission table has the current data to be transmitted or not through inquiry.
If the transmission table has the current data to be transmitted, execute step S62: and extracting database statements from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statements into a target database.
In this embodiment, when a database statement is extracted from a transmission table and written into a destination database, the database statement is written according to a transmission flag of current data to be transmitted and operation time. The transmission marks and the operation time are primary and secondary two-level writing standards, secondary sequencing is adopted, firstly, classification is carried out according to the transmission marks, then classification is carried out under the same transmission marks according to the sequence of the operation time, and database statements are written into a target database in sequence.
S63: and judging whether the write operation on the current data to be transmitted is successful.
If the write operation fails, go to step S64: and adding 1 to the transmission marks of all data which are the same as the table name and the primary key of the current data to be transmitted in the transmission table.
That is, the number of write failures is counted and updated when the write operation fails, thereby providing feedback of data transmission conditions for the user. The embodiment adopts a mode of adding 1 to the transmission mark for marking.
If the write operation is successful, go to step S65: and deleting the current data to be transmitted from the transmission table.
By deleting the successfully written data in time, the transmission table can be cleared, and the data transmission efficiency is improved.
After the write operation is successful, step S66 is executed: and judging whether the transmission mark of the current data to be transmitted is greater than 0.
If the transmission flag is greater than 0, go to step S67: and judging that the table name and the primary key of the current data to be transmitted have transmission failure records and the data repair is finished.
S68: and setting the transmission marks of all data in the transmission table, which are the same as the table name and the primary key of the current data to be transmitted, to be 0.
By setting the transmission flag to 0, it is indicated that the data under the current table name and the primary key are both data that have been successfully transmitted.
In summary, the data transmission process of this embodiment is implemented by two threads, namely, a data packing thread and a data sending thread. The operation procedure of the packed thread includes steps S4 and S5, and the operation procedure of the send thread is step S6. The two threads run in parallel and asynchronously, and data transmission work is carried out circularly. The parallel operation is that: the packaging thread and the sending thread are both started, data packaging operation is executed firstly and then data sending operation is executed for a specific piece of data, and when the data packaging operation is executed, the sending thread always inquires to confirm whether packaged data are transmitted into a transmission table or not, so that the sending thread is also in a starting state.
According to the embodiment, by setting the data main key, the whole data related to the current data to be transmitted can be effectively taken out in the data transmission process, all operation information of the whole data is gathered, and the data transmission accuracy is greatly improved. In addition, in the embodiment, the data transmission flag is set through the data primary key, and corresponding processing and updating are performed on data with the same table name and primary key, which is beneficial to further improving the data transmission efficiency. And because any data table is provided with a corresponding table name and a corresponding primary key, the transmission process of other data cannot be influenced when one data table fails, and the transmission condition of the whole database cannot be influenced, so that the fault tolerance of data transmission is higher.
Example two
Referring to fig. 2 on the basis of the embodiment shown in fig. 1, fig. 2 is a schematic structural diagram of a data transmission system based on a trigger and a primary key according to an embodiment of the present application. As can be seen from fig. 2, the data transmission system based on the trigger and the primary key in this embodiment mainly includes: the device comprises a first establishing module, a second establishing module, a first storing module, an extracting module, a second storing module and a writing module.
The first establishing module is used for establishing a trigger table and a transmission table in a source database, wherein the trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission; the second establishing module is used for respectively establishing triggers in all data tables of the service system, wherein a plurality of data to be transmitted are stored in any data table; the first storage module is used for storing the data transmission information of each data table into the trigger table, and the data transmission information comprises: the table name, the primary key for adding, deleting and modifying data, the operation mark for adding, deleting and modifying operation and the operation time; the extraction module is used for extracting the whole data of the current data to be transmitted from the data table according to the table name and the main key of the current data to be transmitted when the current data to be transmitted exists in the trigger table in the packing thread, and forming a database statement according to the operation mark; the second storage module is used for storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into the transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the main key of the existing data in the transmission table; and the writing module is used for extracting the database statement from the transmission table and writing the database statement into the target database according to the transmission mark and the operation time of the current data to be transmitted in the sending thread.
Further, the extraction module comprises: the system comprises a first judging unit, a first extracting unit, a second extracting unit and a database statement forming unit. The first judging unit is used for judging whether the trigger table has current data to be transmitted or not through inquiry; the first extraction unit is used for extracting data transmission information from the trigger table by the packaging thread according to the operation time sequence when the current data to be transmitted exists in the trigger table; the second extraction unit is used for extracting the whole piece of data of the current data to be transmitted from the data table according to the table name and the primary key of the current data to be transmitted; and the database statement forming unit is used for forming database statements according to the whole piece of data of the current data to be transmitted and the operation marks.
The write module includes: the device comprises a second judgment unit, a third extraction unit, a third judgment unit, a first transmission mark updating unit, a first deletion unit, a fourth judgment unit and a second transmission mark updating unit. The second judging unit is used for judging whether the transmission table has current data to be transmitted or not through inquiry; the third extraction unit is used for extracting database statements from the transmission table and writing the database statements into a target database according to the transmission mark and the operation time of the current data to be transmitted when the current data to be transmitted exists in the transmission table; the third judging unit is used for judging whether the write-in operation of the current data to be transmitted is successful or not; the first transmission mark updating unit is used for adding 1 to the transmission marks of all data which are identical to the table name and the primary key of the current data to be transmitted in the transmission table when the writing operation fails; the first deleting unit is used for deleting the current data to be transmitted from the transmission table when the writing operation is successful; the fourth judging unit is used for judging whether the transmission mark of the current data to be transmitted is larger than 0; and the second transmission mark updating unit is used for judging that the table name and the primary key of the current data to be transmitted have transmission failure records and finish data repair when the transmission mark is larger than 0, and setting the transmission marks of all data, which are identical to the table name and the primary key of the current data to be transmitted, in the transmission table to be 0.
The second saving module further comprises: a fifth judging unit, a third transmission flag updating unit and a fourth transmission flag updating unit. The fifth judging unit is configured to judge whether there is the same data in the transmission table by querying, where the same data is: transmitting data which is the same as the table name and the main key of the current data to be transmitted in the table, and if the same data exists in the transmission table, judging that the table name and the main key of the current data to be transmitted have transmission failure records; and if the transmission table does not have the same data, judging that the table name and the primary key of the current data to be transmitted have no transmission failure record. And the third transmission mark updating unit is used for storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into the transmission table when the table name and the main key of the current data to be transmitted have transmission failure records, and setting the transmission mark of the current data to be transmitted as the transmission mark of the same data. And the fourth transmission mark updating unit is used for storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into the transmission table when the table name and the main key of the current data to be transmitted have no transmission failure record, and setting the transmission mark of the current data to be transmitted to be 0.
Further, the second saving module further comprises a second deleting unit, which is used for saving the database statement, the table name of the current data to be transmitted, the main key and the operation time in the transmission table, and deleting the current data to be transmitted from the trigger table after setting the transmission flag of the current data to be transmitted as the transmission flag of the same data.
The working principle and the working method of the data transmission system based on the trigger and the primary key in this embodiment have been explained in detail in the embodiment shown in fig. 1, and the two embodiments can be referred to each other and are not described herein again.
EXAMPLE III
The present application further provides a terminal, including: the device comprises a processor and a memory which is connected with the processor in a communication way, wherein the memory stores instructions which can be executed by the processor, and the instructions are executed by the processor, so that the processor can execute the data transmission method based on the trigger and the main key.
The data transmission method based on the trigger and the primary key executed by the processor is as follows:
s1: establishing a trigger table and a transmission table in a source database, wherein the trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission;
s2: respectively establishing triggers in each data table of a service system, wherein a plurality of data to be transmitted are stored in any data table;
s3: storing the data transmission information of each data table into a trigger table, wherein the data transmission information comprises: the table name, the primary key for adding, deleting and modifying data, the operation mark for adding, deleting and modifying operation and the operation time;
s3: in the packing thread, when the trigger table is inquired that the current data to be transmitted exists, extracting the whole data of the current data to be transmitted from the data table according to the table name and the main key of the current data to be transmitted, and forming a database statement according to the operation mark;
s4: storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table;
s5: and in the sending thread, extracting a database statement from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statement into a target database.
Specific implementation steps and principles of the above method can be referred to in the first embodiment shown in fig. 1, and are not described herein again.
The above description is merely exemplary of the present application and is presented to enable those skilled in the art to understand and practice the present application. Various modifications to these embodiments will be readily apparent to those skilled in the art, and the generic principles defined herein may be applied to other embodiments without departing from the spirit or scope of the application. Thus, the present application is not intended to be limited to the embodiments shown herein but is to be accorded the widest scope consistent with the principles and novel features disclosed herein.
Claims (10)
1. A method for data transmission based on a trigger and a primary key, the method comprising:
establishing a trigger table and a transmission table in a source database, wherein the trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission;
respectively establishing triggers in each data table of a service system, wherein a plurality of data to be transmitted are stored in any data table;
storing data transmission information of each data table into a trigger table, wherein the data transmission information comprises: the table name, the primary key for adding, deleting and modifying data, the operation mark for adding, deleting and modifying operation and the operation time;
in a packing thread, when the current data to be transmitted exists in a trigger table, extracting the whole data of the current data to be transmitted from the data table according to the table name and the main key of the current data to be transmitted, and forming a database statement according to an operation mark;
storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table;
and in the sending thread, extracting a database statement from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statement into a target database.
2. The data transmission method based on the trigger and the primary key according to claim 1, wherein when the current data to be transmitted is inquired in a trigger table, according to a table name and the primary key of the current data to be transmitted, extracting the whole data of the current data to be transmitted from the data table, and forming a database statement according to an operation mark, the method comprises:
judging whether the trigger table has data to be transmitted currently or not through query;
if so, the packing thread extracts the data transmission information from the trigger table according to the operation time sequence;
extracting the whole data of the current data to be transmitted from the data table according to the table name and the primary key of the current data to be transmitted;
and forming a database statement according to the whole data of the current data to be transmitted and the operation mark.
3. The data transmission method based on the trigger and the primary key according to claim 1, wherein the method for storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time in a transmission table and setting the transmission flag of the current data to be transmitted according to the table name of the existing data in the transmission table and the primary key comprises the following steps:
judging whether the transmission table has the same data or not through query, wherein the same data is as follows: transmitting data which is the same as the table name and the primary key of the current data to be transmitted in the table;
if the same data exists in the transmission table, judging that the table name and the primary key of the current data to be transmitted have transmission failure records;
storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted as a transmission mark of the same data;
if the same data does not exist in the transmission table, judging that the table name and the primary key of the current data to be transmitted have no transmission failure record;
and storing the database statement, the table name of the current data to be transmitted, the main key and the operation time into a transmission table, and setting the transmission mark of the current data to be transmitted to be 0.
4. The data transmission method based on the trigger and the primary key as claimed in claim 3, wherein after saving the database statement, the table name of the data to be transmitted currently, the primary key and the operation time in a transmission table, and setting the transmission flag of the data to be transmitted currently as the transmission flag of the same data, the method further comprises:
the data currently to be transmitted is deleted from the trigger table.
5. The data transmission method based on the trigger and the primary key according to claim 1, wherein in the sending thread, according to the transmission flag and the operation time of the data to be transmitted at present, the database statement extracted from the transmission table is written into the destination database, and the method comprises:
judging whether the transmission table has current data to be transmitted or not through inquiry;
if so, extracting a database statement from the transmission table according to the transmission mark and the operation time of the current data to be transmitted, and writing the database statement into a target database;
judging whether the write operation of the current data to be transmitted is successful;
if the write operation fails, adding 1 to the transmission marks of all data in the transmission table, which are the same as the table name and the primary key of the current data to be transmitted;
if the write operation is successful, deleting the current data to be transmitted from the transmission table;
judging whether a transmission mark of the current data to be transmitted is greater than 0;
if the transmission mark is larger than 0, judging that the table name and the primary key of the current data to be transmitted have transmission failure records and the data repair is finished;
and setting the transmission marks of all data in the transmission table, which are the same as the table name and the primary key of the current data to be transmitted, to be 0.
6. The trigger and primary key based data transmission method of any one of claims 1-5, wherein the packed thread and the sending thread perform data transmission in parallel, asynchronously, and in a loop.
7. A data transmission system based on a trigger and a primary key, the system comprising:
the device comprises a first establishing module, a second establishing module and a third establishing module, wherein the first establishing module is used for establishing a trigger table and a transmission table in a source database, the trigger table is used for a packaging thread of data transmission, and the transmission table is used for a sending thread of the data transmission;
the second establishing module is used for respectively establishing triggers in all data tables of the service system, wherein a plurality of data to be transmitted are stored in any one data table;
the first storage module is configured to store data transmission information of each data table into a trigger table, where the data transmission information includes: the table name, the primary key for adding, deleting and modifying data, the operation mark for adding, deleting and modifying operation and the operation time;
the extraction module is used for extracting the whole data of the current data to be transmitted from the data table according to the table name and the main key of the current data to be transmitted when the current data to be transmitted exists in the trigger table in the packing thread, and forming a database statement according to the operation mark;
the second storage module is used for storing the database statement, the table name of the current data to be transmitted, the primary key and the operation time into a transmission table, and setting a transmission mark of the current data to be transmitted according to the table name and the primary key of the existing data in the transmission table;
and the writing module is used for extracting the database statement from the transmission table and writing the database statement into the target database according to the transmission mark and the operation time of the current data to be transmitted in the sending thread.
8. The trigger and primary key based data transmission system of claim 7, wherein said extraction module comprises:
the first judgment unit is used for judging whether the trigger table has current data to be transmitted or not through inquiry;
the first extraction unit is used for extracting the data transmission information from the trigger table by the packaging thread according to the operation time sequence when the current data to be transmitted exists in the trigger table;
the second extraction unit is used for extracting the whole piece of data of the current data to be transmitted from the data table according to the table name and the primary key of the current data to be transmitted;
and the database statement forming unit is used for forming database statements according to the whole piece of data of the current data to be transmitted and the operation marks.
9. The system of claim 7, wherein the write module comprises:
the second judging unit is used for judging whether the current data to be transmitted exists in the transmission table or not through inquiry;
the third extraction unit is used for extracting database statements from the transmission table and writing the database statements into a target database according to the transmission mark and the operation time of the current data to be transmitted when the current data to be transmitted exists in the transmission table;
the third judging unit is used for judging whether the write-in operation of the current data to be transmitted is successful or not;
the first transmission mark updating unit is used for adding 1 to the transmission marks of all data which are identical to the table name and the primary key of the current data to be transmitted in the transmission table when the writing operation fails;
the first deleting unit is used for deleting the current data to be transmitted from the transmission table when the writing operation is successful;
the fourth judging unit is used for judging whether the transmission mark of the current data to be transmitted is larger than 0;
and the second transmission mark updating unit is used for judging that the table name and the primary key of the current data to be transmitted have transmission failure records and finish data repair when the transmission mark is larger than 0, and setting the transmission marks of all data, which are identical to the table name and the primary key of the current data to be transmitted, in the transmission table to be 0.
10. A terminal, characterized in that the terminal comprises: a processor, and a memory communicatively coupled to the processor, wherein,
the memory has stored therein instructions executable by the processor to enable the processor to perform the trigger and primary key based data transfer method of any one of claims 1 to 6.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110590666.7A CN113590695B (en) | 2021-05-28 | 2021-05-28 | Data transmission method, system and terminal based on trigger and primary key |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110590666.7A CN113590695B (en) | 2021-05-28 | 2021-05-28 | Data transmission method, system and terminal based on trigger and primary key |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN113590695A true CN113590695A (en) | 2021-11-02 |
| CN113590695B CN113590695B (en) | 2024-02-23 |
Family
ID=78243324
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110590666.7A Active CN113590695B (en) | 2021-05-28 | 2021-05-28 | Data transmission method, system and terminal based on trigger and primary key |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113590695B (en) |
Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN103761318A (en) * | 2014-01-27 | 2014-04-30 | 中国工商银行股份有限公司 | Method and system for data synchronization of relational heterogeneous databases |
| US20140317087A1 (en) * | 2013-04-22 | 2014-10-23 | Salesforce.Com, Inc. | Systems and methods for implementing and maintaining sampled tables in a database system |
| CN104391865A (en) * | 2014-10-24 | 2015-03-04 | 浪潮软件股份有限公司 | Method for recording operation logs based on trigger principle |
| CN110019512A (en) * | 2017-10-11 | 2019-07-16 | 北京国双科技有限公司 | A kind of data processing method and device |
| CN111752901A (en) * | 2020-06-23 | 2020-10-09 | 网易（杭州）网络有限公司 | Index creation method and device, electronic equipment and storage medium |
-
2021
- 2021-05-28 CN CN202110590666.7A patent/CN113590695B/en active Active
Patent Citations (5)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20140317087A1 (en) * | 2013-04-22 | 2014-10-23 | Salesforce.Com, Inc. | Systems and methods for implementing and maintaining sampled tables in a database system |
| CN103761318A (en) * | 2014-01-27 | 2014-04-30 | 中国工商银行股份有限公司 | Method and system for data synchronization of relational heterogeneous databases |
| CN104391865A (en) * | 2014-10-24 | 2015-03-04 | 浪潮软件股份有限公司 | Method for recording operation logs based on trigger principle |
| CN110019512A (en) * | 2017-10-11 | 2019-07-16 | 北京国双科技有限公司 | A kind of data processing method and device |
| CN111752901A (en) * | 2020-06-23 | 2020-10-09 | 网易（杭州）网络有限公司 | Index creation method and device, electronic equipment and storage medium |
Non-Patent Citations (2)
| Title |
|---|
| 刘伟玲;闫伟;: "数据库触发器及简单应用", 农业网络信息, no. 08, 26 August 2008 (2008-08-26) * |
| 程鲁明;肖菊香;: "oracle数据库批量数据无损迁移技术研究", 电子设计工程, no. 18, 18 September 2020 (2020-09-18) * |
Also Published As
| Publication number | Publication date |
|---|---|
| CN113590695B (en) | 2024-02-23 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107729366B (en) | Universal multi-source heterogeneous large-scale data synchronization system | |
| CN101009516B (en) | A method, system and device for data synchronization | |
| CN101334797B (en) | Distributed file systems and its data block consistency managing method | |
| CN110262929B (en) | Method for ensuring consistency of copying affairs and corresponding copying device | |
| CN106777270A (en) | A kind of Heterogeneous Database Replication parallel execution system and method based on submission point time line locking | |
| CN106155775B (en) | Message processing method, device and system | |
| CN103034564B (en) | Data disaster tolerance drilling method, data disaster tolerance practice device and system | |
| US20220035786A1 (en) | Distributed database management system with dynamically split b-tree indexes | |
| CN109086382B (en) | Data synchronization method, device, equipment and storage medium | |
| CN111143470A (en) | Cross-platform database data synchronization comparison method and device | |
| CN104462562A (en) | Data migration system and method based on data warehouse automation | |
| CN102521356A (en) | Regular expression matching equipment and method on basis of deterministic finite automaton | |
| CN108182198B (en) | Control device for storing advanced controller operation data and reading method | |
| CN107870982A (en) | Data processing method, system and computer-readable recording medium | |
| CN103502970B (en) | A kind of method of operating of key-value pair and device | |
| CN111865969B (en) | Secure transmission method and system suitable for financial information | |
| CN110413629B (en) | Method for checking data consistency of heterogeneous database based on log analysis | |
| CN111708835B (en) | Block chain data storage method and device | |
| CN113590695A (en) | Data transmission method, system and terminal based on trigger and main key | |
| CN108737246B (en) | Message issuing method, device, storage medium and server | |
| Membrey et al. | A disk based stream oriented approach for storing big data | |
| CN112488708B (en) | Block chain account relevance query method and false transaction screening method | |
| CN115310960A (en) | Digital economy method based on internet | |
| CN110727726B (en) | Method and system for extracting data from document database to relational database | |
| CN114595288A (en) | SQL command level-based multi-IDC distributed system data synchronization method |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant |