Acquisition method based on time physical sub-table

Abstract

The invention discloses a time-based physical sub-table acquisition method, which relates to the technical field of database management, and comprises the following steps of: S1, acquiring a system starting instruction and a database reference table structure, automatically checking and creating physical sub-tables of a current month and a next month, and setting monthly fixed time to trigger timed automatic checking and creating logic tasks at the same time, the physical sub-table of the next month is ensured to be created in advance; s2, receiving a data writing request, and extracting a time attribute of to-be-written data; according to the calculation method based on the time physical sub-table provided by the invention, through a data management strategy, the efficiency and the flexibility of the database in processing large-scale time sequence data are improved, full-automatic management is realized, the operation and maintenance cost is effectively reduced, errors and omissions possibly caused by manual intervention are avoided, and the operation efficiency is improved. And a dynamic table name routing mechanism ensures the accuracy of data writing, so that service codes do not need to perceive details of sub-tables, and the usability is enhanced.

Description

Technical Field

[0001] This invention relates to the field of database management technology, specifically to a method for obtaining time-based physical table partitioning. Background Technology

[0002] With the rapid development of fields such as the Internet of Things (IoT) and industrial monitoring, massive amounts of time-series data are constantly emerging. This explosive growth in data volume places higher demands on database storage capacity, read / write efficiency, and query response speed. To adapt to the management needs of this type of data, the industry has gradually developed technical solutions such as database sharding, database partitioning, and ORM framework extensions. The core idea is to reduce the load on a single table by splitting and storing data, thereby ensuring the basic operating performance of the system in large-scale data scenarios. This has become the mainstream technical path for time-series data storage and management.

[0003] While existing technical solutions have achieved some success in practical applications, they still have some limitations that need improvement. Some table partitioning solutions rely on manually predefined table naming rules and manual creation and maintenance of table structures, which not only increases the workload of operation and maintenance but may also lead to inconsistencies or omissions in table structures due to human operation. The database's built-in partitioning function is limited by engine characteristics, resulting in insufficient flexibility in the number and configuration of partitions, and limited room for performance optimization when querying across partitions. Some solutions also lack integrated support for automatic table creation, table existence verification, and query routing, requiring additional adaptation logic to be developed at the application layer, leading to strong code intrusion and difficulty in flexibly handling dynamically changing time-range data storage needs. These factors mean that existing solutions still have room for improvement in balancing efficiency, ease of use, and scalability in managing large-scale time-series data. To address this, we propose a time-based physical table partitioning acquisition method. Summary of the Invention

[0004] To address the aforementioned technical issues, this paper provides a time-based physical table partitioning acquisition method. This solution resolves the problems of increased operational workload and potential table structure issues associated with manual table maintenance, insufficient flexibility of built-in database partitioning, limited cross-partition query performance, lack of integrated support such as automatic table creation, the need for additional development of adaptation logic, strong code intrusion, and difficulty in flexibly handling data storage requirements with dynamic time ranges.

[0005] To achieve the above objectives, the technical solution adopted by the present invention is as follows: A method for obtaining time-based physical partitioning tables includes the following steps: S1. Obtain the system startup command and database baseline table structure, automatically check and create physical partitions for the current month and the next month, and set a fixed time each month to trigger a timed automatic check and creation logical task to ensure that the physical partitions for the next month are created in advance; S2. Receive the data write request, extract the time attribute of the data to be written, parse the physical table name of the target month according to the preset table name rules, bind it to the current thread through the ThreadLocal mechanism, and then have the MyBatis Plus interceptor intercept the SQL, replace the preset logical table name in the SQL with the target physical table name, and complete the data write. S3. Receive query requests with time ranges, parse the start and end times and calculate all months covered; generate a list of physical sub-tables according to preset table name rules; query each physical sub-table separately, aggregate all query results and return them.

[0006] Preferably, S1 specifically includes: The system monitors startup commands. The startup commands are triggered in scenarios including the initial startup after the system's first deployment, system failure recovery restart, and system version update restart. When a startup command is detected, the system automatically enters the table partitioning initialization process. Determine the database baseline table structure. The database baseline table is a pre-configured partitioned table template table, which contains all fields, field data types, and index configurations required by the target business scenario. The target business scenario includes alarm record storage and monitoring data storage. A stable connection to the target database is established through a database connection pool. The database metadata query interface is called to read the field information, index information and table engine configuration of the baseline table, and the above information is cached in local memory. Call the time utility class to get the current system time, and convert the current time to yyyy-MM format to determine the current month; Calculate the next month based on the current month to ensure no deviation in month calculations in cross-year scenarios; convert the current month and the next month into a unified yyyyMM format time code; generate the physical table name corresponding to the current month and the physical table name corresponding to the next month according to the preset table partitioning rules; Execute SQL statements to check the existence of database tables, and query the existence status of the physical tables corresponding to the current month and the next month respectively; The existence of a physical table is determined based on the query results. If the query results return a table name record, the corresponding physical table is considered to exist. If the query results do not return any results, the corresponding physical table is considered to not exist, and the name of the non-existent table is recorded.

[0007] Preferably, S1 further includes: After determining that at least one physical partition table for the current month and the physical partition table for the next month does not exist, the physical partition table creation process is automatically started. The creation process is based on the base table field information, index information and table engine configuration cached in local memory. Based on the non-existent table name in the record, and combined with the base table structure information, the SQL statement for creating the table is automatically generated. The SQL statement for creating the table includes the table name, field definition, field data type, index creation rules, and table engine specifications. Execute the generated table creation SQL statements to create physical partitions for the current month or the next month that do not exist.

[0008] Preferably, S1 further includes: After the table creation operation is executed, the database table existence verification SQL statement is called again to query the existence status of the created partitioned table and confirm that the partitioned table has been created successfully. If the query result shows that the partitioned table already exists, the table creation operation is considered complete and the system ends the current partitioned table creation process. If the query result still shows that the partitioned table does not exist, the table creation operation is considered to have failed, the table creation failure log is recorded and the partitioned table creation process is paused. At the same time, a failure prompt is pushed to the operation and maintenance terminal.

[0009] Preferably, S2 specifically includes: The system receives a data write request, which contains business data to be written. The business data corresponds to time-series data of a target business scenario, including alarm record storage and monitoring data storage. Extract the time attribute from the business data to be written. The time attribute is a time-related field that comes with the business data, specifically: data generation time, data submission time, and business association time. The extracted time attributes are formatted and a preset table name rule is invoked. The preset standard format for the format validation is yyyy-MM-dd HH:mm:ss. The preset table name rule is a rule that combines the logical table name with the time dimension suffix. The logical table name is the table identifier name corresponding to the business scenario, and the time dimension suffix is ​​the year and month information extracted based on the time attribute. The year and month information is in the format yyyyMM. Based on the extracted time attributes, the corresponding year and month information is parsed out. The year and month are extracted from the time attributes that conform to the standard format and converted into the yyyyMM format year and month code. The logical table name is combined with the parsed yyyyMM format year and month code to generate the target month physical table name corresponding to the data to be written, thus completing the parsing of the target month physical table name.

[0010] Preferably, S2 further includes: After obtaining the physical table name of the target month through parsing, the system starts a thread-level data binding process, using the ThreadLocal mechanism to bind the physical table name of the target month to the current processing thread; The system initializes a ThreadLocal instance, which is used to store the physical table name of the target month corresponding to the current thread; the set method of the ThreadLocal instance is called, and the parsed physical table name of the target month is passed as a value to bind it to the thread currently processing the data write request; After binding is complete, the system confirms that the current thread has been associated with the physical table name of the target month.

[0011] Preferably, S2 further includes: Before the system executes the data writing to the corresponding SQL statement, the pre-configured MyBatis Plus interceptor triggers the SQL interception operation to intercept the original SQL statement to be executed, which contains the preset business logic table name; The MyBatis Plus interceptor retrieves the bound target month physical table name from the ThreadLocal instance of the current thread by calling the get method, replaces the preset logical table name in the original SQL statement with the target month physical table name, and generates the final execution SQL adapted to the target physical table. Execute the final SQL statement after replacement to write the data to be written to the physical table of the target month. Once the SQL execution is complete and the data is successfully written, the data writing operation is considered complete.

[0012] Preferably, S3 specifically includes: The system receives query requests containing a time range and extracts the query start time and query end time from the request; Perform format validation on the query start time and query end time; Extract the start year and start month from the query start time, and extract the end year and end month from the query end time, and combine them into start year-month code and end year-month code; Based on the starting and ending year and month codes, calculate all months covered by the query time range: if the starting and ending year and month codes are the same, then the month is covered; if they are different, then generate all intermediate year and month codes by incrementing the month code from the starting year and month until the ending year and month code.

[0013] Preferably, S3 further includes: Call the preset table name rules, extract the year and month code for each month covered by the query time range, and concatenate the code with the logical table name of the current query business scenario; Generate the physical table name corresponding to each month, and sort all physical table names by year and month code to form an ordered physical table list.

[0014] Preferably, S3 further includes: Perform an existence check on each physical partition in the list of physical partitions. If the partition does not exist, record the exception information and skip the query. For each existing physical partition, generate and execute a single-table query statement to obtain the query result set of each partition. Sort all result sets by the time attribute field and remove duplicate data records based on the unique identifier field; encapsulate the sorted and deduplicated data together with the exception information and return it to the business end.

[0015] Compared with the prior art, the beneficial effects of the present invention are as follows: The time-based physical table partitioning method proposed in this invention improves the efficiency and flexibility of databases when processing large-scale time-series data through data management strategies. It achieves fully automated management, effectively reduces operation and maintenance costs, and avoids errors and omissions that may be caused by manual intervention. Its dynamic table name routing mechanism ensures the accuracy of data writing, so that business code does not need to be aware of the table partitioning details, enhancing the transparency and ease of use of the system. In terms of querying, by automatically calculating the query time range and aggregating the results, it significantly reduces full table scans and improves query efficiency. Its flexible scalability enables it to meet the data storage needs of different business scenarios, providing strong support for the efficient management of time-series data. It shows significant advantages in improving database performance, reducing operation and maintenance complexity, and enhancing system flexibility. Attached Figure Description

[0016] Figure 1 This is a flowchart of the method of the present invention. Detailed Implementation

[0017] The following description is intended to disclose the invention and enable those skilled in the art to implement it. The preferred embodiments described below are merely examples, and other obvious variations will occur to those skilled in the art.

[0018] Reference Figure 1 As shown, a method for obtaining time-based physical partitioning tables includes the following steps: S1. Obtain the system startup command and the database baseline table structure, automatically check and create physical tables for the current month and the next month, and set a fixed time each month to trigger a timed automatic check and creation logical task to ensure that the physical tables for the next month are created in advance; for example, if the current month is August 2025, the system will create two tables, table_202508 and table_202509, based on the baseline table.

[0019] S1 specifically includes: The system monitors startup commands. The startup commands are triggered in scenarios including the initial startup after the system's first deployment, system failure recovery restart, and system version update restart. When a startup command is detected, the system automatically enters the table partitioning initialization process. Determine the database baseline table structure. The database baseline table is a pre-configured partitioned table template table, which contains all fields, field data types, and index configurations required for the target business scenario. The target business scenario includes alarm record storage and monitoring data storage. All required fields include alarm ID, device number, alarm start time, alarm level, alarm content, etc. The index configuration is such as a B-tree index with "alarm start time" as the key field.

[0020] Establish a stable connection with the target database through a database connection pool, call the database metadata query interface to read the field information, index information and table engine configuration of the base table, and cache the above information in local memory; call the database metadata query interface such as MySQL's INFORMATION_SCHEMA.COLUMNS query.

[0021] Call the time utility class to get the current system time, and convert the current time to yyyy-MM format to determine the current month; The calculation of the next month is based on the current month to ensure accurate month calculation in cross-year scenarios. For example, if the current month is not December, such as August, the next month is the current month plus 1, which is September. If the current month is December, such as 2025-12, the next month is January of the following year, i.e., 2026-01. The current month and the next month are uniformly converted to yyyyMM format time encoding, such as 2025-08 → "202508", 2025-09 → "202509". According to the preset table partitioning rules, the physical table name corresponding to the current month and the physical table name corresponding to the next month are generated. Execute SQL statements to check the existence of database tables, and query the existence status of the physical tables corresponding to the current month and the next month respectively; The existence of a physical table is determined based on the query results. If the query results return a table name record, the corresponding physical table is considered to exist. If the query results do not return any results, the corresponding physical table is considered to not exist, and the name of the non-existent table is recorded.

[0022] S1 further includes: After determining that at least one physical partition table for the current month and the physical partition table for the next month does not exist, the physical partition table creation process is automatically started. The creation process is based on the base table field information, index information and table engine configuration cached in local memory. Based on the non-existent table name in the record, and combined with the base table structure information, the SQL statement for creating the table is automatically generated. The SQL statement for creating the table includes the table name, field definition, field data type, index creation rules, and table engine specifications. Execute the generated table creation SQL statements to create physical partitions for the current month or the next month that do not exist.

[0023] S1 further includes: After the table creation operation is executed, the database table existence verification SQL statement is called again to query the existence status of the created partitioned table and confirm that the partitioned table has been created successfully. If the query result shows that the partitioned table already exists, the table creation operation is considered complete and the system ends the current partitioned table creation process. If the query result still shows that the partitioned table does not exist, the table creation operation is considered to have failed, the table creation failure log is recorded and the partitioned table creation process is paused. At the same time, a failure prompt is pushed to the operation and maintenance terminal.

[0024] When using it, please refer to the above: In existing technologies, database partitioning management often relies on manual intervention or static configuration, lacking dynamic automation mechanisms. This results in physical partitions not being ready when the system starts, leading to data storage failures or service unavailability. Traditional methods do not integrate scheduled automatic creation logic, making it difficult to ensure advance preparation of partitions for future months, posing a risk of business interruption due to missing partitions. Furthermore, the partitioning creation process often lacks robust existence checks and failure handling, easily leading to problems such as duplicate table creation or no alerts for failed table creation.

[0025] This step triggers automatic table initialization via system startup commands. Combined with baseline table structure synchronization and time-driven table naming, it achieves intelligent checking and creation of physical tables for the current month and the next month. By setting fixed monthly scheduled tasks, it ensures the continuity and foresight of table creation. The secondary verification and failure log push mechanism after table creation improves the reliability and maintainability of table management, effectively guaranteeing the stable operation and seamless expansion of the database in high-concurrency business scenarios.

[0026] S2. Receive the data write request, extract the time attribute of the data to be written, parse the physical table name of the target month according to the preset table name rules, bind it to the current thread through the ThreadLocal mechanism, and then have the MyBatis Plus interceptor intercept the SQL, replace the preset logical table name in the SQL with the target physical table name, and complete the data write. S2 specifically includes: The system receives a data write request, which contains business data to be written. The business data corresponds to time-series data of a target business scenario, including alarm record storage and monitoring data storage. Extract the time attribute from the business data to be written. The time attribute is a time-related field that comes with the business data, specifically: data generation time, data submission time, and business association time. The extracted time attributes are formatted and a preset table name rule is invoked. The preset standard format for the format validation is yyyy-MM-dd HH:mm:ss. The preset table name rule is a rule that combines the logical table name with the time dimension suffix. The logical table name is the table identifier name corresponding to the business scenario, and the time dimension suffix is ​​the year and month information extracted based on the time attribute. The year and month information is in the format yyyyMM. Based on the extracted time attributes, the corresponding year and month information is parsed out. The year and month are extracted from the time attributes that conform to the standard format and converted into the yyyyMM format year and month code. The logical table name is combined with the parsed yyyyMM format year and month code to generate the target month physical table name corresponding to the data to be written, thus completing the parsing of the target month physical table name.

[0027] S2 further includes: After obtaining the physical table name of the target month through parsing, the system starts a thread-level data binding process, using the ThreadLocal mechanism to bind the physical table name of the target month to the current processing thread; The system initializes a ThreadLocal instance, which is used to store the physical table name of the target month corresponding to the current thread; the set method of the ThreadLocal instance is called, and the parsed physical table name of the target month is passed as a value to bind it to the thread currently processing the data write request; After binding is complete, the system confirms that the current thread has been associated with the physical table name of the target month.

[0028] S2 further includes: Before the system executes the data writing to the corresponding SQL statement, the pre-configured MyBatis Plus interceptor triggers the SQL interception operation to intercept the original SQL statement to be executed, which contains the preset business logic table name; The MyBatis Plus interceptor retrieves the bound target month physical table name from the ThreadLocal instance of the current thread by calling the get method, replaces the preset logical table name in the original SQL statement with the target month physical table name, and generates the final execution SQL adapted to the target physical table. Execute the final SQL statement after replacement to write the data to be written to the physical table of the target month. Once the SQL execution is complete and the data is successfully written, the data writing operation is considered complete.

[0029] When using it, please refer to the above: In existing technologies, data writing and table partitioning routing often rely on hard-coded or manually configured physical table names, resulting in tight coupling between business logic and the table partitioning mechanism, leading to high development and maintenance costs. Traditional methods lack unified rules for extracting time attributes and resolving table names, which can easily cause data writing errors due to inconsistent time formats or parsing errors. At the same time, in multi-threaded environments, table name passing often uses parameter pass-through or global variables, which poses thread safety risks and performance bottlenecks. Furthermore, ORM frameworks lack dynamic table name replacement capabilities, requiring developers to manually concatenate SQL, increasing code complexity and the probability of errors.

[0030] This step automatically extracts and verifies the time attribute when receiving data write requests, and accurately parses the physical table name of the target month according to preset table name rules; it uses the ThreadLocal mechanism to achieve isolation binding between table names and threads, ensuring data security and context isolation in multi-threaded concurrent scenarios; and then uses MyBatis Plus interceptors to dynamically intercept and replace the logical table name in the SQL, realizing transparent and automated table routing, greatly reducing development complexity, improving the accuracy of data writing and system maintainability, and effectively supporting seamless storage and efficient management of high-concurrency time-series business data.

[0031] S3. Receive query requests with time ranges, parse the start and end times and calculate all months covered; generate a list of physical sub-tables according to preset table name rules; query each physical sub-table separately, aggregate all query results and return them.

[0032] S3 specifically includes: The system receives query requests containing a time range and extracts the query start time and query end time from the request; Perform format validation on the query start time and query end time; Extract the start year and start month from the query start time, and extract the end year and end month from the query end time, and combine them into start year-month code and end year-month code; Based on the starting and ending year and month codes, calculate all months covered by the query time range: if the starting and ending year and month codes are the same, then the month is covered; if they are different, then generate all intermediate year and month codes by incrementing the month code from the starting year and month until the ending year and month code.

[0033] S3 further includes: Call the preset table name rules, extract the year and month code for each month covered by the query time range, and concatenate the code with the logical table name of the current query business scenario; Generate the physical table name corresponding to each month, and sort all physical table names by year and month code to form an ordered physical table list.

[0034] S3 further includes: Perform an existence check on each physical partition in the list of physical partitions. If the partition does not exist, record the exception information and skip the query. For each existing physical partition, generate and execute a single-table query statement to obtain the query result set of each partition. Sort all result sets by the time attribute field and remove duplicate data records based on the unique identifier field; encapsulate the sorted and deduplicated data together with the exception information and return it to the business end.

[0035] When using it, please refer to the above: In existing technologies, cross-table queries often rely on manually calculating the mapping between time ranges and physical tables, resulting in complex and error-prone query logic. Traditional methods lack a unified mechanism for month parsing and table name generation, making it difficult to efficiently handle queries across complex time intervals such as months and years. At the same time, the existence verification of shards is mostly done manually, which can easily lead to query anomalies due to missing shards. Furthermore, result aggregation often relies on external tools or manual deduplication, which carries the risk of data omission or duplication, affecting query accuracy and system reliability.

[0036] This step automatically parses the start and end times and calculates the covered months when receiving query requests with time ranges, and dynamically generates a list of physical shards based on preset table naming rules. Through automated existence checks and shard-level queries, it ensures the integrity of the query range and the stability of execution. Finally, it intelligently sorts and deduplicates the result sets of multiple shards, realizing transparency and efficiency in cross-shard queries, significantly reducing the complexity of business-side queries, improving the accuracy of data retrieval and system response efficiency, and effectively supporting the needs of historical querying and statistical analysis of large-scale time-series data.

[0037] Based on the above steps, the specific implementation method is as follows: Suppose that on August 1, 2025, a major cloud service provider's intelligent operations and maintenance monitoring platform completed a version upgrade and restarted. This system needs to process hundreds of millions of monitoring metrics and alarm events daily, and the efficient storage and retrieval of its time-series data is crucial to ensuring business stability. The following is a complete workflow of this system during startup and subsequent operation: Upon detecting the startup command, the system immediately triggers the table partitioning initialization process. It first connects to the core business database and reads the predefined baseline table structure for "alarm records"—this structure contains complete fields and index configurations, including alarm ID, alarm level, alarm details, and generation time. Subsequently, the system's time tool obtains the current time, calculates the current month as "202508" and the next month as "202509". By executing an existence check SQL, the system discovered that the physical tables "table_202508" and "table_202509" did not exist. This indicated the start of a new cycle or the system's first deployment to a new environment. Without hesitation, the system automatically generated and executed the SQL statements to create these two tables based on the cached baseline table structure. After successful table creation, the system performed another verification check, ensuring that the "data warehouse" for August and September was ready before the first batch of data was written, thus preventing data write failures due to missing table structures from the outset.

[0038] On August 20th, the CPU utilization of a core server consistently exceeded the threshold, triggering an alarm. The platform generated this alarm record, with its "Time of Generation" field valued at "2025-08-20 14:35:22". When the data write service received this record, it first accurately extracted the time field and, according to the preset "yyyyMM" rule, parsed the target physical table name to be "table_202508".

[0039] Next, the system uses the ThreadLocal mechanism to bind the table name "table_202508" to the thread currently processing the request. When the MyBatisPlus framework prepares to execute the INSERT statement, its built-in interceptor is activated. The interceptor easily retrieves the bound table name from the current thread's ThreadLocal and dynamically replaces the logical table name "logical_alert_table" in the original SQL with "table_202508". Ultimately, this critical alert record is accurately stored in the August partitioned table. The entire process is completely transparent to the business code; developers don't need to worry about where the data ultimately ends up, only needing to program against the logical tables, greatly simplifying development and ensuring 100% accuracy in data routing.

[0040] To stay ahead of schedule, the system sets up a scheduled task at 23:30 on the 25th of each month. Late at night on August 25th, this task silently started. It calculated the code "202510" for the next month—October—and checked if "table_202510" existed. The check showed that the table had not yet been created, so the task immediately invoked the table creation process and successfully created the table for October.

[0041] This mechanism ensures that even if no one is on duty during the National Day holiday, the system has already prepared "storage space" for the massive amount of data that will be generated at midnight on October 1st. This forward-looking self-management capability completely liberates maintenance personnel from tedious and forgetful manual table creation work, realizing truly "unmanned" maintenance.

[0042] At the end of the month, an operations engineer needed to review the overall alert situation from August to September in the management backend. He set the query time range from "2025-08-01" to "2025-09-30" and submitted a request. After receiving the request, the system automatically parsed the time range, calculated that it needed to query the two months "202508" and "202509", and then generated a physical table list "[table_202508,table_202509]".

[0043] The system initiated queries on both tables in parallel. First, it confirmed that both tables existed (thanks to previous automatic creation), then executed the queries separately, retrieving 150,000 records from the August table and 120,000 records from the September table. Subsequently, the system uniformly sorted all 270,000 records according to their "generation time" and performed global deduplication based on "alarm ID," ultimately returning a complete, ordered, and unique aggregated result set to the front-end interface. The engineer gained a coherent global perspective, completely unaware that two physical tables were working collaboratively behind the scenes.

[0044] In summary, this fully automated process constructs a highly autonomous time-series data management ecosystem through intelligent management of table lifecycles (creation, validation), precise and transparent data routing (parsing, binding, replacement), and efficient processing of query aggregations (calculation, validation, merging). It significantly reduces the complexity and error rate of manual operations and maintenance, while providing businesses with continuous, stable, and efficient massive data storage and retrieval capabilities. It is the core foundation for maintaining high performance and high availability in high-concurrency, high-data-volume scenarios.

[0045] The foregoing has shown and described the basic principles, main features, and advantages of the present invention. Those skilled in the art should understand that the present invention is not limited to the above embodiments. The embodiments and descriptions in the specification are merely principles of the invention. Various changes and modifications can be made to the invention without departing from its spirit and scope, and all such changes and modifications fall within the scope of the claimed invention. The scope of protection claimed by the appended claims and their equivalents is defined.

Claims

1. A method for obtaining time-based physical table partitioning, characterized in that, Includes the following steps: S1. Obtain the system startup command and database baseline table structure, automatically check and create physical partitions for the current month and the next month, and set a fixed time each month to trigger a timed automatic check and creation logical task to ensure that the physical partitions for the next month are created in advance; S2. Receive the data write request, extract the time attribute of the data to be written, parse the physical table name of the target month according to the preset table name rules, bind it to the current thread through the ThreadLocal mechanism, and then have the MyBatis Plus interceptor intercept the SQL, replace the preset logical table name in the SQL with the target physical table name, and complete the data write. S3. Receive query requests containing time ranges, parse the start and end times and calculate all months covered; generate a list of physical table partitions according to preset table name rules; Query each physical table separately, aggregate all query results and return them.

2. The method for obtaining a time-based physical table according to claim 1, characterized in that, S1 specifically includes: The system monitors startup commands. The startup commands are triggered in scenarios including the initial startup after the system's first deployment, system failure recovery restart, and system version update restart. When a startup command is detected, the system automatically enters the table partitioning initialization process. Determine the database baseline table structure. The database baseline table is a pre-configured partitioned table template table, which contains all fields, field data types, and index configurations required by the target business scenario. The target business scenario includes alarm record storage and monitoring data storage. A stable connection to the target database is established through a database connection pool. The database metadata query interface is called to read the field information, index information and table engine configuration of the baseline table, and the above information is cached in local memory. Call the time utility class to get the current system time, and convert the current time to yyyy-MM format to determine the current month; Calculate the next month based on the current month to ensure no deviation in month calculations in cross-year scenarios; convert the current month and the next month into a unified yyyyMM format time code; generate the physical table name corresponding to the current month and the physical table name corresponding to the next month according to the preset table partitioning rules; Execute SQL statements to check the existence of database tables, and query the existence status of the physical tables corresponding to the current month and the next month respectively; The existence of a physical table is determined based on the query results. If the query results return a table name record, the corresponding physical table is considered to exist. If the query results do not return any results, the corresponding physical table is considered to not exist, and the name of the non-existent table is recorded.

3. The method for obtaining a time-based physical table according to claim 2, characterized in that, S1 further includes: After determining that at least one physical partition table for the current month and the physical partition table for the next month does not exist, the physical partition table creation process is automatically started. The creation process is based on the base table field information, index information and table engine configuration cached in local memory. Based on the non-existent table name in the record, and combined with the base table structure information, the SQL statement for creating the table is automatically generated. The SQL statement for creating the table includes the table name, field definition, field data type, index creation rules, and table engine specifications. Execute the generated table creation SQL statements to create physical partitions for the current month or the next month that do not exist.

4. The method for obtaining a time-based physical table according to claim 3, characterized in that, S1 further includes: After the table creation operation is executed, the database table existence verification SQL statement is called again to query the existence status of the created partitioned table and confirm that the partitioned table has been created successfully. If the query result shows that the partitioned table already exists, the table creation operation is considered complete and the system ends the current partitioned table creation process. If the query result still shows that the partitioned table does not exist, the table creation operation is considered to have failed, the table creation failure log is recorded and the partitioned table creation process is paused. At the same time, a failure prompt is pushed to the operation and maintenance terminal.

5. The method for obtaining a time-based physical table according to claim 1, characterized in that, S2 specifically includes: The system receives a data write request, which contains business data to be written. The business data corresponds to time-series data of a target business scenario, including alarm record storage and monitoring data storage. Extract the time attribute from the business data to be written. The time attribute is a time-related field that comes with the business data, specifically: data generation time, data submission time, and business association time. The extracted time attributes are formatted and a preset table name rule is invoked. The preset standard format for the format validation is yyyy-MM-dd HH:mm:ss. The preset table name rule is a rule that combines the logical table name with the time dimension suffix. The logical table name is the table identifier name corresponding to the business scenario, and the time dimension suffix is ​​the year and month information extracted based on the time attribute. The year and month information is in the format yyyyMM. Based on the extracted time attributes, the corresponding year and month information is parsed out. The year and month are extracted from the time attributes that conform to the standard format and converted into the yyyyMM format year and month code. The logical table name is combined with the parsed yyyyMM format year and month code to generate the target month physical table name corresponding to the data to be written, thus completing the parsing of the target month physical table name.

6. The method for obtaining a time-based physical table according to claim 5, characterized in that, S2 further includes: After obtaining the physical table name of the target month through parsing, the system starts a thread-level data binding process, using the ThreadLocal mechanism to bind the physical table name of the target month to the current processing thread; The system initializes a ThreadLocal instance, which is used to store the physical table name of the target month corresponding to the current thread; the set method of the ThreadLocal instance is called, and the parsed physical table name of the target month is passed as a value to bind it to the thread currently processing the data write request; After binding is complete, the system confirms that the current thread has been associated with the physical table name of the target month.

7. The method for obtaining a time-based physical table according to claim 6, characterized in that, S2 further includes: Before the system executes the data writing to the corresponding SQL statement, the pre-configured MyBatis Plus interceptor triggers the SQL interception operation to intercept the original SQL statement to be executed, which contains the preset business logic table name; The MyBatis Plus interceptor retrieves the bound target month physical table name from the ThreadLocal instance of the current thread by calling the get method, replaces the preset logical table name in the original SQL statement with the target month physical table name, and generates the final execution SQL adapted to the target physical table. Execute the final SQL statement after replacement to write the data to be written to the physical table of the target month. Once the SQL execution is complete and the data is successfully written, the data writing operation is considered complete.

8. The method for obtaining a time-based physical table according to claim 1, characterized in that, S3 specifically includes: The system receives query requests containing a time range and extracts the query start time and query end time from the request; Perform format validation on the query start time and query end time; Extract the start year and start month from the query start time, and extract the end year and end month from the query end time, and combine them into start year-month code and end year-month code; Based on the starting and ending year and month codes, calculate all months covered by the query time range: if the starting and ending year and month codes are the same, then the month is covered; if they are different, then generate all intermediate year and month codes by incrementing the month code from the starting year and month until the ending year and month code.

9. The method for obtaining a time-based physical table according to claim 8, characterized in that, S3 further includes: Call the preset table name rules, extract the year and month code for each month covered by the query time range, and concatenate the code with the logical table name of the current query business scenario; Generate the physical table name corresponding to each month, and sort all physical table names by year and month code to form an ordered physical table list.

10. The method for obtaining a time-based physical table according to claim 9, characterized in that, S3 further includes: Perform an existence check on each physical partition in the list of physical partitions. If the partition does not exist, record the exception information and skip the query. For each existing physical partition, generate and execute a single-table query statement to obtain the query result set of each partition. Sort all result sets by the time attribute field and remove duplicate data records based on the unique identifier field; encapsulate the sorted and deduplicated data together with the exception information and return it to the business end.

Images