CN114281842A - Method and device for sub-table query of database - Google Patents
Method and device for sub-table query of database Download PDFInfo
- Publication number
- CN114281842A CN114281842A CN202111488662.4A CN202111488662A CN114281842A CN 114281842 A CN114281842 A CN 114281842A CN 202111488662 A CN202111488662 A CN 202111488662A CN 114281842 A CN114281842 A CN 114281842A
- Authority
- CN
- China
- Prior art keywords
- query
- syntax tree
- abstract syntax
- sql statement
- splitting
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Abstract
The method comprises the steps of obtaining an SQL statement corresponding to a query instruction input by a user, and calling Gorm query based on the SQL statement; intercepting the SQL statement based on the called Gorm query; analyzing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists; and performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and inquiring a database according to the tabulated SQL statement to obtain an inquiry result. Therefore, a plug-in mechanism is realized in the ORM framework Gorm of Go, the database sub-table action is intelligently and efficiently completed, and the performance, capacity and stability of the system are improved.
Description
Technical Field
The present application relates to the field of computers, and in particular, to a method and an apparatus for sub-table query of a database.
Background
Currently, a large amount of data is generated by a large-scale user using an application program, and a core service table in the application program can reach the data volume of a billion data level. In the prior art, the processing mode of the single table of the database can generate various problems under the data scale of the billion data level, such as the reduction of query performance, the incapability of storing due to the overlarge capacity of a single machine, the poor stability of the system and the like.
At present, mature sub-table solutions exist in other languages, but no proper solution exists in the ecological application environments of Go language and Gorm, for example, the solutions are easy to damage the service, difficult to integrate into the existing system and pollute service data.
Disclosure of Invention
An object of the present application is to provide a method and an apparatus for querying a database in a sub-table manner, so as to solve the problem that the database in the prior art cannot be queried in the Go language and the Gorm ecological application environment.
According to one aspect of the present application, there is provided a method for sub-table query of a database, the method comprising:
acquiring an SQL statement corresponding to a query instruction input by a user, and calling Gorm query based on the SQL statement;
intercepting the SQL statement based on the called Gorm query;
analyzing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists;
and performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and inquiring a database according to the tabulated SQL statement to obtain an inquiry result.
Optionally, the SQL statement includes a string query instruction and a query value array parameter, and parsing the intercepted SQL statement into an abstract syntax tree object includes:
assembling the intercepted character string query instruction and the query value array parameters to obtain a complete SQL statement;
and resolving the complete SQL statement into an abstract syntax tree object.
Optionally, before the invoking of the Gorm query based on the SQL statement, the method includes:
determining a sub-table rule by defining a sub-table key, a sub-table name generating function and a main key number generating function, and storing the sub-table rule in an appointed position of a hash table structure;
and registering the sub-table rule into a Gorm plug-in of a Gorm layer.
Optionally, intercepting the SQL statement based on the invoked Gorm query, including:
modifying instance parameters of a database which needs to execute SQL statements at a Gorm layer through a Gorm plug-in, and determining a database connection pool according to the modified instance parameters, wherein the database connection pool is used for connecting the Gorm layer and the database;
and intercepting the SQL statement into the database connection pool.
Optionally, the table partitioning related parameters include a table partitioning key, a table partitioning name, and a table partitioning key value, and the table partitioning processing is performed on the abstract syntax tree object according to the table partitioning related parameters to obtain a table-partitioned SQL statement, including:
determining a query type of the query instruction according to the abstract syntax tree object, wherein the query type comprises an insertion statement, a query statement, an update statement or a deletion statement;
and correspondingly processing the abstract syntax tree object according to the table dividing key, the table dividing name and the table dividing key value based on the query type to obtain the SQL sentence after table division.
Optionally, performing corresponding processing on the abstract syntax tree object according to the table splitting key, the table splitting name and the table splitting key value based on the query type to obtain a tabulated SQL statement, including:
when the query type is a query statement, an update statement or a delete statement, calculating a new branch table name according to the branch table key value, and updating the branch table name in the abstract syntax tree object by using the new branch table name to obtain a partitioned SQL statement;
and when the query type is an insert statement, calculating a new branch table name according to the branch table key value, updating the branch table name in the abstract syntax tree object by using the new branch table name, judging whether a primary key exists, if not, generating a primary key value with a fixed format according to a primary key calculation function, updating the primary key value in the abstract syntax tree object by using the primary key value with the fixed format, and obtaining the SQL statement after the branch table.
Optionally, the determining, according to the abstract syntax tree object, whether a table splitting rule exists or not, and if so, traversing child nodes in the abstract syntax tree object to determine table splitting related parameters includes:
judging whether a table splitting rule exists according to the table splitting name of the abstract syntax tree object, and traversing child nodes of the abstract syntax tree object if the table splitting rule exists;
checking whether the child node has a sub-table key, if so, returning the related parameters of the sub-table; if not, an error is reported.
Optionally, the performing error reporting includes:
if the child node is traversed, error reporting is carried out on the table dividing key which does not exist;
and if the traversal of the child node fails, re-traversing the child node.
According to another aspect of the present application, there is also provided an apparatus for sub-table query of a database, the apparatus comprising:
the data acquisition module is used for acquiring SQL sentences corresponding to query instructions input by a user and calling Gorm query based on the SQL sentences;
the instruction interception module is used for intercepting the SQL statement based on the called Gorm query;
the semantic parsing module is used for parsing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists;
and the data processing module is used for performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and querying the database according to the tabulated SQL statement to obtain a query result.
According to yet another aspect of the application, there is also provided a computer readable medium having computer readable instructions stored thereon, the computer readable instructions being executable by a processor to implement the method of any of the preceding claims.
According to another aspect of the present application, there is also provided an apparatus for database sub-table lookup, the apparatus comprising:
one or more processors; and
a memory storing computer readable instructions that, when executed, cause the processor to perform operations of any of the methods described above.
Compared with the prior art, the Gorm query is called based on the SQL sentences by acquiring the SQL sentences corresponding to the query instructions input by the user; intercepting the SQL statement based on the called Gorm query; analyzing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists; and performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and inquiring a database according to the tabulated SQL statement to obtain an inquiry result. A developer does not need to specially specify a sub-table when writing business logic (SQL query), the SQL sentence of ordinary single-table query is still kept, when the SQL execution reaches a middleware sub-table logic link, automatic processing can be realized, the business pollution is low, the application program is transparent, no damage is caused to the business, the SQL sentence can be integrated into the existing system only by defining and slightly adjusting (according with the query condition of the sub-table logic), the performance is better due to the extremely small consumption of the character string processing resources, all SQL queries of the whole Go language ecology can be supported, and the SQL query is unrelated to a database and a framework, so that a plug-in mechanism is realized in the ORM framework Gorm of Go, the database sub-table action is intelligently and efficiently finished, and the performance, the capacity and the stability of the system are improved.
Drawings
Other features, objects and advantages of the present application will become more apparent upon reading of the following detailed description of non-limiting embodiments thereof, made with reference to the accompanying drawings in which:
FIG. 1 illustrates a method flow diagram of a database sub-table query provided in accordance with an aspect of the present application;
FIG. 2 is a diagram illustrating a semantic structure of an abstract syntax tree object in an alternative embodiment of the present application;
FIG. 3 is a flow diagram illustrating a method for traversing a tree structure of abstract syntax tree objects in an alternative embodiment of the present application;
FIG. 4 is a flowchart illustrating a method for obtaining a tabular SQL statement in an alternative embodiment of the present application;
FIG. 5a is a flow chart illustrating a method for sub-table lookup of a database in an alternative embodiment of the present application;
FIG. 5b is a schematic diagram illustrating a method for invoking a Gorm database by the Gorm layer in an alternative embodiment of the present application;
FIG. 6 illustrates an apparatus framework architecture diagram for database sub-table lookup in accordance with another aspect of the subject application.
The same or similar reference numbers in the drawings identify the same or similar elements.
Detailed Description
The present application is described in further detail below with reference to the attached figures.
In a typical configuration of the present application, the terminal, the device serving the network, and the trusted party each include one or more processors (CPUs), input/output interfaces, network interfaces, and memory.
The memory may include forms of volatile memory in a computer readable medium, Random Access Memory (RAM) and/or non-volatile memory, such as Read Only Memory (ROM) or flash memory (flash RAM). Memory is an example of a computer-readable medium.
Computer-readable media, including both non-transitory and non-transitory, removable and non-removable media, may implement information storage by any method or technology. The information may be computer readable instructions, data structures, modules of a program, or other data. Examples of computer storage media include, but are not limited to, phase change memory (PRAM), Static Random Access Memory (SRAM), Dynamic Random Access Memory (DRAM), other types of Random Access Memory (RAM), Read Only Memory (ROM), Electrically Erasable Programmable Read Only Memory (EEPROM), flash memory or other memory technology, compact disc read only memory (CD-ROM), Digital Versatile Discs (DVD) or other optical storage, magnetic cassettes, magnetic tape, magnetic disk storage or other magnetic storage devices, or any other non-transmission medium that can be used to store information that can be accessed by a computing device. As defined herein, computer readable media does not include non-transitory computer readable media (transient media), such as modulated data signals and carrier waves.
Fig. 1 is a schematic flow chart illustrating a method for sub-table query of a database, according to an aspect of the present application, the method includes: S100-S400, wherein in S100, SQL sentences corresponding to query instructions input by a user are obtained, and Gorm query is called based on the SQL sentences; in S200, intercepting the SQL statement based on the called Gorm query; in S300, the intercepted SQL statement is analyzed into an abstract syntax tree object, whether a table splitting rule exists or not is judged according to the abstract syntax tree object, and if yes, sub-nodes in the abstract syntax tree object are traversed to determine table splitting related parameters; in S400, performing table splitting processing on the abstract syntax tree object according to the table splitting related parameters to obtain a tabulated SQL statement, and querying a database according to the tabulated SQL statement to obtain a query result. A developer does not need to specially specify a sub-table when writing business logic (SQL query), the SQL sentence of ordinary single-table query is still kept, when the SQL execution reaches a middleware sub-table logic link, automatic processing can be realized, the business pollution is low, the application program is transparent, no damage is caused to the business, the SQL sentence can be integrated into the existing system only by defining and slightly adjusting (according with the query condition of the sub-table logic), the performance is better due to the extremely small consumption of the character string processing resources, all SQL queries of the whole Go language ecology can be supported, and the SQL query is unrelated to a database and a framework, so that a plug-in mechanism is realized in the ORM framework Gorm of Go, the database sub-table action is intelligently and efficiently finished, and the performance, the capacity and the stability of the system are improved.
Specifically, in S100, an SQL statement corresponding to a query instruction input by a user is acquired, and a Gorm query is invoked based on the SQL statement. Here, the SQL statement corresponding to the query command input by the user is obtained through a tool such as a web page, and the SQL statement may include an original query command (SQL) of a string type and a value array parameter (args) of the SQL query, and invoke a Gorm query based on the SQL statement, where the Gorm query invokes a Gorm database in a Gorm layer to replace an original connection port database. Wherein, sql: string type original SQL statements, e.g., select from posts where channel id 100; args: value array (array) parameters for SQL queries, e.g., select from posts where channel id? and status? There will generally be a corresponding value corresponding? Positions, e.g., [100, 2 ].
In S200, the SQL statement is intercepted based on the invoked Gorm query. Here, all SQL statements input by the user are intercepted using the called Gorm query, and then the intercepted SQL statements may be replaced with myconpool to ensure that all SQL statements are intercepted.
In S300, the intercepted SQL statement is parsed into an abstract syntax tree object, whether a table splitting rule exists is determined according to the abstract syntax tree object, and if so, child nodes in the abstract syntax tree object are traversed to determine table splitting related parameters. Here, some application programs may be used to parse the intercepted SQL statement into an abstract syntax tree object (AST tree object) that is easy to understand, for example, SQL Parser, and then determine whether a table splitting rule for splitting a table is defined according to some relevant parameters in the abstract syntax tree object, if not, the SQL statement input by the user is not processed, the original SQL statement is directly returned, the obtained query request is an un-split table, and a database resource is applied based on the un-split table query request to obtain a query result. When a table splitting rule exists, child nodes (nodes) in the abstract syntax tree object are traversed to determine table splitting related parameters, such as a table splitting key, a table splitting name, and a table splitting query value. The AST tree object can be obtained by analyzing based on SQL Parser, and the AST object is in a tree structure.
In S400, performing table splitting processing on the abstract syntax tree object according to the table splitting related parameters to obtain a tabulated SQL statement, and querying a database according to the tabulated SQL statement to obtain a query result. Here, the abstract syntax tree object is subjected to table splitting processing according to the table splitting related parameters, for example, the table splitting names in the abstract syntax tree object are replaced according to the table splitting names to obtain the tabulated SQL statements, and the database is queried based on the tabulated SQL statements to obtain corresponding query results. A developer does not need to specially specify a sub-table when writing business logic (SQL query), the SQL sentence of ordinary single-table query is still kept, when the SQL execution reaches a middleware sub-table logic link, automatic processing can be realized, the business pollution is low, the application program is transparent, no damage is caused to the business, the SQL sentence can be integrated into the existing system only by defining and slightly adjusting (according with the query condition of the sub-table logic), the performance is better due to the extremely small consumption of the character string processing resources, all SQL queries of the whole Go language ecology can be supported, and the SQL query is unrelated to a database and a framework, so that a plug-in mechanism is realized in the ORM framework Gorm of Go, the database sub-table action is intelligently and efficiently finished, and the performance, the capacity and the stability of the system are improved.
In an optional embodiment of the present application, in S300, the SQL statement includes a string query instruction and a query value array parameter, and the intercepted string query instruction and query value array parameter are assembled to obtain a complete SQL statement; and resolving the complete SQL statement into an abstract syntax tree object. Here, the SQL statement includes a string query command (SQL) and a query value array parameter (args), for example, when a user inputs news about a desired item, there are 100 channel numbers, and when the latest 10 news data with the channel number of 100 need to be queried, we do not know where the data of the channel 100 is stored because of using table-splitting query, and therefore, the table-splitting sequence number is calculated by parsing the complete SQL statement into an abstract syntax tree object. In order to obtain all abstract syntax tree objects corresponding to the complete SQL statement, query value array parameters (args) are substituted into the character string query instruction one by one to obtain character string query instructions under all query values, so that the complete SQL statement is obtained, and the complete abstract syntax tree objects can be obtained after the complete SQL statement is analyzed.
In an optional embodiment of the present application, before the Gorm query is invoked based on the SQL statement, a table splitting rule is determined by defining a table splitting key, a table splitting name generating function, and a primary key number generating function, and the table splitting rule is stored in a designated location of a hash table structure; and registering the sub-table rule into a Gorm plug-in of a Gorm layer. Here, the data processing module 400 may define a table-dividing key, a table-dividing name generating function, and a primary key number generating function to determine a table-dividing rule. In an alternative embodiment of the present application, we need to define a table partitioning rule, and register the table partitioning rule on Gorm plug-in (Gorm plug-in) in Gorm layer in combination with the characteristics of Gorm plug-in (Gorm plug-in) to ensure that all SQL queries of Gorm can be intercepted by our table partitioning (sharing) program. We need to define a sub-table rule for each table so that the database name and table name of each table can be determined. Defining a table splitting rule and storing the table splitting rule in resolutions, wherein the resolutions are a hash table structure, a key (key) is an original table name needing table splitting, and a key value (value) is defined by the rule needing table splitting. Each resolver needs to define separately: a string type table-dividing key (ShardingKey) for defining which field value this resolver adopts to calculate a table-dividing key, and inquiring whether the table-dividing key is carried and extracting the value of the table-dividing key; a branch table name generation rule function (ShardingTable) which receives the extracted branch table key value and returns a string type branch table name for generating the branch table name; one is used to generate a primary key number function (GeneratePrimaryKey) for primary key generation of an insert statement. Wherein, resolver is a sub-table rule defined for different tables, and includes key information describing the sub-table rule, such as a sub-table key ShardingKey, a sub-table ShardingTable, a primary key generateplicaykey, and the like; resolvers is an array type for storing the branch table rules (resolvers) we define for different branch tables of the whole system.
In an optional embodiment of the present application, in S200, an instance parameter of a database that needs to execute an SQL statement is modified by a Gorm plug-in at a Gorm layer, and a database connection pool is determined according to the modified instance parameter, where the database connection pool is used to connect the Gorm layer with the database; and intercepting the SQL statement into the database connection pool. Here, instance parameters (db parameters) of a database that needs to execute the SQL statements are modified by a Gorm plug-in at the Gorm layer, a database connection pool (myconpool) is determined according to the modified instance parameters, and the myconpool is used for connecting the Gorm layer and the database, and then the SQL statements are intercepted into the myconpool.
After the branch table and the rules of the branch table are defined, the rules are registered to the Gorm plugin. The Gorm plugin has a set of standard interfaces, so that the customized plugin can be supported by the Gorm plugin. Gorm provides a Use function (function) to register a plug-in, which needs to implement the plug-in interface code as follows:
where Name is a function (function) for defining the Name of a plug-in, and returns a string type of plug-in Name, the plug-in may be named: "gorm: sharing"; initialize is a function (function) that can receive parameters of a Gorm database instance object and can implement modification actions on db parameters within the plug-in's Initialize function.
Here, there is a connection pool (ConnPool) object on the database instance (db instance) and db.statement of Gorm (db.connpool and db.statement.connpool are the same thing), which implements the method of connecting instances of the Go language standard library database/sql, i.e. the connection pool (ConnPool). In order to be completely intercepted and replaced before the SQL statement is executed to the database and then executed to the database, a user-defined MyConPool can be used for replacing (db.ConPool and db.State element.ConPool), and the interception processing of the SQL statement sent by all users is completed through all the query instructions by the MyConPool. When the MyConnPool intercepts the SQL statement, the corresponding table splitting function is called, and then the SQL statement is replaced, so that the purpose of table splitting query can be achieved. The ConnPool is a Conn implementation based on Go language database/SQL, and comprises a plurality of key functions including Execcontext, QueryContext and QueryRowContext, and SQL will pass through the 3 functions before querying a database.
In an optional embodiment of the present application, in S400, the table division related parameters include a table division key, a table division name, and a table division key value, and a query type of the query instruction is determined according to the abstract syntax tree object, where the query type includes an insert statement, a query statement, an update statement, or a delete statement; and correspondingly processing the abstract syntax tree object according to the table dividing key, the table dividing name and the table dividing key value based on the query type to obtain the SQL sentence after table division. Here, the table related parameters include a table key (ShardingKey), a table name (TableName), and a table key, and the query type of the query instruction is determined according to semantics in the abstract syntax tree, where the query type includes an insert statement (ast.insert), a query statement (ast.select), an update statement (ast.update), or a delete statement (ast.delete), and then the abstract syntax tree object is processed accordingly based on the query type, for example, the table name may be determined by calculating according to the table key using a table name generating function (ShardingTable function), and the table name included in the abstract syntax tree object is replaced according to the calculated table name, so as to generate the SQL statement after table partitioning. Wherein, TableName is the table name of the string type of the SQL statement.
In an optional embodiment of the present application, in S400, when the query type is a query statement, an update statement, or a delete statement, a new table name is calculated according to the table-dividing key value, and the table-dividing name in the abstract syntax tree object is updated by using the new table name, so as to obtain a tabulated SQL statement; and when the query type is an insert statement, calculating a new branch table name according to the branch table key value, updating the branch table name in the abstract syntax tree object by using the new branch table name, judging whether a primary key exists, if not, generating a primary key value with a fixed format according to a primary key calculation function, updating the primary key value in the abstract syntax tree object by using the primary key value with the fixed format, and obtaining the SQL statement after the branch table. When the query type is an insert statement, updating the branch table name in the abstract syntax tree object by using the new branch table name, judging whether a primary key exists, if not, generating a primary key value in a fixed format according to a primary key calculation function, and updating the primary key value in the abstract syntax tree object by using the primary key value in the fixed format. Because the self-increment of the primary key of the database is performed according to a single table, the default self-increment primary key of the database cannot be used at the moment, some self-defined primary key value generation rules need to be implemented or adopted, a primary key calculation function can be called to generate the primary key value with a fixed format, and a primary key value of an int64 type is generated and returned by defining a GeneratePrimaryKey function. In an alternative embodiment of the present application, the following may be applied: snowflash, which can generate numbers in int64 format, the front part is a time stamp, which is larger and larger along with the time and can be used for sorting; a UUID-UUID generator that can generate globally unique IDs, but cannot be used for sorting; with database auto-increment feature implementation, for example, PostgreSQL can directly call the function of sequence management Functions to implement auto-increment number generation.
In an optional embodiment of the present application, in S300, it is determined whether a table splitting rule exists according to a table splitting name of the abstract syntax tree object, and if so, sub-nodes of the abstract syntax tree object are traversed; checking whether the child node has a sub-table key, if so, returning the related parameters of the sub-table; if not, an error is reported. Here, whether the table division rule is defined is judged according to the table division name (TableName) of the abstract syntax tree object, and if so, the child Node (Node) of the abstract syntax tree object is traversed. Optionally, before judging whether the table splitting rule exists according to the table splitting name of the abstract syntax tree object, determining whether a syntax error exists according to child nodes in the abstract syntax tree object, and if so, reporting the syntax error.
Fig. 2 is a schematic diagram illustrating a semantic structure of an abstract syntax tree object in an alternative embodiment of the present application, and by traversing a tree structure of the abstract syntax tree object, we can find out nodes such as SELECT, FROM, WHERE, ORDER, LIMIT, and the like, and details of child nodes. In the sub-table application scenario of this embodiment, a news table (posts) is preset, where the news table includes a channel field (channel _ id), and the current posts table has more data and needs to be split into multiple word tables, and taking the posts table as an example, we need to define the posts table, split according to the channel _ id, and the splitting mode is 16 tables with evenly distributed channel _ id. Then a resolver needs to be defined: TableName is posts, ShardingKey is channel _ id, ShardingTable function is implemented, incoming branch table key values are modulo 16 to serve as a branch table index (tableIndex), and then the "posts _" + tableIndex is returned to obtain the branch table names, that is, 16 table names of posts _0, posts _1.. posts _14 and posts _15 can be obtained by calculating 16 branch tables. The generateprimaykey function is implemented, returning a primary key number (int64 type) generated using snowflash. Wherein, tablelndex is an integer type starting from 0, and represents a first sublist.
In the above embodiment, the table needs to be split into 16 word tables, then the table splitting rule is set to split the table by taking the modulo 16 value based on the value of channel _ id, and the name of the sub-table is prefixed by posts _ followed by the modulo value, and the range of the table splitting would be: posts _0, posts _2,. and posts _ 15. That is, the code can be obtained as:
the name of the sublist ═ posts _ "+ (channel _ id% 16)
Here, the SQL parsing and replacing process is exemplified by querying data, and the flow of inserting data, updating data, or deleting data is similar. Assuming that the current data is already in 16 word tables, the data is uniformly distributed into 16 sub-tables of posts _0, posts _2,. and posts _15 based on channel _ id modulo, then the following string (string) type SQL statement exists:
select*from posts where channel_id＝100and status＝1limit 10order by id desc
the SQL statement means that the latest 10 items are obtained from the data of the channel of the news table which is 100. Based on the 16 table sub-table rule, the data of this channel 100 should be distributed in the posts _4 table as follows:
the name of the sublist "+ (100% 16) ═ posts _4"
Next, the portion of "from posts" in the SQL statement is replaced with "from posts _4" to ensure that the query points to the correct sub-table to fetch the data.
In an optional embodiment of the present application, in S300, if the child node completes traversal, an error is reported to the child node that has no table-splitting key; and if the traversal of the child node fails, re-traversing the child node. Here, when the child node completes traversal, an error can be reported for the table splitting key which does not exist, and the table splitting key which does not exist cannot be used for subsequent table splitting processing; and if the child node is not successfully traversed, the child node is traversed again.
Fig. 3 is a schematic flow chart illustrating a method for traversing a tree structure of an abstract syntax tree object in an alternative embodiment of the present application, where to find a sublist name TableName, a FROM node in a subnode can be found by traversing a subnode of the abstract syntax tree object, and after the FROM node is found, a sublist name posts can be obtained FROM the TableName node. And then determining whether a table splitting rule is defined or not based on the table splitting name, if not, ignoring the table, if so, further traversing child nodes of the WHERE, checking whether a table splitting key is included, and if so, successfully acquiring the table splitting name, the table splitting key and a table splitting key value. When the necessary nodes are absent in the traversal of the abstract syntax tree object, the syntax error is reported. When the table-dividing key is not included, judging whether the traversal of the abstract syntax tree object is completed or not, if so, reporting an error to the table-dividing key which is not included; if not, the node is traversed again.
Fig. 4 is a schematic flow chart of a method for obtaining a tabular SQL statement in an optional embodiment of the present application, where the method may call a router function to process a tabular logic to obtain the tabular SQL statement, where the router is a tabular rule parsing function (function) for receiving an SQL statement and replacing the SQL statement with a tabular rule, and the SQL statement is called each time it is processed. Firstly, SQL and args input by a user are assembled to obtain a complete SQL statement, then the complete SQL statement is parsed into an abstract syntax tree object (AST object), and then a query type is determined according to the AST object. When the query type is a query statement, an update statement or a delete statement, whether a table splitting rule is defined is judged, if so, whether a table splitting key exists is further judged, if so, the table splitting key and the table splitting key value can be determined, then a table splitting name is obtained through calculation according to the table splitting key value based on a table splitting function (ShardingTable function), then the table splitting name in the abstract syntax tree object is replaced by the calculated table splitting name, and then a new SQL statement is generated from the abstract syntax tree object, namely the SQL statement of the character string type (string type) after table splitting.
According to the embodiment, when the query type is an insertion statement, whether a table splitting rule is defined or not is judged, if yes, whether a table splitting key exists or not is further judged, if yes, the table splitting key and the table splitting key value can be determined, then a table splitting name is obtained through calculation according to the table splitting key value based on a table splitting function (ShardingTable function), the table splitting name in the abstract syntax tree object is replaced by the calculated table splitting name, at the moment, whether a main key exists or not is judged, and if yes, a new SQL statement, namely, the SQL statement of the character string type (string type) after table splitting is generated from the abstract syntax tree object. When a primary key does not exist, a primary key value of an int64 type is generated based on a primary key algorithm GeneratePrimaryKey function, and the generated primary key value of the int64 type is used for replacing the primary key value of the abstract syntax tree, so that a new SQL statement, namely the SQL statement of the character string type (string type) after the table division, is generated from the abstract syntax tree object. When the table splitting rule is not defined, ignoring the table and returning the original SQL statement data; and when the table-dividing key does not exist, reporting the error of the table-dividing key. The principle of the application is to deeply understand the characteristics of the Go language standard library, utilize a mechanism for intercepting the SQL statement, and analyze the syntax of the SQL statement in combination with the SQLParser, understand the table Name (TableName) of the SQL statement, the query condition (Where statement) or the field nouns (Column Name) in the Insert, Update, Delete statements (Insert, Update, Delete, and other possible Update statements). When matching with the table splitting rule, the SQL statement is replaced by the table splitting logic and then the execution is continued to the database. Because we have replaced TableName in SQL statement as a new branch table name, then SQL inquiry is executed to different branch tables naturally, thus achieving the inquiry or writing purpose of the branch table, and because the whole set of logic is in database/SQL or Gorm Plugin, application program developer does not need to specially appoint to inquire the branch table when writing SQL inquiry, the realized internal will be processed automatically, the program logic pollution is small, and it is easy to use.
In addition, because SQL replacement is implemented at the Conn interface of database/SQL, the same scheme can also perform many derivative extension functions, such as writing data double writes (writing data to a branch table and an aggregation table at the same time when writing data, where the aggregation table contains all data to facilitate some scenarios requiring unified query), and so on. The method mainly demonstrates how to realize the branch table logic based on Gorm and how to integrate the scheme of realizing the branch table based on database/sql Conn by writing Gorm Plugin. In fact, due to the characteristics of the Go language standard library, the implementation mode can be widely applied to any ORM framework of all Go languages (including direct use of database/sql standard library query).
Fig. 5a is a schematic flow chart illustrating a method for query by table of a database in an alternative embodiment of the present application, which obtains an SQL statement sent by a user, invokes a Gorm query, and then invokes a Gorm database for a Gorm layer in step a 1. Fig. 5b is a schematic flow chart illustrating a method for invoking the Gorm database by the Gorm layer in an alternative embodiment of the present application, which is a sub-step of step a1, and after entering the Gorm layer, invokes the Gorm database, and then executes the code: querycontext (sql, args), thereby implementing a custom database instance connection pool (myconpool). Specifically, in the database/sql standard library of Go language, Conn is a base Interface (Interface) for database query. For example: exec executes the execcoxt function of SQL; query SQL-QueryContext function. Basically, database queries developed by almost all Go language projects at present implement database calls through the Interface Conn, and the executed SQL statements all pass through 3 main functions, namely ExecContext, QueryContext and QueryRowContext.
The following are definitions of several functions of the Go language:
the ExecContext function: executing SQL for executing actions such as Insert, Update and Delete that need to be updated, but do not need to return a record;
QueryContext function: SQL query, executing SQL actions such as Select which need to acquire data;
QueryRowContext function: SQL queries a single line of data.
In connection with the above embodiment, to intercept the SQL statement, one myconpool may be implemented, and 3 functions (functions) of ExecContext, QueryContext, and queryroccoxt may be implemented in the myconpool. Finally, the ConnPool layer of Gorm or other Go language ORM frameworks is replaced by MyConnPool, so that the interception of all SQL queries can be realized.
Then, in the myconpool layer, a router function processing list splitting logic can be called, SQL and args are assembled to obtain complete SQL, SQL Parser can be used to analyze SQL statements to obtain an abstract syntax tree object, whether list splitting rules are defined is determined according to a list name TableName in the abstract syntax tree object, if not, the SQL statements are not processed and original SQL statement data is returned, query of non-list splitting is performed, and other commands such as table splitting rules, channels, drafts and the like without lists are executed in a database layer to obtain query results. The router is a table-splitting rule parsing function (function) and is used for receiving an SQL statement and replacing the SQL statement according to a table-splitting rule, and the SQL statement is called each time the SQL is processed.
And if the table splitting rule is defined, traversing all nodes of the abstract syntax tree object, searching WhereColumn fields, checking whether a table splitting key defined by the table splitting rule exists or not, and performing matching processing. When the table-dividing key (ShardingKey) is not found, an error is reported, and the query is required to be provided with the table-dividing key. When the table key is hit (i.e. match _ sharing _ key is obtained), the table name is obtained, for example, the table number "posts + (100% 16) is calculated by using the value 100 of whitevalue, the table name is posts _4", a new table name is obtained, and the new table name is used to replace the table name (TableName) in the abstract syntax tree object to obtain a new SQL statement after table sharing. Next, in the database layer, a table splitting query is performed based on the tabulated SQL statement, for example, data is uniformly distributed in 16 sub-tables of posts _0, posts _2, and posts _15 in a modulo manner based on channel _ id according to 16 word tables, and then the following string (string) type SQL statement exists:
select*from posts where channel_id＝100and status＝1limit 10order by id desc
and performing table-splitting query on the database based on the SQL statement to obtain data in a fifth table posts _4, and determining a query result from the data in the posts _ 4.
Embodiments of the present application further provide a computer-readable medium, on which computer-readable instructions are stored, where the computer-readable instructions are executable by a processor to implement the foregoing method for querying a database table.
In correspondence with the method described above, the present application also provides a terminal, which includes a module or a unit capable of executing the method steps described in fig. 1 or fig. 2 or fig. 3 or fig. 4 or fig. 5a or fig. 5b or various embodiments, and the module or the unit can be implemented by hardware, software or a combination of hardware and software, and the present application is not limited thereto. For example, in an embodiment of the present application, there is also provided an apparatus for sub-table query of a database, where the apparatus includes:
one or more processors; and
a memory having computer readable instructions stored thereon that, when executed, cause the processor to perform the operations of one of the aforementioned database sub-table lookup methods.
For example, the computer readable instructions, when executed, cause the one or more processors to:
acquiring an SQL statement corresponding to a query instruction input by a user, and calling Gorm query based on the SQL statement; intercepting the SQL statement based on the called Gorm query; analyzing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists; and performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and inquiring a database according to the tabulated SQL statement to obtain an inquiry result.
FIG. 6 is a block diagram of an apparatus framework for database sub-table lookup, according to another aspect of the present application, wherein the apparatus comprises: the data acquisition module 100 is configured to acquire an SQL statement corresponding to a query instruction input by a user, and invoke Gorm query based on the SQL statement; an instruction interception module 200, configured to intercept the SQL statement based on the invoked Gorm query; the semantic parsing module 300 is configured to parse the intercepted SQL statement into an abstract syntax tree object, determine whether a table splitting rule exists according to the abstract syntax tree object, and if so, traverse child nodes in the abstract syntax tree object to determine table splitting related parameters; and the data processing module 400 is configured to perform table splitting processing on the abstract syntax tree object according to the table splitting related parameters to obtain a tabulated SQL statement, and query the database according to the tabulated SQL statement to obtain a query result. A developer does not need to specially specify a sub-table when writing business logic (SQL query), the SQL sentence of ordinary single-table query is still kept, when the SQL execution reaches a middleware sub-table logic link, automatic processing can be realized, the business pollution is low, the application program is transparent, no damage is caused to the business, the SQL sentence can be integrated into the existing system only by defining and slightly adjusting (according with the query condition of the sub-table logic), the performance is better due to the extremely small consumption of the character string processing resources, all SQL queries of the whole Go language ecology can be supported, and the SQL query is unrelated to a database and a framework, so that a plug-in mechanism is realized in the ORM framework Gorm of Go, the database sub-table action is intelligently and efficiently finished, and the performance, the capacity and the stability of the system are improved.
It should be noted that the content executed by the data obtaining module 100, the instruction intercepting module 200, the semantic parsing module 300, and the data processing module 400 is respectively the same as or corresponding to the content in the above steps S100, S200, S300, and S400, and for the sake of brevity, no further description is repeated here.
It will be apparent to those skilled in the art that various changes and modifications may be made in the present application without departing from the spirit and scope of the application. Thus, if such modifications and variations of the present application fall within the scope of the claims of the present application and their equivalents, the present application is intended to include such modifications and variations as well.
It should be noted that the present application may be implemented in software and/or a combination of software and hardware, for example, implemented using Application Specific Integrated Circuits (ASICs), general purpose computers or any other similar hardware devices. In one embodiment, the software programs of the present application may be executed by a processor to implement the steps or functions described above. Likewise, the software programs (including associated data structures) of the present application may be stored in a computer readable recording medium, such as RAM memory, magnetic or optical drive or diskette and the like. Additionally, some of the steps or functions of the present application may be implemented in hardware, for example, as circuitry that cooperates with the processor to perform various steps or functions.
In addition, some of the present application may be implemented as a computer program product, such as computer program instructions, which when executed by a computer, may invoke or provide methods and/or techniques in accordance with the present application through the operation of the computer. Program instructions which invoke the methods of the present application may be stored on a fixed or removable recording medium and/or transmitted via a data stream on a broadcast or other signal-bearing medium and/or stored within a working memory of a computer device operating in accordance with the program instructions. An embodiment according to the present application comprises an apparatus comprising a memory for storing computer program instructions and a processor for executing the program instructions, wherein the computer program instructions, when executed by the processor, trigger the apparatus to perform a method and/or a solution according to the aforementioned embodiments of the present application.
It will be evident to those skilled in the art that the present application is not limited to the details of the foregoing illustrative embodiments, and that the present application may be embodied in other specific forms without departing from the spirit or essential attributes thereof. The present embodiments are therefore to be considered in all respects as illustrative and not restrictive, the scope of the application being indicated by the appended claims rather than by the foregoing description, and all changes which come within the meaning and range of equivalency of the claims are therefore intended to be embraced therein. Any reference sign in a claim should not be construed as limiting the claim concerned. Furthermore, it is obvious that the word "comprising" does not exclude other elements or steps, and the singular does not exclude the plural. A plurality of units or means recited in the apparatus claims may also be implemented by one unit or means in software or hardware. The terms first, second, etc. are used to denote names, but not any particular order.
Claims (11)
1. A method of sub-table lookup of a database, wherein the method comprises:
acquiring an SQL statement corresponding to a query instruction input by a user, and calling Gorm query based on the SQL statement;
intercepting the SQL statement based on the called Gorm query;
analyzing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists;
and performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and inquiring a database according to the tabulated SQL statement to obtain an inquiry result.
2. The method of claim 1, wherein the SQL statement comprises string query instructions and query value array parameters, and parsing the intercepted SQL statement into abstract syntax tree objects comprises:
assembling the intercepted character string query instruction and the query value array parameters to obtain a complete SQL statement;
and resolving the complete SQL statement into an abstract syntax tree object.
3. The method of claim 1, wherein said invoking Gorm query based on the SQL statement is preceded by:
determining a sub-table rule by defining a sub-table key, a sub-table name generating function and a main key number generating function, and storing the sub-table rule in an appointed position of a hash table structure;
and registering the sub-table rule into a Gorm plug-in of a Gorm layer.
4. The method of claim 3, wherein intercepting the SQL statement based on a called Gorm query comprises:
modifying instance parameters of a database which needs to execute SQL statements at a Gorm layer through a Gorm plug-in, and determining a database connection pool according to the modified instance parameters, wherein the database connection pool is used for connecting the Gorm layer and the database;
and intercepting the SQL statement into the database connection pool.
5. The method of claim 1, wherein the table-splitting related parameters include a table-splitting key, a table-splitting name and a table-splitting key, and the table-splitting processing is performed on the abstract syntax tree object according to the table-splitting related parameters to obtain a tabulated SQL statement, including:
determining a query type of the query instruction according to the abstract syntax tree object, wherein the query type comprises an insertion statement, a query statement, an update statement or a deletion statement;
and correspondingly processing the abstract syntax tree object according to the table dividing key, the table dividing name and the table dividing key value based on the query type to obtain the SQL sentence after table division.
6. The method of claim 5, wherein correspondingly processing the abstract syntax tree object according to the table splitting key, the table splitting name and the table splitting key value based on the query type to obtain the tabulated SQL statement comprises:
when the query type is a query statement, an update statement or a delete statement, calculating a new branch table name according to the branch table key value, and updating the branch table name in the abstract syntax tree object by using the new branch table name to obtain a partitioned SQL statement;
and when the query type is an insert statement, calculating a new branch table name according to the branch table key value, updating the branch table name in the abstract syntax tree object by using the new branch table name, judging whether a primary key exists, if not, generating a primary key value with a fixed format according to a primary key calculation function, updating the primary key value in the abstract syntax tree object by using the primary key value with the fixed format, and obtaining the SQL statement after the branch table.
7. The method of claim 1, wherein the determining whether a table splitting rule exists according to the abstract syntax tree object, and if so, traversing child nodes in the abstract syntax tree object to determine table splitting related parameters comprises:
judging whether a table splitting rule exists according to the table splitting name of the abstract syntax tree object, and traversing child nodes of the abstract syntax tree object if the table splitting rule exists;
checking whether the child node has a sub-table key, if so, returning the related parameters of the sub-table; if not, an error is reported.
8. The method of claim 7, wherein the error reporting comprises:
if the child node is traversed, error reporting is carried out on the table dividing key which does not exist;
and if the traversal of the child node fails, re-traversing the child node.
9. An apparatus for database sub-table lookup, wherein the apparatus comprises:
the data acquisition module is used for acquiring SQL sentences corresponding to query instructions input by a user and calling Gorm query based on the SQL sentences;
the instruction interception module is used for intercepting the SQL statement based on the called Gorm query;
the semantic parsing module is used for parsing the intercepted SQL statement into an abstract syntax tree object, judging whether a table splitting rule exists according to the abstract syntax tree object, and traversing child nodes in the abstract syntax tree object to determine table splitting related parameters if the table splitting rule exists;
and the data processing module is used for performing table division processing on the abstract syntax tree object according to the table division related parameters to obtain a tabulated SQL statement, and querying the database according to the tabulated SQL statement to obtain a query result.
10. A computer readable medium having computer readable instructions stored thereon which are executable by a processor to implement the method of any one of claims 1 to 8.
11. An apparatus for database sub-table lookup, wherein the apparatus comprises:
one or more processors; and
a memory storing computer readable instructions that, when executed, cause the processor to perform the operations of the method of any of claims 1 to 8.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111488662.4A CN114281842A (en) | 2021-12-07 | 2021-12-07 | Method and device for sub-table query of database |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202111488662.4A CN114281842A (en) | 2021-12-07 | 2021-12-07 | Method and device for sub-table query of database |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN114281842A true CN114281842A (en) | 2022-04-05 |
Family
ID=80871219
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202111488662.4A Pending CN114281842A (en) | 2021-12-07 | 2021-12-07 | Method and device for sub-table query of database |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN114281842A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115630085A (en) * | 2022-12-02 | 2023-01-20 | 天津南大通用数据技术股份有限公司 | Database variable parameter scope control method, device and equipment |
-
2021
- 2021-12-07 CN CN202111488662.4A patent/CN114281842A/en active Pending
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115630085A (en) * | 2022-12-02 | 2023-01-20 | 天津南大通用数据技术股份有限公司 | Database variable parameter scope control method, device and equipment |
| CN115630085B (en) * | 2022-12-02 | 2023-03-28 | 天津南大通用数据技术股份有限公司 | Database variable parameter scope control method, device and equipment |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US8447744B2 (en) | Extensibility platform using data cartridges | |
| US8959106B2 (en) | Class loading using java data cartridges | |
| US9430494B2 (en) | Spatial data cartridge for event processing systems | |
| US7743066B2 (en) | Anonymous types for statically typed queries | |
| US20090070300A1 (en) | Method for Processing Data Queries | |
| US11893011B1 (en) | Data query method and system, heterogeneous acceleration platform, and storage medium | |
| WO2020238597A1 (en) | Hadoop-based data updating method, device, system and medium | |
| US20180137167A1 (en) | Dynamic generation of database queries in query builders | |
| CN112579610A (en) | Multi-data source structure analysis method, system, terminal device and storage medium | |
| CN112860730A (en) | SQL statement processing method and device, electronic equipment and readable storage medium | |
| CN114356971A (en) | Data processing method, device and system | |
| CN111259067A (en) | Method, device and equipment for realizing DAO interface based on Spring | |
| CN109885585A (en) | Support the distributed data base system and method for storing process, trigger and view | |
| CN114281842A (en) | Method and device for sub-table query of database | |
| WO2024016594A1 (en) | Pseudo column implementation method and apparatus, electronic device, and storage medium | |
| WO2021259367A1 (en) | Sql unification method, system, and device, and medium | |
| CN114168122A (en) | Data script generation method and device | |
| CN115952203B (en) | Data query method, device, system and storage medium | |
| CN116578585B (en) | Data query method, device, electronic equipment and storage medium | |
| CN110569243B (en) | Data query method, data query plug-in and data query server | |
| CN117349332B (en) | Method and device for generating application programming interface API and electronic equipment | |
| CN114925142A (en) | Multi-type database compatibility method, device, equipment and medium of ORM framework | |
| CN114385664A (en) | Splitting method, system, device and storage medium for single project micro-service | |
| WO2023164294A1 (en) | Query splitter for an inverted index datastore | |
| CN116069862A (en) | Method, device, equipment and storage medium for synchronizing heterogeneous database data |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| PB01 | Publication | ||
| PB01 | Publication | ||
| SE01 | Entry into force of request for substantive examination | ||
| SE01 | Entry into force of request for substantive examination |