A method for importing large capacity SQL files for relational databases

Abstract

The application discloses a method for importing large-capacity SQL files of a relational database, comprising the following steps: S100, lexical analysis of the SQL file, comprising the following steps: S101, configuring lexical analysis rules to generate a lexical analysis configuration file; according to SQL lexical rules and lexical analyzer configuration rules, configuring recognition rules of lexical types and generating a lexical analysis configuration file; S102, using a lexical analyzer to generate an executable file from the lexical analysis configuration file; S103, using the executable file to generate lexical tags; S200, syntax analysis of the SQL file; generating a syntax analysis configuration file and using a syntax analyzer to generate syntax structure body objects corresponding to each statement in the SQL file; S300, syntax structure persistence; using the syntax structure body objects to extract database structures and store them into a JSON file; S400, creating a relational database according to the JSON file and importing the obtained text file into the relational database.

Description

TECHNICAL FIELD

[0001] The present application belongs to the field of database and data recovery, and relates to a large-capacity SQL file import method, in particular to a large-capacity SQL file import method for a relational database. BACKGROUND

[0002] With the rapid development of information technology, various data also grow exponentially, and the demand for storage, backup and backup download of these data is increasingly prominent. For this reason, various databases such as MYSQL, SQLServer, Oracle, etc. have emerged. Some manufacturers provide cloud database storage services such as Aliyun, Amazon Cloud, etc. These manufacturers provide export backup or download functions for database migration and data security.

[0003] In the prior art, most of the export backup or download file formats are SQL format files (for convenience, hereinafter referred to as SQL files). These SQL files are very large, often exceeding tens of GB, or even hundreds of GB. This results in a large amount of time spent on re-importing into the database, and even more so, if there is an error in the SQL statement during the import process, the entire import will fail, and the next time the import is performed, it will start from the beginning, which is very time-consuming and inefficient. The import flexibility is poor, and users cannot select the required part for import, but can only import the entire disk.

[0004] For example, in the prior art, phpmyadmin is used for import, which has limitations on memory; for another example, Navicat Premium tool is used to run SQL, which is low in work efficiency; source command is used for import, which is also very time-consuming, for example, it takes an hour to import a 2.G file; in addition, the above methods all have the problem that an error in the SQL statement during the import process will cause the entire import to fail. SUMMARY

[0005] In view of the technical problems of the prior art, the present application provides a large-capacity SQL file import method for a relational database, which uses a lexical analyzer and a syntax analyzer to divide the SQL file into table creation statements and text files, simultaneously visualizes the database creation syntax as a database node tree, and opens multiple parallel threads to improve the import speed, including the following steps:

[0006] S100: lexical analysis of the SQL file, including the following steps:

[0007] S101: configure the lexical analysis rules to generate a lexical analysis configuration file: according to the SQL lexical rules and the lexical analyzer configuration rules, configure the recognition rules of the lexical type and generate a lexical analysis configuration file;

[0008] S102: generating the lexical analysis configuration file into an executable file by using the lexical analyzer;

[0009] S103: generating the lexical token by using the executable file;

[0010] S200: syntax analysis of the SQL file: generating a syntax analysis configuration file and generating syntax structure object corresponding to each statement in the SQL file by using a syntax analyzer, wherein the statements in the SQL file include a database creation statement, a database selection statement, a table creation statement and an insertion statement;

[0011] S300: syntax structure persistence: extracting the database structure and storing it into a JSON file by using the syntax structure object;

[0012] S400: creating a relational database according to the JSON file and importing the obtained text file into the relational database.

[0013] Preferably, the lexical analyzer is FLEX and the syntax analyzer is BISON.

[0014] Preferably, the lexical type includes SQL keywords, user tokens, value tokens and symbol tokens, the SQL keywords include create, insert, delete, table and database, the user tokens are user-named tokens including database name, table name and field name, the value tokens include string value, integer value, floating point value, hexadecimal value and binary value, and the symbol tokens include arithmetic operator symbol, logical operator symbol, punctuation symbol and end-of-file symbol.

[0015] Preferably, for the encoding and file encoding header that cannot be recognized / processed by the lexical analyzer, different types of encoding and file encoding header are respectively excluded / ignored and no lexical token is generated.

[0016] Preferably, step S200 includes the following steps:

[0017] S201: configuring syntax analysis rules and generating a syntax analysis configuration file: configuring the recognition rules of each statement in the SQL file according to the SQL syntax rules and the syntax analyzer configuration rules and generating a syntax analysis configuration file;

[0018] S202: generating the syntax analysis configuration file into an executable file by using the syntax analyzer;

[0019] S203: inputting the generated lexical token into the syntax analyzer to produce the SQL syntax structure object.

[0020] Preferably, the syntax structure persistence includes the following steps:

[0021] S301: read the current syntax structure object, and determine whether the type of the current syntax structure object is one of the following types:

[0022] A. a database creation statement / library selection statement: execute step S302;

[0023] B. a table creation statement: execute step S303;

[0024] C. an insert statement: execute step S304;

[0025] S302: persist the syntax structure object of the database creation statement / library selection statement, and execute step S305;

[0026] S303: persist the syntax structure object of the table creation statement, and execute step S305;

[0027] S304: persist the syntax structure object of the insert statement, and store table data into a text file, set the size of the text file to be no more than a first threshold value, for querying / importing, wherein the first threshold value is 200M bytes, and execute step S305;

[0028] S305: determine whether the syntax structure object in the large-capacity SQL file has been read completely, if yes, execute step S400, otherwise, execute step S306;

[0029] S306: read the next syntax structure object in the SQL file, and execute step S301.

[0030] Preferably, for the type of the current syntax structure object being a database creation statement / library selection statement, the following steps are executed:

[0031] S302-1: read the database name of the current syntax structure object, and determine whether the current database name is an empty string, if yes, execute step S302-2, otherwise, execute step S302-3;

[0032] S302-2: read the current SQL file name as the database name;

[0033] S302-3: find the database JSON node in the JSON structure according to the current database name, if not found, execute step S302-4, otherwise, execute step S302-5;

[0034] S302-4: construct the database JSON node, the database configuration information of the currently constructed JSON node including a database ID, a database name, and a table array node;

[0035] S302-5: record the current database name as the first database name, and execute step S305.

[0036] Preferably, for the type of the current syntax structure object being a table creation statement, the following steps are executed:

[0037] S303-1: determine whether the current syntax structure object has a database name, if yes, execute step S303-3, otherwise, execute step S303-2;

[0038] S303-2: determine whether the current first database name is an empty string, if yes, execute step S303-5, otherwise, execute step S303-4;

[0039] S303-3: obtain the database name in which the current syntax structure object exists, and execute step S303-6;

[0040] S303-4: take the current first database name as the database name, and execute step S303-6;

[0041] S303-5: read the current SQL file name as the database name;

[0042] S303-6: search for the database JSON node in the JSON structure according to the current database name, if found, execute step S303-7, otherwise, execute step S303-8;

[0043] S303-7: construct the database JSON node, the database configuration information of the currently constructed JSON node including the database ID, the database name, and the table array node;

[0044] S303-8: record the current database name as the first database name, and read the table name in the current syntax structure object;

[0045] S303-9: search for the database JSON node in the JSON structure according to the current table name, if not found, execute step S303-A, otherwise, execute step S305;

[0046] S303-A: construct the table JSON node and add it to the table array node, the table configuration information of the JSON node including the table ID, the table name, the field information, and the current table data storage file number;

[0047] S303-B: execute step S305.

[0048] Preferably, for the type of the current syntax structure object being an insert statement, the following steps are executed:

[0049] S304-1: judging whether the current syntax structure object has a field list, if yes, executing step S304-2, otherwise, executing step S304-3;

[0050] S304-2: constructing the field list according to the value list inserted into the syntax structure object, wherein each field name is respectively named as the first domain, the second domain, the third domain,..., the Nth domain, and each field list is respectively set with a default field type, wherein the default field type is the text type, and N is a natural number greater than 1;

[0051] S304-3: creating the database and the table JSON node by using the method of steps S303-1 to S303-A;

[0052] S304-4: judging whether the current inserted table and the last inserted table are the same table, if yes, executing step S304-5, otherwise, executing step S304-7;

[0053] S304-5: judging whether the size of the current written text file exceeds the first threshold, if yes, executing step S304-6, otherwise, executing step S304-8;

[0054] S304-6: the table data storage file number of the current table written text file is increased by 1;

[0055] S304-7: newly creating a text file for storing table data;

[0056] S304-8: writing each value of the inserted syntax structure object into the text file, and assigning a default value to the field without value in the inserted syntax structure object, wherein the default value is empty.

[0057] Preferably, step S400 comprises the following steps:

[0058] The visual database structure tree is created, so that the user can select the required table node of the imported relational database, and multiple parallel threads are started to improve the import speed.

[0059] The present application has the following beneficial effects:

[0060] 1. The import speed is fast, and the import failure caused by the error of the SQL statement in the import process is avoided;

[0061] 2. The import is flexible, and the user can select the required part for import. BRIEF DESCRIPTION OF DRAWINGS

[0062] Figure 1 It is a flow chart of the large-capacity SQL file import method for the relational database provided by the present application;

[0063] Figure 2 is the flow chart of the persistence of the syntax structure object of the build library statement / library selection statement in the present application;

[0064] Figure 3 is the flow chart of the persistence of the syntax structure object of the build table statement in the present application;

[0065] Figure 4 is the flow chart of the persistence of the syntax structure object of the insert statement in the present application. DETAILED DESCRIPTION

[0066] Figure 1 A flow chart of a mass SQL file import method for a relational database according to the present application is shown. For convenience of description, the present embodiment selects a MYSQL database as an example, and the import methods of other databases are similar.

[0067] As shown in Figure 1 , the method comprises the following steps:

[0068] S100: lexical analysis of the SQL file, comprising the following steps:

[0069] S101: configuring lexical analysis rules to generate a lexical analysis configuration file:

[0070] In the present embodiment, FLEX is used as the lexical analyzer.

[0071] According to the SQL lexical rules and the FLEX configuration rules of the lexical analyzer, the recognition rules of the lexical types are configured and the lexical analysis configuration file is generated;

[0072] Specifically, the lexical types include SQL keywords, user labels, value labels and symbol labels, the SQL keywords include CREATE, INSERT, TABLE, DATABASE, USE, the user labels are user-named labels, including database names, table names and field names, the value labels include string values, integer values, floating point values, hexadecimal values and binary values, and the symbol labels include arithmetic operator symbols, logical operator symbols, punctuation symbols and file end symbols.

[0073] Taking the floating point value as an example, according to the SQL lexical rules and the FLEX configuration rules of the lexical analyzer, the recognition rules of the floating point value of the value label in the lexical type are configured and are generated in the lexical analysis configuration file, and an example is as follows:

[0074] / *floating point value* /

[0075] [0-9]+\.[0-9]*|

[0076] "."[0-9]*{

[0077] yylval->fval = atof(yytext);

[0078] return SQL_FLOATVAL;

[0079] }

[0080] Wherein, the regular expression of the floating-point number identification is "[0-9]+\.[0-9]*|" "."[0-9]*"; yylval->fval = atof(yytext); is the C++ writing method, which obtains the floating-point number value; and SQL_FLOATVAL is the floating-point number identification.

[0081] Those skilled in the art should understand that, according to the SQL lexical rule and the lexical analyzer FLEX configuration rule, other lexical type identification rules can be configured and generated in the lexical analysis configuration file.

[0082] S102: Adopting the lexical analyzer FLEX, the lexical analysis configuration file is generated into a C++ executable file;

[0083] S103: Adopting the executable file, the lexical mark is generated;

[0084] It is worth noting that, for the encoding and file encoding head that cannot be recognized / processed by the lexical analyzer, according to different types of encoding and file encoding head, each is respectively excluded / ignored and no lexical mark is generated.

[0085] For example, the SQL file is input into the lexical analyzer FLEX, and a lexical mark Token is produced after the processing of the lexical analyzer FLEX. It is worth noting that the lexical analyzer FLEX can only process the content in the SQL file, and cannot process the encoding mark head of the SQL file, for example: the head of the SQL file with BOM head of UTF-8 encoding has 0xEF, 0xBB, 0xBF three identification bytes, and these three bytes need to be skipped / excluded / ignored before analysis.

[0086] S200: Syntax analysis of the SQL file: generating a syntax analysis configuration file, and adopting a syntax analyzer to generate a syntax structure body object corresponding to each statement in the SQL file, wherein the statements in the SQL file include a database building statement, a database selection statement, a table building statement and an insertion statement;

[0087] In this embodiment, BISON is adopted as the syntax analyzer.

[0088] Step S200 includes the following steps:

[0089] S201: Configure syntax analysis rules and generate a syntax analysis configuration file: according to the SQL syntax rules and the syntax analyzer configuration rules, configure the recognition rules of each statement in the SQL file and generate a syntax analysis configuration file;

[0090] For example, according to the SQL syntax rules and the BISON syntax analyzer configuration rules, the database creation statement rule is configured into the syntax analysis configuration file. The specific syntax recognition definition rule is as follows:

[0091] / * Database creation statement * /

[0092] create_db_statement:

[0093] CREATE DATABASE opt_not_exists IDENTIFIER{

[0094] $$=new CreateStatement(kCreateDatabase);

[0095] $$->ifNotExists=$3;

[0096] $$->schema=$4;

[0097] } ;

[0099] Wherein, "create_db_statement" is the database creation statement identifier; "CREATE" and "DATABASE" are SQL keywords; "opt_not_exists" is the database existence identifier; "IDENTIFIER" is the user identifier (in this embodiment, the database name); and the curly braces are the value acquisition method configured by BISON, which creates a structure object of the database creation statement.

[0100] Those skilled in the art should understand that according to the SQL lexical rules and the BISON syntax analyzer configuration rules, the recognition rules of other statements can be configured and generated in the syntax analysis configuration file.

[0101] S202: Use the syntax analyzer BISON to generate a c++ executable file from the syntax analysis configuration file;

[0102] S203: Pass the generated lexical token Token into the syntax analyzer BISON to produce the SQL syntax structure object.

[0103] S300: Syntax structure persistence: use the syntax structure object to extract the database structure and store it into a JSON file, including the following steps:

[0104] S301: read the current syntax structure object, and determine whether the type of the current syntax structure object is one of the following types:

[0105] A. library creation statement / library selection statement: then execute step S302;

[0106] B. table creation statement: then execute step S303;

[0107] C. insert statement: then execute step S304;

[0108] S302: persistence of the syntax structure object of the library creation statement / library selection statement, and execute step S305.

[0109] Figure 2 A flowchart of persistence of the syntax structure object of the library creation statement / library selection statement in the present application is shown. As shown in Figure 2 , the following steps are included:

[0110] S302-1: read the database name of the current syntax structure object and determine whether the current database name is an empty string, if yes, execute step S302-2, otherwise, execute step S302-3;

[0111] S302-2: read the current SQL file name as the database name;

[0112] S302-3: find the database JSON node in the JSON structure according to the current database name, if not found, execute step S302-4, otherwise, execute step S302-5;

[0113] S302-4: construct the database JSON node, the database configuration information of the current constructed JSON node includes the database ID, the database name, and the table array node;

[0114] S302-5: record the current database name as dbName, and execute step S305;

[0115] S303: persistence of the syntax structure object of the table creation statement, and execute step S305.

[0116] Figure 3 A flowchart of persistence of the syntax structure object of the table creation statement in the present application is shown. As shown in Figure 3 , the following steps are included:

[0117] S303-1: determine whether the current syntax structure object has a database name, if yes, execute step S303-3, otherwise, execute step S303-2;

[0118] S303-2: judging whether the current first database name is a null string, if yes, executing step S303-5, otherwise executing step S303-4;

[0119] S303-3: obtaining the database name in which the current syntax structure object exists, and executing step S303-6;

[0120] S303-4: taking the current dbName as the database name, and executing step S303-6;

[0121] S303-5: reading the current SQL file name as the database name;

[0122] S303-6: searching the database JSON node in the JSON structure according to the current database name, if found, executing step S303-7, otherwise executing step S303-8;

[0123] S303-7: constructing the database JSON node, the database configuration information of the current constructed JSON node including the database ID, the database name and the table array node;

[0124] S303-8: recording the current database name as dbName, and reading the table name in the current syntax structure object;

[0125] S303-9: searching the database JSON node in the JSON structure according to the current table name, if not found, executing step S303-A, otherwise executing step S305;

[0126] S303-A: constructing the table JSON node, and adding it to the table array node, the table configuration information of the JSON node including the table ID, the table name, the field information and the current table data storage file number;

[0127] S303-B: executing step S305;

[0128] S304: persisting the syntax structure object of the insert statement, and storing the table data into the CSV file, setting the size of the CSV file not more than 200M bytes, facilitating the query / import, and executing step S305.

[0129] Figure 4 A flow chart of persisting the syntax structure object of the insert statement in the present application is shown. Figure 4 As shown, it includes the following steps:

[0130] S304-1: judging whether the current syntax structure object exists the field list, if yes, executing step S304-2, otherwise executing step S304-3;

[0131] S304-2: Construct a field list according to the value list inserted into the syntax structure object, wherein each field name is respectively named as Field1, Field2, Field3, and each field list is respectively provided with a default field type, and the default field type is longtext in the embodiment;

[0132] S304-3: Create the database and table JSON nodes by using the method of steps S303-1 to S303-A;

[0133] S304-4: Determine whether the current insertion table and the previous insertion table are the same table, if yes, execute step S304-5, otherwise, execute step S304-7;

[0134] S304-5: Determine whether the size of the current written CSV file exceeds 200M bytes, if yes, execute step S304-6, otherwise, execute step S304-8;

[0135] S304-6: The table data storage file number of the current table is increased by 1;

[0136] S304-7: Create a new CSV file to store table data;

[0137] S304-8: Write each value of the insertion syntax structure object into the CSV file, and assign a default value to the field without value in the insertion syntax structure object, and the default value is empty in the embodiment;

[0138] S305: Determine whether the syntax structure object in the large capacity SQL file has been read, if yes, execute step S400, otherwise, execute step S306;

[0139] S306: Read the next syntax structure object in the SQL file, and execute step S301.

[0140] S400: Create a relational database according to the JSON file, and import the obtained CSV file into the relational database, including the following steps:

[0141] Create a visual database structure tree according to the JSON file, check the table node required to be imported into the MYSQL database in the visual database structure tree according to the user demand, and perform import, so as to facilitate the user to select the table node required to be imported into the relational database, and start multiple parallel threads to improve the import speed.

[0142] By using the above method provided by the application, the decompressed data can be obtained, and the technical problem that there is no import method for the large capacity SQL file of the relational database in the prior art is solved.

[0143] It is to be understood that the application is not limited to the particulars described above, and that all modifications and alternative constructions can be made by those skilled in the art without departing from the spirit and scope of the application as set forth in the claims.

Claims

1. A method for importing large SQL files into a relational database, characterized in that, Includes the following steps: S100: Lexical analysis of SQL files, including the following steps: S101: Configure lexical analysis rules and generate lexical analysis configuration file: Configure lexical type recognition rules and generate lexical analysis configuration file according to SQL lexical rules and lexical analyzer configuration rules; S102: Using a lexical analyzer, the lexical analysis configuration file is generated into an executable file; S103: Using the executable file, generate lexical tags; S200: SQL file syntax analysis: Generates a syntax analysis configuration file and uses a syntax analyzer to generate syntax structure objects corresponding to each statement in the SQL file. The statements in the SQL file include database creation statements, database selection statements, table creation statements, and insert statements. S300: Syntax Structure Persistence: Using the syntax structure object, the database structure is extracted and stored in a JSON file. The syntax structure persistence includes the following steps: S301: Read the current syntax structure object and determine whether the type of the current syntax structure object is one of the following types: A. Database creation statement / database selection statement: then execute step S302; B. Table creation statement: Then execute step S303; C. Insert statement: Then execute step S304; S302: Persist the syntax structure object of the database creation statement / database selection statement, and execute step S305; S303: Persist the syntax structure object of the table creation statement and execute step S305; S304: Persist the syntax structure object of the insert statement and store the table data in a text file. Set the size of the text file to not exceed a first threshold for querying / importing. The first threshold is 200M bytes. Then execute step S305. For a current syntax structure object of type insert statement, perform the following steps: S304-1: Determine whether the current syntax structure object has a field list. If it does, proceed to step S304-2; otherwise, proceed to step S304-3. S304-2: Construct a field list based on the list of values ​​in the insert syntax structure object, where each field is named as the first field, the second field, the third field, ..., the Nth field, and each field list is set with a default field type, where the default field type is text and N is a natural number greater than 1. S304-3: Using the methods in steps S303-1 to S303-A, create the database and table JSON nodes; S304-4: Determine whether the currently inserted table is the same as the previously inserted table. If yes, proceed to step S304-5; otherwise, proceed to step S304-7. S304-5: Determine whether the size of the currently written text file exceeds the first threshold. If yes, proceed to step S304-6; otherwise, proceed to step S304-8. S304-6: The table data storage file number of the text file currently being written to the table is incremented by 1; S304-7: Create a new text file to store table data; S304-8: Write each value of the inserted syntax structure object to a text file, and assign default values ​​to fields in the inserted syntax structure object that have no values, where the default value is empty; S305: Determine whether the syntax structure object in the large SQL file has been completely read. If yes, proceed to step S400; otherwise, proceed to step S306. S306: Read the next syntax structure object from the SQL file and execute step S301; S400: Create a relational database based on the JSON file and import the obtained text file into the relational database.

2. The method for importing large SQL files into a relational database according to claim 1, characterized in that, The lexical analyzer is FLEX, and the syntax analyzer is BISON.

3. The method for importing large SQL files into a relational database according to claim 1, characterized in that, The lexical types include SQL keywords, user tags, value tags, and symbol tags. The SQL keywords include create, insert, delete, table, and database. The user tags are user-named tags, including database name, table name, and field name. The value tags include string values, integer values, floating-point values, hexadecimal values, and binary values. The symbol tags include arithmetic operators, logical operators, punctuation marks, and end-of-file symbols.

4. The method for importing large SQL files into a relational database according to claim 1, characterized in that, For encodings and file headers that the lexical analyzer cannot recognize / process, lexical tags are removed / ignored and not generated for each type of encoding and file header.

5. The method for importing large SQL files into a relational database according to claim 1, characterized in that, Step S200 includes the following steps: S201: Configure syntax analysis rules and generate syntax analysis configuration file: Based on the SQL syntax rules and the syntax analyzer configuration rules, configure the recognition rules for each statement in the SQL file and generate the syntax analysis configuration file; S202: Use a syntax analyzer to generate an executable file from the syntax analysis configuration file; S203: Pass the generated lexical tags into the parser to produce SQL syntax structure objects.

6. The method for importing large SQL files into a relational database according to claim 1, characterized in that, For the current syntax structure object, which is a database creation statement / database selection statement, perform the following steps: S302-1: Read the database name of the current syntax structure object and determine whether the current database name is an empty string. If it is, execute step S302-2; otherwise, execute step S302-3. S302-2: Read the current SQL file name as the database name; S302-3: Search for the database JSON node in the JSON structure based on the current database name. If not found, proceed to step S302-4; otherwise, proceed to step S302-5. S302-4: Construct the database JSON node. The database configuration information of the currently constructed JSON node includes the database ID, database name, and table array node. S302-5: Record the current database name as the first database name and execute step S305.

7. The method for importing large SQL files into a relational database according to claim 1, characterized in that, For a current syntax structure object of type `table_create`, perform the following steps: S303-1: Determine if the current syntax structure object has a database name. If it does, proceed to step S303-3; otherwise, proceed to step S303-2. S303-2: Determine if the current first database name is an empty string. If it is, proceed to step S303-5; otherwise, proceed to step S303-4. S303-3: Obtain the database name where the current syntax structure object exists, and proceed to step S303-6; S303-4: Use the current first database name as the database name and proceed to step S303-6; S303-5: Read the current SQL file name as the database name; S303-6: Based on the current database name, search for the database JSON node in the JSON structure. If found, proceed to step S303-7; otherwise, proceed to step S303-8. S303-7: Construct the database JSON node. The database configuration information of the currently constructed JSON node includes the database ID, database name, and table array node. S303-8: Record the current database name as the first database name, and read the table name in the current syntax structure object; S303-9: Based on the current table name, search for the database JSON node in the JSON structure. If not found, proceed to step S303-A; otherwise, proceed to step S305. S303-A: Construct a table JSON node and add it to the table array node. The table configuration information of the JSON node includes table ID, table name, field information, and the current table data storage file number. S303-B: Execute step S305.

8. The method for importing large SQL files into a relational database according to claim 1, characterized in that, Step S400 includes the following steps: Create a visual database structure tree to allow users to select the table nodes of the relational database they want to import, and start multiple parallel threads to improve import speed.

Images