CN113032439A - Method, device and system for querying database with database-to-table structure - Google Patents
Method, device and system for querying database with database-to-table structure Download PDFInfo
- Publication number
- CN113032439A CN113032439A CN202110551017.6A CN202110551017A CN113032439A CN 113032439 A CN113032439 A CN 113032439A CN 202110551017 A CN202110551017 A CN 202110551017A CN 113032439 A CN113032439 A CN 113032439A
- Authority
- CN
- China
- Prior art keywords
- sub
- database
- field
- range
- data
- 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
Classifications
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/24—Querying
- G06F16/245—Query processing
- G06F16/2455—Query execution
-
- G—PHYSICS
- G06—COMPUTING; CALCULATING OR COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/22—Indexing; Data structures therefor; Storage structures
- G06F16/2282—Tablespace storage structures; Management thereof
Landscapes
- Engineering & Computer Science (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Databases & Information Systems (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Computational Linguistics (AREA)
- Software Systems (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention discloses a method for inquiring a database with a database and table structure, which comprises the following steps: establishing a database sub-table rule of associating the time stamp with a database sub-table structure database; writing the service data including the time stamp into a database with a sub-database and sub-table structure to form a data record; and according to the time condition query request, obtaining a data record of a time stamp corresponding to the time condition query request in the database sub-table structure data. The invention can avoid inquiring all the database with the sub-database and sub-table structure when inquiring the record in the database with the sub-database and sub-table structure. Based on the time stamp, only the database with the sub-database sub-table structure matched with the record main key ID retrieved from the sub-database sub-table is queried to obtain detailed service data recorded in the database with the sub-database sub-table structure, so that the query efficiency of the database with the sub-database sub-table structure is improved, and a user can more quickly delete and search required data.
Description
Technical Field
The invention belongs to the technical field of databases, and particularly relates to a method, a device and a system for querying a database with a database-by-table structure.
Background
With the development of information society and the wide popularization of Internet application, more and more information is digitalized, and especially with the development of Internet (Internet) technology, data is explosively increased. As a driving factor of a network, information data is becoming a core of the network, and secure and efficient storage and management of data are increasingly gaining attention as a basis for network development, but storage and access of mass data are becoming a bottleneck problem of system design due to high-speed increase of data volume.
In the prior art, the technical scheme of database division and table division can well solve the problems, and the original database is transversely divided, namely, the table division is carried out; and meanwhile, the database may be longitudinally divided according to the service, namely, the database is divided. However, in the prior art, data can be queried only through the primary key of the table, and cannot be queried through other fields, and for a database with a database-in-table structure, all databases and tables need to be scanned to realize fuzzy query, so that the working efficiency of querying the database is low.
Disclosure of Invention
The present invention is to solve the above technical problems, and provide a method and an apparatus for managing mapping data.
In order to solve the problems, the invention is realized according to the following technical scheme:
in a first aspect, the present invention provides a method for querying a database with a database and table structure, including the following steps:
establishing a database sub-table rule of associating the time stamp with a database sub-table structure database;
writing the service data including the time stamp into a database with a sub-database and sub-table structure to form a data record;
according to the query request, obtaining a data record corresponding to the query request from the database sub-table structure data, specifically comprising:
when the query request is ID range condition query, acquiring a data record corresponding to the ID range condition in the database sub-table structure data based on the ID range condition;
when the query request contains a time range, converting the time range into an ID field range, wherein the ID field range is used for positioning the sub-warehouse sub-tables and further obtaining data records corresponding to the query request in the sub-warehouse sub-table structure data;
when the query request comprises an ID range condition and a time range for query, determining the sub-warehouse sub-tables based on the ID range condition and the ID field range converted by the time range in a cooperative manner, and further obtaining corresponding data records in the sub-warehouse sub-table structure data.
With reference to the first aspect, the present invention provides a first possible implementation manner of the first aspect, wherein the creating a database sub-table rule in which a timestamp is associated with a database of a database sub-table structure specifically includes:
establishing a mapping relation between the timestamp and the ID field, and further establishing a database-dividing and table-dividing rule related to the timestamp and the ID field;
the ID field is generated based on the time stamp, and the time stamp for generating the ID field and the time stamp for writing the service data are the same time stamp.
With reference to the first aspect, the present invention provides a second possible implementation manner of the first aspect, wherein the creating a database sub-table rule in which a timestamp is associated with a database of a database sub-table structure specifically includes:
generating a corresponding ID field based on the time field value of the timestamp so as to convert the time range of the timestamp into the ID field range of the ID field and further establish the mapping relation between the timestamp and the ID field;
and establishing a mapping relation between the time stamp and the database and table dividing rule based on the ID field.
With reference to the first aspect, the present invention provides a 3 rd possible implementation manner of the first aspect, wherein writing the service data including the timestamp into the sub-repository sub-table structure database to form a data record specifically includes:
acquiring a timestamp of current service data;
generating an ID field based on a timestamp of current service data, wherein the ID field is used in a database partitioning table and is used for establishing a mapping relation between the database partitioning table and the service data;
searching a corresponding database sub-table and a database sub-table structure according to the ID field;
writing the time stamp part of the ID field, the time field value of the associated sub-database sub-table and the service data containing the time stamp into a corresponding sub-database sub-table structure database to form a data record; and the time stamp part of the ID field and the time field value of the associated sub-library sub-table are generated by the same time stamp as the business data.
With reference to the first aspect, the present invention provides a 4 th possible implementation manner of the first aspect, where generating the ID field based on the timestamp of the current service data specifically includes:
the ID field is composed of a tag, a timestamp portion, a machine address portion, and a random number portion;
and acquiring the time stamp of the current service data, and writing the time field value of the time stamp into the time stamp part of the ID field.
With reference to the first aspect, the present invention provides a 5 th possible implementation manner of the first aspect, where, when the query request includes a time range, the time range is converted into an ID field range, and the ID field range is used to locate the sub-repository sub-table, so as to obtain a data record corresponding to the query request in the sub-repository sub-table structure data, specifically including:
analyzing the query request to obtain an operation condition, wherein the operation condition comprises a time range and an operation field for querying;
converting the time ranges in the operating conditions into corresponding ID field ranges;
adding an ID field range under an operation condition, wherein the ID field range is used for positioning sub-warehouse sub-tables;
and acquiring a data record of a time stamp corresponding to the query request in the database-based and table-based structure data according to the ID field range and the database-based and table-based rules.
With reference to the first aspect, the present invention provides a 6 th possible implementation manner of the first aspect, where obtaining, according to an ID field range and a sub-repository sub-table rule, a data record of a timestamp corresponding to the query request from sub-repository sub-table structure data specifically includes:
determining a database sub-table structure database and a database sub-table corresponding to an ID field range according to a database sub-table rule, wherein the ID field range corresponds to an ID field generated based on a timestamp in the database sub-table;
and acquiring a sub-database and sub-table, and inquiring corresponding data records based on the operation conditions.
In a second aspect, the present invention further provides a query device for a database with a sub-database and sub-table structure, including:
the rule establishing module is used for establishing a database sub-table rule of which the time stamp is associated with the database sub-table structure database;
the data writing module is used for writing the time stamp part of the ID field, the time field value of the associated sub-database sub-table and the service data comprising the time stamp into the sub-database sub-table structure database together to form a data record; the time stamp part of the ID field and the time field value of the associated sub-library sub-table are both generated by the same time stamp as the service data;
the query module is used for acquiring data records corresponding to the query request from the database sub-table structure data according to the query request, and specifically executes the following operations:
when the query request is ID range condition query, acquiring a data record corresponding to the ID range condition in the database sub-table structure data based on the ID range condition;
when the query request contains a time range, converting the time range into an ID field range, wherein the ID field range is used for positioning the sub-warehouse sub-tables and further obtaining data records corresponding to the query request in the sub-warehouse sub-table structure data;
when the query request comprises an ID range condition and a time range for query, determining the sub-warehouse sub-tables based on the ID range condition and the ID field range converted by the time range in a cooperative manner, and further obtaining corresponding data records in the sub-warehouse sub-table structure data.
In a third aspect, the present invention further provides a query system for database with sub-database and sub-table structure, comprising a user terminal, an application server, a database with sub-database and sub-table structure, a query terminal and the query device according to claim 8;
the user terminal communicates with the application server to generate service data, and the application server stores the service data into the database with the database-dividing and table-dividing structure;
the query device establishes a database sub-table rule of which the time stamp is associated with a database sub-table structure database;
the inquiry device writes a time stamp part of an ID field, a time field value of an associated sub-database table and service data comprising a time stamp into a sub-database table structure database together to form a data record, wherein the time stamp part of the ID field and the time field value of the associated sub-database table are generated by the time stamp which is the same as the service data;
and the inquiry device acquires a data record of a time stamp corresponding to the inquiry request from the database sub-table structure data according to the inquiry request sent by the inquiry terminal.
Compared with the prior art, the invention has the beneficial effects that:
according to the technical scheme provided by the embodiment of the application, the invention can avoid inquiring all the database sub-table structure databases when records are inquired in the database sub-table structure databases. Based on the time stamp, the database with the sub-database sub-table structure matched with the record main key ID retrieved from the sub-database sub-table is queried to obtain detailed service data recorded in the database with the sub-database sub-table structure, so that the query efficiency of the database with the sub-database sub-table structure is improved, and a user can more quickly delete and modify the needed data.
On the other hand, data deleting, modifying and checking are carried out in the time field value of the timestamp or the ID range associated with the timestamp, the reference data volume is effectively reduced, the size of the reference index is reduced, the deleting, modifying and checking speed is improved, and therefore the overall operation speed of the system is improved. Moreover, managers can query by adopting ID range conditions and/or time ranges, and the method is strong in universality and flexible.
Drawings
Embodiments of the invention are described in further detail below with reference to the attached drawing figures, wherein:
FIG. 1 is a flow chart of a method for querying a database with a sub-database and sub-table structure according to the present invention;
FIG. 2 is a schematic diagram of an apparatus for querying a database with a sub-database and sub-table structure according to the present invention;
FIG. 3 is a block diagram of the query system of the database with a sub-database and sub-table structure according to the present invention.
Detailed Description
The preferred embodiments of the present invention will be described in conjunction with the accompanying drawings, and it will be understood that they are described herein for the purpose of illustration and explanation and not limitation.
In order to make the purpose, technical solutions and advantages of the embodiments of the present application clearer, the technical solutions in the embodiments of the present application will be clearly and completely described below with reference to the drawings in the embodiments of the present application, and it should be understood that the drawings in the present application are for illustrative and descriptive purposes only and are not used to limit the scope of protection of the present application. Additionally, it should be understood that the schematic drawings are not necessarily drawn to scale. The flowcharts used in this application illustrate operations implemented according to some embodiments of the present application. It should be understood that the operations of the flow diagrams may be performed out of order, and steps without logical context may be performed in reverse order or simultaneously. One skilled in the art, under the guidance of this application, may add one or more other operations to, or remove one or more operations from, the flowchart.
The described embodiments are only some embodiments of the present application and not all embodiments. The components of the embodiments of the present application, generally described and illustrated in the figures herein, can be arranged and designed in a wide variety of different configurations. Thus, the following detailed description of the embodiments of the present application, presented in the accompanying drawings, is not intended to limit the scope of the claimed application, but is merely representative of selected embodiments of the application. All other embodiments, which can be derived by a person skilled in the art from the embodiments of the present application without making any creative effort, shall fall within the protection scope of the present application.
In the existing query mode of the database with the database-in-table structure, data can be queried only through the primary key of the table and cannot be queried through other fields. On the other hand, the sub-table can be located only by one field or a plurality of fields in the prior art.
Therefore, the invention provides a query method of a database with a database sub-table structure based on a timestamp, which is characterized in that the relation between the database with the database sub-table structure and a main key in a database record is established in the embodiment, so that the database to which the record belongs can be positioned through the main key containing a time part, and the main key corresponding to the timestamp is searched in the database sub-table according to a query request, thereby accurately positioning the database with a specific database sub-table structure, avoiding querying in all database with the database sub-table structure and improving the efficiency of database query work.
Meanwhile, the invention realizes various inquiry modes of ID range condition, time range, ID range condition and time range combination, and data deletion, modification and check are carried out in the time field of the time stamp or the ID range associated with the time stamp, so that the reference data volume is effectively reduced, the reference index size is reduced, the deletion, modification and check speed is improved, and the integral operation speed of the system is improved. Moreover, ID range conditions and/or timestamp conditions can be adopted for query, and the method is strong in universality and flexible.
As shown in fig. 1, which is a flowchart of a query method for a database with a sub-base and sub-table structure based on timestamps according to an embodiment of the present application, the query method for a database with a sub-base and sub-table structure based on timestamps includes:
s100: and establishing a database and table dividing rule of which the time stamp is associated with the database of the database and table dividing structure.
In one implementation of the present invention, the creating of the sub-base and sub-table rule in which the timestamp is associated with the sub-base and sub-table structure database specifically includes: establishing a mapping relation between the timestamp and the ID field, and further establishing a database-dividing and table-dividing rule related to the timestamp and the ID field; the ID field is generated based on the time stamp, and the time stamp for generating the ID field and the time stamp for writing the service data are the same time stamp.
In one implementation, the method for establishing the database-to-table rule with the time stamp associated with the database of the database-to-table structure comprises the following steps:
s110: and generating a corresponding ID field based on the time field value of the timestamp so as to convert the time range of the timestamp into the ID field range of the ID field, and further establishing the mapping relation between the timestamp and the ID field.
In the present invention, the ID field includes a tag, a timestamp portion, a machine address portion, and a random number portion; the time field value of the time stamp is written to the time stamp portion of the ID field.
S120: and establishing a mapping relation between the time stamp and the database and table dividing rule based on the ID field.
The invention defines the association relation based on the time stamp and the main key, and in the concrete implementation, the ID of the main key and the time stamp can be defined by using the annotation or the configuration file, so that the ID and the time stamp are based on the association relation of the same time field value.
In a preferred implementation, the generation of the primary key ID and foreign key ID is configured to determine whether the time stamp portion needs to be generated at the time of data record insertion. The method specifically comprises the following steps: the corresponding ID record is the primary key ID of the table, and is generally configured to require the generation of a time stamp portion. The corresponding ID record is the foreign key ID of the table, and is generally configured such that no time stamp portion need be generated.
The invention defines the rules of database division and table division: the invention defines the time field value of the time stamp and the generation rule of the ID field, converts the time field value of the time stamp into the range of the corresponding ID field by removing the non-time related value in the time stamp, and writes the range into the ID field. And mapping relation of the library and table dividing rules defined according to the range of the corresponding ID field.
According to the embodiment of the application, the database sub-table rule of the timestamp and the database sub-table structure is established through the primary key according to the time field value of the timestamp. Storing data records in a certain time stamp range into the same database with a sub-database and sub-table structure, for example, a time stamp of service data (a time stamp field can be 1/1970 (08: 00:00 GMT)), matching the service data into a database in a corresponding time stamp range in the database with the sub-database and sub-table structure according to the time stamp of the service data, and storing the service data into a specific table in the database according to the time stamp of the service data.
Specifically, the timestamp range, i.e., timestamp field, of the database may be an integer or time type from 1/1970 (08: 00:00 GMT) to the total seconds of the current time.
S200: and writing the service data including the time stamp into a database with a sub-database sub-table structure to form a data record.
Specifically, a time stamp part of an ID field, a time field value of an associated sub-database sub-table and business data comprising a time stamp are written into a sub-database sub-table structure database together to form a data record; and the time stamp part of the ID field and the time field value of the associated sub-library sub-table are generated by the same time stamp as the business data.
In an implementation of the present invention, a data record generating method is provided, where service data including a timestamp is written into a database with a sub-base and sub-table structure to form a data record, and the data record generating method specifically includes:
s210: and acquiring the time stamp of the current service data.
S220: and generating an ID field based on the timestamp of the current service data, wherein the ID field is used in the sub-database sub-table and is used for establishing the mapping relation between the sub-database sub-table and the service data.
S230: and searching a corresponding database sub-table and a database of a database sub-table structure according to the ID field.
S240: writing the time stamp part of the ID field, the time field value of the associated sub-database sub-table and the service data containing the time stamp into a corresponding sub-database sub-table structure database to form a data record; and the time stamp part of the ID field and the time field value of the associated sub-library sub-table are generated by the same time stamp as the business data.
In this embodiment, an ID field value is generated based on a timestamp of the service data, and the corresponding ID field value is used for table sorting and library sorting. The same corresponding ID field generation algorithm associated with the current timestamp is employed in reference S110 to generate a corresponding ID field. Reference may also be made to the concept of snowflake-like algorithms.
And searching the corresponding sub-library and sub-table structure database according to the sub-library and sub-table to which the corresponding ID field belongs in the step. And storing the business data into a database with a database-in-database and table-in-table structure, wherein the timestamp field of the database with the database-in-database and table-in-table structure can be a long integer or a time type from 1/1970 (08: 00:00 GMT) to the total seconds of the current time, and the timestamp field range of the database with the database-in-database and table-in-table structure corresponds to the timestamp of the ID field.
S300: according to the query request, obtaining a data record corresponding to the query request from the database sub-table structure data, specifically comprising:
s310: when the query request is ID range condition query, obtaining data records corresponding to the ID range condition in the database sub-table structure data based on the ID range condition.
Wherein, the ID range condition is queried according to the ID field query condition in the ID range condition.
S320: and when the query request contains the time range, converting the time range into an ID field range, wherein the ID field range is used for positioning the sub-warehouse sub-tables, and further acquiring the data record corresponding to the query request from the sub-warehouse sub-table structure data.
S330: when the query request comprises an ID range condition and a time range for query, determining the sub-warehouse sub-tables based on the ID range condition and the ID field range converted by the time range in a cooperative manner, and further obtaining corresponding data records in the sub-warehouse sub-table structure data.
The invention provides a query method of database-based and table-based structure data, which is characterized in that when the operation of time conditions is related, the query conditions of a main key range converted from a time range are added on the basis of the original query, so that the data can use indexes containing corresponding IDs and table-based rules. Meanwhile, the method can also be used for deleting and checking the corresponding id fields and deleting and checking the time ranges and the corresponding id fields.
Analyzing the operation condition of the ORM framework or the sql statement, and processing the operation condition corresponding to the following three conditions:
(1) a pruning operation with a corresponding ID field:
and determining which sub-library and sub-table structure database and sub-library and sub-table the primary key belongs to according to the sub-library and sub-table rule, and then entering the corresponding sub-library and sub-table to look up the service data corresponding to the corresponding ID.
(2) Censoring of temporal conditions containing corresponding temporal fields (which would overwrite the ORM framework or the operating conditions of the sql statement):
and analyzing the data of the ORM framework or the deleting and modifying condition of the finally generated query statement. If there is a query containing a time condition, a corresponding ID range query condition converted from the time condition is added on the basis of the original query, so that the data can use an index containing a corresponding ID.
Specifically, when the query request includes a time range, the time range is converted into an ID field range, and the ID field range is used for positioning the sub-warehouse sub-tables, so as to obtain a data record corresponding to the query request from the sub-warehouse sub-table structure data, including:
s321: the query request is parsed to obtain operating conditions, which include a time range and an operating field for the query.
S322: the time ranges in the operating conditions are translated into corresponding ID field ranges.
S323: and adding an ID field range under the operation condition, wherein the ID field range is used for positioning the sub-library and sub-table.
S324: and acquiring a data record of a time stamp corresponding to the query request in the database-based and table-based structure data according to the ID field range and the database-based and table-based rules.
Specifically, according to a database sub-table rule, a database sub-table structure database and a database sub-table corresponding to an ID field query condition are determined, wherein the ID field query condition corresponds to an ID field generated based on a timestamp in the database sub-table; and acquiring a sub-database and sub-table, and inquiring corresponding data records based on the operation conditions.
On the other hand, when the query request comprises an ID range condition and a time range for query, determining the ranges of the sub-database sub-tables respectively based on the ID range condition and the ID field range converted by the time range, taking the intersection of the sub-database sub-table ranges obtained by the two query modes as the hit sub-database sub-table range, quickly determining the sub-database sub-tables and the sub-database sub-table structure database, and further obtaining the corresponding data records in the sub-database sub-table structure data.
(3) Pruning operation without time range and corresponding ID field:
in this case, data deletion, modification and check can be performed on all the sub-database sub-tables of the corresponding service just like ordinary deletion, modification and check.
As shown in fig. 2, the present application provides a query device for a database with a sub-base and sub-table structure based on a timestamp, so as to implement the above query method for the database with the sub-base and sub-table structure based on the timestamp, where the query device includes a rule establishing module, a data writing module, and a query module.
The rule establishing module is used for establishing a database sub-table rule of which the time stamp is associated with the database sub-table structure database.
The data writing module is used for writing data into the module, and is used for writing the time stamp part of the ID field, the time field value of the associated sub-database sub-table and the service data comprising the time stamp into the sub-database sub-table structure database together to form a data record; and the time stamp part of the ID field and the time field value of the associated sub-library sub-table are generated by the same time stamp as the business data.
The query module is used for acquiring data records corresponding to the query request from the database sub-table structure data according to the query request, and specifically executes the following operations:
when the query request is ID range condition query, acquiring a data record corresponding to the ID range condition in the database sub-table structure data based on the ID range condition;
when the query request contains a time range, converting the time range into an ID field range, wherein the ID field range is used for positioning the sub-warehouse sub-tables and further obtaining data records corresponding to the query request in the sub-warehouse sub-table structure data;
when the query request comprises an ID range condition and a time range for query, determining the sub-warehouse sub-tables based on the ID range condition and the ID field range converted by the time range in a cooperative manner, and further obtaining corresponding data records in the sub-warehouse sub-table structure data.
As shown in fig. 3, the present invention further provides a query system of database with sub-database and sub-table structure based on timestamp, which comprises a user terminal, an application server, a database with sub-database and sub-table structure, a query terminal and the query device as described above;
the user terminal communicates with the application server to generate service data, and the application server stores the service data into the database with the database-dividing and table-dividing structure;
the query device establishes a database sub-table rule of which the time stamp is associated with a database sub-table structure database;
the inquiry device writes a time stamp part of an ID field, a time field value of an associated sub-database table and service data comprising a time stamp into a sub-database table structure database together to form a data record, wherein the time stamp part of the ID field and the time field value of the associated sub-database table are generated by the time stamp which is the same as the service data;
and the inquiry device acquires a data record of a time stamp corresponding to the inquiry request from the database sub-table structure data according to the inquiry request sent by the inquiry terminal.
The above description is only a preferred embodiment of the present invention, and is not intended to limit the present invention in any way, so that any modification, equivalent change and modification made to the above embodiment according to the technical spirit of the present invention are within the scope of the technical solution of the present invention.
Claims (9)
1. A query method for a database with a database and table structure is characterized by comprising the following steps:
establishing a database sub-table rule of associating the time stamp with a database sub-table structure database;
writing the service data including the time stamp into a database with a sub-database and sub-table structure to form a data record;
according to the query request, obtaining a data record corresponding to the query request from the database sub-table structure data, specifically comprising:
when the query request is ID range condition query, acquiring a data record corresponding to the ID range condition in the database sub-table structure data based on the ID range condition;
when the query request contains a time range, converting the time range into an ID field range, wherein the ID field range is used for positioning the sub-warehouse sub-tables and further obtaining data records corresponding to the query request in the sub-warehouse sub-table structure data;
when the query request comprises an ID range condition and a time range for query, determining the sub-warehouse sub-tables based on the ID range condition and the ID field range converted by the time range in a cooperative manner, and further obtaining corresponding data records in the sub-warehouse sub-table structure data.
2. The method for querying the database with the database-based and table-based structure according to claim 1, wherein the establishing of the database-based and table-based rule with the timestamp associated with the database with the table-based and table-based structure specifically comprises:
establishing a mapping relation between the timestamp and the ID field, and further establishing a database-dividing and table-dividing rule related to the timestamp and the ID field;
the ID field is generated based on the time stamp, and the time stamp for generating the ID field and the time stamp for writing the service data are the same time stamp.
3. The method for querying the database with the sub-database and sub-table structure according to claim 1 or 2, wherein the establishing of the sub-database and sub-table rule with the timestamp associated with the database with the sub-database and sub-table structure specifically comprises:
generating a corresponding ID field based on the time field value of the timestamp so as to convert the time range of the timestamp into the ID field range of the ID field and further establish the mapping relation between the timestamp and the ID field;
and establishing a mapping relation between the time stamp and the database and table dividing rule based on the ID field.
4. The method for querying the database with the sub-base and sub-table structure according to claim 3, wherein writing the service data including the timestamp into the database with the sub-base and sub-table structure to form a data record, specifically comprising:
acquiring a timestamp of current service data;
generating an ID field based on a timestamp of current service data, wherein the ID field is used in a database partitioning table and is used for establishing a mapping relation between the database partitioning table and the service data;
searching a corresponding database sub-table and a database sub-table structure according to the ID field;
writing the time stamp part of the ID field, the time field value of the associated sub-database sub-table and the service data containing the time stamp into a corresponding sub-database sub-table structure database to form a data record;
and the time stamp part of the ID field and the time field value of the associated sub-library sub-table are generated by the same time stamp as the business data.
5. The method for querying the database with the database and table structure according to claim 4, wherein generating the ID field based on the timestamp of the current service data specifically comprises:
the ID field is composed of a tag, a timestamp portion, a machine address portion, and a random number portion;
and acquiring the time stamp of the current service data, and writing the time field value of the time stamp into the time stamp part of the ID field.
6. The method according to claim 4, wherein when the query request includes a time range, the time range is converted into an ID field range, and the ID field range is used for locating the sub-database and sub-table, so as to obtain the data record corresponding to the query request from the sub-database and sub-table structure data, specifically comprising:
analyzing the query request to obtain an operation condition, wherein the operation condition comprises a time range and an operation field for querying;
converting the time ranges in the operating conditions into corresponding ID field ranges;
adding an ID field range under an operation condition, wherein the ID field range is used for positioning sub-warehouse sub-tables;
and acquiring a data record of a time stamp corresponding to the query request in the database-based and table-based structure data according to the ID field range and the database-based and table-based rules.
7. The method for querying the database with the database-based and table-based structure according to claim 6, wherein the obtaining of the data record with the timestamp corresponding to the query request from the database-based and table-based structure data according to the ID field range and the database-based and table-based rule specifically comprises:
determining a database sub-table structure database and a database sub-table corresponding to an ID field range according to a database sub-table rule, wherein the ID field range corresponds to an ID field generated based on a timestamp in the database sub-table;
and acquiring a sub-database and sub-table, and inquiring corresponding data records based on the operation conditions.
8. An apparatus for querying a database having a sub-database and sub-table structure, comprising:
the rule establishing module is used for establishing a database sub-table rule of which the time stamp is associated with the database sub-table structure database;
the data writing module is used for writing the time stamp part of the ID field, the time field value of the associated sub-database sub-table and the service data comprising the time stamp into the sub-database sub-table structure database together to form a data record; the time stamp part of the ID field and the time field value of the associated sub-library sub-table are both generated by the same time stamp as the service data;
the query module is used for acquiring data records corresponding to the query request from the database sub-table structure data according to the query request, and specifically executes the following operations:
when the query request is ID range condition query, acquiring a data record corresponding to the ID range condition in the database sub-table structure data based on the ID range condition;
when the query request contains a time range, converting the time range into an ID field range, wherein the ID field range is used for positioning the sub-warehouse sub-tables and further obtaining data records corresponding to the query request in the sub-warehouse sub-table structure data;
when the query request comprises an ID range condition and a time range for query, determining the sub-warehouse sub-tables based on the ID range condition and the ID field range converted by the time range in a cooperative manner, and further obtaining corresponding data records in the sub-warehouse sub-table structure data.
9. A database query system for database with sub-database and sub-table structure, comprising a user terminal, an application server, a database with sub-database and sub-table structure, a query terminal and the query device according to claim 8;
the user terminal communicates with the application server to generate service data, and the application server stores the service data into the database with the database-dividing and table-dividing structure;
the query device establishes a database sub-table rule of which the time stamp is associated with a database sub-table structure database;
the inquiry device writes a time stamp part of an ID field, a time field value of an associated sub-database table and service data comprising a time stamp into a sub-database table structure database together to form a data record, wherein the time stamp part of the ID field and the time field value of the associated sub-database table are generated by the time stamp which is the same as the service data;
and the inquiry device acquires a data record of a time stamp corresponding to the inquiry request from the database sub-table structure data according to the inquiry request sent by the inquiry terminal.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110551017.6A CN113032439A (en) | 2021-05-20 | 2021-05-20 | Method, device and system for querying database with database-to-table structure |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN202110551017.6A CN113032439A (en) | 2021-05-20 | 2021-05-20 | Method, device and system for querying database with database-to-table structure |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN113032439A true CN113032439A (en) | 2021-06-25 |
Family
ID=76455381
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN202110551017.6A Pending CN113032439A (en) | 2021-05-20 | 2021-05-20 | Method, device and system for querying database with database-to-table structure |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN113032439A (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115543934A (en) * | 2022-10-17 | 2022-12-30 | 安测半导体技术(义乌)有限公司 | Method and system for exporting chip test data document |
Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20140089284A1 (en) * | 2012-09-27 | 2014-03-27 | Hewlett-Packard Development Company, L.P. | Visual Approach to Searching Databases |
| CN105138676A (en) * | 2015-09-08 | 2015-12-09 | 浙江维融电子科技股份有限公司 | Sub-library and sub-table merge query method based on high-level language concurrent aggregation calculation |
| CN105488050A (en) * | 2014-09-17 | 2016-04-13 | 阿里巴巴集团控股有限公司 | Database multi-index method, apparatus and system |
| CN106528579A (en) * | 2015-09-15 | 2017-03-22 | 阿里巴巴集团控股有限公司 | Search method, device and system based on sharding structure databases |
| CN107818115A (en) * | 2016-09-14 | 2018-03-20 | 苏宁云商集团股份有限公司 | A kind of method and device of processing data table |
| US20180285421A1 (en) * | 2017-03-29 | 2018-10-04 | Hitachi, Ltd. | Database management system and database management method |
| CN110308980A (en) * | 2019-06-27 | 2019-10-08 | 深圳前海微众银行股份有限公司 | Batch processing method, device, equipment and the storage medium of data |
| CN112015741A (en) * | 2020-10-19 | 2020-12-01 | 武汉物易云通网络科技有限公司 | Method and device for storing massive data in different databases and tables |
-
2021
- 2021-05-20 CN CN202110551017.6A patent/CN113032439A/en active Pending
Patent Citations (8)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20140089284A1 (en) * | 2012-09-27 | 2014-03-27 | Hewlett-Packard Development Company, L.P. | Visual Approach to Searching Databases |
| CN105488050A (en) * | 2014-09-17 | 2016-04-13 | 阿里巴巴集团控股有限公司 | Database multi-index method, apparatus and system |
| CN105138676A (en) * | 2015-09-08 | 2015-12-09 | 浙江维融电子科技股份有限公司 | Sub-library and sub-table merge query method based on high-level language concurrent aggregation calculation |
| CN106528579A (en) * | 2015-09-15 | 2017-03-22 | 阿里巴巴集团控股有限公司 | Search method, device and system based on sharding structure databases |
| CN107818115A (en) * | 2016-09-14 | 2018-03-20 | 苏宁云商集团股份有限公司 | A kind of method and device of processing data table |
| US20180285421A1 (en) * | 2017-03-29 | 2018-10-04 | Hitachi, Ltd. | Database management system and database management method |
| CN110308980A (en) * | 2019-06-27 | 2019-10-08 | 深圳前海微众银行股份有限公司 | Batch processing method, device, equipment and the storage medium of data |
| CN112015741A (en) * | 2020-10-19 | 2020-12-01 | 武汉物易云通网络科技有限公司 | Method and device for storing massive data in different databases and tables |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN115543934A (en) * | 2022-10-17 | 2022-12-30 | 安测半导体技术(义乌)有限公司 | Method and system for exporting chip test data document |
| CN115543934B (en) * | 2022-10-17 | 2023-10-03 | 安测半导体技术(义乌)有限公司 | Method and system for exporting chip test data document |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN108920716B (en) | Data retrieval and visualization system and method based on knowledge graph | |
| US8825581B2 (en) | Simplifying a graph of correlation rules while preserving semantic coverage | |
| EP3702932A1 (en) | Method, apparatus, device and medium for storing and querying data | |
| US20090049021A1 (en) | System and method for storing text annotations with associated type information in a structured data store | |
| CN106407303A (en) | Data storage method and apparatus, and data query method and apparatus | |
| CN109947796B (en) | Caching method for query intermediate result set of distributed database system | |
| US20140046928A1 (en) | Query plans with parameter markers in place of object identifiers | |
| CN109656958B (en) | Data query method and system | |
| US20090106216A1 (en) | Push-model based index updating | |
| CN106484815B (en) | A kind of automatic identification optimization method based on mass data class SQL retrieval scene | |
| CN116955399A (en) | Unified SQL query method, system and medium based on Calcite | |
| CN113032439A (en) | Method, device and system for querying database with database-to-table structure | |
| CN114661832A (en) | Multi-mode heterogeneous data storage method and system based on data quality | |
| CN108241709A (en) | A kind of data integrating method, device and system | |
| CN117251414A (en) | Data storage and processing method based on heterogeneous technology | |
| CN104750860A (en) | Data storage method of uncertain data | |
| CN116186116A (en) | Asset problem analysis method based on equal protection assessment | |
| CN111522918A (en) | Data aggregation method and device, electronic equipment and computer readable storage medium | |
| US20060101045A1 (en) | Methods and apparatus for interval query indexing | |
| CN116069810A (en) | Data query method and device and terminal equipment | |
| CN115168310A (en) | Log management method, device, medium and system in LDAP system | |
| CN112347314B (en) | Data resource management system based on graph database | |
| CN114281842A (en) | Method and device for sub-table query of database | |
| CN115952203B (en) | Data query method, device, system and storage medium | |
| CN116680445B (en) | Knowledge-graph-based multi-source heterogeneous data fusion method and system for electric power optical communication system |
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 | ||
| RJ01 | Rejection of invention patent application after publication | ||
| RJ01 | Rejection of invention patent application after publication |
Application publication date: 20210625 |