CN113326277A

CN113326277A - MySQL data batch adding method and device

Info

Publication number: CN113326277A
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: 2021-08-31
Anticipated expiration: 2041-06-30
Also published as: CN113326277B

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: respectively corresponding target data types of all target data corresponding to the target batch uploading request aiming at the target MySQL database and the total number of all target data under the target data types; searching the current maximum value of the source table main key corresponding to the target data type from a preset sequence number recording parameter table as a target value; and updating the current maximum value of the primary key of the source 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 according to the current maximum value of the primary key of the source table. The method and the device can effectively improve the efficiency and the reliability of the batch data adding process of 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.

Description

MySQL data batch adding method and device

Technical Field

The application relates to the technical field of data processing, in particular to the technical field of block chains, and specifically relates to a MySQL data batch adding method and device.

Background

Supply chain finance has become a good tool for accelerating industry fusion and resource integration, widening financing channels of small and micro enterprises and ensuring capital flow to entity economy. The digital credit evidence tool and the like have detachable and circulatable functions, and by depending on a scene financial service platform and an alliance mechanism platform of financial mechanisms such as banks and the like, the functions of issuing, delivering, increasing credit, transferring, signing, pledging, cash withdrawal and the like of the digital credit evidence can inject core enterprise credit into the whole industry chain, thereby solving the problems of traditional supply chain finance. In the traditional credit granting, the single enterprise information is uploaded in batch because the operator is single, the concurrency is not large, and the dirty data is few. However, in a financial system based on the whole supply chain, the trade information is huge, a single service staff cannot maintain the trade information, and a team is needed to maintain the trade information of the whole financial system, so that a plurality of people can upload the trade information in batch at the same time. And when a large concurrent data writing situation is encountered, the problems of long time consumption and excessive resource consumption occur. That is to say, in the batch data adding process, the source table needs to be traversed to obtain the maximum value of the primary key for each added data, so that the maximum value of the primary key needs to be taken many times and the new addition is performed after the maximum serial number is generated, and therefore, the existing data adding method for the MySQL database has the problems of a large amount of resources being wasted, low intelligent degree, low adding efficiency and the like.

Disclosure of Invention

Aiming at the problems in the prior art, the application provides a 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 problem, the application provides the following technical scheme:

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

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

searching the current maximum value of the source table main key corresponding to the target data type from a preset sequence number recording 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 recording 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 primary key of the source table;

and updating the current maximum value of the 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 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 field of the primary key of the source table consists of a custom type field and a preset self-increment field which are sequentially arranged;

the custom type field comprises a region number and/or an enterprise code;

the source table which respectively and uniquely corresponds to each source table identifier is used for storing the corresponding relation between each source table main key and the data information;

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

respectively reading the current maximum value respectively corresponding to each custom type field in the source table of each data type from the source table of each data type according to the custom type field in the source table field of each data type, so as to take the current maximum value respectively corresponding to each custom type field in each source table as the current maximum value of the source table main key corresponding to the data type to which the corresponding source table belongs;

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 recording parameter table.

Further, the respectively corresponding target data types of 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 types include:

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 acquiring target data types corresponding to the target data;

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

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

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

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

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 under 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, summing the target value and the total number of each target data in 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 recording parameter table according to the corresponding summing result.

Further, still include:

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 judged to be not equal to the target value of the source table main key corresponding to the target data type, the target value of the source table main key corresponding to the target data type is obtained again, and 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 is judged again.

Further, after 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 primary key of the source table 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 addition result identifier shows that the data addition of the source table corresponding to the target data type fails, judging 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 again.

Further, the target MySQL database is a block chain-based MySQL database.

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

the quantity confirmation module is used for respectively corresponding target data types of all target data corresponding to the target batch uploading requests aiming at the MySQL database and the total quantity of all 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 recording 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 serial number recording 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 primary key of the source table;

and the batch adding module is used for updating the current maximum value of the primary key of the source table corresponding to the target data type in the sequence number recording parameter table based on the target value and the total number of all target data under the target data type, and inserting all 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 primary key of the source table corresponding to the target data type.

In a third aspect, the present application provides an electronic device, which includes a memory, a processor, and a computer program that is stored in the memory and is executable on the processor, where the processor implements the MySQL data batch adding method when executing the program.

In a fourth aspect, the present application provides a computer-readable storage medium, on which a computer program is stored, where the computer program, when executed by a processor, implements the MySQL data batch adding method.

According to the technical scheme, the MySQL data batch adding method and device provided by the application comprise the following steps: according to target data types corresponding to all target data corresponding to target batch uploading requests of a target MySQL database and the total number of all target data under the target data types; searching the current maximum value of the source table main key corresponding to the target data type from a preset sequence number recording 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 recording 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 primary key of the source table; updating the current maximum value of the main key of the source table 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, inserting each target data in the target data type into the source table corresponding to the target data type based on the current maximum value of the main key of the source table corresponding to the updated target data type, and setting the 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 main key of the source table, so that only one table of the sequence number recording parameter table needs to be accessed once 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 number of resources is avoided, and the memory occupation rate is effectively reduced, the memory occupancy rate can be reduced by 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.

Drawings

In order to more clearly illustrate the embodiments of the present application or the technical solutions in the prior art, the drawings needed to be used in the description of the embodiments or the prior art will be briefly introduced below, and it is obvious that the drawings in the following description are some embodiments of the present application, and it is obvious for those skilled in the art to obtain other drawings based on these drawings without creative efforts.

Fig. 1 is an 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 batch addition method for MySQL data in the embodiment of the present application.

Fig. 3 is a second flowchart of the MySQL data batch adding method in the embodiment of the present application.

Fig. 4 is a third flowchart of the MySQL data batch adding method in the embodiment of the present application.

Fig. 5 is a fourth flowchart illustrating a batch addition method for MySQL data in the embodiment of the present application.

Fig. 6 is a fifth flowchart illustrating a batch addition method for MySQL data in the embodiment of the present application.

Fig. 7 is a sixth flowchart of the MySQL data batch adding method in the embodiment of the present application.

Fig. 8 is a seventh flowchart illustrating a MySQL data batch adding method in the embodiment of the present application.

Fig. 9 is a schematic structural diagram of a MySQL data batch adding apparatus 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 illustrating the operation 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

In order to make the objects, technical solutions and advantages of the embodiments of the present application clearer, 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 some embodiments of the present application, but not all 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.

It should be noted that the method and the device for batch addition of MySQL data disclosed in the present application may be used in the technical field of a block chain, and may also be used in any field other than the technical field of a block chain.

In the batch data adding process, the source table needs to be traversed to obtain the maximum value of the primary key for each added data, so that the maximum value of the primary key needs to be taken for multiple times, the maximum serial number is generated, and then the new addition is carried out, and therefore the problems that a large amount of resources are wasted, the intelligent degree is low, the new addition efficiency is low and the like exist in the existing data adding mode for the MySQL database. Based on this, the embodiment of the application provides a MySQL data batch adding method, which includes the steps of respectively corresponding target data types to each target data corresponding to a target batch uploading request of a target MySQL database and the total number of each target data under the target data types; searching the current maximum value of the source table main key corresponding to the target data type from a preset sequence number recording 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 recording 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 primary key of the source table; updating the current maximum value of the main key of the source table 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, inserting each target data in the target data type into the source table corresponding to the target data type based on the current maximum value of the main key of the source table corresponding to the updated target data type, and setting the 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 main key of the source table, so that only one table of the sequence number recording parameter table needs to be accessed once 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 number of resources is avoided, and the memory occupation rate is effectively reduced, the memory occupancy rate can be reduced by 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.

Based on the above content, 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, the MySQL data batch adding device may receive a target batch upload request for a target MySQL database sent by the client device, 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 the 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 devices may include smart phones, tablet electronic devices, network set-top boxes, portable computers, desktop computers, Personal Digital Assistants (PDAs), in-vehicle devices, smart wearable devices, and the like. Wherein, intelligence wearing equipment can include intelligent glasses, intelligent wrist-watch, intelligent bracelet etc..

In another practical application scenario, the foregoing MySQL data batch adding apparatus performs the MySQL data batch adding part in the server with the content, or all operations are completed in the client device. The selection may be specifically performed according to the processing capability of the client device, the limitation of the user usage scenario, and the like. This is not a limitation of the present application. If all the operations are completed in the client device, the client device may further include a processor for performing specific processing of batch addition of MySQL data.

The client device may have a communication module (i.e., a communication unit), and may be communicatively connected to a remote server to implement data transmission with the server. The server may include a server on the task scheduling center side, and in other implementation scenarios, the server may also include a server on an intermediate platform, for example, a server on a third-party server platform that is communicatively linked to the task scheduling center server. The server may include a single computer device, or may include a server cluster formed by a plurality of servers, or a server structure of a distributed apparatus.

The server and the client device may communicate using any suitable network protocol, including network protocols not yet developed at the filing date of this application. The network protocol may include, for example, a TCP/IP protocol, a UDP/IP protocol, an HTTP protocol, an HTTPS protocol, or the like. Of course, the network Protocol may also include, for example, an RPC Protocol (Remote Procedure Call Protocol), a REST Protocol (Representational State Transfer Protocol), and the like used above the above 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 the actual traffic, or that is illegal in data format, and that has irregular 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 rolled back. Before rolling back, however, another transaction B reads the value of data item X (after A updates), A rolls back the transaction and the data item recovers its original value. Transaction B reads a "temporary" value, i.e., dirty data, for data item X.

The following embodiments and application examples are specifically and individually described in detail.

In order to solve the problems that a large amount of resources are wasted, the intelligence degree is low, the adding efficiency is low and the like in the existing data adding mode for the MySQL database, the application provides an embodiment of a MySQL data batch adding method, which is suitable for a block chain scene, and referring to fig. 2, the MySQL data batch adding method executed by the MySQL data batch adding device specifically includes the following contents:

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

In step 100, information such as a contract uploaded by a user foreground is uploaded by a CSV, a counter is first introduced and initialized to n equal to 0, then files can be read line by line from a second line continuously through an executive readLine (), and if a 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 CSVs of this time is obtained, and assuming that n is 10, this time 10 pieces of data are inserted.

Step 200: searching the current maximum value of the source table main key corresponding to the target data type from a preset sequence number recording 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 recording 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 primary key of the source table.

In step 200, the query sequence number records the current maximum value of the primary key of the source table, such as the maximum sequence number of the certificate, in the parameter table, and records the current maximum value as N_max。

Step 300: and updating the current maximum value of the 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 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, in the MySQL data batch adding method provided in the embodiment of the present application, by setting the sequence number recording 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, the sequence number recording parameter table, needs to be accessed at one time 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 rate is effectively reduced, and the memory occupancy rate can be reduced by 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 distinguish the current maximum value of the primary key of the source table in the source table according to the area code, the enterprise code and the like, in one embodiment of the MySQL data batch adding method provided by the application, the field of the primary 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 comprises a region number and/or an enterprise code; the source table which respectively and uniquely corresponds to each source table identifier is used for storing the corresponding relation between each source table main key and the data information; referring to fig. 3, before step 100 of the MySQL data batch adding method, the following contents are also specifically included:

step 010: according to the user-defined type fields in the source table of each data type, respectively reading the current maximum values respectively corresponding to the user-defined type fields in the source table of each data type from the source table of each data type, so that the current maximum values respectively corresponding to the user-defined type fields in each source table are all used as the current maximum values of the source table main keys corresponding to the data type to which the corresponding source table belongs.

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 recording parameter table.

Specifically, for example, the maximum serial number of the voucher in the source table such as the trade information table can be extracted through the MAX function. And writes this number into the sequence number recording parameter table by insert.

For example: taking the source table as a trade information table (trade _ info) as an example, the primary key (TI _ ID) of the source table is composed of 6-digit area number + 6-digit enterprise code + 16-digit self-increment field, because the area and the main enterprise are relatively fixed in the supply chain financial transaction link, the first 12 digits are fixed values, the latter is self-increment field, and the information is continuously increased after being inserted. The maximum value of the source table primary key (assumed to be 100020388422000000001234) 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 in the current sequence number recording parameter table (parameter _ info), and the field name of the trade information table (rade _ info) is still TI _ ID in the sequence number recording parameter table (parameter _ info). If there are other strongly associated information tables in this table, the other information table hosts can be synchronized based on the primary key TI _ ID by means of multi-table joint check, and merged into a piece of row information to be inserted into the sequence number recording parameter table (parameter _ info).

As can be seen from the above description, according to the MySQL data batch adding method provided in the embodiment of the present application, according to the custom type fields in the source table fields in the source tables of each data type, the current maximum values of the source table main keys corresponding to the custom type fields in the source tables of each data type are respectively read from the source tables of each data type, the current maximum values corresponding to each custom type field in each source table can be all used as the current maximum values of the source table main keys of the source tables, and then the current maximum values of the source table main keys in the source tables can be distinguished according to the region number and/or the enterprise code, so as to effectively improve the efficiency and accuracy of searching the current maximum values in the subsequent self-sequence number recording tables, and further improve the MySQL data batch adding efficiency and accuracy.

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

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

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

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

Specifically, after the user uploads information such as a contract, the total number of records to be inserted at this time can be calculated and recorded as n before the certificate information passes 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 the CSV, a counter is first introduced and initialized to n equal to 0, then the file can be read line by line from the second line continuously by executing a 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 CSVs of this time is obtained, and if n is 10, 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 embodiment of the present application, by receiving the target batch uploading request for the target MySQL database, and respectively obtaining the target data types corresponding to each target data, the efficiency and reliability for determining the data types and the total number of each target data in each type can be effectively improved, and further, the efficiency and reliability for MySQL data batch adding can be further improved.

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

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

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

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

As can be seen from the above description, in the MySQL data batch adding method provided in the embodiment of the present application, by respectively obtaining the target custom type field corresponding to each target data from the target batch upload request for the target MySQL database, and searching the current maximum value of the source table main key where the target custom type field is located according to the target custom type field corresponding to each target data, the efficiency and accuracy for searching the current maximum value of the source table main key corresponding to the target data type can be further improved, and further the efficiency and accuracy for MySQL data batch adding can be further improved.

In order to avoid the operation of locking a database, and solve the problem of multi-thread security access and batch uploading efficiency on the premise of increasing little overhead, in an embodiment of the MySQL data batch adding method provided by 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 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, if so, executing 320.

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 primary key corresponding to the target data type in the sequence number recording parameter table according to the corresponding addition result.

Step 330: and 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 primary key of the source table corresponding to the target data type.

As can be seen from the above description, in the MySQL data batch adding method provided in this embodiment 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 recording parameter table is equal to the target value of the source table primary key corresponding to the target data type, the problems of multithread secure access and batch upload efficiency can be solved by avoiding the operation of database locking on the premise of increasing a small overhead.

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

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 process returns to step 220 to obtain the target value of the source table primary key corresponding to the target data type again, 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.

From the above description, the MySQL data batch adding method provided in the embodiment of the present application can avoid the operation of locking the database through a "light" circular design, and further solve the problems of multithreading security access and batch uploading efficiency on the premise of increasing a small overhead.

In order to further avoid the operation of locking the database, in an embodiment of the MySQL data batch adding method provided in the present application, referring to fig. 8, the following content is also specifically included after step 300 in the MySQL data batch adding method:

step 400: and receiving a data adding result identifier of a source table corresponding to the target data type.

Step 500: and judging whether the data addition result identifier shows that the data addition of the source table corresponding to the target data type is successful, if so, ending the current process, otherwise, returning to the step 310 to judge 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 again.

In order to further avoid the operation of locking the database, in an embodiment of the MySQL data batch adding method provided by the application, the target MySQL database in the MySQL data batch adding method is a block chain-based MySQL database.

From the above description, the MySQL data batch adding method provided in the embodiment of the present application can provide a dedicated MySQL data batch adding method for the block chain technology, and further can effectively improve the operation reliability and stability of the block chain.

In terms of software, in order to solve the problems of a large amount of resources being wasted, a low degree of intelligence being achieved, and a low efficiency of data addition in the existing MySQL database, the present application provides an embodiment of a MySQL data batch addition apparatus for executing all or part of the content in the MySQL data batch addition method, referring to fig. 9, where the MySQL data batch addition apparatus specifically includes the following contents:

and the quantity confirmation module 10 is configured to respectively correspond to the target data types corresponding to the target data corresponding to the target batch upload request for the MySQL database, and a total quantity of the target data under the target data types.

In the quantity confirmation module 10, the information such as the contract uploaded by the user foreground is uploaded by the CSV, a counter is first introduced and initialized to n equal to 0, then the file can be read line by line from the second line continuously through an execution program readLine (), and if the return value is not null, the counter is continuously added with 1 until the return value is null. At this time, the total number of CSVs of this time is obtained, and assuming that n is 10, this time 10 pieces of data are inserted.

A maximum value obtaining module 20, configured to search a preset sequence number recording parameter table for a current maximum value of a source table primary key corresponding to the target data type, 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 recording 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 primary key of the source table.

In the maximum value obtaining module 20, the query sequence number records the current maximum value of the primary key of the source table, such as the maximum sequence number of the certificate, in the parameter table, and records the current maximum value as N_max。

And the batch adding module 30 is configured to update 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 piece of target data in the target data type, and insert each piece of 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 primary key corresponding to the target data type.

The embodiment of the MySQL data batch adding device provided by the 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 of the MySQL data batch adding device are not described herein again, and refer to the detailed description of the embodiment of the method.

As can be seen from the above description, the MySQL data batch adding device provided in the embodiment of the present application, by setting the sequence number recording 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 access to the sequence number recording parameter table 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 rate, and can reduce the memory occupancy rate by 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 scheme, the application also provides a specific application example of the MySQL data batch adding method realized by applying the MySQL data batch adding system, and relates to how to simultaneously upload a large amount of trade information into the system in batches in a distributed system in a supply chain financial transaction system.

In a financial system based on the whole supply chain, the trade information is huge, single service personnel cannot maintain the trade information, a team is needed to maintain the trade information of the whole financial system, and multiple persons can upload the trade information in batch at the same time. And when a large concurrent data writing situation is encountered, the problems of long time consumption and excessive resource consumption occur. At this time, if a series of voucher codes are manually customized according to the traditional method and are put in a table as the primary key and trade information, the following problems can occur:

1) the maximum value of the database needs to be traversed for each generation, the time consumption is long, and the resource consumption is overlarge. Particularly, when the certificates are uploaded in batches, the maximum value is required to be taken and the maximum sequence number is generated for many times, so that a large amount of resources are wasted, and the efficiency is greatly influenced.

2) There is a serious problem of dirty data reading when accessing multiple threads. For example, the A, B thread takes the same current maximum, i.e., generates the same credential code, resulting in a credential save error.

In order to solve the above problems, the application example of the present application provides a sequence number recording parameter table, which uses regions and parameter codes as main keys to facilitate subsequent service expansion, can generate different sequence numbers according to different service regions, and avoids database locking operation through a 'light' cycle design, thereby solving the problems of multithreading safety access and batch uploading efficiency on the premise of increasing small overhead.

Because the MySQL database does not provide a Sequence method which can be similarly used for an Oracle database, the Sequence generation mechanism is referred to in the application, and a Sequence number generation method is compiled. Designing a sequence number recording parameter table, recording the current maximum sequence number, and inquiring the table before generating a new sequence number. And the problem of reading dirty data in multi-thread access is solved by adding where condition matching when updating the record.

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

the batch uploading module 101: the user can upload the import contract and the receipt in batch in the form of FTP on the foreground page. The total number of uploaded messages is calculated and transmitted to the sequence number recording parameter table module 102.

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

The trade information table module 103: and the information in the batch uploading module 101 is stored in the database. The maximum credential information number from the sequence number recording parameter table module 102 is received.

Referring to fig. 11, the sequence number recording parameter table module 102 has the following operation principle:

step 1, designing a sequence number recording parameter table, and initializing. And taking out the maximum sequence number of the voucher of the trade information table in the trade information table module 103 through the MAX function, and writing the sequence number into a sequence number recording parameter table through insert.

And 2, after the batch uploading module 101 uploads information such as contracts and the like, calculating the total record number required to be inserted at this time and recording the total record number as n before the certificate information passes 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 and initialized to be n-0, then, files can be read line by line from the second line continuously through an executive program readLine (), and if the return value is not null, the counter is added by 1 continuously until the return value is null. At this time, the total number of CSVs of this time is obtained, and if n is 10, we will insert 10 pieces of data this time.

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

For example, the current maximum number of voucher N_maxIs "100020388422000000001234".

Step 4, updating the maximum sequence number in the sequence number recording parameter table as follows: the current maximum sequence number + the number of the inserted records, and the updating conditions are as follows: maximum serial number N in parameter table_max。

Specifically, the maximum sequence number in the update sequence number recording parameter table is: current maximum sequence number (e.g., 100020388422000000001234) + the number of this insertion record (e.g., 10). here, the update condition is set as the maximum sequence number N in the parameter table_maxThis is because a scenario may occur, when the maximum value of the A process is 100020388422000000001234, 10 entries are ready to be inserted, and during the middle fraction of a second, the B process updates the maximum sequence number of the parameter table, such as adding 1 entry, when the maximum sequence number of the parameter table is 100020388422000000001235, because 100020388422000000001235! Since Nmax is not available, the a process needs to re-assign the maximum sequence number of the parameter table to 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 5, if the actual updating record number is not 0, the current preamble number segment is successfully occupied, and the method can be safely used. Otherwise, the read data is indicated to be dirty data, the number segment is used by other threads in advance, and the steps 3, 4 and 5 need to be repeated until the actual update record number is not 0.

Specifically, the method comprises the following steps: 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 is used to determine that the maximum sequence number of the parameter table is N before updating the "update" and before determining that the "parameter table is the maximum sequence number of N ═ N_max"there is a delay of milliseconds after that, at this point if someone else updates the table, the update statement of the current process will returnThe update number is 0, and then steps 3-5 are repeated.

The difference between the specific application example of the MySQL data batch adding method realized by applying the MySQL data batch adding system and the sequence of oracle is as follows:

1. the sequence of oracle is a caching method which does not occupy a disk and occupies a memory, when uncontrollable factors such as downtime, restart and instantaneous interruption occur in a database, the content in the memory can completely disappear, and a scene with discontinuous sequence numbers can possibly occur.

2. 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 final self-increment fields can be used as the main keys to improve the query efficiency.

Based on this, the application example of the application is an exclusive locking method when the MySQL-based multithread accesses the database, is suitable for a frequently-increased and less-modified scene, and has the following advantages compared with the traditional method of directly inserting data into the table:

the sequence number recording parameter table is designed independently and decoupled with a normal service system, so that subsequent service expansion is facilitated, different elements can be fused into the parameter table according to different requirements of services, and the stock service function is not influenced. When a user has a large number of insertion operations, only one piece of data of one table needs to be inquired, and the source table does not need to be traversed.

2, by means of a 'light' cycle design (two judgment boxes in a flow chart), the operation of locking a database is avoided, and on the premise of increasing small overhead, the method solves the problems of multithreading safety access and batch uploading efficiency.

In terms of hardware, in order to solve the problems of a large amount of resources being wasted, low intelligent degree, low adding efficiency and the like in the existing data adding method for the MySQL database, the present application provides an embodiment of an electronic device for implementing all or part of contents in the MySQL data batch adding method, where the electronic device specifically includes the following contents:

fig. 12 is a schematic block diagram of a system configuration of an electronic device 9600 according to an embodiment of the present application. As shown in fig. 12, the electronic device 9600 can 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 structure to implement telecommunications or other functions.

In one embodiment, the MySQL data bulk add function can be integrated into the central processor. Wherein the central processor may be configured to control:

As can be seen from the above description, in the electronic device provided in the embodiment of the present application, by setting the sequence number recording 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 access to the sequence number recording parameter table is needed 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 rate is effectively reduced, and the memory occupancy rate can be reduced by 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 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 realized by 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 also does not necessarily include all of the components shown in fig. 12; further, the electronic device 9600 may further include components not shown in fig. 12, which can be referred to in the related art.

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

The memory 9140 can 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 relating to the failure may be stored, and a program for executing the information may be stored. And the central processing unit 9100 can execute the program stored in the memory 9140 to realize information storage or processing, or 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. Power supply 9170 is used to provide power to electronic device 9600. The display 9160 is used for displaying display objects such as images and characters. The display may be, for example, an LCD display, but is not limited thereto.

The memory 9140 can be a solid state memory, e.g., Read Only Memory (ROM), Random Access Memory (RAM), a SIM card, or the like. There may also be a memory that holds information even when power is off, can be selectively erased, and is provided with more data, an example of which is sometimes called an EPROM or the like. The memory 9140 could also be some other type of device. 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 being used for storing application programs and function programs or for executing a flow of operations of the electronic device 9600 by the central processor 9100.

The memory 9140 can also include a data store 9143, the data store 9143 being used to store 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 for the electronic device for communication functions and/or for performing other functions of the electronic device (e.g., messaging applications, contact book applications, etc.).

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

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, 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 receive audio input from the microphone 9132, thereby implementing ordinary telecommunications functions. The audio processor 9130 may include any suitable buffers, decoders, amplifiers and so forth. In addition, the audio processor 9130 is also coupled to the central processor 9100, thereby enabling recording locally through the microphone 9132 and enabling locally stored sounds to be played through the speaker 9131.

An embodiment of the present application further provides a computer-readable storage medium capable of implementing all steps in the MySQL data batch adding method in the foregoing 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 steps of the MySQL data batch adding method in which an execution subject is a server or a client in the foregoing embodiment, for example, when the processor executes the computer program, the processor implements the following steps:

As can be seen from the above description, in the computer-readable storage medium provided in this embodiment of the present application, by setting the sequence number recording 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 recording parameter table, needs to be accessed at one time 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 rate is effectively reduced, and the memory occupancy rate can be reduced by as high as 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.

As will be appreciated by one skilled in the art, 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 flow diagrams and/or block diagrams, and combinations of flows and/or blocks in the flow diagrams 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 principle and the implementation mode of the invention are explained by applying specific embodiments in the invention, and the description of the embodiments is only used for helping to understand the method and the core idea of the invention; meanwhile, for a person skilled in the art, according to the idea of the present invention, there may be variations in the specific embodiments and the application scope, and in summary, the content of the present specification should not be construed as a limitation to the present invention.

Claims

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

2. The MySQL data batch addition method of claim 1, wherein the fields of the primary keys of the source table are composed of custom type fields and preset auto-increment fields which are sequentially arranged;

the custom type field comprises a region number and/or an enterprise code;

3. The MySQL data batch addition method of claim 1, wherein the adding of the target data types corresponding to the target data corresponding to the target batch upload request of the target MySQL database and the total number of the target data under the target data types includes:

respectively acquiring target data types corresponding to the target data;

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

5. The MySQL data batch addition method of 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 recording parameter table based on the target value and the total number of each target data under the target data type includes:

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

7. The MySQL data batch addition method of claim 5, after 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 primary key of the source table corresponding to the target data type, further comprising:

8. The MySQL data batch adding method of any one of claims 1 to 7, wherein the target MySQL database is a block chain-based MySQL database.

9. The utility model provides a MySQL data adds device in batches 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 adding method of any of claims 1 to 8 when executing the computer program.

11. A computer-readable storage medium on which a computer program is stored, the computer program, when being executed by a processor, implementing the MySQL data batch addition method according to any one of claims 1 to 8.