Database field type adjusting method and device
By periodically monitoring the database field length ratio and automatically adjusting the field type, the data writing failure caused by improper database field type design is solved, ensuring the successful progress of the writing service.
Patent Information
- Application Number
- CN202311841665.0
- Authority / Receiving Office
- CN · China
- Patent Type
- Applications(China)
- Current Assignee / Owner
- Filing Date
- 2023-12-28
- Publication Date
- 2025-07-08
AI Technical Summary
In a business system, improper design of the database field type causes data to exceed the length limit when writing, resulting in the write service failure.
Periodically obtain the maximum data length value under each target field inserted into the target database within the preset time period, calculate the length ratio, and automatically adjust the field type according to the relationship between the ratio and the preset threshold.
Automatic adjustment of database field types is realized, avoiding the problem of data being unable to be written and ensuring the successful progress of writing services.
Smart Images
Figure CN120277047A_ABST
Abstract
Description
Technical Field
[0001] This document relates to database technology, especially a method and device for adjusting database field types. Background Art
[0002] During the construction of business systems, databases are generally used for storage.
[0003] In related technologies, it is necessary to design the field types of the database in advance according to the types of data, and then write the data into the fields according to the designed field types of the database.
[0004] However, each field type has a corresponding length limit. When the data to be written exceeds the corresponding length limit, the problem of data being unable to be written will occur, resulting in the failure of the write operation. Summary of the Invention
[0005] This application provides a method and device for adjusting database field types, which can automatically adjust the database field types, avoid the problem of data being unable to be written, and ensure the successful execution of the write operation.
[0006] On the one hand, this application provides a method for adjusting database field types, including:
[0007] Periodically obtain the maximum length of the data inserted into each target field in the target database within a preset time period;
[0008] For each of the target fields, obtain the length ratio of the obtained maximum length to the preset maximum length value corresponding to the target field, and adjust the field type corresponding to the target field according to the relationship between the length ratio and a preset ratio threshold.
[0009] On the other hand, this application provides a fault introduction device, including: a memory and a processor, where the memory is used to store an executable program;
[0010] The processor is used to read and execute the executable program to implement the above method for adjusting database field types.
[0011] Compared with related technologies, this application includes periodically obtaining the maximum length of the data inserted into each target field in the target database within a preset time period; for each of the target fields, obtaining the length ratio of the obtained maximum length to the preset maximum length value corresponding to the target field, and adjusting the field type corresponding to the target field according to the relationship between the length ratio and a preset ratio threshold. Therefore, it realizes the automatic adjustment of database field types, avoids the problem of data being unable to be written, and ensures the successful execution of the write operation.
[0012] Other features and advantages of the present application will be set forth in the following description, and in part will be obvious from the description, or can be learned by practice of the present application. Other advantages of the present application can be realized and obtained by the solutions described in the description and the drawings. Description of the Drawings
[0013] The drawings are used to provide an understanding of the technical solutions of the present application and constitute a part of the description. They are used together with the embodiments of the present application to explain the technical solutions of the present application and do not constitute a limitation to the technical solutions of the present application.
[0014] Figure 1 It is a schematic flowchart of a method for adjusting the type of database fields provided for an embodiment of the present application. Detailed Embodiments
[0015] The present application describes multiple embodiments, but the description is exemplary rather than restrictive, and it will be obvious to those of ordinary skill in the art that there can be more embodiments and implementation solutions within the scope of the embodiments described in the present application. Although many possible feature combinations are shown in the drawings and discussed in the detailed embodiments, many other combinations of the disclosed features are also possible. Unless specifically restricted, any feature or element of any embodiment can be combined with any other feature or element in any other embodiment, or can replace any other feature or element in any other embodiment.
[0016] The present application includes and contemplates combinations with features and elements known to those of ordinary skill in the art. The embodiments, features, and elements already disclosed in the present application can also be combined with any conventional features or elements to form unique inventive solutions defined by the claims. Any feature or element of any embodiment can also be combined with features or elements from other inventive solutions to form another unique inventive solution defined by the claims. Therefore, it should be understood that any feature shown and / or discussed in the present application can be implemented alone or in any suitable combination. Therefore, the embodiments are not subject to other limitations except those made according to the appended claims and their equivalents. In addition, various modifications and changes can be made within the scope of protection of the appended claims.
[0017] In addition, when describing representative embodiments, the specification may have presented the method and / or process as a specific sequence of steps. However, to the extent that the method or process does not depend on the specific order of the steps described herein, the method or process should not be limited to the specific order of steps described. As will be understood by those of ordinary skill in the art, other step orders are possible. Therefore, the specific order of steps set forth in the specification should not be construed as a limitation on the claims. In addition, the claims directed to the method and / or process should not be limited to performing their steps in the order written, as those skilled in the art can readily understand that these orders can vary and still remain within the spirit and scope of the embodiments of the present application.
[0018] An embodiment of the present application provides a method for adjusting the database field type, as Figure 1 shown, including:
[0019] Step 101, periodically obtain the maximum value of the lengths of the data inserted into each target field in the target database within a preset time period;
[0020] Step 102, for each of the target fields, obtain the length ratio of the obtained maximum length value to the preset maximum length value corresponding to the target field, and adjust the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold.
[0021] In the related art, there is no corresponding monitoring measure for the field type of the database, and it is impossible to know the current usage situation. When the business fails or some field information cannot be written, the field type is changed to solve the problem. The method for adjusting the database field type provided by the application embodiment monitors the usage situation of the field type during the process of using the database. The main purpose of the present invention is to solve the problem of monitoring the usage situation of the field type during the process of using the database.
[0022] Exemplarily, when a field type adjustment is required, the database administrator and developer can be confirmed by email and text message. After both parties confirm that there is no error, click confirmation in the system to change the field type.
[0023] The method for adjusting the database field type provided by the embodiment of the present application periodically obtains the maximum value of the lengths of the data inserted into each target field in the target database within a preset time period; for each of the target fields, obtains the length ratio of the obtained maximum length value to the preset maximum length value corresponding to the target field, and adjusts the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold, thereby realizing the automatic adjustment of the database field type, avoiding the problem that data cannot be written, and thus ensuring the successful progress of the write operation.
[0024] In an exemplary instance, the preset ratio threshold includes: a preset upper threshold and a preset lower ratio threshold.
[0025] In an exemplary instance, the target database includes: a SQL Server database and a MySQL database.
[0026] In an exemplary instance, the field types corresponding to the target fields include: numeric type, character type.
[0027] In an exemplary instance, when the field type corresponding to the target field is the numeric type, adjusting the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold includes:
[0028] When the difference between the length ratio and the preset upper ratio threshold is equal to the preset difference, adjust the numeric type corresponding to the target field to a numeric type with a larger range than the current numeric type;
[0029] When the length ratio is less than the preset lower ratio threshold and confirmation information for numeric type adjustment is obtained, adjust the numeric type corresponding to the target field to a numeric type with a smaller range than the current numeric type.
[0030] Exemplarily, the length ratio gradually increases. When the difference between the length ratio and the preset upper ratio threshold is equal to the preset difference, it indicates that the length ratio is close to the preset upper ratio threshold, which means that the data inserted under the target field may exceed the storage length of the current data type. Therefore, the numeric type corresponding to the target field is adjusted to a numeric type with a larger range than the current numeric type so that longer data can be stored under the target field subsequently. When the length ratio is less than the preset lower ratio threshold, it means that the storage length of the data inserted under the target field uses fewer storage bits of the current data type. Therefore, the numeric type corresponding to the target field is adjusted to a numeric type with a smaller range than the current numeric type to avoid waste of storage length, thereby saving resource overhead.
[0031] Exemplarily, the obtained confirmation information for numeric type adjustment can specifically be obtained by sending text messages and email alerts to the developers-to-be and getting a return after confirmation by the developers-to-be.
[0032] Exemplarily, the maximum value of the data length inserted under each target field in the target database is obtained through the max() function. Adjusting the field type corresponding to the target field is achieved by automatically generating an SQL field type change instruction, such as: alter table xx modify column - field name, new field type.
[0033] In an exemplary instance, when the target database is a SQL Server database, the range of the digital types corresponding to the target field from small to large includes: tinyint, smallint, int, and bigint.
[0034] Exemplarily, the descriptions and storage bytes of the digital types tinyint, smallint, int, and bigint in the SQL Server database are shown in Table 1.
[0035]
[0036] Table 1
[0037] In an exemplary instance, when the target database is a MySQL database, the range of the digital types corresponding to the target field from small to large includes: TINYINT, SMALLINT, INT, MEDIUMINT, and BIGINT.
[0038] Exemplarily, the descriptions of the digital types TINYINT, SMALLINT, INT, MEDIUMINT, and BIGINT in the MySQL database are shown in Table 2.
[0039]
[0040] Table 2
[0041] Among them, "size" in Table 2 represents the maximum number of digits.
[0042] In an exemplary instance, when the field type corresponding to the target field is the character type, adjusting the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold includes:
[0043] When the difference between the length ratio and the preset upper limit ratio threshold is equal to the preset difference, adjust the upper limit of the string length of the character type corresponding to the target field to a higher upper limit of the string length than the current one;
[0044] When the length ratio is less than the lower preset ratio threshold and the confirmation information for adjusting the upper limit of the string length is obtained, adjust the upper limit of the string length of the character type corresponding to the target field to a lower upper limit of the string length than the current one.
[0045] Exemplarily, the length ratio gradually increases. When the difference between the length ratio and the preset upper limit ratio threshold is equal to the preset difference, it indicates that the data inserted under the target field will exceed the string length upper limit of the current character type. Therefore, the character type corresponding to the target field is adjusted to a string length upper limit higher than the current one, so that subsequent longer data can be stored under the target field.
[0046] Exemplarily, the confirmation information for the adjusted string length upper limit obtained can specifically be obtained by sending text messages and email alerts to the developers-to-be and then getting their confirmation and return.
[0047] In an exemplary instance, when the target database is a MySQL database, the method further includes:
[0048] When the string length upper limit of the character type corresponding to the target field has been adjusted to the maximum upper limit and the length ratio is greater than the preset upper limit ratio threshold, the character type corresponding to the target field is adjusted to the TEXT type.
[0049] In an exemplary instance, when the target database is a SQL Server database, the field types corresponding to the target field include: char and varchar.
[0050] Exemplarily, the usage restrictions of the character types in the SQL Server database are as shown in Table 3 below,
[0051]
[0052] Table 3
[0053] In an exemplary instance, when the target database is a MySQL database, the field types corresponding to the target field include: CHAR and VARCHAR.
[0054] Exemplarily, the usage restrictions of the character types in the MySQL database are as shown in Table 4 below,
[0055]
[0056] Table 4
[0057] Exemplarily, for the monitoring of character types, it needs to be obtained through data functions. The SQL Server database uses the datalength function, and mysql uses the length function to obtain the maximum value of the field, and then divides it by the N length in the table structure definition to get the length ratio.
[0058] Exemplarily, adjusting the field type corresponding to the target field is achieved by automatically generating an SQL field type change instruction, such as: alter table xx modify column field name—string type (length).
[0059] In an exemplary instance, when the target database is a MySQL database and the field type corresponding to the target field is the TMESTAMP date type, the method further includes:
[0060] When data inserted is detected under the target field with the TMESTAMP date type in the target database, adjust the field type of the target field from the TMESTAMP date type to the DATETIME date type.
[0061] Exemplarily, the usage restrictions of the date type in the MySQL database are as shown in Table 5 below,
[0062]
[0063]
[0064] Table 5
[0065] Exemplarily, for those using the TMESTAMP date type in the database, there is a limit in 2038. It is necessary to obtain the current date for reminder and convert it to the DATETIME date type.
[0066] In an exemplary instance, adjusting the field type corresponding to the target field includes:
[0067] First, obtain the Transactions Per Second (TPS) of the target database;
[0068] Second, when the TPS of the obtained target database is less than the preset TPS threshold, adjust the field type corresponding to the target field.
[0069] Exemplarily, when it is necessary to adjust the field type corresponding to the target field, the monitoring system can be connected to obtain the system load condition, and the change can be dynamically initiated during the low business peak period. After the change is completed, reminders are sent via email and text message.
[0070] The embodiment of the present application further provides an adjustment device for the database field type, including: a memory and a processor. The memory is used to store an executable program;
[0071] The processor is used to read and execute the executable program to implement the adjustment method for the database field type described in any of the above embodiments.
[0072] The database field type adjustment device provided by the embodiment of the present application periodically obtains the maximum value of the lengths of the data inserted under each target field in the target database within a preset time period; for each of the target fields, obtains the length ratio of the obtained maximum length value to the preset maximum length value corresponding to the target field, and adjusts the field type corresponding to the target field according to the relationship between the length ratio and a preset ratio threshold, thereby realizing the automatic adjustment of the database field type, avoiding the problem that data cannot be written, and thus ensuring the successful progress of the write operation.
[0073] Those of ordinary skill in the art can understand that all or some of the steps in the methods disclosed above, and the functional modules / units in the systems and devices, can be implemented as software, firmware, hardware, and appropriate combinations thereof. In the hardware implementation, the division of the functional modules / units mentioned above does not necessarily correspond to the division of physical components; for example, one physical component may have multiple functions, or one function or step may be executed by several physical components in cooperation. Some or all of the components may be implemented as software executed by a processor, such as a digital signal processor or a microprocessor, or may be implemented as hardware, or may be implemented as an integrated circuit, such as an application specific integrated circuit. Such software can be distributed on a computer-readable medium, which may include a computer storage medium (or a non-transitory medium) and a communication medium (or a transitory medium). As is well known to those of ordinary skill in the art, the term computer storage medium includes volatile and non-volatile, removable and non-removable media implemented in any method or technology for storing information, such as computer-readable instructions, data structures, program modules, or other data. Computer storage media includes, but is not limited to, RAM, ROM, EEPROM, flash memory or other memory technologies, CD-ROM, digital versatile disk (DVD) or other optical disk storage, magnetic cassettes, tapes, magnetic disk storage or other magnetic storage devices, or any other medium that can be used to store the desired information and can be accessed by a computer. In addition, as is well known to those of ordinary skill in the art, a communication medium typically includes computer-readable instructions, data structures, program modules, or other data in a modulated data signal such as a carrier wave or other transmission mechanism, and may include any information delivery medium.
Claims
1. A method for adjusting the type of database fields, characterized in that Including: Periodically obtain the maximum length of the data inserted under each target field in the target database within a preset time period; For each of the target fields, obtain the length ratio of the obtained maximum length to the preset maximum length value corresponding to the target field, and adjust the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold.
2. The method according to claim 1, wherein The preset ratio threshold includes: a preset upper threshold and a preset lower ratio threshold; The target database includes: SQL Server database and MySQL database; The field types corresponding to the target fields include: numeric type, character type.
3. The method according to claim 2, wherein When the field type corresponding to the target field is the numeric type, the adjusting the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold includes: When the difference between the length ratio and the preset upper ratio threshold is equal to the preset difference, adjust the numeric type corresponding to the target field to a numeric type with a larger range than the current numeric type; When the length ratio is less than the preset lower ratio threshold and confirmation information for numeric type adjustment is obtained, adjust the numeric type corresponding to the target field to a numeric type with a smaller range than the current numeric type.
4. The method according to claim 3, wherein When the target database is a SQL Server database, the ranges of the numeric types corresponding to the target fields from small to large include: tinyint, smallint, int, and bigint; When the target database is a MySQL database, the ranges of the numeric types corresponding to the target fields from small to large include: TINYINT, SMALLINT, INT, MEDIUMINT, and BIGINT.
5. The method according to claim 2, wherein When the field type corresponding to the target field is the character type, the adjusting the field type corresponding to the target field according to the relationship between the length ratio and the preset ratio threshold includes: When the difference between the length ratio and the preset upper ratio threshold is equal to the preset difference, adjust the upper limit of the string length of the character type corresponding to the target field to a higher upper limit of the string length than the current one; When the length ratio is less than the lower preset ratio threshold and confirmation information for adjusting the upper limit of the string length is obtained, adjust the upper limit of the string length of the character type corresponding to the target field to a lower upper limit of the string length than the current one.
6. The method according to claim 5, wherein When the target database is a MySQL database, the method further includes: When the upper limit of the string length of the character type corresponding to the target field has been adjusted to the maximum upper limit and the length ratio is greater than the preset upper ratio threshold, adjust the character type corresponding to the target field to the TEXT type.
7. The method according to claim 5, characterized in that When the target database is a SQL Server database, the field types corresponding to the target fields include: char and varchar; When the target database is a MySQL database, the field types corresponding to the target fields include: CHAR and VARCHAR.
8. The method according to claim 1, characterized in that, When the target database is a MySQL database and the field type corresponding to the target field is the TMESTAMP date type, the method further includes: When data inserted is detected under the target field whose field type in the target database is the TMESTAMP date type, adjust the field type of the target field from the TMESTAMP date type to the DATETIME date type.
9. The method according to claim 1, characterized in that, Adjusting the field type corresponding to the target field includes: Obtain the TPS of the target database; When the obtained TPS of the target database is less than the preset TPS threshold, adjust the field type corresponding to the target field.
10. An adjustment device for database field types, characterized in that including: A memory and a processor, the memory is used to store an executable program; The processor is configured to read and execute the executable program to implement the method for adjusting the database field type according to any one of claims 1-9.