A database field automatic detection and conversion method, device and medium

Abstract

The embodiment of the application discloses a kind of database field automatic detection and conversion method, equipment and medium, belong to database technical field, solve the conversion of field attribute executed manually, it can lead to the problem of forming a large number of special field state of null but not null in database table. Including, extracting the user table meeting preset screening condition in database, and the field filtering of each user table is carried out, and the field to be counted is obtained;The record quantity corresponding to the user table where the field to be counted is located is acquired, and based on the comparison relationship between the preset quantity threshold and the record quantity, the corresponding table capacity determination strategy is determined;Through different table capacity determination strategy, the null value proportion corresponding to the user table where the field to be counted is located is determined;Based on the comparison relationship between the preset proportion threshold and the null value proportion, the corresponding field conversion strategy is matched, to carry out corresponding conversion to null field by field conversion strategy.

Description

Technical Field

[0001] This application relates to the field of database technology, and in particular to a method, device and medium for automatic detection and conversion of database fields. Background Technology

[0002] In backend application development, databases, as the core data storage carrier, require their table structures to continuously evolve with the iteration of business needs. In scenarios involving the addition of new business-related fields, to avoid triggering database table locks during field addition and impacting the normal operation of business in the production environment, the industry commonly adopts a step-by-step migration approach: First, the newly added field is marked as nullable to ensure that the field addition operation is completed quickly and does not block business operations. After the relevant logic of the backend application is updated and historical data is supplemented through a data backfilling mechanism, the field's attribute is then changed from nullable to not null to meet the business requirement that the field must be filled.

[0003] However, in actual production environments, field attribute conversions are often performed manually, which can lead to omissions in converting nullable fields to not nullable fields. This results in a large number of special field states in the database table that are nullable but not nullable. In other words, the field still retains the nullable identifier in the database schema definition, but there are no null values ​​in the actual stored data, thus causing a mismatch between the field schema and the data. Summary of the Invention

[0004] This application provides a method, device, and medium for automatic detection and conversion of database fields to solve the following technical problem: manual conversion of field attributes often results in the omission of converting nullable to not null, leading to a large number of special field states in the database table that can be nullable but are not nullable, thus causing a mismatch between the field schema and the data.

[0005] The embodiments of this application adopt the following technical solutions:

[0006] This application provides a method for automatic detection and conversion of database fields. The method includes: extracting user tables that meet preset filtering conditions from the database; filtering the fields in each user table to obtain fields to be counted; obtaining the number of records corresponding to the user table containing the field to be counted; determining a corresponding table capacity determination strategy based on a comparison between a preset quantity threshold and the number of records; wherein the table capacity determination strategy includes at least one of a numerical calculation strategy and a sampling estimation strategy; determining the proportion of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies; and matching a corresponding field conversion strategy based on a comparison between a preset proportion threshold and the proportion of null values, so as to convert nullable fields accordingly using the field conversion strategy.

[0007] In one implementation of this application, user tables that meet preset filtering conditions are extracted from the database, and field filtering is performed on each user table to obtain the fields to be counted. Specifically, this includes: reading database system metadata, extracting user tables that meet preset filtering conditions from the system metadata, and generating a list of tables to be scanned; wherein, the preset filtering conditions include at least one of the structural change operations or data read / write operations corresponding to the user table within a preset time range; based on preset column filtering conditions, column elimination processing is performed on each user table in the list of tables to be scanned to obtain the fields to be counted; wherein, the preset column filtering conditions include at least one of the following: system built-in columns, specified data type columns, and columns with declared non-null constraints.

[0008] In one implementation of this application, the proportion of null values ​​in the user table containing the field to be counted is determined by different table capacity determination strategies. Specifically, this includes: when the table capacity determination strategy is determined to be a numerical calculation strategy, triggering a pre-set query statement set to query the user table containing the field to be counted, and obtaining the number of null values ​​and the total number of records; and obtaining the proportion of null values ​​based on the ratio between the number of null values ​​and the total number of records.

[0009] In one implementation of this application, the proportion of null values ​​corresponding to the user table containing the field to be counted is determined through different table capacity determination strategies. Specifically, this includes: when the table capacity determination strategy is determined to be a sampling estimation strategy, extracting reference business dimensions based on current business characteristics; performing mutual information processing on each reference business dimension to filter out related dimensions; dividing each related dimension into levels based on dimension values, performing density clustering within each level, and assigning sampling weights to different clusters; extracting samples within each level of the related dimension based on the sampling weights, and determining the proportion of null values ​​in the samples corresponding to each level; and weighting and summing the proportions of null values ​​in the samples using the proportion of records in the entire table for each level as the weight, to obtain the proportion of null values ​​corresponding to the user table containing the field to be counted.

[0010] In one implementation of this application, based on the comparison between a preset proportional threshold and the proportion of null values, a corresponding field conversion strategy is matched to convert nullable fields accordingly. Specifically, this includes: when the proportion of null values ​​is 0, performing immediate field conversion or window-period field conversion on the field based on the business type associated with the field; when the proportion of null values ​​is greater than 0 but not greater than the proportional threshold, determining the field completion method in a preset completion SQL template based on the field type, and performing field conversion on the field after completion; when the proportion of null values ​​is greater than the proportional threshold, determining the generation time of the null values, determining the exception type based on the generation time, and re-checking or generating a fault work order based on the exception type in the user table where the field to be counted is located.

[0011] In one implementation of this application, after field completion, field conversion is performed, specifically including: simulating the conversion of the completed field to obtain conversion information; wherein, the conversion information includes at least one of the following: obtaining the execution plan corresponding to the conversion instruction, the resource consumption data corresponding to the execution plan, and the transaction lock duration; obtaining the predicted table lock duration based on the real-time running parameters of the user table where the field is located and the conversion information corresponding to the simulated conversion; comparing the predicted table lock duration with a preset lock duration threshold; if the predicted table lock duration is not greater than the preset lock duration threshold, the field conversion instruction is executed directly; if the predicted table lock duration is greater than the preset lock duration threshold, a batch conversion strategy is enabled.

[0012] In one implementation of this application, a batch conversion strategy is enabled, specifically including: obtaining the identifier field of the user table where the field is located, and dividing the value range of the identifier field into multiple continuous data segments based on a preset batch data volume; executing field conversion instructions sequentially for each conversion batch according to the value order of the identifier field in the data segments; applying transaction locks to the records in the data segments corresponding to the current conversion batch, releasing the transaction lock resources after the current conversion batch is completed, and executing the next conversion batch after a preset interval; wherein, the preset interval is related to the database lock resource release efficiency and the cycle duration of related business transactions; collecting the actual table locking duration and real-time database load parameters corresponding to each conversion batch in real time, and dynamically adjusting the batch data volume and preset interval of the next batch if the actual table locking duration and / or the real-time database load parameters do not meet the threshold conditions.

[0013] In one implementation of this application, after matching the corresponding field conversion strategy based on the comparison relationship between the preset ratio threshold and the proportion of null values, the method further includes: obtaining the associated attributes corresponding to each field and quantifying and assigning values ​​to the associated attributes; wherein, the associated attributes include at least one of the following: field association relationship, default value configuration status, and historical null value records; obtaining historical risk judgment data, and determining the dynamic weight values ​​corresponding to each associated attribute based on historical error information and business feedback factors; obtaining the risk values ​​corresponding to each field based on the quantified assignment results and the dynamic weight values ​​corresponding to each associated attribute; and determining the conversion risk level based on the difference between the risk value and the preset risk threshold.

[0014] This application provides an automatic database field detection and conversion device, including: at least one processor; and a memory communicatively connected to the at least one processor; wherein the memory stores instructions executable by the at least one processor, which, when executed by the at least one processor, enable the at least one processor to: extract user tables that meet preset filtering conditions from the database, and perform field filtering on each user table to obtain fields to be counted; obtain the number of records corresponding to the user table containing the field to be counted, and determine a corresponding table capacity determination strategy based on a comparison between a preset quantity threshold and the number of records; wherein the table capacity determination strategy includes at least one of a numerical calculation strategy and a sampling estimation strategy; determine the proportion of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies; and match a corresponding field conversion strategy based on a comparison between a preset proportion threshold and the proportion of null values, so as to convert nullable fields accordingly through the field conversion strategy.

[0015] This application provides a non-volatile computer storage medium storing computer-executable instructions. These instructions are configured to: extract user tables from a database that meet preset filtering conditions, and filter the fields in each user table to obtain fields to be counted; obtain the number of records corresponding to the user table containing the field to be counted, and determine a corresponding table capacity determination strategy based on a comparison between a preset quantity threshold and the number of records; wherein the table capacity determination strategy includes at least one of a numerical calculation strategy and a sampling estimation strategy; determine the proportion of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies; and match a corresponding field conversion strategy based on a comparison between a preset proportion threshold and the proportion of null values, so as to convert nullable fields accordingly through the field conversion strategy.

[0016] The above-mentioned technical solutions adopted in this application embodiment can achieve the following beneficial effects: Firstly, by extracting and filtering user tables that meet the conditions to obtain the fields to be counted, this application embodiment can accurately focus on the target object, avoid irrelevant tables and fields, and further filter redundant fields that do not need to be counted, reducing subsequent calculations and improving process efficiency. Secondly, by comparing the number of records with a preset threshold to determine the table capacity judgment strategy, differentiated processing is achieved, balancing statistical accuracy and performance, making the statistical process adaptable to different data scales, and taking into account both efficiency and accuracy. Furthermore, this application embodiment determines the proportion of null values ​​through different strategies, ensuring that the null value statistics results are reliable and efficient. Finally, based on the null value proportion and the preset threshold matching conversion strategy, conversion is performed to automate field conversion. The conversion scheme is dynamically selected based on the null value proportion, avoiding the risk of blind conversion, reducing manual operation costs, and ensuring consistency between the database schema and data state. Attached Figure Description

[0017] To more clearly illustrate the technical solutions in the embodiments of this application or the prior art, the drawings used in the description of the embodiments or the prior art will be briefly introduced below. Obviously, the drawings described below are only some embodiments recorded in this application. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort. In the drawings:

[0018] Figure 1 A flowchart illustrating an automatic database field detection and conversion method provided in this application embodiment;

[0019] Figure 2 This is a schematic diagram of the structure of a database field automatic detection and conversion device provided in an embodiment of this application.

[0020] Figure label:

[0021] 200: Database field automatic detection and conversion device; 201: Processor; 202: Memory. Detailed Implementation

[0022] This application provides a method, device, and medium for automatic detection and conversion of database fields.

[0023] To enable those skilled in the art to better understand the technical solutions in this application, the technical solutions in the embodiments of this application will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of this application, and not all embodiments. Based on the embodiments of this specification, all other embodiments obtained by those skilled in the art without creative effort should fall within the scope of protection of this application.

[0024] The technical solutions proposed in the embodiments of the present invention will be described in detail below with reference to the accompanying drawings.

[0025] Figure 1 This is a flowchart illustrating an automatic database field detection and conversion method provided in an embodiment of this application. Figure 1 As shown, the automatic detection and conversion method for database fields includes the following steps:

[0026] Step 101: Extract user tables that meet the preset filtering conditions from the database, and filter the fields of each user table to obtain the fields to be counted.

[0027] In one implementation of this application, for the currently detected database, its corresponding CPU utilization, I / O throughput, and database connection idle ratio are periodically collected. If all three are lower than the preset threshold or the current time falls within the maintenance window, the subsequent statistical process is triggered.

[0028] In one implementation of this application, database system metadata is read, and user tables that meet preset filtering conditions are extracted from the system metadata to generate a list of tables to be scanned. The preset filtering conditions include at least one of the following: a structural change operation or a data read / write operation corresponding to the user table within a preset time range. Based on the preset column filtering conditions, each user table in the list of tables to be scanned undergoes column removal processing to obtain the fields to be counted. The preset column filtering conditions include at least one of the following: a system-built-in column, a column of a specified data type, and a column with declared non-null constraints.

[0029] Specifically, when the target database is determined to be PostgreSQL, its core directory tables are located and accessed. Specifically, `pg_class` (stores basic table information), `pg_stat_all_tables` (records table statistics), `pg_depend` (reflects object dependencies), `pg_attribute` (stores field attributes), and `pg_stat_user_tables` (user table statistics) need to be read to obtain key information such as user table structure change records and data read / write frequency. Next, user tables are filtered. Operation records of user tables are extracted from the integrated system metadata to determine whether each user table meets the conditions for structure change operations or data read / write operations. Structure change operations include DDL (Data Definition Language) operations that occurred within a preset time range, such as 30 days. Data read / write operations include a large number of writes or updates within a preset time range. User tables that meet either condition are filtered out, generating a list of tables to be scanned.

[0030] Furthermore, for the user tables in the list of tables to be scanned, this application provides preset column filtering conditions, including system-built-in columns, fields without business meaning, columns with specified data types, and columns with declared non-null constraints. Fields without business meaning include oid, ctid, etc.; columns with specified data types include JSONB, TEXT, and binary large object type columns; columns with declared non-null constraints refer to fields in the table structure that are explicitly marked as not null. Column removal processing is performed on each user table in the list of tables to be scanned. For each user table, the attribute information of all its fields is determined, and the preset column filtering conditions are used to sequentially remove system-built-in columns, columns with specified data types, and columns with declared non-null constraints, retaining only ordinary business columns. The remaining fields after processing for each user table are summarized to form the fields to be counted for that user table.

[0031] Step 102: Obtain the number of records in the user table corresponding to the field to be counted, and determine the corresponding table capacity determination strategy based on the comparison between the preset number threshold and the number of records.

[0032] In one implementation of this application, the number of rows in the user table corresponding to the field to be counted is obtained. If the number of rows is less than a preset threshold, the table capacity determination strategy is determined to be a numerical calculation strategy. If the number of rows is not less than the preset threshold, the table capacity determination strategy is determined to be a sampling estimation strategy.

[0033] Step 103: Determine the percentage of null values ​​in the user table corresponding to the field to be counted by using different table capacity determination strategies.

[0034] In one implementation of this application, when the table capacity determination strategy is determined to be a numerical calculation strategy, a pre-set set of query statements is triggered to query the user table containing the field to be counted, so as to obtain the number of null values ​​and the total number of records, and the proportion of null values ​​is obtained based on the ratio between the number of null values ​​and the total number of records.

[0035] Specifically, the system calls a total record count query statement from the pre-defined query statement set to perform a full query on the user table containing the field to be counted. By traversing and counting all records in the table, the total number of records in the user table is obtained. Next, the system calls a non-null record count query statement from the pre-defined query statement set for the field to be counted. This performs a full scan only on the field to be counted, counting the number of records with all non-null values ​​in that field. The total number of records is subtracted from the number of non-null records to obtain the number of null values ​​in the field to be counted. Then, the ratio of the number of null values ​​to the total number of records is calculated to obtain the percentage of null values.

[0036] In one implementation of this application, when the table capacity determination strategy is determined to be a sampling estimation strategy, reference business dimensions are extracted based on the current business characteristics. Mutual information processing is performed on each reference business dimension to filter out related dimensions. Each related dimension is hierarchically divided based on its dimension value. Density clustering is performed within each level, and sampling weights are assigned to different clusters. Samples are extracted within each level of the related dimension based on these sampling weights, and the proportion of null values ​​in the samples corresponding to each level is determined. Using the proportion of records in the entire table at each level as weight, the proportions of null values ​​in the samples are weighted and summed to obtain the proportion of null values ​​corresponding to the user table containing the field to be statistically analyzed.

[0037] Specifically, when the table capacity determination strategy is determined to be a sampling estimation strategy, business dimensions that are related to the field to be counted are first extracted from the business dictionary, table relationships, and historical operation logs of the PostgreSQL database. These dimensions are strong in relation to business logic, such as user level, registration region, and activity level stratification in the user table. For each extracted reference business dimension, the mutual information value between that dimension and the null state of the field to be counted is calculated to determine the degree of correlation between them. This embodiment of the application sets a mutual information threshold, and reference business dimensions with mutual information values ​​not less than this threshold are selected as related dimensions. For each selected related dimension, it is divided into multiple continuous and non-overlapping levels according to the specific value range of the dimension or business classification rules. For example, the registration region dimension is divided into levels according to regions such as North China, East China, and South China. Each level corresponds to a specific value range of the related dimension, ensuring that the level division conforms to business understanding and reflects differences in data distribution.

[0038] For each association dimension, density-based clustering is used to cluster records within each level, identifying core clusters with dense null value distributions and non-core clusters with sparse null value distributions. Core clusters with dense null values ​​are assigned higher sampling weights to ensure sufficient sampling to reflect the true distribution of null values, while non-core clusters are assigned lower sampling weights to control the total number of samples while ensuring data representativeness. Based on the sampling weights of different clusters within each level, a corresponding number of samples are randomly selected from each cluster to ensure that the sample size is proportional to the weight. After extraction, the number of null values ​​and the total number of samples within each level are counted to calculate the proportion of null values ​​in the samples at each level.

[0039] Finally, the percentage of records for each level of each related dimension in the entire table is obtained by querying the PostgreSQL system table. This percentage is then used as a weight and weighted by the percentage of null values ​​in the corresponding level. By summing the results of "level record percentage × sample null value percentage" for all levels, the final percentage of null values ​​in the user table containing the field to be analyzed is obtained.

[0040] Step 104: Based on the comparison relationship between the preset ratio threshold and the proportion of null values, match the corresponding field conversion strategy to convert nullable fields accordingly.

[0041] In one implementation of this application, when the percentage of null values ​​is 0, the field is converted either instantly or within a window period based on the business type related to the field. When the percentage of null values ​​is greater than 0 but not greater than a threshold, the field completion method is determined in a pre-set completion SQL template based on the field type, and the field is converted after completion. When the percentage of null values ​​is greater than the threshold, the generation time of the null values ​​is determined, and the exception type is determined based on the generation time. Based on the exception type, the user table containing the field to be counted is then re-checked or a fault work order is generated.

[0042] Specifically, when the percentage of null values ​​is 0, the business type associated with the field to be counted is extracted from the database business dictionary and the associated metadata table, such as core transaction type, ordinary query type, and log record type. If the associated business type is ordinary query type, an immediate field conversion is triggered, and the non-null constraint addition operation is directly executed; if the associated business type is core transaction type, that is, there are high-frequency write or update operations, it is determined that a window period conversion is required, and the database maintenance window calendar is automatically queried to perform field conversion during the preset maintenance period to avoid the conversion operation affecting business continuity.

[0043] Furthermore, when the percentage of null values ​​is greater than 0 but not greater than the percentage threshold, the data type of the field to be counted is first identified, and the corresponding completion method is matched from the pre-set completion SQL template library. Specifically, numeric fields are matched and filled with the business default value template, date fields are matched and filled with the most recent valid date template, and character fields are matched and filled with the standard default text template. During the maintenance period, field non-null constraint conversion is performed.

[0044] Furthermore, when the proportion of null values ​​exceeds a threshold, the generation time of all null value records for the field to be counted is extracted from the PostgreSQL transaction log and table creation or update records. The number of null values ​​generated is then summarized by time dimension to form a null value generation time distribution curve. If null values ​​are concentrated in a specific time period and no new null values ​​are added subsequently, it is determined to be a one-time anomaly. In this case, a re-detection process is triggered, and the null value proportion is counted again after the impact of the fault is eliminated. If null values ​​are evenly distributed across multiple time periods and are continuously generated, it is determined to be a persistent anomaly. In this case, a fault ticket is generated, which includes information such as the null value generation time distribution and related business modules, and is pushed to the development team's ticket system.

[0045] In one implementation of this application, when performing field conversion after field completion, a simulated conversion is performed on the completed field to obtain conversion information. This conversion information includes at least one of the following: the execution plan corresponding to the conversion instruction, the resource consumption data corresponding to the execution plan, and the transaction lock duration. Based on the real-time running parameters of the user table containing the field and the conversion information corresponding to the simulated conversion, a predicted table lock duration is obtained. The predicted table lock duration is compared with a preset lock duration threshold. If the predicted table lock duration is not greater than the preset lock duration threshold, the field conversion instruction is executed directly. If the predicted table lock duration is greater than the preset lock duration threshold, a batch conversion strategy is enabled.

[0046] Specifically, after completing the data completion operation for the field to be analyzed and confirming that no null values ​​remain, a simulation conversion mechanism is triggered to simulate the execution of the non-null conversion instruction for the field to be executed. This process only generates conversion-related information without actually modifying field attributes, avoiding impact on production data, while ensuring complete acquisition of key technical parameters during the execution of the conversion instruction. During the simulation conversion process, the following conversion information is acquired: first, the execution plan corresponding to the conversion instruction, including the table scan method, index usage, and related object processing logic involved in the execution process; second, the resource consumption data corresponding to the execution plan, including estimated CPU utilization, memory usage, and I / O operation counts; and third, the transaction lock duration, recording the lock resource occupation period and potential lock waiting situations by simulating the process of transaction lock acquisition and release. Through the PostgreSQL system monitoring view, the running status data of the user table containing the field to be analyzed is collected in real time, mainly including the current number of active transactions in the table, data page cache hit rate, overall database I / O throughput rate, and real-time trend of table data volume changes. These parameters reflect the actual operating load of the current database, providing a real-time environmental dimension reference for predicting table lock duration.

[0047] Furthermore, a pre-defined table lock duration prediction model is invoked, using the collected conversion information and real-time operating parameters as input variables. The complexity of the execution plan and I / O throughput requirements in the resource consumption data are used as basic influencing factors, while the number of active transactions and cache hit rate are used as dynamic adjustment factors. A preliminary predicted table lock duration is calculated through multi-variable weighted calculation. Simultaneously, the preliminary results are calibrated by referring to historical table lock duration data for similar field conversions to ensure that the predicted values ​​more closely match the actual scenario and reduce the deviation between theoretical calculations and actual execution. The predicted table lock duration is compared with a preset lock duration threshold. If the predicted duration is not greater than the threshold, it indicates that the conversion operation has a controllable impact on business operations, and the field conversion instruction is executed directly within the current maintenance period. During execution, lock resource usage is monitored in real time to ensure no abnormal lock waiting. If the predicted duration is greater than the threshold, a batch conversion strategy is immediately activated, suspending the current full conversion plan and entering the batch conversion process design stage. By splitting the data volume, the lock occupation time of a single conversion is reduced, avoiding prolonged table locks that could cause business blockage.

[0048] In one implementation of this application, a batch conversion strategy is enabled. Specifically, this includes obtaining the identifier field of the user table containing the field, and dividing the value range of the identifier field into multiple continuous data segments based on a preset batch data volume. Following the value order of the identifier field in each data segment, field conversion instructions are executed sequentially for each conversion batch. Transaction locks are applied to the records within the corresponding data segment of the current conversion batch. After the current conversion batch is completed, the transaction lock resources are released, and the next conversion batch is executed after a preset interval. The preset interval is related to the database lock resource release efficiency and the cycle length of associated business transactions. The actual table locking duration and real-time database load parameters corresponding to each conversion batch are collected in real time. If the actual table locking duration and / or the real-time database load parameters do not meet the threshold conditions, the batch data volume and preset interval for the next batch are dynamically adjusted.

[0049] Specifically, the identifier field in the user table containing the field to be converted is identified, prioritizing the primary key field; if the primary key does not exist, a unique index field is selected as the identifier field. Based on the preset batch data volume and the minimum and maximum values ​​of the identifier field, its value range is evenly divided into multiple continuous and non-overlapping data segments. Each segment corresponds to a conversion batch, ensuring no data duplication or omission between batches. The field conversion instructions for each conversion batch are triggered sequentially according to the ascending order of the identifier field values ​​of the data segments. Before executing the current batch conversion, it is first verified whether the previous batch has been completed to avoid batch execution chaos. The conversion instructions target the data segment corresponding to the current batch, adjusting only the field non-null attribute of records within that segment, without affecting data in other segments, reducing the resource consumption of a single conversion.

[0050] Furthermore, during the execution of the current conversion batch, transaction locks are applied to all records within the corresponding data segment of that batch to ensure that data is not concurrently modified during the conversion process, thus guaranteeing data consistency. Once the current batch conversion is complete, the transaction lock resources are immediately released to avoid prolonged lock occupation leading to business blockage. Subsequently, a waiting period is established according to a preset duration. Only after the lock resources are fully released and the database load stabilizes is the next conversion batch executed, balancing conversion efficiency and business availability. During the execution of each conversion batch, two core metrics are collected in real time using database monitoring tools: the actual table locking duration for the current batch and the real-time database load parameters. The collected actual table locking duration is compared with a preset lock duration threshold, and the real-time database load parameters are compared with a preset load threshold. If the actual table locking duration exceeds the threshold, or the database load parameters are abnormal, the parameters for the next batch are automatically adjusted: reducing the batch data volume and extending the preset interval duration. This dynamic adjustment reduces the pressure on the database from subsequent batches, ensuring a stable and controllable batch conversion process.

[0051] In one implementation of this application, after matching the corresponding field conversion strategy based on the comparison between a preset proportional threshold and the proportion of null values, it is also necessary to obtain the associated attributes corresponding to each field and quantify and assign values ​​to these associated attributes. The associated attributes include at least one of the following: field association relationship, default value configuration status, and historical null value records. Historical risk assessment data is obtained, and based on historical error information and business feedback factors, the dynamic weight values ​​corresponding to each associated attribute are determined. Based on the quantified assignment results and the dynamic weight values ​​corresponding to each associated attribute, the risk value corresponding to each field is obtained. Based on the difference between the risk value and the preset risk threshold, the conversion risk level is determined.

[0052] Specifically, after calculating the null value ratio and matching the conversion strategy, the associated attributes of each field to be converted are extracted from database metadata, business dictionary, and historical operation logs. These attributes include field relationships, default value configuration status, and historical null value records. Based on preset quantification rules, each associated attribute is assigned a value; for example, a core foreign key relationship is assigned 10 points, a fixed default value is assigned 8 points, and no null values ​​in the past 30 days are assigned 9 points, transforming qualitative attributes into calculable quantitative indicators. The historical risk assessment dataset is retrieved, containing the quantified values ​​of associated attributes from past field conversion cases, post-conversion error messages, and business feedback results. By analyzing the correlation between historical error messages and each associated attribute, a basic weight is calculated. Combined with business feedback factors, the basic weight is dynamically adjusted to generate real-time dynamic weight values ​​for each associated attribute, ensuring that the weight allocation aligns with actual business risk scenarios. A weighted summation algorithm is used to multiply the quantified value of each field's associated attribute by its corresponding dynamic weight value and then sum them to obtain the original risk value for that field. For example, if a field's correlation quantification value is 8 points (weight 30%), the default value configuration quantification value is 6 points (weight 25%), and the historical null value record quantification value is 9 points (weight 45%), then the original risk value is 8 × 0.3 + 6 × 0.25 + 9 × 0.45 = 8.25 points. The original risk value is then normalized to a standard risk value range of 0-100 points for easier subsequent risk assessment.

[0053] Furthermore, the calculated standard risk value is compared with a preset risk threshold. The risk level is determined by calculating the difference between the risk value and the threshold: a risk value below the low-risk threshold with a large positive difference is classified as low-risk; a risk value between the low-risk and medium-risk thresholds with a near-zero difference is classified as medium-risk; and a risk value above the medium-risk threshold with a large negative absolute difference is classified as high-risk. Different risk levels correspond to different processing strategies. For example, low-risk levels can be automatically converted, while high-risk levels require manual review before a decision is made to ensure that the risk is controllable.

[0054] Figure 2 This is a schematic diagram of the structure of a database field automatic detection and conversion device provided in an embodiment of this application. Figure 2As shown, the construction safety monitoring equipment 200 for laying thermal pipelines includes: at least one processor 201; and a memory 202 communicatively connected to the at least one processor 201. The memory 202 stores instructions executable by the at least one processor 201, which, when executed, enable the at least one processor 201 to: extract user tables from a database that meet preset filtering conditions, and perform field filtering on each user table to obtain fields to be counted; obtain the number of records corresponding to the user table containing the field to be counted, and determine a corresponding table capacity determination strategy based on a comparison between a preset quantity threshold and the number of records; wherein the table capacity determination strategy includes at least one of a numerical calculation strategy and a sampling estimation strategy; determine the proportion of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies; and match a corresponding field conversion strategy based on a comparison between a preset proportion threshold and the proportion of null values, so as to convert nullable fields accordingly through the field conversion strategy.

[0055] This application provides a non-volatile computer storage medium storing computer-executable instructions. The computer-executable instructions are configured to: extract user tables from a database that meet preset filtering conditions, and filter the fields in each user table to obtain fields to be counted; obtain the number of records corresponding to the user table containing the field to be counted, and determine a corresponding table capacity determination strategy based on a comparison between a preset quantity threshold and the number of records; wherein the table capacity determination strategy includes at least one of a numerical calculation strategy and a sampling estimation strategy; determine the proportion of null values ​​corresponding to the user table containing the field to be counted through different table capacity determination strategies; and match a corresponding field conversion strategy based on a comparison between a preset proportion threshold and the proportion of null values, so as to convert nullable fields accordingly through the field conversion strategy.

[0056] The various embodiments in this application are described in a progressive manner. Similar or identical parts between embodiments can be referred to mutually. Each embodiment focuses on describing the differences from other embodiments. In particular, the embodiments of apparatus, devices, and non-volatile computer storage media are basically similar to the method embodiments, so the descriptions are relatively simple; relevant parts can be referred to the descriptions of the method embodiments.

[0057] The above descriptions are merely embodiments of this application and are not intended to limit the scope of this application. For those skilled in the art, various modifications and variations can be made to the embodiments of this application. These modifications or substitutions do not cause the essence of the corresponding technical solutions to depart from the spirit and scope of the technical solutions in the embodiments of this application.

Claims

1. A method for automatic detection and conversion of database fields, characterized in that, The method includes: Extract user tables that meet the preset filtering conditions from the database, and filter the fields of each user table to obtain the fields to be counted; Obtain the number of records corresponding to the user table containing the field to be counted, and determine the corresponding table capacity determination strategy based on the comparison relationship between the preset number threshold and the number of records; wherein, the table capacity determination strategy includes at least one of the numerical calculation strategy and the sampling estimation strategy; By using different table capacity determination strategies, the percentage of null values ​​corresponding to the user table containing the field to be counted is determined. When the table capacity determination strategy is determined to be a numerical calculation strategy, a preset set of query statements is triggered to query the user table containing the field to be counted, and the number of null values ​​and the total number of records are obtained; the proportion of null values ​​is obtained based on the ratio between the number of null values ​​and the total number of records. When the table capacity determination strategy is determined to be the sampling estimation strategy, reference business dimensions are extracted based on the current business characteristics; mutual information processing is performed on each reference business dimension to filter out related dimensions; each related dimension is hierarchically divided based on the dimension value, density clustering is performed within each level, and sampling weights are assigned to different clusters; samples are extracted within each level of the related dimension based on the sampling weights, and the proportion of null values ​​in the samples corresponding to each level is determined; the proportion of null values ​​in the samples is weighted and summed using the proportion of records in the entire table for each level as the weight, to obtain the proportion of null values ​​in the user table where the field to be counted is located; Based on the comparison relationship between the preset ratio threshold and the proportion of null values, a corresponding field conversion strategy is matched to convert nullable fields accordingly.

2. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, The step of extracting user tables that meet preset filtering conditions from the database and filtering the fields in each user table to obtain the fields to be counted specifically includes: Read the database system metadata, extract user tables that meet preset filtering conditions from the system metadata, and generate a list of tables to be scanned; wherein, the preset filtering conditions include at least one of the structural change operations or data read / write operations corresponding to the user tables within a preset time range; Based on preset column filtering conditions, each user table in the list of tables to be scanned is subjected to column removal processing to obtain the fields to be counted; wherein, the preset column filtering conditions include at least one of the following: system built-in columns, specified data type columns, and columns with declared non-null constraints.

3. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, The comparison between the preset proportional threshold and the proportion of null values, and the matching of corresponding field conversion strategies, to perform corresponding conversions on nullable fields through the field conversion strategies, specifically includes: When the percentage of null values ​​is 0, the field is converted either instantly or within a window period based on the business type that is related to the field. When the proportion of null values ​​is greater than 0 and not greater than the proportion threshold, the field completion method is determined in the preset completion SQL template based on the field type, and the field is converted after the field is completed; If the proportion of null values ​​is greater than the proportion threshold, the generation time of the null values ​​in the field is determined, and the anomaly type is determined based on the generation time. Based on the anomaly type, the user table containing the field to be counted is re-inspected or a fault work order is generated.

4. The method for automatic detection and conversion of database fields according to claim 3, characterized in that, The process of performing field conversion on the field after field completion specifically includes: The completed fields are simulated and transformed to obtain transformation information; wherein, the transformation information includes at least one of the following: the execution plan corresponding to the transformation instruction, the resource consumption data corresponding to the execution plan, and the transaction lock occupancy duration; Based on the real-time running parameters of the user table where the field is located and the conversion information corresponding to the simulated conversion, the predicted table locking duration is obtained; The predicted table lock duration is compared with the preset lock duration threshold. If the predicted table lock duration is not greater than the preset lock duration threshold, the field conversion instruction is executed directly. If the predicted lock duration exceeds the preset lock duration threshold, then the batch conversion strategy is enabled.

5. The method for automatic detection and conversion of database fields according to claim 4, characterized in that, The implementation of the batch conversion strategy specifically includes: Obtain the identifier field of the user table where the field is located, and divide the value range of the identifier field into multiple continuous data segments based on the preset batch data volume; The field conversion instructions are executed sequentially for each conversion batch according to the value order of the identifier field of the data segment; A transaction lock is applied to the records within the data segment corresponding to the current conversion batch. After the current conversion batch is completed, the transaction lock resource is released, and the next conversion batch is executed after a preset interval. The preset interval is related to the database lock resource release efficiency and the cycle length of the associated business transaction. The actual table locking duration and real-time database load parameters corresponding to each conversion batch are collected in real time. If the actual table locking duration and / or the real-time database load parameters do not meet the threshold conditions, the batch data volume and preset duration of the next batch are dynamically adjusted.

6. The method for automatic detection and conversion of database fields according to claim 1, characterized in that, After matching the corresponding field conversion strategy based on the comparison relationship between the preset ratio threshold and the proportion of null values, the method further includes: Obtain the associated attributes corresponding to each field, and assign quantitative values ​​to the associated attributes; wherein, the associated attributes include at least one of the following: field association relationship, default value configuration status, and historical null value record; Obtain historical risk assessment data, and determine the dynamic weight values ​​corresponding to each of the aforementioned related attributes based on historical error information and business feedback factors; Based on the quantitative assignment results and the dynamic weight values ​​corresponding to each of the associated attributes, the risk values ​​corresponding to each of the fields are obtained. The risk level is determined based on the difference between the risk value and the preset risk threshold.

7. A database field automatic detection and conversion device, characterized in that, The device includes a memory for storing computer program instructions and a processor for executing the program instructions, wherein when the computer program instructions are executed by the processor, the device is triggered to perform the method described in any one of claims 1-6.

8. A non-volatile computer storage medium storing computer-executable instructions, characterized in that, The computer-executable instructions are capable of performing the method described in any one of claims 1-6.

Images