CN113326277B

CN113326277B - Batch new method and device for MySQL data

Info

Publication number: CN113326277B
Application number: CN202110739676.2A
Authority: CN
Inventors: 周满园; 刘颖; 刘倩倩; 王岩
Original assignee: Industrial and Commercial Bank of China Ltd ICBC
Current assignee: Industrial and Commercial Bank of China Ltd ICBC
Priority date: 2021-06-30
Filing date: 2021-06-30
Publication date: 2024-03-12
Anticipated expiration: 2041-06-30
Also published as: CN113326277A

Abstract

The embodiment of the application provides a MySQL data batch adding method and device, which can be used in the technical field of block chains, and the method comprises the following steps: according to the target data types corresponding to the target batch uploading requests of the target MySQL database and the total number of the target data under the target data types; searching a current maximum value of a source table main key corresponding to a target data type from a preset sequence number record parameter table as a target value; and updating the current maximum value of the main key of the source table based on the target value and the total number of the target data under the target data type, and inserting the target data under the target data type into the source table corresponding to the target data type according to the current maximum value of the main key of the source table. According to the method and the device, the efficiency and the reliability of the batch data newly-added process aiming at the MySQL database can be effectively improved, waste of a large amount of resources is avoided, the memory occupancy rate is effectively reduced, and the intelligent degree of the batch data newly-added process is improved.

Description

Batch new method and device for MySQL data

Technical Field

The application relates to the technical field of data processing, in particular to the technical field of blockchain, and specifically relates to a method and a device for adding MySQL data in batches.

Background

Supply chain finance has become the edge tool that accelerates industry integration, accelerates resource integration, widens little micro-enterprise financing channel, ensures that the fund flows to entity economy. The digital credit receipt tool and the like have the functions of splitting and transferring, and the functions of issuing, delivering, increasing credit, transferring, signing, mortgage, presenting and the like of the digital credit receipt can be used for injecting the credit of a core enterprise into the whole industrial chain by depending on a scene financial service platform and a alliance mechanism platform of a financial institution such as a bank and the like, so that the problem of the finance of the traditional supply chain is solved. In the traditional credit, the batch uploading of the information aiming at a single enterprise is carried out because the operators are single, the concurrency is not large, and the dirty data is very little. However, in a financial system based on the whole supply chain, trade information is huge, a single business person cannot maintain the trade information, and a team is required to maintain the trade information of the whole financial system, so that batch uploading of the trade information by multiple persons at the same time occurs. And when a large concurrent data writing situation is encountered, the problems of long time consumption and overlarge resource consumption occur. That is, in the batch data adding process, the source table needs to be traversed to obtain the maximum value of the primary key for each new data, so that the data needs to be added after the maximum value of the primary key is obtained and the maximum sequence number is generated for many times, and therefore, the existing data adding mode for the MySQL database has the problems of waste of a large amount of resources, low intelligent degree, low adding efficiency and the like.

Disclosure of Invention

Aiming at the problems in the prior art, the application provides the MySQL data batch adding method and device, which can effectively improve the efficiency and reliability of the batch data adding process aiming at the MySQL database, avoid the waste of a large amount of resources, effectively reduce the memory occupancy rate and improve the intelligent degree of the batch data adding process aiming at the MySQL database.

In order to solve the technical problems, the application provides the following technical scheme:

in a first aspect, the present application provides a MySQL data batch addition method, including:

according to the target data types corresponding to the target batch uploading requests of the target MySQL database and the total number of the target data under the target data types;

searching a current maximum value of a source table main key corresponding to the target data type from a preset sequence number record parameter table, and determining the current maximum value of the source table main key as a target value of the source table main key corresponding to the target data type; the sequence number record parameter table is used for storing the corresponding relation between the source table identification of each data type in the target MySQL database and the current maximum value of the source table main key;

Updating the current maximum value of the source table main key corresponding to the target data type in the sequence number record parameter table based on the target value and the total number of each target data in the target data type, and inserting each target data in the target data type into the source table corresponding to the target data type based on the updated current maximum value of the source table main key corresponding to the target data type.

Further, the fields of the source table main key consist of custom type fields and preset self-increasing fields which are sequentially arranged;

the custom type field includes a location number and/or an enterprise code;

the source table identifiers are respectively and uniquely corresponding to the source tables and are used for storing the corresponding relation between the source table main keys and the data information;

correspondingly, the MySQL data batch adding method further comprises the following steps:

according to the custom type fields in the source table fields in the source tables of all data types, respectively reading the current maximum values respectively corresponding to the custom type fields in the source tables of all data types from the source tables of all data types, so that the current maximum values respectively corresponding to the custom type fields in each source table are used as the current maximum values of the source table main keys corresponding to the data types to which the corresponding source tables belong;

And storing the current maximum value of the source table main key corresponding to each data type and the corresponding source table identifier into the sequence number record parameter table.

Further, the step of respectively corresponding to each target data according to the target batch uploading request for the target MySQL database and the total number of each target data under the target data type includes:

receiving a target batch uploading request aiming at a target MySQL database, wherein the target batch uploading request comprises a plurality of target data;

respectively obtaining the corresponding target data types of the target data;

and determining the total number of the corresponding target data under each target data type according to the corresponding target data type of each target data.

Further, searching the current maximum value of the source table primary key corresponding to the target data type from the preset sequence number record parameter table, including:

respectively acquiring target custom type fields corresponding to the target data from target batch uploading requests aiming at a target MySQL database;

and according to the target custom type field corresponding to each target data, searching the current maximum value of the source table main key where the source table identifier corresponding to the target data type is located in the sequence number record parameter table.

Further, the updating the current maximum value of the source table primary key corresponding to the target data type in the sequence number recording parameter table based on the target value and the total number of each target data in the target data type includes:

and judging whether the current maximum value of the source table main key corresponding to the target data type in the sequence number recording parameter table is equal to the target value of the source table main key corresponding to the target data type, if so, adding the target value and the total number of all target data under the target data type, so as to update the current maximum value of the source table main key corresponding to the target data type in the sequence number recording parameter table according to the corresponding adding result.

Further, the method further comprises the following steps:

and if the current maximum value of the source table main key corresponding to the target data type in the sequence number recording parameter table is not equal to the target value of the source table main key corresponding to the target data type, re-acquiring the target value of the source table main key corresponding to the target data type, and judging whether the current maximum value of the source table main key corresponding to the target data type in the sequence number recording parameter table is equal to the target value of the source table main key corresponding to the target data type again.

Further, after each target data in the target data type is inserted into the source table corresponding to the target data type based on the updated current maximum value of the source table primary key corresponding to the target data type, the method further includes:

receiving a data newly-added result identifier of a source table corresponding to the target data type;

if the data newly-added result identifier indicates that the data of the source table corresponding to the target data type fails to be newly added, judging whether the current maximum value of the source table main key corresponding to the target data type in the sequence number record parameter table is equal to the target value of the source table main key corresponding to the target data type again.

Further, the target MySQL database is a blockchain-based MySQL database.

In a second aspect, the present application provides a MySQL data batch adding device, including:

the quantity confirmation module is used for respectively corresponding to each target data according to the target batch uploading request aiming at the MySQL database and the total number of each target data under the target data types;

the maximum value acquisition module is used for searching the current maximum value of the source table main key corresponding to the target data type from a preset sequence number record parameter table, and determining the current maximum value of the source table main key as the target value of the source table main key corresponding to the target data type; the sequence number record parameter table is used for storing the corresponding relation between the source table identification of each data type in the MySQL database and the current maximum value of the source table main key;

And the batch adding module is used for updating the current maximum value of the source table main key corresponding to the target data type in the sequence number record parameter table based on the target value and the total number of each target data in the target data type, and inserting each target data in the target data type into the source table corresponding to the target data type based on the updated current maximum value of the source table main key corresponding to the target data type.

In a third aspect, the present application provides an electronic device, including a memory, a processor, and a computer program stored on the memory and executable on the processor, where the processor implements the MySQL data batch addition method when executing the program.

In a fourth aspect, the present application provides a computer readable storage medium having stored thereon a computer program which when executed by a processor implements the MySQL data batch update method.

According to the technical scheme, the method and the device for batch adding of MySQL data provided by the application comprise the following steps: according to the target data types corresponding to the target batch uploading requests of the target MySQL database and the total number of the target data under the target data types; searching a current maximum value of a source table main key corresponding to the target data type from a preset sequence number record parameter table, and determining the current maximum value of the source table main key as a target value of the source table main key corresponding to the target data type; the sequence number record parameter table is used for storing the corresponding relation between the source table identification of each data type in the target MySQL database and the current maximum value of the source table main key; updating the current maximum value of a source table main key corresponding to the target data type in the sequence number recording parameter table based on the target value and the total number of each target data under the target data type, inserting each target data under the target data type into the source table corresponding to the target data type based on the updated current maximum value of the source table main key corresponding to the target data type, and setting a sequence number recording parameter table for storing the corresponding relation between the source table identifier of each data type and the current maximum value of the source table main key, so that only one table of the sequence number recording parameter table is needed in the batch data adding process of the MySQL database, the efficiency and the reliability of the batch data adding process of the MySQL database can be effectively improved, the waste of a large amount of resources is avoided, the memory occupancy is effectively reduced, and the memory occupancy up to 90% can be reduced through detection; the intelligent degree of the batch data adding process aiming at the MySQL database can be effectively improved, and the running stability and reliability of the MySQL database can be further improved.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings that are required in the embodiments or the description of the prior art will be briefly described, and it is obvious that the drawings in the following description are some embodiments of the present application, and other drawings may be obtained according to these drawings without inventive effort for a person skilled in the art.

Fig. 1 is a schematic interaction diagram between a MySQL data batch adding device and a client device in an embodiment of the present application.

Fig. 2 is a schematic flow chart of a first method for adding MySQL data in batch according to an embodiment of the present application.

Fig. 3 is a second flowchart of a MySQL data batch update method according to an embodiment of the present application.

Fig. 4 is a third flowchart of a MySQL data batch addition method according to an embodiment of the present application.

Fig. 5 is a fourth flowchart of a MySQL data batch addition method according to an embodiment of the present application.

Fig. 6 is a fifth flowchart of a MySQL data batch update method according to an embodiment of the present application.

Fig. 7 is a sixth flowchart of a MySQL data batch update method according to an embodiment of the present application.

Fig. 8 is a seventh flowchart of a MySQL data batch addition method according to an embodiment of the present application.

Fig. 9 is a schematic structural diagram of a MySQL data batch adding device in the embodiment of the present application.

Fig. 10 is a schematic structural diagram of a MySQL data batch addition system in an application example of the present application.

Fig. 11 is a schematic diagram of the working principle of the sequence number recording parameter table module 102 in the application example of the present application.

Fig. 12 is a schematic structural diagram of an electronic device in an embodiment of the present application.

Detailed Description

For the purposes of making the objects, technical solutions and advantages of the embodiments of the present application more clear, 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 apparent that the described embodiments are some embodiments of the present application, but not all embodiments. All other embodiments, which can be made by one of ordinary skill in the art based on the embodiments herein without making any inventive effort, are intended to be within the scope of the present application.

It should be noted that the MySQL data batch adding method and device disclosed in the present application may be used in the blockchain technical field, and may also be used in any field other than the blockchain technical field, and the application field of the MySQL data batch adding method and device disclosed in the present application is not limited.

In the batch data adding process, a source table is required to be traversed to acquire a main key maximum value for each new data, so that the main key maximum value is required to be acquired for many times, and the data is added after the maximum sequence number is generated, and the existing data adding mode for the MySQL database has the problems of waste of a large amount of resources, low intelligent degree, low new adding efficiency and the like. Based on the above, the embodiment of the application provides a MySQL data batch adding method, according to the target data types corresponding to each target data corresponding to the target batch uploading request of the target MySQL database and the total number of each target data under the target data types; searching a current maximum value of a source table main key corresponding to the target data type from a preset sequence number record parameter table, and determining the current maximum value of the source table main key as a target value of the source table main key corresponding to the target data type; the sequence number record parameter table is used for storing the corresponding relation between the source table identification of each data type in the target MySQL database and the current maximum value of the source table main key; updating the current maximum value of a source table main key corresponding to the target data type in the sequence number recording parameter table based on the target value and the total number of each target data under the target data type, inserting each target data under the target data type into the source table corresponding to the target data type based on the updated current maximum value of the source table main key corresponding to the target data type, and setting a sequence number recording parameter table for storing the corresponding relation between the source table identifier of each data type and the current maximum value of the source table main key, so that only one table of the sequence number recording parameter table is needed in the batch data adding process of the MySQL database, the efficiency and the reliability of the batch data adding process of the MySQL database can be effectively improved, the waste of a large amount of resources is avoided, the memory occupancy is effectively reduced, and the memory occupancy up to 90% can be reduced through detection; the intelligent degree of the batch data adding process aiming at the MySQL database can be effectively improved, and the running stability and reliability of the MySQL database can be further improved.

Based on the foregoing, the present application further provides a MySQL data batch adding device for implementing the MySQL data batch adding method provided in one or more embodiments of the present application, referring to fig. 1, the MySQL data batch adding device may be in communication connection with a client device, where the MySQL data batch adding device may receive a target batch uploading request sent by the client device and directed against a target MySQL database, and then the MySQL data batch adding device performs data batch adding processing on the target MySQL database based on the MySQL data batch adding method mentioned in the present application, and after obtaining data batch adding processing result data of the corresponding target MySQL database, sends the processing result data to the client device of a financial institution for storage or display, and so on.

In an actual application situation, the MySQL data batch adding device may be implemented by a server; the server may be communicatively coupled to at least one client device.

It is understood that the client device may include a smart phone, a tablet electronic device, a network set top box, a portable computer, a desktop computer, a Personal Digital Assistant (PDA), an in-vehicle device, a smart wearable device, etc. Wherein, intelligent wearing equipment can include intelligent glasses, intelligent wrist-watch, intelligent bracelet etc..

In another practical application scenario, the portion of the MySQL data batch adding device that performs MySQL data batch adding may be executed in a server as described above, or all operations may be completed in the client device. Specifically, the selection may be made according to the processing capability of the client device, and restrictions of the use scenario of the user. The present application is not limited in this regard. If all operations are completed in the client device, the client device may further include a processor for specific processing of MySQL data batch additions.

The client device may have a communication module (i.e. a communication unit) and may be connected to a remote server in a communication manner, so as to implement data transmission with the server. The server may include a server on the side of the task scheduling center, and in other implementations may include a server of an intermediate platform, such as a server of a third party server platform having a communication link with the task scheduling center server. The server may include a single computer device, a server cluster formed by a plurality of servers, or a server structure of a distributed device.

Any suitable network protocol may be used for communication between the server and the client device, including those not yet developed at the filing date of this application. The network protocols may include, for example, TCP/IP protocol, UDP/IP protocol, HTTP protocol, HTTPS protocol, etc. Of course, the network protocol may also include, for example, RPC protocol (Remote Procedure Call Protocol ), REST protocol (Representational State Transfer, representational state transfer protocol), etc. used above the above-described protocol.

In one or more embodiments of the present application, dirty data refers to data in the source system that is not within a given range or meaningless to actual traffic, or that is in an illegal data format, and that has unnormalized coding and ambiguous traffic logic in the source system. In database technology, dirty data is generated in a temporary update (dirty read). Transaction a updates a certain data item X, but for some reason transaction a has a problem and is then rolled back. But before rollback, another transaction B reads the value of data item X (after a update), a rollbacks the transaction and the data item resumes its original value. Transaction B reads what is a "temporary" value for data item X, which is dirty data.

The following embodiments and application examples are described in detail.

In order to solve the problems of a large amount of wasted resources, low intelligent degree, low new adding efficiency and the like in the existing data adding mode aiming at the MySQL database, the application provides an embodiment of a MySQL data batch adding method, which is applicable to a block chain scene, and is implemented by applying a MySQL data batch adding device, referring to FIG. 2, the MySQL data batch adding method specifically comprises the following contents:

step 100: and respectively corresponding target data types and total number of the target data under the target data types according to the target batch uploading request corresponding to the target MySQL database.

In step 100, the information such as contract uploaded by the user foreground is uploaded by CSV, the counter is first introduced to initialize n=0, then the file can be continuously read from the second line by executing the readLine () line, and if the return value is not null, the counter is continuously incremented by 1 until the return value is null. At this time, the total number of CSV will be obtained, and assuming that n=10, this secondary insertion of 10 pieces of data will be described.

Step 200: searching a current maximum value of a source table main key corresponding to the target data type from a preset sequence number record parameter table, and determining the current maximum value of the source table main key as a target value of the source table main key corresponding to the target data type; the sequence number record parameter table is used for storing the corresponding relation between the source table identification of each data type in the target MySQL database and the current maximum value of the source table main key.

In step 200, the current maximum value of the primary key of the source table, such as the maximum number of the certificate, in the sequence number record parameter table is queried and recorded as N _max 。

Step 300: updating the current maximum value of the source table main key corresponding to the target data type in the sequence number record parameter table based on the target value and the total number of each target data in the target data type, and inserting each target data in the target data type into the source table corresponding to the target data type based on the updated current maximum value of the source table main key corresponding to the target data type.

As can be seen from the above description, according to the MySQL data batch adding method provided by the embodiment of the present application, by setting the sequence number record parameter table for storing the correspondence between the source table identifier of each data type and the current maximum value of the source table primary key, only one table, i.e. the sequence number record parameter table, needs to be accessed once in the batch data adding process for the MySQL database, so that the efficiency and reliability of the batch data adding process for the MySQL database can be effectively improved, the waste of a large amount of resources is avoided, the memory occupancy is effectively reduced, and the memory occupancy up to 90% can be reduced through detection; the intelligent degree of the batch data adding process aiming at the MySQL database can be effectively improved, and the running stability and reliability of the MySQL database can be further improved.

In order to distinguish the current maximum value of the main key of the source table according to the area code and/or the enterprise code, in one embodiment of the MySQL data batch adding method provided by the application, the field of the main key of the source table consists of a custom type field and a preset self-adding field which are sequentially arranged; the custom type field includes a location number and/or an enterprise code; the source table identifiers are respectively and uniquely corresponding to the source tables and are used for storing the corresponding relation between the source table main keys and the data information; referring to fig. 3, before step 100 of the MySQL data batch addition method, the following is specifically included:

step 010: according to the custom type fields in the source table fields in the source tables of the data types, current maximum values corresponding to the custom type fields in the source tables of the data types are read from the source tables of the data types respectively, so that the current maximum values corresponding to the custom type fields in the source tables are used as the current maximum values of the source table main keys corresponding to the data types to which the corresponding source tables belong.

Step 020: and storing the current maximum value of the source table main key corresponding to each data type and the corresponding source table identifier into the sequence number record parameter table.

For example, the maximum sequence number of a voucher for a source table such as a trade information table may be fetched by a MAX function. And writes this number into a sequence number record parameter table by insert.

For example: taking the trade information table (trade_info) as an example, the primary key (ti_id) of the source table is composed of a 6-bit region number+6-bit enterprise code+16-bit self-increasing field, and because the region and the primary enterprise are relatively fixed in the supply chain financial transaction link, the first 12 bits are fixed values, the later are self-increasing fields, and the information is continuously increased after being inserted. The maximum value of the source table primary key (assumed to be 100020388422000000001234) that can then be taken by executing the program "select MAX (TI_ID) from trade_info order by TI_ID desc limit 1"; the obtained value (100020388422000000001234) is written into the current sequence number recording parameter table (parameter_info), and meanwhile, the field name of the trade information table (rade_info) is unchanged and still is TI_ID in the sequence number recording parameter table (parameter_info). If there are other information tables with strong association, the other information table hosts can be synchronized by multi-table joint check based on the main key TI_ID, and combined into a piece of line information to be inserted into a sequence number record parameter table (parameter_info).

As can be seen from the foregoing description, according to the MySQL data batch adding method provided in the embodiments of the present application, according to the custom type field in the source table of each data type, the current maximum value of the source table main key corresponding to each custom type field in the source table of each data type is read from the source table of each data type, so that the current maximum value corresponding to each custom type field in each source table is used as the current maximum value of the source table main key of the source table, and then the current maximum values of the source table main keys in the source table can be distinguished according to the area code and/or the enterprise code, and further the efficiency and accuracy of searching the current maximum values in the subsequent self-sequence number record parameter table can be effectively improved, and further the efficiency and accuracy of MySQL data batch adding can be further improved.

In order to improve the efficiency and reliability of determining the data type and the total number of each target data under each type, in one embodiment of the MySQL data batch addition method provided in the present application, referring to fig. 4, step 100 of the MySQL data batch addition method specifically includes the following contents:

step 110: and receiving a target batch uploading request aiming at a target MySQL database, wherein the target batch uploading request comprises a plurality of target data.

Step 120: and respectively acquiring the corresponding target data types of the target data.

Step 130: and determining the total number of the corresponding target data under each target data type according to the corresponding target data type of each target data.

Specifically, after uploading information such as contract, the user may calculate the total number of records to be inserted this time and record n before the credential information passes the verification and is ready to be written into the database.

Specifically, for example, the information such as the contract uploaded by the user foreground is uploaded by CSV, the counter is first introduced to be initialized to n=0, then the file can be continuously read from the second line by executing the program readLine (), and if the return value is not null, the counter is continuously incremented by 1 until the return value is null. At this time, the total number of the present CSV will be obtained, and assuming that n=10, we will say that we will insert 10 pieces of data this time.

As can be seen from the above description, according to the MySQL data batch adding method provided in the embodiments of the present application, by receiving a target batch uploading request for a target MySQL database, respectively obtaining the target data types corresponding to each of the target data, the efficiency and reliability of determining the data types and the total number of each target data under each type can be effectively improved, and further the efficiency and reliability of MySQL data batch adding can be further improved.

In order to further improve efficiency and accuracy of searching the current maximum value of the source table primary key corresponding to the target data type, in one embodiment of the MySQL data batch addition method provided in the present application, referring to fig. 5, step 200 of the MySQL data batch addition method specifically includes the following contents:

step 210: respectively acquiring target custom type fields corresponding to the target data from target batch uploading requests aiming at a target MySQL database;

step 220: and according to the target custom type field corresponding to each target data, searching the current maximum value of the source table main key where the source table identifier corresponding to the target data type is located in the sequence number record parameter table.

Step 230: and determining the current maximum value of the source table main key as a target value of the source table main key corresponding to the target data type.

As can be seen from the foregoing description, according to the MySQL data batch adding method provided in the embodiment of the present application, the target custom type fields corresponding to each target data are obtained from the target batch uploading request for the target MySQL database, and the current maximum value of the source table primary key where the target custom type field is located is searched according to the target custom type field corresponding to each target data, so that the efficiency and accuracy of searching the current maximum value of the source table primary key corresponding to the target data type can be further improved, and further the efficiency and accuracy of MySQL data batch adding can be further improved.

In order to avoid the database locking operation, on the premise of small increase of overhead, the method for solving the problems of multithreading security access and batch uploading efficiency is provided, and in one embodiment of the MySQL data batch adding method provided in the present application, referring to fig. 6, step 300 in the MySQL data batch adding method specifically includes the following contents:

step 310: and judging whether the current maximum value of the source table main key corresponding to the target data type in the sequence number recording parameter table is equal to the target value of the source table main key corresponding to the target data type, and executing 320 if so.

Step 320: and adding the target value and the total number of each target data under the target data type to update the current maximum value of the source table main key corresponding to the target data type in the sequence number record parameter table according to the corresponding adding result.

Step 330: and inserting each target data under the target data type into a source table corresponding to the target data type based on the updated current maximum value of the source table main key corresponding to the target data type.

As can be seen from the above description, according to the MySQL data batch adding method provided in the embodiments of the present application, by determining whether the current maximum value of the source table primary key corresponding to the target data type in the sequence number record parameter table is equal to the target value of the source table primary key corresponding to the target data type, the method for solving the problems of multithreading security access and batch uploading efficiency on the premise of increasing small overhead by avoiding the operation of locking the database.

In order to avoid the database locking operation through the "light" loop design, in one embodiment of the MySQL data batch addition method provided in the present application, referring to fig. 7, the MySQL data batch addition method further specifically includes the following contents:

if it is determined in step 310 that the current maximum value of the source table primary key corresponding to the target data type in the sequence number recording parameter table is not equal to the target value of the source table primary key corresponding to the target data type, the execution returns to step 220 to reacquire the target value of the source table primary key corresponding to the target data type, and it is determined again whether the current maximum value of the source table primary key corresponding to the target data type in the sequence number recording parameter table is equal to the target value of the source table primary key corresponding to the target data type.

As can be seen from the above description, the batch adding method of MySQL data provided by the embodiment of the application can avoid the locking operation of the database through the 'light and handy' loop design, and further solve the problems of multithreading security access and batch uploading efficiency on the premise of small increase of overhead.

In order to further avoid the database locking operation, in one embodiment of the MySQL data batch addition method provided in the present application, referring to fig. 8, step 300 in the MySQL data batch addition method further specifically includes the following:

Step 400: and receiving a data newly-added result identifier of a source table corresponding to the target data type.

Step 500: and judging whether the data newly-added result identifier shows that the data of the source table corresponding to the target data type is newly-added successfully, if so, ending the current flow, otherwise, returning to the execution step 310 to judge whether the current maximum value of the source table main key corresponding to the target data type in the sequence number record parameter table is equal to the target value of the source table main key corresponding to the target data type again.

In order to further avoid the database locking operation, in one embodiment of the MySQL data batch addition method provided in the present application, the target MySQL database in the MySQL data batch addition method is a MySQL database based on a blockchain.

From the above description, the MySQL data batch addition method provided by the embodiment of the present application can provide a special MySQL data batch addition method for the blockchain technology, so as to effectively improve the operational reliability and stability of the blockchain.

In order to solve the problems of waste of a large amount of resources, low intelligent degree, low new efficiency and the like in the existing data adding mode for the MySQL database from the software aspect, the application provides an embodiment of a MySQL data batch adding device for executing all or part of the content in the MySQL data batch adding method, and referring to fig. 9, the MySQL data batch adding device specifically includes the following contents:

the quantity confirmation module 10 is configured to respectively correspond to each target data type and the total number of each target data under each target data type according to each target data corresponding to the target batch uploading request for the MySQL database.

In the quantity confirmation module 10, the information such as contract uploaded by the user foreground is uploaded by CSV, the counter is first introduced to initialize n=0, then the file can be continuously read from the second line by executing the program readLine (), and if the return value is not null, the counter is continuously incremented by 1 until the return value is null. At this time, the total number of CSV will be obtained, and assuming that n=10, this secondary insertion of 10 pieces of data will be described.

The maximum value obtaining module 20 is configured to find a current maximum value of a source table primary key corresponding to the target data type from a preset sequence number record parameter table, and determine the current maximum value of the source table primary key as a target value of the source table primary key corresponding to the target data type; the sequence number record parameter table is used for storing the corresponding relation between the source table identification of each data type in the MySQL database and the current maximum value of the source table main key.

In the maximum value acquisition module 20, the current maximum value of the primary key of the source table, such as the maximum serial number of the certificate, in the serial number recording parameter table is queried and recorded as N _max 。

And a batch adding module 30, configured to update a current maximum value of a source table primary key corresponding to the target data type in the sequence number record parameter table based on the target value and a total number of each target data in the target data type, and insert each target data in the target data type into a source table corresponding to the target data type based on the updated current maximum value of the source table primary key corresponding to the target data type.

The embodiment of the MySQL data batch adding device provided in the present application may be specifically used to execute the processing flow of the embodiment of the MySQL data batch adding method in the above embodiment, and the functions thereof are not described herein again, and may refer to the detailed description of the above method embodiment.

As can be seen from the above description, by setting the sequence number record parameter table for storing the correspondence between the source table identifier of each data type and the current maximum value of the source table primary key, the MySQL data batch adding device provided by the embodiment of the present application can enable only one table, namely the sequence number record parameter table, to be accessed once in the batch data adding process for the MySQL database, can effectively improve the efficiency and reliability of the batch data adding process for the MySQL database, avoid the waste of a large amount of resources, effectively reduce the memory occupancy, and can reduce the memory occupancy up to 90% through detection; the intelligent degree of the batch data adding process aiming at the MySQL database can be effectively improved, and the running stability and reliability of the MySQL database can be further improved.

In order to further explain the book scheme, the application also provides a specific application example of the MySQL data batch addition method realized by applying the MySQL data batch addition system, and relates to how to simultaneously upload a large amount of trade information into the system in batch in a distributed system in a supply chain financial transaction system.

In a financial system based on the whole supply chain, trade information is huge, a single business person cannot maintain the trade information, a team is required to maintain the trade information of the whole financial system, and batch uploading of the trade information by multiple persons at the same time occurs. And when a large concurrent data writing situation is encountered, the problems of long time consumption and overlarge resource consumption occur. At this time, if a series of voucher codes are manually customized as a primary key and are placed in a table together with trade information according to a traditional method, the following problems exist:

1) Every generation needs to traverse the database to take the maximum value, and the time consumption is long and the resource consumption is overlarge. Especially, when uploading credentials in batches, the maximum value is needed to be taken for many times and the maximum sequence number is generated, so that a large amount of resources are wasted and the efficiency is greatly affected.

2) In the case of multi-threaded access, there is a serious problem of dirty data reading. For example, A, B thread takes the same current maximum value, i.e., generates the same credential code, resulting in a credential save error.

In order to solve the problems, the application example of the application provides a method for recording the parameter table by taking the region and the parameter code as the main key, facilitating the subsequent service expansion, generating different serial numbers according to different service regions, avoiding the operation of locking a database through the 'light and handy' circulation design, and solving the problems of multithreaded safety access and batch uploading efficiency on the premise of increasing small expenditure.

Since MySQL database does not provide a Sequence method similar to that available for Oracle database, the present application refers to a Sequence generation mechanism to write a Sequence number generation method. A sequence number recording parameter table is designed, the current maximum sequence number is recorded, and the table is inquired before a new sequence number is generated. And the problem of dirty data reading during multi-thread access is solved by adding a method of Where condition matching when updating records.

Referring to fig. 10, the MySQL data batch addition system specifically includes the following contents:

batch upload module 101: the user conducts the contract and the bill in batches on the foreground page in the form of FTP. The total number of the uploaded information is calculated and transferred to the sequence number recording parameter table module 102.

Sequence number record parameter table module 102: the core module of the application example is used for recording the maximum value of the voucher number in the current trade information. The next maximum voucher information number is passed to the trade information table module 103. The total number of information from the bulk upload module 101 is received.

Trade information table module 103: the batch stored in the database uploads the information in module 101. The maximum credential information number is received from the sequence number record parameter table module 102.

Referring to fig. 11, the sequence number record parameter table module 102 operates as follows:

step 1, designing a sequence number recording parameter table, and initializing. The maximum sequence number of the voucher of the trade information table in the trade information table module 103 is fetched by MAX function and written into the sequence number record parameter table by insert.

Step 2, after uploading information such as contract, the batch uploading module 101 calculates the total record number to be inserted this time and records as n before the certificate information passes the verification and is ready to be written into the database.

For example: the information such as contract uploaded by the user foreground is uploaded by CSV, firstly, a counter is introduced to initialize to n=0, then, the file can be continuously read from the second line by line through executing a program readLine (), and if the return value is not null, the counter is continuously increased by 1 until the return value is null. At this time, the total number of the present CSV will be obtained, and assuming that n=10, we will say that we will insert 10 pieces of data this time.

Step 3, inquiring the maximum serial number of the certificate in the serial number record parameter table and recording as N _max 。

For example, the current maximum number of vouchers N _max Is "100020388422000000001234".

And 4, updating the maximum sequence number in the sequence number record parameter table to be: the current maximum sequence number+the number of the record inserted at this time is as follows: maximum number of parameter table=n _max 。

Specifically, the maximum sequence number in the updated sequence number record parameter table is: the current maximum number (e.g., 100020388422000000001234) +the number of the current insertion record (e.g., 10), where the update condition is set to the parameter table maximum number=n _max Because a scenario may occur in which, when the maximum value taken by the process a is 100020388422000000001234, 10 pieces are ready to be inserted, and in the middle of this fraction of seconds, the process B updates the maximum sequence number of the parameter table, for example, by 1 piece, and at this time the maximum sequence number of the parameter table is 100020388422000000001235,because 100020388422000000001235-! Because Nmax is not updated, the a process needs to re-fetch the maximum sequence number of the parameter table and assign Nmax. If there is no other process interference, the maximum sequence number is updated from 100020388422000000001234 to 100020388422000000001244, and the update statement may be:

“Update parameter_info SET TI_ID＝‘100020388422000000001244’where TI_ID＝‘100020388422000000001234’”。

and step 5, if the actual updated record number is not 0, the current sequence number segment is successfully occupied, and the safe use can be realized. Otherwise, the read data is dirty data, the number segment is used by other threads in advance, and the steps 3, 4 and 5 are repeated until the actual updated record number is not 0.

Specifically: at this time, if the update is successful, mySQL returns an update number of 1, but a scenario appears in which the second decision box in fig. 11, before updating "update" and before determining "parameter table maximum number=n _max After "there is a millisecond delay, and if someone else updates the table, the update statement of the current process returns an update number of 0, and then steps 3-5 are repeated.

The specific application example of the MySQL data batch addition method realized by the MySQL data batch addition system provided by the application is different from the sequence of oracle in that:

1. the sequence of oracle is a caching method which does not occupy a disk and occupies a memory, uncontrollable factors such as downtime, restarting and instantaneous disconnection occur in a database, the content in the memory can completely disappear, a scene with discontinuous sequence numbers can occur, in the application example of the application, information is stored in the disk, double judgment is performed, the disk is allocated first, then data is inserted, the final consistency of the data is ensured, and a scene with discontinuous data in the middle of a transaction can not occur.

2. The sequence of oracle is a simple counting method, and in the application example of the application, the fields of the parameter table can manually define corresponding business logic, so that the fields have business significance, and the last self-increasing field can be used as a main key to improve the query efficiency.

Based on the above, the application example is an exclusive lock method based on MySQL when multithreading accesses to a database, and is applicable to a scene with increased frequency and less modification, and compared with the traditional direct table insertion data, the application example has the following advantages:

And 1, independently designing a sequence number recording parameter table, decoupling with a normal service system, facilitating subsequent service expansion, and simultaneously fusing different elements into the parameter table according to different requirements of services without affecting the functions of the stock service. When a great number of insertion operations occur to a user, only one piece of data of one table is required to be queried, and the source table is not required to be traversed.

2 through the 'light and handy' loop design (two judgment boxes in the flow chart), the operation of locking the database is avoided, and the problems of multithreading safety access and batch uploading efficiency are solved on the premise of small increase of overhead.

In order to solve the problems of waste of a large amount of resources, low intelligent degree, low new addition efficiency and the like in the existing data addition mode for the MySQL database from the hardware level, the application provides an embodiment of electronic equipment for realizing all or part of contents in the MySQL data batch addition method, wherein the electronic equipment specifically comprises the following contents:

fig. 12 is a schematic block diagram of a system configuration of an electronic device 9600 of an embodiment of the present application. As shown in fig. 12, the electronic device 9600 may include a central processor 9100 and a memory 9140; the memory 9140 is coupled to the central processor 9100. Notably, this fig. 12 is exemplary; other types of structures may also be used in addition to or in place of the structures to implement telecommunications functions or other functions.

In one embodiment, mySQL data batch add-on functionality may be integrated into the central processor. Wherein the central processor may be configured to control:

As can be seen from the above description, by setting the sequence number record parameter table for storing the correspondence between the source table identifier of each data type and the current maximum value of the source table primary key, the electronic device provided in the embodiment of the present application can make only one table, which is the sequence number record parameter table, need to be accessed once in the batch data adding process for MySQL database, so that the efficiency and reliability of the batch data adding process for MySQL database can be effectively improved, the waste of a large amount of resources can be avoided, the memory occupancy can be effectively reduced, and the memory occupancy up to 90% can be reduced through detection; the intelligent degree of the batch data adding process aiming at the MySQL database can be effectively improved, and the running stability and reliability of the MySQL database can be further improved.

In another embodiment, the MySQL data batch adding device may be configured separately from the central processor 9100, for example, the MySQL data batch adding device may be configured as a chip connected to the central processor 9100, and the MySQL data batch adding function is implemented under the control of the central processor.

As shown in fig. 12, the electronic device 9600 may further include: a communication module 9110, an input unit 9120, an audio processor 9130, a display 9160, and a power supply 9170. It is noted that the electronic device 9600 need not include all of the components shown in fig. 12; in addition, the electronic device 9600 may further include components not shown in fig. 12, and reference may be made to the related art.

As shown in fig. 12, the central processor 9100, sometimes referred to as a controller or operational control, may include a microprocessor or other processor device and/or logic device, which central processor 9100 receives inputs and controls the operation of the various components of the electronic device 9600.

The memory 9140 may be, for example, one or more of a buffer, a flash memory, a hard drive, a removable media, a volatile memory, a non-volatile memory, or other suitable device. The information about failure may be stored, and a program for executing the information may be stored. And the central processor 9100 can execute the program stored in the memory 9140 to realize information storage or processing, and the like.

The input unit 9120 provides input to the central processor 9100. The input unit 9120 is, for example, a key or a touch input device. The power supply 9170 is used to provide power to the electronic device 9600. The display 9160 is used for displaying display objects such as images and characters. The display may be, for example, but not limited to, an LCD display.

The memory 9140 may be a solid state memory such as Read Only Memory (ROM), random Access Memory (RAM), SIM card, etc. But also a memory which holds information even when powered down, can be selectively erased and provided with further data, an example of which is sometimes referred to as EPROM or the like. The memory 9140 may also be some other type of device. The memory 9140 includes a buffer memory 9141 (sometimes referred to as a buffer). The memory 9140 may include an application/function storage portion 9142, the application/function storage portion 9142 storing application programs and function programs or a flow for executing operations of the electronic device 9600 by the central processor 9100.

The memory 9140 may also include a data store 9143, the data store 9143 for storing data, such as contacts, digital data, pictures, sounds, and/or any other data used by an electronic device. The driver storage portion 9144 of the memory 9140 may include various drivers of the electronic device for communication functions and/or for performing other functions of the electronic device (e.g., messaging applications, address book applications, etc.).

The communication module 9110 is a transmitter/receiver 9110 that transmits and receives signals via an antenna 9111. A communication module (transmitter/receiver) 9110 is coupled to the central processor 9100 to provide input signals and receive output signals, as in the case of conventional mobile communication terminals.

Based on different communication technologies, a plurality of communication modules 9110, such as a cellular network module, a bluetooth module, and/or a wireless local area network module, etc., may be provided in the same electronic device. The communication module (transmitter/receiver) 9110 is also coupled to a speaker 9131 and a microphone 9132 via an audio processor 9130 to provide audio output via the speaker 9131 and to receive audio input from the microphone 9132 to implement usual telecommunications functions. The audio processor 9130 can include any suitable buffers, decoders, amplifiers and so forth. In addition, the audio processor 9130 is also coupled to the central processor 9100 so that sound can be recorded locally through the microphone 9132 and sound stored locally can be played through the speaker 9131.

The embodiment of the present application further provides a computer readable storage medium capable of implementing all the steps in the MySQL data batch addition method in the above embodiment, where the computer readable storage medium stores a computer program, and when the computer program is executed by a processor, the computer program implements all the steps in the MySQL data batch addition method in the above embodiment that the execution subject is a server or a client, for example, the processor implements the following steps when executing the computer program:

As can be seen from the above description, the computer readable storage medium provided in the embodiments of the present application, by setting the sequence number record parameter table for storing the correspondence between the source table identifier of each data type and the current maximum value of the source table primary key, can enable only one table, which is the sequence number record parameter table, to be accessed once in the batch data adding process for MySQL database, can effectively improve the efficiency and reliability of the batch data adding process for MySQL database, avoid the waste of a large amount of resources, effectively reduce the memory occupancy, and can reduce the memory occupancy up to 90% through detection; the intelligent degree of the batch data adding process aiming at the MySQL database can be effectively improved, and the running stability and reliability of the MySQL database can be further improved.

It will be apparent to those skilled in the art that embodiments of the present invention may be provided as a method, apparatus, or computer program product. Accordingly, the present invention may take the form of an entirely hardware embodiment, an entirely software embodiment or an embodiment combining software and hardware aspects. Furthermore, the present invention may take the form of a computer program product embodied on one or more computer-usable storage media (including, but not limited to, disk storage, CD-ROM, optical storage, and the like) having computer-usable program code embodied therein.

The present invention is described with reference to flowchart illustrations and/or block diagrams of methods, apparatus (devices), and computer program products according to embodiments of the invention. It will be understood that each flow and/or block of the flowchart illustrations and/or block diagrams, and combinations of flows and/or blocks in the flowchart illustrations and/or block diagrams, can be implemented by computer program instructions. These computer program instructions may be provided to a processor of a general purpose computer, special purpose computer, embedded processor, or other programmable data processing apparatus to produce a machine, such that the instructions, which execute via the processor of the computer or other programmable data processing apparatus, create means for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be stored in a computer-readable memory that can direct a computer or other programmable data processing apparatus to function in a particular manner, such that the instructions stored in the computer-readable memory produce an article of manufacture including instruction means which implement the function specified in the flowchart flow or flows and/or block diagram block or blocks.

These computer program instructions may also be loaded onto a computer or other programmable data processing apparatus to cause a series of operational steps to be performed on the computer or other programmable apparatus to produce a computer implemented process such that the instructions which execute on the computer or other programmable apparatus provide steps for implementing the functions specified in the flowchart flow or flows and/or block diagram block or blocks.

The principles and embodiments of the present invention have been described in detail with reference to specific examples, which are provided to facilitate understanding of the method and core ideas of the present invention; meanwhile, as those skilled in the art will have variations in the specific embodiments and application scope in accordance with the ideas of the present invention, the present description should not be construed as limiting the present invention in view of the above.

Claims

1. A MySQL data batch adding method is characterized by comprising the following steps:

2. The batch adding method of MySQL data according to claim 1, wherein the fields of the primary key of the source table consist of custom type fields and preset self-adding fields which are sequentially arranged;

The custom type field includes a location number and/or an enterprise code;

3. The MySQL data batch addition method according to claim 1, wherein the target data type corresponding to each target data corresponding to the target batch upload request for the target MySQL database and the total number of each target data under the target data type respectively include:

respectively obtaining the corresponding target data types of the target data;

4. The batch adding method of MySQL data according to claim 2, wherein searching the current maximum value of the source table primary key corresponding to the target data type from the preset sequence number record parameter table comprises:

5. The MySQL data batch addition method according to claim 1, wherein the updating the current maximum value of the source table primary key corresponding to the target data type in the sequence number record parameter table based on the target value and the total number of each target data under the target data type comprises:

6. The MySQL data batch addition method of claim 5, further comprising:

7. The MySQL data batch addition method according to claim 5, further comprising, after inserting each target data of the target data type into the source table corresponding to the target data type based on the updated current maximum value of the source table primary key corresponding to the target data type:

8. The batch MySQL data enrichment method according to any one of claims 1-7, wherein the target MySQL database is a blockchain-based MySQL database.

9. The utility model provides a MySQL data batch newly-increased device which characterized in that includes:

10. An electronic device comprising a memory, a processor and a computer program stored on the memory and executable on the processor, wherein the processor implements the MySQL data batch addition method of any one of claims 1 to 8 when the computer program is executed by the processor.

11. A computer readable storage medium having stored thereon a computer program, which when executed by a processor implements the MySQL data batch addition method of any one of claims 1 to 8.