CN115168371A

CN115168371A - Data processing method and device, computer equipment and storage medium

Info

Publication number: CN115168371A
Application number: CN202210592498.XA
Authority: CN
Inventors: 张倩倩; 王春生
Original assignee: Shenzhen Xishima Data Technology Co ltd
Current assignee: Shenzhen Xishima Data Technology Co ltd
Priority date: 2022-05-28
Filing date: 2022-05-28
Publication date: 2022-10-11

Abstract

The application discloses a data processing method, a data processing device, computer equipment and a storage medium. The method comprises the following steps: responding to the fact that the version of the MySQL database is a first version, screening out a first database table needing to be updated from an original database table according to input query conditions, wherein JSON character strings are stored in the first database table; updating the first database table by using a character string function to obtain a second database table; updating the second database table into the original database table. By implementing the method, when the previous version of the MySQL database 5.7 is used, part of contents in JSON data stored in the MySQL database can be updated in batch, and the JSON data updating efficiency is further improved.

Description

Data processing method and device, computer equipment and storage medium

Technical Field

The present application relates to the field of computer technologies, and in particular, to a data processing method and apparatus, a computer device, and a storage medium.

Background

JSON (JavaScript Object notification) is a lightweight data exchange format, has the characteristics of small data size, network transmission block, convenience in conversion and the like, and is a main data transmission format for data transmission of internet application at present. Therefore, a business system generally stores some unstructured business data in a relational database such as MySQL in the form of JSON character strings. With the increase of the data volume of the business table, partial contents and related fields of JSON unstructured data in the MySQL database may need to be updated.

Currently, the MySQL database adds JSON data type support in versions of 5.7 or more, and a JSON data processing function supported by the database can be used to update part of the contents and related fields of JSON data. However, in previous versions of the MySQL database 5.7, JSON data cannot be updated by using JSON functions supported by the database because JSON functions are not built in. Generally, manual updating is performed by operating a service system or records in a database are updated one by one, which consumes a lot of manpower and time, and has low efficiency. Therefore, on the premise that the MySQL database version is not updated and the JSON data processing function supported by the database cannot be used, how to improve the efficiency of JSON data update becomes a problem to be solved urgently.

Disclosure of Invention

The embodiment of the application provides a data processing method and device, computer equipment and a storage medium, and when previous versions of a MySQL database 5.7 are used, partial contents in JSON data stored in the JSON data can be updated in batches, so that the JSON data updating efficiency is improved.

In a first aspect, an embodiment of the present application provides a data processing method, where:

responding to the fact that the version of the MySQL database is a first version, screening out a first database table needing to be updated from an original database table according to input query conditions, wherein JSON character strings are stored in the first database table;

updating the first database table by using a character string function to obtain a second database table;

updating the second database table into the original database table.

In a second aspect, an embodiment of the present application provides an apparatus for data processing, where:

the screening unit is used for screening a first database table needing to be updated in an original database table according to an input query condition in response to the fact that the version of the MySQL database is a first version, and JSON character strings are stored in the first database table;

the first updating unit is used for performing updating operation on the first database table by using a character string function to obtain a second database table;

a second updating unit, configured to update the second database table to the original database table.

In a third aspect, this application provides a computer device comprising a processor, a memory and a communication interface, wherein the memory stores a computer program configured to be executed by the processor, and the computer program comprises instructions for some or all of the steps as described in the first aspect of this application.

In a fourth aspect, embodiments of the present application provide a computer-readable storage medium storing a computer program, where the computer program causes a computer to perform some or all of the steps described in the first aspect of the embodiments of the present application.

The embodiment of the application has the following beneficial effects:

by adopting the data processing method, the data processing device, the computer equipment and the storage medium, when the version of the MySQL database is the first version, namely the previous version of the MySQL database 5.7, the JSON function supported by the database cannot be used for updating the JSON data due to the fact that the JSON function is not built in. Therefore, after the first database table in which the JSON character strings to be updated are stored is screened out from the original database table according to the input query conditions, the first database table in which the JSON character strings to be updated are stored can be updated by using a character string function to obtain a second database table, and the second database table is updated into the original database table to realize batch update of the JSON data. According to the embodiment of the application, on the premise that the MySQL database version is not updated and JSON data processing functions supported by the database cannot be used, the JSON data in the MySQL database table can be updated in batches by using the character string functions, so that the problems that the time consumption is long, the efficiency is low and the data is wrong and cannot be checked when a manual operating system is optimized and improved to update the JSON data are solved, and the efficiency of updating the JSON data is 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 described below, it is obvious that the drawings in the following description are only some embodiments of the present application, and for those skilled in the art, other drawings can be obtained based on these drawings without creative efforts. Wherein:

fig. 1 is a schematic diagram of a system architecture according to an embodiment of the present application;

fig. 2 is a schematic flowchart of a data processing method according to an embodiment of the present application;

fig. 3 is a schematic structural diagram of a data processing apparatus according to an embodiment of the present application;

fig. 4 is a schematic structural diagram of a computer device according to an embodiment of the present application.

Detailed Description

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, but not all, of the embodiments of the present application. 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.

The terms "first," "second," "third," and "fourth," etc. in the description and claims of this application and in the accompanying drawings are used for distinguishing between different objects and not for describing a particular order. Furthermore, the terms "include" and "have," as well as any variations thereof, are intended to cover non-exclusive inclusions. For example, a process, method, system, article, or apparatus that comprises a list of steps or elements is not limited to only those steps or elements listed, but may alternatively include other steps or elements not listed, or inherent to such process, method, article, or apparatus.

Reference herein to "an embodiment" means that a particular feature, structure, or characteristic described in connection with the embodiment can be included in at least one embodiment of the application. The appearances of the phrase in various places in the specification are not necessarily all referring to the same embodiment, nor are separate or alternative embodiments mutually exclusive of other embodiments. It is explicitly and implicitly understood by one skilled in the art that the embodiments described herein can be combined with other embodiments.

It should also be understood that the term "and/or" herein is merely one type of association that describes an associated object, meaning that three relationships may exist, e.g., a and/or B may mean: a exists alone, A and B exist simultaneously, and B exists alone. In addition, the character "/" herein generally indicates that the former and latter related objects are in an "or" relationship.

In order to better understand the technical solution of the embodiments of the present application, a system architecture that may be involved in the embodiments of the present application is introduced first. Referring to fig. 1, a system architecture diagram provided in an embodiment of the present application may include: an electronic device 101 and a server 102. The electronic device 101 and the server 102 may communicate with each other through a network. Network communications may be based on any wired and wireless network, including but not limited to the Internet, wide area networks, metropolitan area networks, local area networks, virtual Private Networks (VPNs), wireless communication networks, and the like.

The number of the electronic devices and the number of the servers are not limited, and the servers can provide services for the electronic devices at the same time. In the embodiment of the application, the electronic equipment can screen out a first database table needing to be updated from original database tables stored in a MySQL database of a first version according to input query conditions, wherein JSON character strings are stored in the first database table; or performing update operation on the first database table by using a character string function to obtain a second database table; and updating the second database table into the original database table, thereby completing the batch production of part of contents in the JSON data. The electronic device may be a Personal Computer (PC), a notebook computer, or a smart phone, and may also be an all-in-one machine, a palm computer, a tablet computer (pad), a smart television playing terminal, a vehicle-mounted terminal, or a portable device. The operating system of the PC-side electronic device, such as a kiosk or the like, may include, but is not limited to, operating systems such as Linux system, unix system, windows series system (e.g., windows xp, windows 7, etc.), mac OS X system (operating system of apple computer), and the like. The operating system of the electronic device at the mobile terminal, such as a smart phone, may include but is not limited to an operating system such as an android system, an IOS (operating system of an apple phone), a Window system, and the like.

The server may be an independent server, or may be a cloud server that provides basic cloud computing services such as a cloud service, a cloud database, cloud computing, a cloud function, cloud storage, a network service, cloud communication, a middleware service, a domain name service, a security service, a Content Delivery Network (CDN), and a big data and artificial intelligence platform. The server may alternatively be implemented as a server cluster consisting of a plurality of servers.

JSON (JavaScript Object notification) is a lightweight data exchange format, has the characteristics of small data volume, network transmission block, convenient conversion and the like, and is a main data transmission format for internet application data transmission at present. Therefore, a business system generally stores some unstructured business data in a relational database such as MySQL in the form of JSON character strings. With the increase of the data volume of the service table, partial contents and related fields of JSON unstructured data in the MySQL database may need to be updated.

Currently, the MySQL database is supported by JSON data types in versions of 5.7 or more, and a JSON data processing function supported by the database can be used to update part of contents and related fields of JSON data. However, in previous versions of the MySQL database 5.7, JSON data cannot be updated by using JSON functions supported by the database because JSON functions are not built in. Generally, manual updating is performed by operating a service system or records in a database are updated one by one, which consumes a lot of manpower and time, and has low efficiency. Therefore, on the premise that the MySQL database version is not updated and the JSON data processing function supported by the database cannot be used, how to improve the efficiency of JSON data update becomes a problem to be solved urgently.

In order to solve the above problem, embodiments of the present application provide a data processing method, which can be applied to an electronic device or a server configured in the fields of economic finance, education, and the like. By implementing the method, when previous versions of the MySQL database 5.7 are used, part of contents in JSON data stored in the MySQL database can be updated in batches, and therefore the efficiency of updating the JSON data is improved.

Referring to fig. 2, fig. 2 is a schematic flowchart illustrating a data processing method according to an embodiment of the present disclosure. Taking the application of the method to the electronic device as an example for illustration, the method may include the following steps S201 to S203, where:

step S201: and responding to the fact that the version of the MySQL database is the first version, and screening out a first database table needing to be updated from the original database table according to the input query condition.

The MySQL database is one of the most popular relational database management systems for open source codes at present, and the MySQL database stores data in different data tables which are related to each other, so that the data access speed is increased and the flexibility is improved. The MySQL database uses Structured Query Language (SQL). Wherein, the SQL statement comprises: data definition languages, such as: CREATE, DROP, ALTER, etc.; data manipulation languages, for example: INSERT, UPDATE, DELETE statements; data query languages, such as: a SELECT statement; data control languages, such as: GRANT, revike, etc.; transaction control languages, such as: COMMIT, ROLLBACK, etc.

As JSON has the advantages of small data size, network transmission block, convenience in conversion and the like, a business system (such as accounting teaching and the like) can store some unstructured business data in a MySQL database in the form of JSON character strings. The JSON character string mainly comprises two attribute values of key and value, wherein the key can be used for identifying the name of the key-value pair, and the value is a corresponding numerical value. Currently, the MySQL database adds JSON data type support in versions of 5.7 or more, and a JSON data processing function supported by the database can be used to update part of the contents and related fields of JSON data. However, in previous versions of the MySQL database 5.7, JSON data cannot be updated by using JSON functions supported by the database because JSON functions are not built in.

In this embodiment of the present application, the first version may be a MySQL database version that does not have a JSON function built in and cannot update JSON data using a JSON function supported by a database. More specifically, the first version may refer to a version before the MySQL database 5.7. The original database table may refer to one or more data tables storing JSON strings. The first database table is a database table in which JSON character strings needing to be updated are stored in the original database table. The query condition may be understood as a query field input by a user, and the query field may contain a field of a JSON string to be updated. The user may be understood to be a user of the MySQL database. Specifically, the SELECT statement may be executed according to a query condition input by a user as a screening condition, and a first database table that needs to be updated in the original database table is found. The first database table stores JSON character strings needing to be updated.

Taking a business system as an accounting teaching system as an example, the MySQL database may store business data related to accounting teaching. Wherein the first database table may include a JSON string stored for record field name form data column to be updated, e.g., case answer data may be stored in JSON string form in field name form data column. The number of original scoring items stored in the items column to be updated may also be included, and the like, for example, the number of original scoring items may be used to represent the number of score of the current case answer. For example, a first database table may comprise record 1, wherein form _ data = { "a2": "2018, month 01, day 01," "za9": buy company bond, 1| buy company stock, 0| sell company bond, 0"," ka1_ r1":" st164| bond investment "," ka2_ r1":" st166| cost "," a4_ r1":" 000010000 "," ka2_ r2": st167| interest adjustment", "a4_ r2": 2624400"," ka1_ r3": st003| deposit", "a5_ r3": 102624400"," a20": this 102624400", "a21": this 102624400"," a8": forest silence": 732, ack, 1011,250, bl 1 = 0, 13 in record 1. Alternatively, record 2 may also be included in the first database table, wherein form _ data = { "za9": "2222", "lineStr": 707,136,637,281, black,1,0, 813,136,744,282, blac k,1,0"}, items =3 in record 2.

In record 1, "a2": the "year 2018, month 01," may serve as a key value pair, "a2" may be a key in the key value pair, and is used to identify the name of the key value pair, and "year 2018, month 01," may be a numerical value corresponding to "a 2. Similarly, "lineStr": "732,431,1011,250, black,1,0" may also be used as a key-value pair, and so on. The number of primitive scoring entries stored in the items column is associated with the JSON string stored in the form _ data column. In particular, the number of key-value pairs that may correspond to a JSON string and the primary key attribute are related. Generally, the items column stores the number of original scoring terms and the number of key-value pairs corresponding to the JSON string. For example, for record 1, the JSON character string stored in form _ data has 13 key value pairs in common, so the original number of entries items =13 in record 1. However, the key of "lineStr" is special, and the value is divided by "|" to indicate that several divisions correspond to several original scoring term numbers. For example, the "lineStr" key in record 2 corresponds to a numerical value of "707,136,637,281, black,1,0, 813,136,744,282, black,1,0", and the value is divided into "707,136,637,281, black,1,0" and "813,136,744,282, black,1,0" by "|". Therefore, the number of "lineStr" keys in record 2 corresponding to the score items is 2, so that it is possible to determine the original score item number items =3 in record 2.

In a possible implementation manner, before performing step S201, the following steps may be further included:

verifying the identity information of the user; and responding to the verification passing, and granting the user the authority of connecting the MySQL database.

In an embodiment of the present application, the user may be a user of a MySQL database. Typically, a user may connect to the MySQL database via a database connection tool (e.g., navicat, mysqlworkbbench, etc. tools) in order to better manage the MySQL database. In order to improve the security, before the user connects the MySQL database and uses the MySQL database, the electronic device may verify the identity information of the user, and if the identity information is verified consistently, the user passes the authority of connecting the MySQL database, and step S201 is executed; if the verification fails, the MySQL database cannot be connected.

In a possible implementation manner, the identity information of the user can be verified in a manner of verifying whether information such as a database server address, a user name, a password and the like input by the user is consistent with preset information, and if so, the identity information of the user passes the verification; otherwise, the verification fails. Specifically, when detecting a request of a user for connecting the MySQL database, the electronic device displays an interface for inputting information (for example, information such as a database server address, a user name, a password, and the like) to the user, and determines whether the information received by the interface for inputting information is consistent with preset information, thereby determining whether to grant the user the authority for connecting the MySQL database.

It can be seen that, before step S201 is executed, the user identity information is verified, and if the user identity information passes the verification, the user is granted the right to connect the MySQL database, so as to prevent an illegal user from accessing or modifying the MySQL database, which causes data leakage, and improve security.

In a possible implementation manner, before performing step S201, the following steps may be further included: and backing up the original database table.

In the embodiment of the present application, in order to prevent data loss or damage caused by a later-stage execution error, before step S201 is executed, the original database table may be backed up first, and when the MySQL database is down or the data is in disorder, the data of the original database table may be restored by recovering the backup. In particular, the original database tables may be backed up by standard SQL statements. The original database table can also be backed up by means of physical copying of files. Alternatively, the original database tables may be backed up by a backup tool (e.g., navicat, mySQLdump, etc.). In addition, the original database table can be backed up by writing a timing script. Or, other feasible backup manners may also be adopted to backup the original database table, and the backup manner of the original database table is not limited in the embodiment of the present application.

It can be seen that, before step S201 is executed, the original database table may be backed up first, which is beneficial to restoring the data of the original database table in a backup recovery manner when the MySQL database is down or the data is confused or the data is lost or destroyed due to a later execution error, and is beneficial to improving the reliability of the data.

Step S202: and updating the first database table by using a character string function to obtain a second database table.

In this embodiment of the present application, a string function may refer to a function built in a MySQL database, and may specifically include a character lookup function (e.g., instr () function, etc.), a string truncation function (e.g., left () function, substring () function, right () function, etc.), a concatenation function (e.g., concat () function, etc.), a length function (e.g., length () function, etc.), a replacement function (e.g., place () function, etc.), and the like. The update operations may include operations to delete, modify, and add certain fields in the first data. The second database table is the temporary database table temp after the update operation is performed on the first database table. Taking a business system as an accounting teaching system as an example, the first database table may include a JSON string stored in a field name of form _ data column to be updated, and may further include the number of original scoring items stored in an items column to be updated, and the like, and the second database table may include a target JSON string stored in a field name of form _ data column to be updated, and may further include the number of target scoring items stored in an items column to be updated, and the like. Because the MySQL database of the first version does not have the built-in JSON function, the JSON character string in the first database table cannot be updated by using the JSON function supported by the database, so that the embodiment of the application can use the built-in character string function in the MySQL database to realize batch update of the first database table containing the JSON character string, and improve the update efficiency of JSON data.

In a possible implementation manner, step S202 may specifically include the following steps:

acquiring a target field to be updated in the first database table; searching the index position of the target field from the first database table by using a character searching function; determining a target JSON character string needing to be updated by using a character string interception function and a splicing function according to the index position; and determining a second database table according to the target JSON character string.

In the embodiment of the present application, the target field is a field to be updated in the first database table, for example, the target field may be lineStr in form _ data. The character lookup function may be an instr () function. The function of instr () function is to find the index position where one string first appears in another string, whose syntax can be instr (string 1, string 2). String1 can represent a source character string, namely, the source character string needs to be searched; string2 may represent the target string, i.e., the string to be looked up in string 1. The returned result is the index position where string2 first appears in string1, and if not found, 0 is returned. For example, the index position where lineStr appears in form _ data is looked up from record 2 using the instr () function, which may be in syntax of instr (form _ data, 'lineStr'), which returns a result of 16, indicating that the index position where lineStr first appears in form _ data in record 2 is 16.

The string truncation function may be a left () function, a substring () function, a right () function, or a substring _ index () function, etc. Where the syntax of the left () function may be left (string), string may represent a string to be operated on, and length may be a positive integer for specifying the number of characters of a substring. length should be greater than 0 and if length is less than or equal to 0, then an empty string is returned. Its function is to intercept the length characters starting from the first bit on the left of string. The substring () function is a function specially used for segmenting character strings, and the syntax mainly has two forms: first, string (position); second is string (position, length). Where string may represent a string to be operated on, position may be an "integer" specifying the starting character of the substring, and position may be a positive or negative integer, and if position is greater than the length of the operation string, a null string is returned. length may be a positive integer for specifying the number of characters of the substring. length should be greater than 0 and if length is less than or equal to 0, then an empty string is returned. Where the syntax of the right () function may be right (string), string may represent a character string to be operated on, and length may be a positive integer for specifying the number of characters of the substring. length should be greater than 0 and if length is less than or equal to 0, then an empty string is returned. The first bit on the right of the functional string begins, length characters are intercepted, and the interception is performed in a forward sequence. substring _ index () is a function that intercepts substrings by a specific identifier "delim". The syntax may be substring _ index (string, delay, count), where string may represent a string to be operated, and delay may represent an identifier, that is, a corresponding substring may be intercepted from string by the identifier, and delay may be any non-null character; count may represent the number of occurrences; the count is a positive number and represents a substring before the identifier appears for the second count; negative numbers are opposite, and the substring after the identifier occurs the first count is taken. The concatenation function may be a concat () function, etc., the function of which is to concatenate a plurality of character strings into one character string. Its syntax may contain (string 1, string 2.), where string1, string2, etc. may represent a string to be operated on. If any parameter is null, the return value is null.

Illustratively, the update operation is a delete operation performed on the target field lineStr. For record 2, since lineStr location is the last of the entire JSON string in record 2, after the index location of lineStr is obtained as 16 using a character lookup function, e.g., instr (form _ data, 'lineStr'), the string length that form _ data wants to reserve can be determined from the index location. For example, for record 2, instr (form _ data 'lineStr') -3 can be used to obtain the string length that form _ data wants to reserve, i.e. 16-3=13. And then, acquiring the substrings by using a string interception function, such as a substring () function or a left () function, and splicing the substrings into the target JSON string needing to be updated by using a splicing function, such as a concat () function. For example, concat (format _ data,1, instr (format _ data, 'lineStr') -3), '}') or concat (format _ data, instr (format _ data, 'lineStr') -3), '}') may be used to obtain the target JSON string that needs to be updated, and the updated target JSON string in record 2 may be { "za9": or "2222", so as to implement the deletion operation on the target field lineStr in record 2. The second database table may include a target JSON string, and after the target JSON string is obtained, the target JSON string may be stored as a JSON string that is updated by the second database table with a field name of form _ data column.

It can be seen that after a target field to be updated in a first database table is obtained, an index position of the target field can be searched from the first database table by using a character search function, then a target JSON character string needing to be updated is determined by using a character string interception function and a splicing function according to the index position, and finally a second database table is determined according to the target JSON character string to realize batch updating of the target JSON character string, so that the updating efficiency of JSON data is improved on the premise that a MySQL database version is not updated and JSON data processing functions supported by the database cannot be used.

In a possible implementation manner, after the step of determining the target JSON character string to be updated by using the character string truncation function and the splicing function according to the index position, the following steps may be further included:

determining the number of first scoring items corresponding to the target field by using a character string length function and a replacement function; determining the number of target scoring items according to the first scoring item number and the initial scoring item number; and determining a second database table according to the target JSON character string and the number of the target scoring items.

As described above, in the accounting teaching system, the first database table may include the JSON character string stored in the form _ data column as the field name to be updated, may further include the number of the original scoring items stored in the items column to be updated, and so on. The number of primitive scoring items stored in the items column is associated with the JSON string stored in the form _ data column. In particular, the number of key-value pairs that may correspond to a JSON string and the primary key attribute are related. The calculation method of the number of the original scoring items can refer to the foregoing description, and is not repeated herein.

In the embodiment of the present application, the first number of scoring items is the number of scoring items included in the target field. Taking record 2 as an example, if the number of scoring items corresponding to it before updating is 2, the number of first scoring items corresponding to the target field "lineStr" may be considered to be 2. The number of the target scoring items is the number of scoring items corresponding to the target JSON character string. After the target JSON character string is obtained, the number of target scoring items corresponding to the target JSON character string needs to be obtained so as to realize the follow-up updating of the first database table. The string length function may be a length () function, and the function of the length () function may be to obtain a string length. Its syntax may be length (string), where string may represent a string to operate on. The result returned is the length of the string. The replacement function may be a place () function, and its syntax may be place (string, from _ str, to _ str). Where string may represent the source string, from _ str may represent the sought substring, and to _ str may represent the replacement string. The function of the replace () function may be to update a partial substring in the source string, that is, it may be understood to replace all occurrences of from _ str in string with to _ str.

Specifically, taking an example in which the update operation is a delete operation performed on the target field lineStr in record 2. Can be represented by formula a: backing (form _ data, instr (form _ data, 'lineStr') -1, instr (form _ data, '}') -instr (form _ data, 'lineStr') + 1), obtaining the lineStr key-value pair content in record 2 as "lineStr": 707,136,637,281, black,1,0 land cuts 813,136,744,282, black,1,0". Then, the first number of score items contained in the target field "lineStr" is counted using the length () and place () functions, for example, the first number of score items is 2, which can be obtained using length (formula a) -length (place (formula a, '|', ")) + 1. As can be seen from the foregoing, the original number of entries items =3 in record 2. Since the update operation is a delete operation, the updated target JSON string in record 2 may be { "za9": "2222" } the corresponding target scoring item number = the original scoring item number — the first scoring item number =1. The second database table may include a target JSON character string, and after the target JSON character string and the number of target scoring items corresponding to the target JSON character string are obtained, the target JSON character string may be used as a JSON character string stored in a form _ data column of a field name updated by the second database table, and the number of the target scoring items may be used as the number of scoring items stored in an items column updated by the second database table.

It can be seen that after a target JSON character string needing to be updated is determined by using a character string interception function and a splicing function according to an index position, the number of first scoring items corresponding to a target field can be determined by using a character string length function and a replacing function, then the number of the target scoring items is determined according to the number of the first scoring items and the number of the initial scoring items, and finally a second database table is determined according to the target JSON character string and the number of the target scoring items, so that the efficiency of data updating is improved.

Step S203: updating the second database table into the original database table.

In a possible implementation manner, step S203 may specifically include the following steps:

acquiring a primary key field of the second database table; internally connecting the second database table with the original database table through the primary key field to obtain a target database table; and updating the target database table into the original database table.

In the embodiment of the present application, the second database table is understood to be the temporary database table temp after the update operation of the first database table. The original database table may be understood as one or more data tables storing JSON strings, which may contain data tables of JSON strings to be updated. The primary key field, which may be understood as the primary key ID of a table, may be used to uniquely identify a field or combination of fields of each record (e.g., record 1 and record 2, as mentioned above) in a database table to associate the record with data stored in other tables. Inner join (inner join) can be used to match rows in one table with rows in the other table and allow the row records containing columns to be looked up from both tables, returning rows in both tables with equal join fields. In this embodiment, the primary key field may serve as a connection condition for a field in the second database table that is equal to that in the original database table, so as to implement the internal connection between the second database table and the original database table. The target database table may be understood as the table after the second database table is internally linked with the original database table.

In the embodiment of the application, the primary key field of the second database table is firstly inquired, and the second database table and the original database table are internally connected through the primary key field to obtain the target database table. Specifically, the syntax thereof may be inner join t _ fine _ case _ form tff on temp.id = tff.id. Where t _ definition _ case _ form can be understood as the original database table and temp can be understood as the second database table. It should be noted that t _ fine _ case _ form and temp are only used for example, and in practical applications, specific names are determined according to practical situations, which is not limited in the embodiments of the present application.

After the target database table is obtained, the numbers of the target JSON character strings and the items of the target data in the form _ data field content in the target database table can be updated to the numbers of the JSON character strings and the items of the original data in the form _ data field content in the original database table by executing the UPDATE statement, so that the batch updating of the partial content of the JSON data is realized. For example, the key-value pair corresponding to "lineStr" in the above-mentioned record 1 and record 2 is removed, and the number of items in the items count corresponding thereto is updated. After executing the UPDATE statement, the contents of record 1 in the original database table are updated by the original form _ data = { "a2": "day 01/01 in 2018", "za9": "buy company bond," 1| buy company stock, 0| sell company bond, 0"," ka1_ r1":" st164| debt investment "," ka2_ r1":" st166| cost "," a4_ r1":"100000000"," ka2_ r2": st167| interest adjustment", "a4_ r2": "2624400", "ka1_ r3": "st | bank", "a5_ r3": "102624400", "a20": this 102620 "," a21": 102624400", "a8": forest farm ": account": ack ", 1011,250, blb =1, 431 = 44013: form _ data = { "a2": "2018, 01 month, 01 day", "za9": "buy company bond, 1| buy company stock, 0| sell company bond, 0", "ka1_ r1": st164| bond investment "," ka2_ r1": st166| cost", "a4_ r1": 100000000"," ka2_ r2": st167| interest adjustment", "a4_ r2": 2624400"," ka1_ r3": st003| bank deposit", "a5_ r3": 102620 "," a20": 102620", "a21": 102440620 "," a8":" forest silence "}, items =12. The content of record 2 in the original database table is updated from the original form _ data = { "za9": "2222", "lineStr": 707,136,637,281, black,1,0 yu 813,136,744,282, black,1,0"}, items =3 as: form _ data = { "za9": "2222" }, items =1, to enable batch updates of part of the content in JSON data stored in previous versions of the MySQL database 5.7.

It can be seen that after the first database table is updated to obtain the second database table, the primary key field of the second database table can be obtained; then, internally connecting the second database table with the original database table through the primary key field to obtain a target database table; and finally, the target database table is updated into the original database table so as to realize the quick update of the target database table into the original database table, and the update efficiency of JSON data is improved.

In a possible embodiment, after step S203 is executed, the following steps may be further included:

analyzing and detecting the updated original database table, and determining whether the updated original database table has data abnormality; and in response to the occurrence of data abnormality in the updated original database table, restoring the updated original database table to the original database table, and updating the original database table again.

In the embodiment of the application, after the second database table is updated to the original database table, the updated original database table can be analyzed and detected, and whether data abnormality occurs in the data in the updated original database table is judged. The occurrence of data anomalies in the updated original database table may include, but is not limited to: when the updated original database table executes SQL access data, data exception occurs, for example: repeated keys appear in the main key field of the updated original database table or the main key field is an illegal value; or the updated original database table part data is lost or destroyed, etc. Specifically, whether data abnormality occurs in the updated data in the original database table can be detected by executing a preset script or SQL and the like, so that abnormal data can be rapidly checked. When the detection result is that data abnormality occurs, the data in the updated original database table can be restored to the data before being updated again by acquiring the original database table backed up in advance. And after the data are restored, executing the SQL again to update the JSON data needing to be updated in the original database table again until the fact that the data in the updated original database table are not abnormal is detected.

Therefore, after the second database table is updated to the original database table, the updated original database table can be analyzed and detected, whether data abnormality occurs in the updated original database table or not is determined, and abnormal data can be rapidly checked. And if the updated original database table has data abnormality, restoring the updated original database table into the original database table, and performing updating operation on the original database table again, thereby being beneficial to improving the reliability of the data.

In the method shown in fig. 2, when the version of the MySQL database is the first version, that is, the version before the MySQL database 5.7, there is no JSON function built in, and therefore the JSON function supported by the database cannot be used to update the MySQL database version of JSON data. Therefore, after the first database table in which the JSON character strings to be updated are stored is screened out from the original database table according to the input query conditions, the first database table in which the JSON character strings to be updated are stored can be updated by using a character string function to obtain a second database table, and the second database table is updated into the original database table to realize batch update of the JSON data. According to the embodiment of the application, on the premise that the MySQL database version is not updated and JSON data processing functions supported by the database cannot be used, the JSON data in the MySQL database table can be updated in batches by using the character string functions, so that the problems that the time consumption is long, the efficiency is low and the data is wrong and cannot be checked when a manual operating system is optimized and improved to update the JSON data are solved, and the efficiency of updating the JSON data is improved.

The method of the embodiments of the present application is set forth above in detail and the apparatus of the embodiments of the present application is provided below.

Referring to fig. 3, fig. 3 is a schematic structural diagram of a data processing apparatus according to an embodiment of the present disclosure. The device is applied to electronic equipment. As shown in fig. 3, the data processing apparatus 300 includes a filtering unit 301, a first updating unit 302, and a second updating unit 303, and the details of each unit are as follows:

the screening unit 301 is configured to screen a first database table needing to be updated in an original database table according to an input query condition in response to that the version of the MySQL database is a first version, where JSON character strings are stored in the first database table;

a first updating unit 302, configured to perform an updating operation on the first database table by using a string function to obtain a second database table;

a second updating unit 303, configured to update the second database table into the original database table.

In a possible implementation manner, the first updating unit 302 is specifically configured to obtain a target field to be updated in the first database table; searching the index position of the target field in the first database table by using a character search function; determining a target JSON character string needing to be updated by using a character string interception function and a splicing function according to the index position; and determining a second database table according to the target JSON character string.

In a possible implementation, the first database table further includes an initial number of scoring items, the initial number of scoring items is associated with the JSON string, and the first updating unit 302 is further configured to determine a first number of scoring items corresponding to the target field by using a string length function and a replacement function; determining the number of target scoring items according to the number of the first scoring items and the number of the initial scoring items; and determining a second database table according to the target JSON character string and the number of the target scoring items.

In a possible implementation manner, the second updating unit 303 is specifically configured to obtain a primary key field of the second database table; internally connecting the second database table with the original database table through the primary key field to obtain a target database table; and updating the target database table into the original database table.

In a possible implementation manner, the apparatus 300 for data processing may further include a detection unit not shown in fig. 3, where the detection unit is specifically configured to perform analysis detection on the updated original database table, and determine whether a data anomaly occurs in the updated original database table; and in response to the occurrence of data exception in the updated original database table, restoring the updated original database table to the original database table, and updating the original database table again.

In a possible embodiment, the data processing apparatus 300 may further include an authentication unit not shown in fig. 3, and the authentication unit is specifically configured to authenticate the identity information of the user; and responding to verification passing, and granting the user the authority of connecting the MySQL database.

In a possible implementation manner, the apparatus 300 for data processing may further include a backup unit not shown in fig. 3, where the backup unit is specifically configured to perform a backup operation on the original database table.

It should be noted that the implementation of each unit may also correspond to the corresponding description of the method embodiment shown in fig. 2.

Referring to fig. 4, fig. 4 is a schematic structural diagram of a computer device according to an embodiment of the present disclosure. As shown in fig. 4, the computer device 400 comprises a processor 401, a memory 402 and a communication interface 403, wherein the memory 402 stores a computer program 404. The processor 401, memory 402, communication interface 403 and computer program 404 may be connected by a bus 405.

When the computer device is an electronic device, the computer program 404 is used for executing the following steps:

updating the second database table into the original database table.

In a possible implementation, in the aspect that the updating operation is performed on the first database table by using a string function to obtain a second database table, the computer program 404 is specifically configured to execute the following instructions:

acquiring a target field to be updated in the first database table;

searching the index position of the target field in the first database table by using a character search function;

determining a target JSON character string needing to be updated by using a character string interception function and a splicing function according to the index position;

and determining a second database table according to the target JSON character string.

In one possible implementation, the first database table further comprises an initial number of scoring items associated with the JSON string, and after determining the target JSON string to update using a string truncation function and a concatenation function based on the index position, the computer program 404 is further operable to execute the instructions of:

determining the number of first scoring items corresponding to the target field by using a character string length function and a replacement function;

determining the number of target scoring items according to the number of the first scoring items and the number of the initial scoring items;

and determining a second database table according to the target JSON character string and the number of the target scoring items.

In a possible embodiment, in the aspect of said updating of said second database table into said original database table, said computer program 404 is particularly adapted to execute the following steps:

acquiring a primary key field of the second database table;

internally connecting the second database table with the original database table through the primary key field to obtain a target database table;

and updating the target database table into the original database table.

In a possible embodiment, after said updating of said second database table into said original database table, said computer program 404 is further provided with instructions for carrying out the following steps:

analyzing and detecting the updated original database table, and determining whether the updated original database table has data abnormality;

and in response to the occurrence of data abnormality in the updated original database table, restoring the updated original database table to the original database table, and updating the original database table again.

In one possible implementation, before the version corresponding to the MySQL database is the first version and the first data needing to be updated is screened in the original database table according to the input query condition, the computer program 404 is further configured to execute the following steps:

verifying the identity information of the user;

and responding to verification passing, and granting the user the authority of connecting the MySQL database.

and carrying out backup operation on the original database table.

Those skilled in the art will appreciate that only one memory and processor are shown in fig. 4 for ease of illustration. In an actual terminal or server, there may be multiple processors and memories. The memory 402 may also be referred to as a storage medium or a storage device, which is not limited in this embodiment.

It should be understood that in the embodiments of the present application, the processor 401 may be a Central Processing Unit (CPU), and the processor may also be other general purpose processors, digital Signal Processors (DSPs), application Specific Integrated Circuits (ASICs), field Programmable Gate Arrays (FPGAs) or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components, etc.

It will also be appreciated that the memory 402, referred to in this application embodiment, may be either volatile memory or nonvolatile memory, or may include both volatile and nonvolatile memory. The non-volatile memory may be a read-only memory (ROM), a Programmable ROM (PROM), an Erasable PROM (EPROM), an Electrically Erasable PROM (EEPROM), or a flash memory. Volatile memory can be Random Access Memory (RAM), which acts as external cache memory. By way of example, but not limitation, many forms of RAM are available, such as Static Random Access Memory (SRAM), dynamic Random Access Memory (DRAM), synchronous Dynamic Random Access Memory (SDRAM), double data rate SDRAM, enhanced SDRAM, SLDRAM, synchronous Link DRAM (SLDRAM), and direct bus RAM (DR RAM).

It should be noted that when the processor 401 is a general purpose processor, a DSP, an ASIC, an FPGA or other programmable logic device, discrete gate or transistor logic device, or discrete hardware component, the memory (memory module) is integrated into the processor.

It should be noted that the memory 402 described herein is intended to comprise, without being limited to, these and any other suitable types of memory.

The bus 405 may include a power bus, a control bus, a status signal bus, and the like, in addition to a data bus. But for clarity of illustration the various buses are labeled as buses in the figures.

In implementation, the steps of the above method may be performed by integrated logic circuits of hardware in a processor or by instructions in the form of software. The steps of a method disclosed in connection with the embodiments of the present application may be directly implemented by a hardware processor, or may be implemented by a combination of hardware and software modules in a processor. The software modules may be located in ram, flash, rom, prom, or eprom, registers, etc. as is well known in the art. The storage medium is located in a memory, and a processor reads information in the memory and combines hardware thereof to complete the steps of the method. To avoid repetition, it is not described in detail here.

In the various embodiments of the present application, the sequence numbers of the above-mentioned processes do not mean the execution sequence, and the execution sequence of each process should be determined by its function and inherent logic, and should not constitute any limitation to the implementation process of the embodiments of the present application.

Those of ordinary skill in the art will appreciate that the various Illustrative Logical Blocks (ILBs) and steps described in connection with the embodiments disclosed herein may be implemented as electronic hardware or combinations of computer software and electronic hardware. Whether such functionality is implemented as hardware or software depends upon the particular application and design constraints imposed on the technical solution. Skilled artisans may implement the described functionality in varying ways for each particular application, but such implementation decisions should not be interpreted as causing a departure from the scope of the present application.

In the several embodiments provided in the present application, it should be understood that the disclosed system, apparatus and method may be implemented in other ways. For example, the above-described apparatus embodiments are merely illustrative, and for example, the division of the units is only one logical division, and other divisions may be realized in practice, for example, a plurality of units or components may be combined or integrated into another system, or some features may be omitted, or not executed. In addition, the shown or discussed mutual coupling or direct coupling or communication connection may be an indirect coupling or communication connection through some interfaces, devices or units, and may be in an electrical, mechanical or other form.

The units described as separate parts may or may not be physically separate, and parts displayed as units may or may not be physical units, may be located in one position, or may be distributed on multiple network units. Some or all of the units can be selected according to actual needs to achieve the purpose of the solution of the embodiment.

In addition, functional units in the embodiments of the present application may be integrated into one processing unit, or each unit may exist alone physically, or two or more units are integrated into one unit.

In the above embodiments, all or part of the implementation may be realized by software, hardware, firmware, or any combination thereof. When implemented in software, it may be implemented in whole or in part in the form of a computer program product. The computer program product includes one or more computer instructions. When loaded and executed on a computer, cause the processes or functions described in accordance with the embodiments of the application to occur, in whole or in part. The computer may be a general purpose computer, a special purpose computer, a network of computers, or other programmable device. The computer instructions may be stored in a computer readable storage medium or transmitted from one computer readable storage medium to another, for example, the computer instructions may be transmitted from one website, computer, server, or data center to another website, computer, server, or data center by wire (e.g., coaxial cable, fiber optic, digital subscriber line) or wirelessly (e.g., infrared, wireless, microwave, etc.). The computer-readable storage medium can be any available medium that can be accessed by a computer or a data storage device, such as a server, a data center, etc., that incorporates one or more of the available media. The usable medium may be a magnetic medium (e.g., floppy disk, hard disk, magnetic tape), an optical medium (e.g., DVD), or a semiconductor medium (e.g., solid state disk), among others.

Embodiments of the present application also provide a computer-readable storage medium, which stores a computer program, where the computer program is executed by a processor to implement part or all of the steps of any one of the data processing methods described in the above method embodiments.

Embodiments of the present application also provide a computer program product comprising a non-transitory computer readable storage medium storing a computer program operable to cause a computer to perform some or all of the steps of any one of the data processing methods as described in the above method embodiments.

The above description is only for the specific embodiments of the present application, but the scope of the present application is not limited thereto, and any person skilled in the art can easily conceive of the changes or substitutions within the technical scope of the present application, and shall be covered by the scope of the present application. Therefore, the protection scope of the present application shall be subject to the protection scope of the claims.

Claims

1. A method of data processing, comprising:

updating the second database table into the original database table.

2. The method of claim 1, wherein said updating the first database table using a string function to obtain a second database table comprises:

acquiring a target field to be updated in the first database table;

searching the index position of the target field from the first database table by using a character searching function;

3. The method of claim 2, wherein the first database table further includes an initial number of scoring entries, the initial number of scoring entries being associated with the JSON string, and further comprising, after the determining a target JSON string that needs to be updated using a string intercept function and a concatenation function according to the index position:

determining the number of target scoring items according to the first scoring item number and the initial scoring item number;

4. The method of claim 1, wherein said updating the second database table into the original database table comprises:

acquiring a primary key field of the second database table;

and updating the target database table into the original database table.

5. The method of claim 1, further comprising, after said updating said second database table into said original database table:

and in response to the occurrence of data exception in the updated original database table, restoring the updated original database table to the original database table, and updating the original database table again.

6. The method of claim 1, wherein before the version responsive to MySQL database is the first version, and the first data needing to be updated is screened in the original database table according to the input query condition, the method further comprises:

verifying the identity information of the user;

7. The method according to any one of claims 1-6, wherein before the version responding to the MySQL database is the first version, and the first data needing to be updated is screened in the original database table according to the input query condition, the method further comprises:

and carrying out backup operation on the original database table.

8. An apparatus for data processing, comprising:

and the second updating unit is used for updating the second database table into the original database table.

9. A computer device, characterized in that it comprises a processor, a memory and a communication interface, wherein the memory stores a computer program configured to be executed by the processor, the computer program comprising instructions for carrying out the steps in the method according to any one of claims 1-7.

10. A computer-readable storage medium, characterized in that the computer-readable storage medium stores a computer program, the computer program causing a computer to execute to implement the method of any one of claims 1-7.