A method and device for processing view data table

Abstract

The embodiment of the present invention relates to a method and device for processing a view data table, the method comprising: obtaining a configuration file reflecting the field mapping relationship between a custom view and an associated source table and recording it as a corresponding first configuration file; creating a data table in a MySQL database as a view data table of the custom view according to the first configuration file; after the view data table is successfully created, real-time acquisition of the row mode archive log binlog file output by the MySQL database generates a corresponding first log file; and performing update instruction parsing on the first log file according to the first configuration file to generate a corresponding first update field set; and performing field update processing on the view data table according to the first update field set. Through the present invention, the function of materialized view can be realized in the form of view data table on a MySQL database that does not support materialized view.

Description

Technical Field

[0001] The present invention relates to the technical field of data processing, and in particular to a method and device for processing a view data table. Background Art

[0002] A view in a MySQL database is a virtual table whose content is defined by a query. Through the view function, data from multiple source tables can be merged, but retrieval cannot be performed based on the virtual table. To make the view object searchable, the database needs to support the materialized view feature, but the MySQL database does not support this, which means that it is impossible to create a real materialized view object that can be dynamically updated in the MySQL database. Summary of the invention

[0003] The purpose of the present invention is to provide a method, device, electronic device and computer-readable storage medium for processing a view data table in view of the defects of the prior art. First, the field mapping relationship between the custom view and the associated source table is confirmed to obtain a corresponding configuration file; then a common data table for simulating materialized view is created in the MySQL database as the view data table, and the relevant content of the associated source table is copied to the view data table based on the configuration file to complete the table initialization; then the latest updated content of each associated source table is obtained in time by real-time parsing of the archive log binlog file of the MySQL database, and the data items related to the latest updated content in the view data table are also synchronously updated based on the field mapping relationship reflected in the configuration file. Through the present invention, the function of materialized view can be realized in the form of view data table on a MySQL database that does not support materialized view, and the problem that the MySQL database cannot retrieve the view is avoided.

[0004] To achieve the above objective, a first aspect of an embodiment of the present invention provides a method for processing a view data table, the method comprising:

[0005] A configuration file reflecting the field mapping relationship between the custom view and the associated source table is obtained and recorded as the corresponding first configuration file;

[0006] Creating a data table in the MySQL database according to the first configuration file as a view data table of the custom view;

[0007] After the view data table is successfully created, the row mode archive log binlog file output by the MySQL database is acquired in real time to generate a corresponding first log file; and the update instruction of the first log file is parsed according to the first configuration file to generate a corresponding first update field set; and the field update processing of the view data table is performed according to the first update field set.

[0008] Preferably, the first configuration file includes a first data table name and a plurality of first configuration items; the first configuration item includes a first view field name, a first view field data type, a first associated source table name, a first source table field name, and a first source table field index;

[0009] The first update field set includes multiple first update field groups; the first update field group includes a first update field name, a first update row number and a first update value.

[0010] Preferably, creating a data table in a MySQL database according to the first configuration file as a view data table of the custom view specifically includes:

[0011] According to the CREATE TABLE instruction format of the MySQL database, the table creation instruction parameter configuration processing is performed according to the first configuration file to generate a corresponding first table creation instruction; and the first table creation instruction is executed on the MySQL database to generate a data table as the view data table; the instruction parameters of the first table creation instruction include a table name parameter and multiple table definition option parameters; the table name parameter is the first data table name; among the multiple table definition option parameters, the first table definition option parameter is a preset row number field definition option parameter, and the remaining table definition option parameters correspond to the first configuration item one by one; the table definition option parameters include a column name parameter and a column type parameter; the column name parameter of the row number field definition option parameter is a preset row number field name, and the column type parameter is a preset row number field type parameter; the column name parameter and the column type parameter of the table definition option parameter corresponding to the first configuration item are the first view field name and the first view field data type of the first configuration item respectively;

[0012] Traverse each of the first configuration items of the first configuration file; during the traversal, record the first configuration item currently traversed as the current configuration item, and use the first view field name, the first associated source table name and the first source table field name of the current configuration item as the corresponding current view field name, the current associated source table name and the current source table field name; and perform insert statement configuration processing according to the first data table name, the current view field name, the current associated source table name and the current source table field name according to the INSERT INTO...SELECT...FROM statement format of the MySQL database to generate a corresponding first insert statement; and execute the first insert statement on the MySQL database; the first insert statement includes a target table name parameter, a source table name parameter, a target field name parameter and a source field name parameter; the target table name parameter is the first data table name; the source table name parameter is the current associated source table name; the target field name parameter is the current view field name; and the source field name parameter is the current source table field name.

[0013] Preferably, parsing the update instruction of the first log file according to the first configuration file to generate a corresponding first update field set specifically includes:

[0014] The first log file is subjected to MySQL database UPDATE instruction statement extraction and processing to generate multiple first update instruction statements; each of the first update instruction statements corresponds to an update operation of a data record in a data table; the first update instruction statement includes first, second and third paragraphs; the first paragraph is an UPDATE instruction paragraph, including the name of the data table corresponding to the current first update instruction statement; the second paragraph is a WHERE condition paragraph, including the original field content of all fields of the data record corresponding to the current first update instruction statement before the update operation, and the format of each original field content is field index = original field value; the third paragraph is a SET paragraph, including the new field content of all fields of the data record corresponding to the current first update instruction statement before the update operation, and the format of each new field content is field index = new field value;

[0015] Extract the name of the corresponding data table from each of the first update instruction statements as the corresponding first update data table name, and perform data fusion on each of the original field content and the new field content according to the field index to generate a corresponding first field data sequence, and form the corresponding first update record data from the first update data table name and the first field data sequence; the first field data sequence includes a plurality of first field data; the first field data includes a first field index, a first original field value and a first new field value;

[0016] Record the first update record data corresponding to the first update data table name matching any of the first associated source table names in the first configuration file as second update record data; assign a corresponding first record number to each of the second update record data, and initialize all the first record numbers to 0; and record the data table corresponding to the first update data table name of each of the second update record data as a first update data table;

[0017] and polling the data records of each of the first updated data tables; during polling, recording the currently polled data record as the current data record; and performing full field matching on the contents of each field of the current data record and the first new field values ​​of the corresponding second updated record data according to the corresponding relationship of the field indexes to generate a corresponding first matching result; if the first matching result is not a full match, adding 1 to the first record number and switching to the next data record to continue polling until the last data record; if the first matching result is a full match, adding 1 to the first record number and ending the polling;

[0018] The first field data having the same first original field value and the first new field value in each of the second update record data are deleted to obtain corresponding third update record data; the first original field value of each of the first field data in the third update record data is deleted to obtain corresponding fourth update record data; and the fourth update record data is merged with the corresponding first record number to generate corresponding fifth update record data; the fifth update record data includes the first update data table name, the first record number and a plurality of second field data, and the second field data includes the first field index and the first new field value;

[0019] The second field data of each of the fifth update record data are traversed; during the traversal, the second field data currently traversed is used as the current field data, the first update data table name of the fifth update record data is used as the current update data table name, and the first field index of the current field data is used as the current index; and the first configuration item in the first configuration file whose first associated source table name matches the current update data table name and whose first source table field index matches the current index is recorded as the current configuration item; if the current configuration item is not empty, the first view field name of the current configuration item is extracted as the corresponding first update field name, the first record number of the current field data is extracted as the corresponding first update row number, and the first new field value of the current field data is extracted as the corresponding first update value, and the first update field name, the first update row number and the first update value are obtained to form a corresponding first update field group; when the traversal ends, all the obtained first update field groups constitute the corresponding first update field set.

[0020] Preferably, the performing field update processing on the view data table according to the first update field set specifically includes:

[0021] Each of the first update field groups of the first update field set is traversed; during the traversal, the first update field group currently traversed is used as the current update field group; and according to the UPDATE instruction format of the MySQL database, the update instruction parameter configuration processing is performed according to the first data table name of the first configuration file, the first update field name of the current update field group, the first update value and the first update row number, and the preset row number field name to generate a corresponding first update instruction; and the first update instruction is executed on the MySQL database.

[0022] A second aspect of the embodiments of the present invention provides a device for implementing the method described in the first aspect, including: an acquisition module and a view data table processing module;

[0023] The acquisition module is used to acquire a configuration file reflecting the field mapping relationship between the custom view and the associated source table as the corresponding first configuration file;

[0024] The view data table processing module is used to create a data table in the MySQL database as the view data table of the custom view according to the first configuration file; and after the view data table is successfully created, the row mode archive log binlog file output by the MySQL database is acquired in real time to generate a corresponding first log file; and according to the first configuration file, the update instruction of the first log file is parsed to generate a corresponding first update field set; and the view data table is subjected to field update processing according to the first update field set.

[0025] A third aspect of an embodiment of the present invention provides an electronic device, including: a memory, a processor, and a transceiver;

[0026] The processor is used to be coupled to the memory, read and execute instructions in the memory, so as to implement the method steps described in the first aspect above;

[0027] The transceiver is coupled to the processor, and the processor controls the transceiver to send and receive messages.

[0028] A fourth aspect of an embodiment of the present invention provides a computer-readable storage medium, wherein the computer-readable storage medium stores computer instructions. When the computer instructions are executed by a computer, the computer executes the instructions of the method described in the first aspect above.

[0029] The embodiment of the present invention provides a method, device, electronic device and computer-readable storage medium for processing a view data table. First, the field mapping relationship between a custom view and an associated source table is confirmed to obtain a corresponding configuration file; then a common data table for simulating a materialized view is created in a MySQL database and recorded as a view data table, and the relevant content of the associated source table is copied to the view data table based on the configuration file to complete the table initialization; then, the latest updated content of each associated source table is obtained in time by real-time parsing of the archive log binlog file of the MySQL database, and the data items related to the latest updated content in the view data table are also synchronously updated based on the field mapping relationship reflected in the configuration file. Through the present invention, the function of the materialized view can be realized in the form of a view data table on a MySQL database that does not support materialized views, and the problem that the MySQL database cannot retrieve the view can also be avoided. BRIEF DESCRIPTION OF THE DRAWINGS

[0030] Figure 1 A schematic diagram of a method for processing a view data table provided in Embodiment 1 of the present invention;

[0031] Figure 2 A module structure diagram of a view data table processing device provided in Embodiment 2 of the present invention;

[0032] Figure 3 A schematic diagram of the structure of an electronic device provided in Embodiment 3 of the present invention. DETAILED DESCRIPTION

[0033] In order to make the purpose, technical solution and advantages of the present invention clearer, the present invention will be further described in detail below in conjunction with the accompanying drawings. Obviously, the described embodiments are only part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without creative work are within the scope of protection of the present invention.

[0034] A method for processing a view data table provided in a first embodiment of the present invention is as follows: Figure 1 A schematic diagram of a method for processing a view data table provided in Embodiment 1 of the present invention is shown. The method mainly includes the following steps:

[0035] Step 1: Obtain a configuration file reflecting the field mapping relationship between the custom view and the associated source table and record it as the corresponding first configuration file;

[0036] Among them, the first configuration file includes a first data table name and multiple first configuration items; the first configuration item includes a first view field name, a first view field data type, a first associated source table name, a first source table field name and a first source table field index.

[0037] Here, the first data table name is the table name pre-planned as the view data table corresponding to the custom view; each first configuration item corresponds to a field mapping relationship; the first view field name is the field name in the pre-planned view data table, the first view field data type is the data type of the field, the first associated source table name is the table name of the associated source data table mapped by the field, the first source table field name is the source field name mapped by the field, and the first source table field index is the field index of the source field mapped by the field in the source data table.

[0038] For example, it is known that the associated source table db1 has 2 data records (record A1, record A2), 3 data fields (field 11, field 12, field 13), the field indexes of the 3 data fields (field 11, field 12, field 13) in the associated source table db1 are 1, 2, and 3 respectively, and the data types of all fields are character types:

[0039] Record A1 (field 11 = "1a", field 12 = "1b", field 13 = "1c"),

[0040] Record A2 (field 11 = "2a", field 12 = "2b", field 13 = "2c");

[0041] The associated source table db2 has two data records (record B1, record B2) and two data fields (field 21, field 22). The field indexes of the two data fields (field 21, field 22) in the associated source table db2 are 1 and 2 respectively. The data types of all fields are character types:

[0042] Record B1 (field 21 = "3a", field 22 = "3b"),

[0043] Record B2 (field 21 = "4a", field 22 = "4b");

[0044] Assume that the custom view has three data fields (field 31, field 32, field 33), where fields 31 and 32 are the mapping fields of fields 11 and 12 of the associated source table db1, respectively, and field 33 is the mapping field of field 22 of the associated source table db2; the data table name of the proposed custom view is view data table E; then, the first configuration file should include a first data table name and three first configuration items:

[0045] The first data table name = view data table E,

[0046] The first configuration item (the first view field name is field 31, the first view field data type is character type, the first associated source table name is associated source table db1, the first source table field name is field 11, and the first source table field index is 1),

[0047] The second first configuration item (the first view field name is field 32, the first view field data type is character type, the first associated source table name is associated source table db1, the first source table field name is field 12, and the first source table field index is 2),

[0048] The third first configuration item (the first view field name is field 33, the first view field data type is character type, the first associated source table name is associated source table db2, the first source table field name is field 22, and the first source table field index is 2).

[0049] Step 2: Create a data table in the MySQL database according to the first configuration file as a view data table of the custom view;

[0050] Specifically, the method comprises: step 21, performing table creation instruction parameter configuration processing according to the first configuration file according to the CREATE TABLE instruction format of the MySQL database to generate a corresponding first table creation instruction; and executing the first table creation instruction on the MySQL database to generate a data table as a view data table;

[0051] Among them, the instruction parameters of the first table creation instruction include a table name parameter and multiple table definition option parameters; the table name parameter is the first data table name; among the multiple table definition option parameters, the first table definition option parameter is a preset row number field definition option parameter, and the remaining table definition option parameters correspond to the first configuration item one by one; the table definition option parameters include a column name parameter and a column type parameter; the column name parameter of the row number field definition option parameter is a preset row number field name, and the column type parameter is a preset row number field type parameter; the column name parameter and the column type parameter of the table definition option parameter corresponding to the first configuration item are the first view field name and the first view field data type of the first configuration item respectively;

[0052] Here, the syntax format of the create table instruction (CREATE TABLE) in MySQL is:

[0053] CREATE TABLE ([table definition option parameter]),

[0054] The format of [table definition option parameter] is:

[0055] [<column name parameter 1><column type parameter 1>]…[<column name parameter n><column type parameter n>]; n is a positive integer;

[0056] In the preset row number field definition option parameter format, column name parameter = row number field name, column type parameter = row number field type parameter, where the row number field type parameter defaults to "INT NOT NULL AUTO_INCREMENT", that is, integer and automatically incremented from 1;

[0057] The embodiment of the present invention constructs a first table creation instruction according to the first data table name of the first configuration file and the first view field data type of the first configuration item of each first configuration item;

[0058] For example, it is known that the preset row number field name is row number field 1, and the first configuration file is: first data table name = view data table E,

[0059] The first configuration item (the first view field name is field 31, the first view field data type is character type, the first associated source table name is associated source table db1, the first source table field name is field 11, and the first source table field index is 1),

[0060] The second first configuration item (the first view field name is field 32, the first view field data type is character type, the first associated source table name is associated source table db1, the first source table field name is field 12, and the first source table field index is 2),

[0061] The third first configuration item (the first view field name is field 33, the first view field data type is character type, the first associated source table name is associated source table db2, the first source table field name is field 22, and the first source table field index is 2);

[0062] The default length of all character types is 255;

[0063] Then, the first create table instruction should be:

[0064] CREATE TABLE view data table E (

[0066] Row number field 1 INT NOT NULL AUTO_INCREMENT,

[0067] Field 31 VARCHAR(255) NULL,

[0068] Field 32 VARCHAR(255) NULL,

[0069] Field 33 VARCHAR(255) NULL );

[0071] Executing the above instructions in the MySQL database will create a data table called view data table E, and the table has 4 empty fields: row number field 1, field 31, field 32, field 33;

[0072] Step 22, traverse each first configuration item of the first configuration file; during the traversal, record the first configuration item currently traversed as the current configuration item, and use the first view field name, the first associated source table name, and the first source table field name of the current configuration item as the corresponding current view field name, the current associated source table name, and the current source table field name; and perform insert statement configuration processing according to the first data table name, the current view field name, the current associated source table name, and the current source table field name according to the INSERT INTO ... SELECT ... FROM statement format of the MySQL database to generate a corresponding first insert statement; and execute the first insert statement on the MySQL database;

[0073] The first insert statement includes a target table name parameter, a source table name parameter, a target field name parameter, and a source field name parameter; the target table name parameter is the first data table name; the source table name parameter is the current associated source table name; the target field name parameter is the current view field name; and the source field name parameter is the current source table field name.

[0074] Here, the INSERT INTO...SELECT...FROM statement format of the MySQL database is:

[0075] INSERT INTO <target table name parameter>

[0076] (target field name parameter 1, ...)

[0077] SELECT<source field name parameter 1,……>

[0078] FROM<source table name parameter>;

[0079] The embodiment of the present invention constructs a first insert statement according to the first data table name, the current view field name, the current associated source table name and the current source table field name of the first configuration file; and based on the first insert statement, copies the relevant content of the associated source table to the view data table to complete the table initialization;

[0080] For example, the first data table name in the first configuration file = view data table E, and the first configuration file includes three first configuration items; the three first configuration items are traversed;

[0081] When the current configuration item is the first configuration item, the current view field name is field 31, the current associated source table name is associated source table db1, and the current source table field name is field 11; the first insert statement obtained is:

[0082] INSERT INTO view data table E

[0083] (Field 31)

[0084] SELECT field 11

[0085] FROM associated source table db1;

[0086] After the first insert statement is executed on the MySQL database, the view data table E includes 4 fields (row number field 1, field 31, field 32, field 33) and 2 records (record C1, record C2): record C1 (row number field 1 = 1, field 31 = "1a", field 32 = "", field 33 = ""), record C2 (row number field 1 = 2, field 31 = "2a", field 32 = "", field 33 = "");

[0087] When the current configuration item is the second first configuration item, the current view field name is field 32, the current associated source table name is associated source table db1, and the current source table field name is field 12; the second first insert statement obtained is:

[0088] INSERT INTO view data table E

[0089] (Field 32)

[0090] SELECT field 12

[0091] FROM associated source table db1;

[0092] After executing the second first insert statement on the MySQL database, the view data table E is: record C1 (row number field 1 = 1, field 31 = "1a", field 32 = "1b", field 33 = ""), record C2 (row number field 1 = 2, field 31 = "2a", field 32 = "2b", field 33 = "");

[0093] When the current configuration item is the third first configuration item, the current view field name is field 33, the current associated source table name is associated source table db2, and the current source table field name is field 22; the third first insert statement obtained is:

[0094] INSERT INTO view data table E

[0095] (Field 33)

[0096] SELECT field 22

[0097] FROM associated source table db2;

[0098] After executing the third first insert statement on the MySQL database, the view data table E is: record C1 (row number field 1 = 1, field 31 = "1a", field 32 = "1b", field 33 = "3b"), record C2 (row number field 1 = 2, field 31 = "2a", field 32 = "2b", field 33 = "4b").

[0099] Step 3: After the view data table is successfully created, the row mode archive log binlog file output by the MySQL database is obtained in real time to generate a corresponding first log file; and the update instruction of the first log file is parsed according to the first configuration file to generate a corresponding first update field set; and the field update processing of the view data table is performed according to the first update field set;

[0100] Specifically, it includes: step 31, acquiring the row mode archive log binlog file output by the MySQL database in real time to generate a corresponding first log file;

[0101] Here, MySQL's binary archive log binlog file is a log file used by MySQL to record all database definition language (Data Definition Language, DDL) and data manipulation language (Data Manipulation Language, DML) statements (except data query statement select); binlog files have three log replication modes: STATEMENT mode, that is, SQL statement-based replication (SBR), in this mode MySQL database will only record each SQL statement; ROW mode is also called row mode, that is, row-based replication (RBR), in this mode MySQL database will record the row operation results caused by each SQL statement; MIXED mode, that is, mixed-based replication (MSR). replication, MBR), this mode is actually a mixed mode of STATEMENT and ROW modes, and the MySQL database selects one of the two modes and outputs the corresponding binlog file according to the actual situation; because the embodiment of the present invention needs to track the changes of each record and each field of the source data table, the binlog file in row mode is selected for acquisition; it should be noted that in order to ensure that the binlog file in row mode can be obtained, the parameters related to the binlog file mode on the MySQL database need to be configured in advance, and the specific implementation can be referred to the relevant public technology implementation, which will not be described one by one here;

[0102] Step 32, parsing the update instruction of the first log file according to the first configuration file to generate a corresponding first update field set;

[0103] The first update field set includes a plurality of first update field groups; the first update field group includes a first update field name, a first update row number and a first update value;

[0104] Specifically, it includes: step 321, extracting and processing MySQL database UPDATE instruction statements from the first log file to generate multiple first update instruction statements;

[0105] Among them, each first update instruction statement corresponds to an update operation of a data record in a data table; the first update instruction statement includes first, second and third paragraphs; the first paragraph is an UPDATE instruction paragraph, including the name of the data table corresponding to the current first update instruction statement; the second paragraph is a WHERE condition paragraph, including the original field content of all fields of the data record corresponding to the current first update instruction statement before the update operation, and the format of each original field content is field index = original field value; the third paragraph is a SET paragraph, including the new field content of all fields of the data record corresponding to the current first update instruction statement before the update operation, and the format of each new field content is field index = new field value;

[0106] Here, because the data format of binlog files is public, there are many corresponding parsing tools. When extracting MySQL database UPDATE instruction statements from the first log file, a mature binlog file parsing component can be used to extract UPDATE instruction statements, such as a binlog parser written based on Alibaba's canal component.

[0107] The format of the first update instruction statement obtained in the embodiment of the present invention is as follows:

[0108] First paragraph:

[0109] UPDATE ,

[0110] Second paragraph:

[0111] WHERE

[0112] @1 = original value of field 1

[0113] @2 = original value of field 2

[0114] …

[0115] The third paragraph:

[0116] SET

[0117] @1 = new value of field 1

[0118] @2 = new value of field 2

[0119] …

[0120] In the above format, <data table name> in the first paragraph is the name of the data table modified by the current first update instruction statement; @1 and @2 in the second paragraph are the field indexes of the 1st and 2nd fields of the data record modified by the current first update instruction statement, and the original values ​​of fields 1 and 2 are the original field values ​​of fields 1 and 2 before modification; @1 and @2 in the third paragraph are the field indexes of the 1st and 2nd fields of the data record modified by the current first update instruction statement, and the new values ​​of fields 1 and 2 are the new field values ​​of fields 1 and 2 after modification;

[0121] For example, after updating field 11="1a" of record A1 in the associated source table db1 to "8a", and updating field 21="4a" of record B2 in the associated source table db2 to "9a", the two first update instruction statements obtained should be:

[0122] The first update instruction statement is

[0123] "UPDATE associated source table db1

[0124] WHERE

[0125] @1='1a'

[0126] @2='1b'

[0127] @3='1c'

[0128] SET

[0129] @1='8a'

[0130] @2='1b'

[0131] @3='1c'",

[0132] The second first update instruction statement is

[0133] "UPDATE associated source table db2

[0134] WHERE

[0135] @1='4a'

[0136] @2='4b'

[0137] SET

[0138] @1='9a'

[0139] @2='4b'

[0140] ”;

[0141] Step 322, extract the name of the corresponding data table from each first update instruction statement as the corresponding first update data table name, and perform data fusion on each original field content and the new field content according to the field index to generate a corresponding first field data sequence, and the first update data table name and the first field data sequence form the corresponding first update record data;

[0142] The first field data sequence includes a plurality of first field data; the first field data includes a first field index, a first original field value and a first new field value;

[0143] For example, the first and second first update instruction statements obtained are as described above;

[0144] Then, for the first first update instruction statement, the corresponding first first update data table name = associated source table db1, the first first field data sequence is {first field data 11 (1, '1a', '8a'), first field data 12 (2, '1b', '1b'), first field data 13 (3, '1c', '1c')}; the first first update data table name + the first first field data sequence constitute the first first update record data;

[0145] For the second first update instruction statement, the corresponding second first update data table name = associated source table db2, the second first field data sequence is {first field data 21 (1, '4a', '9a'), first field data 22 (2, '4b', '4b')}, and the second first update data table name + the second first field data sequence constitute the second first update record data;

[0146] Step 323, record the first update record data corresponding to the first update data table name matching any first associated source table name in the first configuration file as the second update record data; assign a corresponding first record number to each second update record data, and initialize all first record numbers to 0; and record the data table corresponding to the first update data table name of each second update record data as the first update data table;

[0147] For example, the first configuration file is as described in the above example, then, because the first first update data table name = associated source table db1 matches the first associated source table names of the first and second first configuration items in the first configuration file, then the first second update record data = the first first update record data can be obtained, the corresponding first first record number = 0, and the first first update data table is the associated source table db1;

[0148] Because the second first update data table name = associated source table db2 matches the first associated source table name of the third first configuration item in the first configuration file, the second second update record data = the second first update record data can be obtained, and the corresponding second first record number = 0, and the second first update data table is the associated source table db2;

[0149] Step 324, and poll the data records of each first updated data table; during polling, the data record currently being polled is recorded as the current data record; and according to the corresponding relationship of the field index, the content of each field of the current data record is matched with each first new field value of the corresponding second updated record data to generate a corresponding first matching result; if the first matching result is not a full match, the first record number is increased by 1 and the next data record is transferred to continue polling until the last data record; if the first matching result is a full match, the first record number is increased by 1 and the polling ends;

[0150] Here, the actual process is to locate the record modification information obtained in the binlog file, that is, the record row number of the second update record data in its own data table, that is, the first update data table;

[0151] Continuing with the above example, data records are polled for the first and second first updated data tables (associated source tables db1 and db2) respectively;

[0152] Poll the data records of the first first update data table (associated source table db1): when the current data record is record A1, perform full field matching on the field contents of the current data record (field 11 = "8a", field 12 = "1b", field 13 = "1c") and the first new field values ​​('8a', 1b', '1c') of the corresponding first second update record data according to the corresponding relationship of the field index, and the first matching result is a full match, add 1 to the first record number to obtain a new first record number = 1, and end the polling;

[0153] Polling the data records of the second first update data table (associated source table db2): when the current data record is record B1, the first matching result obtained by performing full field matching on the field contents of the current data record (field 21="3a", field 22="3b") and the first new field values ​​('9a', '4b') of the corresponding second second update record data according to the corresponding relationship of the field index is not a full match, then the second first record number is increased by 1 to obtain a new second first record number = 1, and the polling continues; when the current data record is record B2, the first matching result obtained by performing full field matching on the field contents of the current data record (field 21="9a", field 22="4b") and the first new field values ​​('9a', '4b') of the corresponding second second update record data according to the corresponding relationship of the field index is a full match, then the second first record number is increased by 1 to obtain a new second first record number = 2, and the polling ends;

[0154] Step 325, delete the first field data having the same first original field value and first new field value in each second update record data to obtain corresponding third update record data; delete the first original field value of each first field data in the third update record data to obtain corresponding fourth update record data; and merge the fourth update record data with the corresponding first record number to generate corresponding fifth update record data;

[0155] The fifth update record data includes a first update data table name, a first record number and a plurality of second field data, and the second field data includes a first field index and a first new field value;

[0156] Continuing with the above example, the first second update record data includes: the first first update data table name (associated source table db1), the first field data 11 (1, '1a', '8a'), the first field data 12 (2, '1b', '1b'), the first field data 13 (3, '1c', '1c'); the corresponding first first record number = 1;

[0157] The second second update record data includes: the second first update data table name (related source table db2), the first field data 21 (1, '4a', '9a'), the first field data 22 (2, '4b', '4b'); the corresponding second first record number = 2;

[0158] Then, the first fifth update record data obtained from the first second update record data includes: the first first update data table name (related source table db1), the first first record number = 1, and the second field data 1 (1, '8a');

[0159] The second fifth update record data obtained from the second second update record data includes: the second first update data table name (related to the source table db2), the second first record number = 2, and the second field data 2 (1, '9a');

[0160] Step 326, traverse the second field data of each fifth update record data; during the traversal, use the second field data currently traversed as the current field data, and use the first update data table name of the fifth update record data as the current update data table name, and use the first field index of the current field data as the current index; and record the first configuration item in the first configuration file whose first associated source table name matches the current update data table name and whose first source table field index matches the current index as the current configuration item; if the current configuration item is not empty, extract the first view field name of the current configuration item as the corresponding first update field name, extract the first record number of the current field data as the corresponding first update row number, and extract the first new field value of the current field data as the corresponding first update value, and form a corresponding first update field group with the obtained first update field name, first update row number and first update value; when the traversal is completed, all the obtained first update field groups form a corresponding first update field set;

[0161] Continuing with the above example, the second field data of the first and second fifth update record data are traversed;

[0162] Traverse the second field data of the first fifth update record data;

[0163] When the current field data is the second field data 1 (1, '8a'), the current update data table name = associated source table db1, the current index = 1, and the first configuration item in the first configuration file whose first associated source table name matches the associated source table db1 and whose first source table field index is 1 is the first first configuration item, then the first update field name is extracted = field 31, the first update row number is extracted as the first first record number = 1, the first update value is extracted as '8a' of the second field data 1 (1, '8a'), and the first first update field group is (field 31, 1, '8a');

[0164] Traverse the second field data of the second fifth update record data;

[0165] When the current field data is the second field data 2 (1, '9a'), the current update data table name = associated source table db2, the current index = 1, and the first configuration item in the first configuration file whose first associated source table name matches the associated source table db2 and the first source table field index is 1 is empty, then no corresponding first update field group is generated;

[0166] The first update field set finally obtained only includes the first first update field group (field 31, 1, '8a');

[0167] Step 33, performing field update processing on the view data table according to the first update field set;

[0168] Specifically, it includes: traversing each first update field group of the first update field set; during the traversal, taking the currently traversed first update field group as the current update field group; and performing update instruction parameter configuration processing according to the UPDATE instruction format of the MySQL database based on the first data table name of the first configuration file, the first update field name, the first update value and the first update row number of the current update field group, and the preset row number field name to generate a corresponding first update instruction; and executing the first update instruction on the MySQL database.

[0169] Here, the update instruction format defined in the embodiment of the present invention according to the UPDATE instruction format of the MySQL database is:

[0170] UPDATE <data table name parameter>

[0171] SET<target field name parameter=field value,...>

[0172] WHERE row number field name parameter = row number value;

[0173] The embodiment of the present invention constructs a first update instruction with the first update field name as a data table name parameter, the first update field name as a target field name parameter, the first update value as the field value of the target field name parameter, the preset row number field name as a row number field name parameter, and the first update row number as the row number value of the row number field name parameter; and based on the first update instruction, the data items related to the latest updated content in the view data table are also synchronously updated.

[0174] Continuing with the above example, it is known that the preset row number field name is row number field 1;

[0175] Traverse the first update field group of the first update field set; when the current update field group is the first first update field group (field 31, 1, '8a'), the first data table name = view data table E, the first update field name = field 31, the first update value = '8a', the first update row number = 1, then the first and only first update instruction is:

[0176] UPDATE view data table E

[0177] SET field 31 = '8a'

[0178] WHERE row number field 1 = 1;

[0179] The contents of view data table E obtained after executing the first update instruction on the MySQL database are: record C1 (row number field 1 = 1, field 31 = "8a", field 32 = "1b", field 33 = "3b"), record C2 (row number field 1 = 2, field 31 = "2a", field 32 = "2b", field 33 = "4b").

[0180] Figure 2 This is a module structure diagram of a view data table processing device provided in the second embodiment of the present invention. The device may be a terminal device or a server that implements the method of the embodiment of the present invention, or may be a device that implements the method of the embodiment of the present invention connected to the above terminal device or server. For example, the device may be a device or a chip system of the above terminal device or server. Figure 2 As shown, the device includes: an acquisition module 201 and a view data table processing module 202.

[0181] The acquisition module 201 is used to acquire a configuration file reflecting the field mapping relationship between the custom view and the associated source table and record it as the corresponding first configuration file.

[0182] The view data table processing module 202 is used to create a data table in the MySQL database as a view data table of a custom view according to the first configuration file; and after the view data table is successfully created, the row mode archive log binlog file output by the MySQL database is obtained in real time to generate a corresponding first log file; and according to the first configuration file, the update instruction of the first log file is parsed to generate a corresponding first update field set; and the view data table is subjected to field update processing according to the first update field set.

[0183] An embodiment of the present invention provides a device for processing a view data table, which can execute the method steps in the above method embodiment. Its implementation principle and technical effects are similar and will not be described in detail here.

[0184] It should be noted that it should be understood that the division of the various modules of the above device is only a division of logical functions. In actual implementation, they can be fully or partially integrated into one physical entity, or they can be physically separated. And these modules can all be implemented in the form of software called by processing elements; they can also be all implemented in the form of hardware; some modules can also be implemented in the form of software called by processing elements, and some modules can be implemented in the form of hardware. For example, the acquisition module can be a separately established processing element, or it can be integrated in a chip of the above device. In addition, it can also be stored in the memory of the above device in the form of program code, and called and executed by a processing element of the above device. The function of the above-mentioned module is determined. The implementation of other modules is similar. In addition, these modules can be fully or partially integrated together, or they can be implemented independently. The processing element described here can be an integrated circuit with signal processing capabilities. In the implementation process, each step of the above method or each module above can be completed by an integrated logic circuit of hardware in the processor element or instructions in the form of software.

[0185] For example, the above modules may be one or more integrated circuits configured to implement the above methods, such as one or more application specific integrated circuits (ASIC), or one or more digital signal processors (DSP), or one or more field programmable gate arrays (FPGA). For another example, when a module above is implemented in the form of a processing element scheduling program code, the processing element may be a general-purpose processor, such as a central processing unit (CPU) or other processor that can call program code. For another example, these modules may be integrated together and implemented in the form of a system-on-a-chip (SOC).

[0186] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware or any combination thereof. When implemented using software, it can 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 the computer program instructions are loaded and executed on a computer, the process or function described in accordance with the embodiment of the present invention is generated in whole or in part. The above computer can be a general-purpose computer, a special-purpose computer, a computer network, or other programmable devices. The above computer instructions can be stored in a computer-readable storage medium, or transmitted from one computer-readable storage medium to another computer-readable storage medium, for example, the above computer instructions can be transmitted from a website site, computer, server or data center by wired (e.g., coaxial cable, optical fiber, digital subscriber line (Digital Subscriber Line, DSL)) or wireless (e.g., infrared, wireless, Bluetooth, microwave, etc.) mode to another website site, computer, server or data center. The above computer-readable storage medium can be any available medium that a computer can access or a data storage device such as a server, data center, etc. that includes one or more available media integrated. The available medium may be a magnetic medium (eg, a floppy disk, a hard disk, a magnetic tape), an optical medium (eg, a DVD), or a semiconductor medium (eg, a solid state disk (SSD)).

[0187] Figure 3 This is a schematic diagram of the structure of an electronic device provided in Embodiment 3 of the present invention. The electronic device may be the aforementioned terminal device or server, or may be a terminal device or server connected to the aforementioned terminal device or server to implement the method of the embodiment of the present invention. Figure 3 As shown, the electronic device may include: a processor 301 (such as a CPU), a memory 302, and a transceiver 303; the transceiver 303 is coupled to the processor 301, and the processor 301 controls the transceiver 303. Various instructions may be stored in the memory 302 to complete various processing functions and implement the methods and processing procedures provided in the above embodiments of the present invention. Preferably, the electronic device involved in the embodiment of the present invention also includes: a power supply 304, a system bus 305 and a communication port 306. The system bus 305 is used to realize communication connection between components. The above-mentioned communication port 306 is used for connection and communication between the electronic device and other peripherals.

[0188] exist Figure 3The system bus mentioned in the figure can be a Peripheral Component Interconnect (PCI) bus or an Extended Industry Standard Architecture (EISA) bus, etc. The system bus can be divided into an address bus, a data bus, a control bus, etc. For ease of representation, only one thick line is used in the figure, but it does not mean that there is only one bus or one type of bus. The communication interface is used to realize the communication between the database access device and other devices (such as clients, read-write libraries, and read-only libraries). The memory may include random access memory (RAM) and may also include non-volatile memory (Non-Volatile Memory), such as at least one disk storage.

[0189] The above-mentioned processor can be a general-purpose processor, including a central processing unit CPU, a network processor (NP), etc.; it can also be a digital signal processor DSP, an application-specific integrated circuit ASIC, a field programmable gate array FPGA or other programmable logic devices, discrete gate or transistor logic devices, discrete hardware components.

[0190] It should be noted that an embodiment of the present invention further provides a computer-readable storage medium, in which instructions are stored. When the storage medium is run on a computer, the computer executes the method and processing process provided in the above embodiments.

[0191] An embodiment of the present invention further provides a chip for executing instructions, and the chip is used to execute the method and processing process provided in the above embodiment.

[0192] The embodiment of the present invention provides a method, device, electronic device and computer-readable storage medium for processing a view data table. First, the field mapping relationship between a custom view and an associated source table is confirmed to obtain a corresponding configuration file; then a common data table for simulating a materialized view is created in a MySQL database and recorded as a view data table, and the relevant content of the associated source table is copied to the view data table based on the configuration file to complete the table initialization; then, the latest updated content of each associated source table is obtained in time by real-time parsing of the archive log binlog file of the MySQL database, and the data items related to the latest updated content in the view data table are also synchronously updated based on the field mapping relationship reflected in the configuration file. Through the present invention, the function of the materialized view can be realized in the form of a view data table on a MySQL database that does not support materialized views, and the problem that the MySQL database cannot retrieve the view can also be avoided.

[0193] The professionals should further realize that the units and algorithm steps of each example described in conjunction with the embodiments disclosed herein can be implemented by electronic hardware, computer software, or a combination of the two. In order to clearly illustrate the interchangeability of hardware and software, the composition and steps of each example have been generally described in the above description according to the function. Whether these functions are performed in hardware or software depends on the specific application and design constraints of the technical solution. Professionals and technicians can use different methods to implement the described functions for each specific application, but such implementation should not be considered to be beyond the scope of the present invention.

[0194] The steps of the method or algorithm described in conjunction with the embodiments disclosed herein may be implemented using hardware, a software module executed by a processor, or a combination of the two. The software module may be placed in a random access memory (RAM), a memory, a read-only memory (ROM), an electrically programmable ROM, an electrically erasable programmable ROM, a register, a hard disk, a removable disk, a CD-ROM, or any other form of storage medium known in the art.

[0195] The specific implementation methods described above further illustrate the objectives, technical solutions and beneficial effects of the present invention in detail. It should be understood that the above description is only a specific implementation method of the present invention and is not intended to limit the scope of protection of the present invention. Any modifications, equivalent substitutions, improvements, etc. made within the spirit and principles of the present invention should be included in the scope of protection of the present invention.

Claims

1. A method for processing a view data table, characterized in that: The method comprises: A configuration file reflecting the field mapping relationship between the custom view and the associated source table is obtained and recorded as the corresponding first configuration file; Creating a data table in the MySQL database according to the first configuration file as a view data table of the custom view; After the view data table is successfully created, the row mode archive log binlog file output by the MySQL database is acquired in real time to generate a corresponding first log file; and the update instruction of the first log file is parsed according to the first configuration file to generate a corresponding first update field set; and the field update processing of the view data table is performed according to the first update field set; The first configuration file includes a first data table name and a plurality of first configuration items; the first configuration item includes a first view field name, a first view field data type, a first associated source table name, a first source table field name, and a first source table field index; The step of creating a data table in the MySQL database according to the first configuration file as the view data table of the custom view specifically includes: According to the CREATE TABLE instruction format of the MySQL database, the table creation instruction parameter configuration processing is performed according to the first configuration file to generate a corresponding first table creation instruction; and the first table creation instruction is executed on the MySQL database to generate a data table as the view data table; Traverse each of the first configuration items of the first configuration file; during the traversal, record the first configuration item currently traversed as the current configuration item, and use the first view field name, the first associated source table name, and the first source table field name of the current configuration item as the corresponding current view field name, the current associated source table name, and the current source table field name; and perform insert statement configuration processing according to the first data table name, the current view field name, the current associated source table name, and the current source table field name according to the INSERT INTO...SELECT...FROM statement format of the MySQL database to generate a corresponding first insert statement; and execute the first insert statement on the MySQL database.

2. The method for processing a view data table according to claim 1, characterized in that: The instruction parameters of the first table creation instruction include a table name parameter and multiple table definition option parameters; the table name parameter is the first data table name; among the multiple table definition option parameters, the first table definition option parameter is a preset row number field definition option parameter, and the remaining table definition option parameters correspond to the first configuration item one by one; the table definition option parameters include a column name parameter and a column type parameter; the column name parameter of the row number field definition option parameter is a preset row number field name, and the column type parameter is a preset row number field type parameter; the column name parameter and the column type parameter of the table definition option parameter corresponding to the first configuration item are the first view field name and the first view field data type of the first configuration item respectively; The first insert statement includes a target table name parameter, a source table name parameter, a target field name parameter, and a source field name parameter; The target table name parameter is the first data table name; The source table name parameter is the name of the current associated source table; the target field name parameter is the name of the current view field; The source field name parameter is the field name of the current source table; The first update field set includes multiple first update field groups; the first update field group includes a first update field name, a first update row number and a first update value.

3. The method for processing a view data table according to claim 2, characterized in that: The step of parsing the update instruction of the first log file according to the first configuration file to generate a corresponding first update field set specifically includes: The first log file is subjected to MySQL database UPDATE instruction statement extraction and processing to generate multiple first update instruction statements; each of the first update instruction statements corresponds to an update operation of a data record in a data table; the first update instruction statement includes first, second and third paragraphs; the first paragraph is an UPDATE instruction paragraph, including the name of the data table corresponding to the current first update instruction statement; the second paragraph is a WHERE condition paragraph, including the original field contents of all fields of the data record corresponding to the current first update instruction statement before the update operation, and the format of each of the original field contents is field index = original field value; the third paragraph is a SET paragraph, including the new field contents of all fields of the data record corresponding to the current first update instruction statement before the update operation, and the format of each of the new field contents is field index = new field value; Extract the name of the corresponding data table from each of the first update instruction statements as the corresponding first update data table name, and perform data fusion on each of the original field content and the new field content according to the field index to generate a corresponding first field data sequence, and form the corresponding first update record data from the first update data table name and the first field data sequence; the first field data sequence includes a plurality of first field data; the first field data includes a first field index, a first original field value and a first new field value; Record the first update record data corresponding to the first update data table name matching any of the first associated source table names in the first configuration file as second update record data; assign a corresponding first record number to each of the second update record data, and initialize all the first record numbers to 0; and record the data table corresponding to the first update data table name of each of the second update record data as a first update data table; and polling the data records of each of the first updated data tables; during polling, recording the currently polled data record as the current data record; and performing full field matching on the contents of each field of the current data record and the first new field values ​​of the corresponding second updated record data according to the corresponding relationship of the field indexes to generate a corresponding first matching result; if the first matching result is not a full match, adding 1 to the first record number and switching to the next data record to continue polling until the last data record; if the first matching result is a full match, adding 1 to the first record number and ending the polling; The first field data having the same first original field value and the first new field value in each of the second update record data are deleted to obtain corresponding third update record data; the first original field value of each of the first field data in the third update record data is deleted to obtain corresponding fourth update record data; and the fourth update record data is merged with the corresponding first record number to generate corresponding fifth update record data; the fifth update record data includes the first update data table name, the first record number and a plurality of second field data, and the second field data includes the first field index and the first new field value; The second field data of each of the fifth update record data are traversed; during the traversal, the second field data currently traversed is used as the current field data, the first update data table name of the fifth update record data is used as the current update data table name, and the first field index of the current field data is used as the current index; and the first configuration item in the first configuration file whose first associated source table name matches the current update data table name and whose first source table field index matches the current index is recorded as the current configuration item; if the current configuration item is not empty, the first view field name of the current configuration item is extracted as the corresponding first update field name, the first record number of the current field data is extracted as the corresponding first update row number, and the first new field value of the current field data is extracted as the corresponding first update value, and the first update field name, the first update row number and the first update value are obtained to form a corresponding first update field group; when the traversal ends, all the obtained first update field groups constitute the corresponding first update field set.

4. The method for processing a view data table according to claim 2, characterized in that: The performing field update processing on the view data table according to the first update field set specifically includes: Each of the first update field groups of the first update field set is traversed; during the traversal, the first update field group currently traversed is used as the current update field group; and according to the UPDATE instruction format of the MySQL database, the update instruction parameter configuration processing is performed according to the first data table name of the first configuration file, the first update field name of the current update field group, the first update value and the first update row number, and the preset row number field name to generate a corresponding first update instruction; and the first update instruction is executed on the MySQL database.

5. A device for implementing the steps of the method for processing a view data table according to any one of claims 1 to 4, characterized in that: The device comprises: an acquisition module and a view data table processing module; The acquisition module is used to acquire a configuration file reflecting the field mapping relationship between the custom view and the associated source table as the corresponding first configuration file; The view data table processing module is used to create a data table in the MySQL database as the view data table of the custom view according to the first configuration file; and after the view data table is successfully created, the row mode archive log binlog file output by the MySQL database is acquired in real time to generate a corresponding first log file; and according to the first configuration file, the update instruction of the first log file is parsed to generate a corresponding first update field set; and the view data table is subjected to field update processing according to the first update field set.

6. An electronic device, characterized in that: include: memory, processors, and transceivers; The processor is used to couple with the memory, read and execute instructions in the memory, so as to implement the method according to any one of claims 1 to 4; The transceiver is coupled to the processor, and the processor controls the transceiver to send and receive messages.

7. A computer-readable storage medium, characterized in that: The computer-readable storage medium stores computer instructions, and when the computer instructions are executed by a computer, the computer is enabled to execute the method according to any one of claims 1 to 4.

Images