A universal unique identification generation method and system based on table value parameter + stored procedure

Abstract

This invention discloses a universal unique identifier generation method and system based on table-valued parameters and stored procedures, belonging to the field of data identifier technology. This invention employs a parent volume retrieval sequence incrementing algorithm, combined with a four-segment strongly readable encoding combination, to achieve unique, readable, and flexibly configurable identifiers. When there is no matching parent volume, the main sequence increments while the sub-sequences are reset; when there is a matching parent volume, the sub-sequences increment. This method, implemented through table-valued parameters and stored procedures, has been stably running for 8 months in the MES system of 12 units in a steel plant, generating over 6000 identifiers with no duplicates or gaps. This invention has strong versatility and has been practically implemented in industrial ERP and MES systems, possessing mature engineering value and promotion potential.

Description

Technical Field

[0001] This invention relates to the field of data identification technology, specifically to a universal unique identifier generation method and system based on table-valued parameters and stored procedures. The core algorithm can be extended to all industries that require unique identifiers, such as metallurgy, food, electronics, logistics, and manufacturing. It is particularly suitable for scenarios such as industrial MES / ERP systems, production line data management, and product lifecycle traceability. It is a universal identifier generation solution that combines strong readability, universal core algorithm, full-dimensional configurability, and strong engineering feasibility. Background Technology

[0002] In various data management, business systems, and industrial production, unique identifiers are the core foundation for achieving data association, traceability, and management. Traditional unique identifier generation methods have many shortcomings: some use random coding or pure serial numbers, which lack business information and readability, making them impossible to read directly with the naked eye, and requiring database queries for traceability, resulting in extremely low efficiency; some use fixed-format coding, with rigid code length and module order, making it impossible to flexibly adjust according to industry / scenario needs, resulting in poor adaptability; some identifier generation logic lacks a unified core algorithm, being only customized development for specific scenarios, and cannot be extended across industries; some lack engineering implementation, remaining only at the algorithm concept level, and cannot meet the actual needs of industrial production; and existing methods have a narrow scope of protection, only covering specific implementation forms, without locking in the core algorithm, making them easy to circumvent by changing the development language, adjusting the coding format, etc.

[0003] Existing technologies lack a universal unique identifier generation solution that is **"general-purpose core algorithm + highly readable + fully configurable + highly practical". It cannot achieve direct visual reading of the code to improve traceability efficiency, nor does it have a unified and scalable core algorithm. Furthermore, it is difficult to form a comprehensive technical protection that covers the algorithmic ideas, and it is difficult to meet the core needs of various industries for uniqueness, readability, scalability, and practicality of identifiers. Summary of the Invention

[0004] Bright eyes This invention aims to provide a universal unique identifier generation method and system based on table-valued parameters and stored procedures, solving the problems of traditional methods such as lack of readability, non-universal core algorithms, poor adaptability, insufficient implementation, and narrow protection scope. This invention extracts a universal core algorithm for incremental parent volume retrieval sequences, which can be extended across industries; it achieves strong readability through a one-to-one mapping of four modules with business information, making the encoding "instantly understandable"; the encoding length, format, and combination logic of each module can be flexibly adjusted in all dimensions to adapt to different industry / scenario needs; it achieves engineering implementation through table-valued parameters and stored procedures, with the stored procedures only adapted for the steel mill scenario, while the core algorithm can be implemented using any technology; simultaneously, it locks the protection scope to the core algorithm and the four-segment combination logic, preventing various infringement circumvention behaviors, ultimately achieving the identifier generation requirements of "universal algorithm, instant understanding, flexible adaptation, stable implementation, and comprehensive protection". 2. Technical Solution

[0005] A general unique identifier generation method based on table-valued parameters and stored procedures is proposed. The core design revolves around a **"general algorithm for incremental retrieval of parent volume sequences"**, combined with a four-segment highly readable module combination, full-dimensional configurability, and globally unique verification. The specific steps are as follows: Table-valued parameter definition: Create custom table-valued parameters as the sole data input carrier between the front-end and back-end. They must contain at least two core fields: parent volume association identifier and business scenario identifier. The number and type of fields can be flexibly expanded according to industry / scenario requirements. The front-end is only responsible for collecting and encapsulating business data and does not participate in any identifier generation logic.

[0006] Highly readable module breakdown: After determining the target business scenario, extract the core business information that can be directly recognized by the naked eye (such as scenario type, generation time, parent volume sequence number, and secondary object sequence number), and break it down into four independent identification modules. All four modules are indispensable and mapped one-to-one with the core business information, ensuring strong readability of the code from the bottom layer. Scene feature identifier: Maps business / scene type, encoding type supports letters / pinyin abbreviations, English, Chinese characters, etc., length is 2-10 characters and can be expanded, and can be distinguished by eye; Time-related identifier: Maps and generates time, supports any combination of times such as YYMMDD and YYYYMMDD, and the length can be adjusted as needed. The time can be read and recognized by the naked eye. Master sequence identifier: Mapped to the parent volume / core object sequence number under the same scene and time, in serial number format, length 2-6 digits expandable, core sequence number can be read and identified by the naked eye; Sub-sequence identifier: Maps the sequence number of secondary objects under the same parent volume, in serial number format, with a length of 1-6 digits that can be expanded, and the sub-sequence number can be read and identified by the naked eye.

[0007] Encoding rule configuration: Configure flexible encoding rules for each module, including character type, encoding length, encoding format, generation method, etc. All adjustments are based on maintaining the mapping relationship between the module and business information and ensuring strong readability, while adapting to the coding habits and needs of different industries.

[0008] Combinatorial logic presets: The preset order of four types of modules and the connectors between modules are preset. The order can be adjusted arbitrarily (such as scene features + time association + main sequence + sub-sequence, sub-sequence + main sequence + time association + scene features, etc.). The connectors can be selected as no connector, hyphen, underscore, etc. After adjustment, the independent readability of each module and the integrity of the overall information are still maintained.

[0009] Core algorithm execution: The general core algorithm of incremental retrieval sequence of the parent volume is executed through a database stored procedure (the stored procedure is an adapted implementation for the steel mill scenario; the core algorithm can be implemented using any technology). The specific logic is as follows: The stored procedure receives table-valued parameters, extracts the parent volume association identifier and business scenario identifier, and obtains the current system time; Using the parent volume association identifier as the search condition, query the system's historical identifier database / business data table to determine if a matching historical record exists; No matching parent volume: The main sequence identifier is the same scene + the historical maximum sequence number value under the current time dimension + 1, and the sub-sequence identifier is set to an initial fixed value (such as 01, 001, which can be flexibly configured). If there is a matching parent volume: the subdivision sequence identifier is taken as the historical maximum sequence number value under the associated identifier of the parent volume + 1, and the main sequence identifier is directly adopted from the historical main sequence identifier corresponding to the parent volume, ensuring that the main sequence identifier of the same parent volume is unique and fixed.

[0010] Identifier generation and global verification: According to the configured encoding rules and combination logic, the four types of modules are concatenated to generate an initial identifier; the historical identifier database of the retrieval system is used to perform a global unique verification. If there is no duplicate, it is confirmed as a valid unique identifier. If there is a duplicate, the main / sub-sequence identifier is automatically adjusted and regenerated until a unique identifier is obtained. The entire verification process is completed automatically without manual intervention.

[0011] Storage and Traceability: Valid unique identifiers and their corresponding module combination rules, encoding rules, generation time, parent volume association identifiers, and other information are stored in the identifier library. Combined with the strong readability of the encoding itself, it achieves dual high-efficiency traceability with **"direct reading of core information by the naked eye + traceability details in the library"**. Core content such as the scene, time, and serial number corresponding to the identifier can be quickly identified without searching the library, greatly improving traceability and management efficiency. 3. Beneficial effects

[0012] The core algorithm is universal and scalable across industries: The extracted parent volume retrieval sequence incremental core algorithm is a universal logic that does not depend on a specific implementation carrier. It can be extended to all industries that require unique identification, such as metallurgy, food, electronics, and logistics. There is no need to redesign the algorithm for different industries, making it highly adaptable. Highly readable and easy to understand: The four-segment module maps one-to-one with business information. The code itself can be read directly by the naked eye, including core information such as scene, time, main sequence, and sub-sequence. It can be quickly identified without looking up the database, improving traceability efficiency by more than 90%, far exceeding traditional random codes and pure serial numbers. Fully configurable with maximum adaptability: The encoding length, format, type, module arrangement order, connector type, and initial sequence value of each module can all be flexibly adjusted. It can be customized according to the actual needs of different industries / scenarios, and still maintains strong readability after adjustment. Highly practical and engineering-ready: The system is implemented through table-valued parameters and stored procedures. The stored procedures have been adapted to 12 production units in a steel plant and have been running stably for 8 months, generating more than 6,000 identifiers with no duplicates, no missing numbers, and a response time of ≤0.3 seconds. It can be directly reused in other industries and the core algorithm can also be implemented through other technical carriers. The protection scope is comprehensive and eliminates any loopholes in infringement: The protection scope is locked on the core algorithm of increasing retrieval sequence of the parent volume + the four-segment highly readable module combination logic, rather than the specific implementation form. Regardless of adjusting the code length, changing the development language / database, or adjusting the module order, as long as the core algorithm and combination logic are used, they will fall within the protection scope, forming an unavoidable technical barrier. High degree of automation and significant efficiency improvement: From data input, identifier generation, global verification to storage traceability, the entire process is completed automatically without human intervention, which greatly improves the efficiency of identifier generation and avoids problems such as coding errors and duplication caused by manual operation; Dual traceability, convenient and efficient: Combining the strong readability of the code with the detailed storage of the identifier library, it realizes a dual traceability method of direct visual reading + in-database traceability. It can quickly identify core information and query detailed business-related information, meeting the traceability needs of different scenarios. Detailed Implementation

[0013] This invention uses a steel mill metallurgical production scenario as a practical example to provide a detailed explanation of the implementation process of the core algorithm for incremental retrieval of the master volume and the combination of four-segment highly readable modules. This example is only an adapted implementation of the core algorithm and is not a limitation of this invention. The core algorithm can be directly extended to other industries. (1). System Configuration

[0014] Create a custom read-only table value parameter

tabletypeZiFu2

MES

HuoQuChengPinJuanHao

[0015] Front-end parameter passing: The front-end collects steel mill production data, unit name = 1# cover retreat (scene feature), raw material coil number = YL260303001 (parent coil association identifier), encapsulates the data into table-valued parameters [tabletypeZiFu2], and calls the stored procedure [HuoQuChengPinJuanHao]; Master roll retrieval: The stored procedure extracts the raw material roll number YL260303001, queries the production process quality inspection record table, finds no matching historical records, and determines it to be a new master roll; Sequence generation: Obtain the current system time 2026-03-03 and generate a time-related identifier 260303; take the historical maximum main sequence identifier under 1# shielded unit + 2026-03-03 as 008, and increment it to 009; set the subdivided sequence identifier to the initial value 01; Identification splicing: According to the combination logic, it is spliced ​​as ZT+260303+009+01=ZT26030300901, which can be read by the naked eye: No. 1 mask removal unit, produced on March 3, 2026, 9th master roll, 1st finished small roll; Global verification: Search the historical identifier database to confirm that there are no duplicates and confirm that it is a valid and unique identifier; Storage Traceability: Store the finished product roll number ZT26030300901 and the corresponding unit name, raw material roll number, production time, coding rules and other information in the production process quality inspection record table to complete the storage. (3) Existing Master Volume Identifier Generation Instance

[0016] Front-end parameter passing: When the same raw material roll number YL260303001 re-enters the production process, the front-end encapsulates the table-value parameters and calls the stored procedure; Master roll retrieval: The stored procedure extracts the raw material roll number YL260303001, queries the production process quality inspection record table, and if there is a matching historical record, it is determined that there is already a master roll, corresponding to the historical finished product roll number ZT26030300901; Sequence generation: Take the historical maximum sub-sequence identifier 01 under this raw material volume number, increment it to 02; the main sequence identifier follows the historical 009; Identification splicing: The splicing is ZT+260303+009+02=ZT26030300902, which can be read by the naked eye: No. 1 cover removal unit, produced on March 3, 2026, 9th master roll, 2nd finished small roll; Global verification + storage: After verifying that no duplicates are found, the identifier is stored in the production process quality inspection record table, thus completing the generation process. (4). Computer program implementation of the core algorithm

[0017] The core algorithm for incremental sequence retrieval of the parent volume described in this invention is implemented in the SQL Server database of the steel plant MES system through the stored procedure

HuoQuChengPinJuanHao

tabletypeZiFu2

[0018] The core algorithm of this invention can be directly extended to the food industry. Only the encoding rules need to be adapted; there is no need to modify the core algorithm. The specific implementation is as follows: Module configuration (adjusted according to the needs of the food industry): Scene feature identifier: 2-letter abbreviation GT (canned goods), the length can be extended to 4 characters, such as GTHS (canned yellow peaches); Time-related identifier: 8-digit number YYYYMMDD (20260303), covering a time dimension of ten thousand years; Main sequence identifier: 4-digit zero-padded serial number (0009), suitable for the high-volume demand of the food industry; Sub-sequence identifier: 3-digit zero-padded serial number (001), mapped to the canned food box number, initial value 001; Combinatorial logic preset: time association identifier + scene feature identifier + main sequence identifier + sub-sequence identifier, no connector; New master roll generation: No matching canned food roll number, take the maximum value of the same scenario + time in the main sequence 0008+1=0009, set the sub-sequence to 001, generate the identifier 20260303GT0009001, which can be read by the naked eye as: produced on March 3, 2026, canned product, 9th roll, 1st box of finished product; A master roll has been generated: the same pre-roll number is produced again, the subdivision sequence is 001+1=002, generating the identifier 20260303GT0009002, which can be read by the naked eye as: produced on March 3, 2026, canned product, 9th pre-roll, 2nd box of finished product.

[0019] Further explanation of specific implementation methods The specific implementation of this invention only takes the steel mill and canned food scenarios as examples. The core algorithm of incremental parent volume retrieval sequence can be directly extended to all industries such as electronics, logistics, and manufacturing. In the electronics industry: the master roll association identifier is set as the component blank number, the subdivision sequence identifier is set as the finished part serial number, and the scene feature identifier is set as DJ (electrode), BJ (part), etc. Logistics industry: The master roll association identifier is set to the logistics tracking number, the subdivision sequence identifier is set to the parcel serial number, and the scenario feature identifier is set to YD (Yunda), ZT (Zhongtong), etc. Manufacturing industry: The master roll association identifier is set to the raw material batch number, the subdivision sequence identifier is set to the finished workpiece serial number, and the scene feature identifier is set to JJ (machine tool), JD (home appliance), etc.

[0020] Each industry can quickly generate identifiers by flexibly adjusting the encoding length, format, type, and combination logic of the four-segment module according to its own actual needs, without modifying the core algorithm. Moreover, all adjusted codes maintain strong readability and can be "read at a glance".

[0021] Patent applicant's statement The universal unique identifier generation method and system based on table-valued parameters and stored procedures disclosed in this invention has been actually implemented and is running in the MES system of 12 production units in a steel plant. All technical details are true and reproducible, and there are no false statements. The scope of protection of this invention is defined by the claims. Any identifier generation scheme that adopts the core algorithm of incremental parent volume retrieval sequence and the four-segment strongly readable module combination logic, regardless of the change of implementation carrier or adjustment of encoding rules, falls within the scope of protection of this invention.

Claims

1. A method for generating a universal unique identifier based on table-valued parameters and stored procedures, characterized in that, Includes the following steps: (1) Define custom table value parameters for batch transmission of core information required for identifier generation, including at least the parent volume association identifier and the business scenario identifier. The table value parameters are the only data transmission carrier between the front end and the back end. The number of fields and the field types can be flexibly expanded according to industry / scenario requirements. The target business scenario for identifier generation is determined, and core business information that can be directly identified by the naked eye is extracted and broken down into four independent identifier modules. These four modules are the necessary foundation for generating unique identifiers and are indispensable. Each module is mapped one-to-one with the core business information to achieve highly readable coding. Scene feature identifier: Feature encoding used to distinguish business types and application scenarios. The encoding type supports letter abbreviations, pinyin abbreviations, English words, Chinese character encoding or mixed encoding. The encoding length is 2-10 bits and can be flexibly expanded. The corresponding scene can be directly read and distinguished by the naked eye. Time-related identifier: The encoding is related to the generation time. The encoding format supports any combination of time such as YYMMDD, YYYYMMDD, YYYYMMDDHHMMSS. The time precision is configurable, and the encoding length can be flexibly adjusted as needed. The specific time can be directly read and identified by the naked eye. Master sequence identifier: Used to distinguish consecutive codes of different master volumes / core objects under the same scene + time combination. It adopts the form of serial number, and the code length can be flexibly expanded from 2 to 6 digits. The core object sequence number can be directly read and identified by the naked eye. Sub-sequence identifier: Used to distinguish consecutive sub-objects within the same parent volume / core object. It adopts a serial number format, with a code length of 1-6 digits that can be flexibly extended. The sub-object sequence number can be directly read and identified by the naked eye. Configure flexible encoding rules for each identifier module, including but not limited to character type, encoding length, encoding format, and generation method. After adjustment, the mapping relationship between each module and business information is maintained, ensuring that the strong readability attribute remains unchanged. The system includes four types of pre-defined module combination logic, including module order and inter-module connectors. The order can be adjusted arbitrarily, and the connector type can be freely selected. After adjustment, the independent readability of each module and the integrity of the overall information are maintained. Based on the core algorithm of incremental sequence retrieval from the parent volume, identifiers are generated through database stored procedures: The stored procedure receives table-valued parameters and extracts the parent volume association identifier, business scenario identifier, and the current system time. Using the parent volume association identifier as the search condition, query the system's historical identifier database / business data table to determine if a matching record exists; If no matching record is found: the main sequence identifier is taken as the maximum historical sequence number value in the same scenario and current time dimension, plus 1; the sub-sequence identifier is set to the initial fixed value. If a matching record exists: the sub-sequence identifier is taken as the historical maximum sequence number value under the associated identifier of the parent volume + 1, and the main sequence identifier is taken as the historical main sequence identifier corresponding to the parent volume; According to the encoding rules and combination logic, the four types of modules are concatenated to generate an initial identifier, and a global uniqueness verification is performed: the system's historical identifier library is searched, and if there is no duplicate, it is confirmed as a valid unique identifier; if there is a duplicate, the main / sub-sequence identifier is adjusted and regenerated until it is unique. The verification process is fully automated and requires no manual intervention. The valid unique identifier and its corresponding module combination rules, encoding rules, generation time, parent volume association identifier and other information are stored in the identifier library. Combined with the strong readability of the encoding, it realizes dual high-efficiency traceability of direct visual reading and traceability within the library.

2. The method according to claim 1, characterized in that, The initial fixed value of the subdivided sequence identifier is the smallest serial number after padding with zeros, such as 01 or 001, which can be flexibly configured according to industry / scenario requirements.

3. The method according to claim 1, characterized in that, The database stored procedure is a practical implementation of the core algorithm. It can be implemented based on any general-purpose or domestic database such as SQL Server, DM, Gaussian, or MySQL, or through other development languages / technical carriers. As long as the core algorithm of incremental parent volume retrieval sequence is used, it falls within the protection scope of this invention.

4. The method according to claim 1, characterized in that, The encoding of the scene feature identifier is a general abbreviation of the business scene. In the metallurgical industry, it can be configured as SX (pickling), ZT (tanning), DX (galvanizing), in the food industry, it can be configured as GT (canned food), SP (food bottle), and in the electronics industry, it can be configured as DJ (electrode) and BJ (accessory). No professional training is required, and relevant industry personnel can directly identify the corresponding scene by visual inspection.

5. The method according to claim 1, characterized in that, The digital codes of the time-related identifiers are arranged in natural chronological order. For example, 260303 can be read as March 3, 2026, and 20260303 can also be read as March 3, 2026. Even people without industry background can directly identify the specific time corresponding to the code by visual inspection.

6. The method according to claim 1, characterized in that, The main sequence identifier and the sub-sequence identifier are consecutive numerical serial numbers padded with zeros. For example, 099 can be read directly as the 99th core object, and 05 can be read directly as the 5th sub-object. The object's serial number and sub-category can be directly read and identified by the naked eye.

7. The method according to any one of claims 1-6, characterized in that, The scope of protection of this invention covers the core algorithm of increasing the retrieval sequence of the parent volume and the logic of the four-segment highly readable module combination. Regardless of adjusting the module arrangement order, modifying the encoding length / format of each module, changing the implementation carrier / development language, or adjusting the initial value of the sequence, as long as the core algorithm and the four-segment combination logic are used, they all fall within the scope of protection of this invention.

8. A universal unique identifier generation system for implementing the method of any one of claims 1-7, characterized in that, include: The module includes a table-valued parameter definition module, a highly readable module decomposition module, an encoding rule configuration module, a core algorithm execution module, a globally unique verification module, and a storage traceability module. Table-valued parameter definition module: Used to create custom table-valued parameters, supporting flexible field expansion, and serving as the unique data input carrier; Highly readable module decomposition module: used to extract the core information that can be identified by the naked eye in the business, and decompose it into four types of identification modules that are mapped one-to-one with the business information to ensure the strong readability of the code; Encoding rule configuration module: Used to configure the encoding type, length, format and other rules of each module, supporting flexible adjustment in all dimensions while maintaining readability; Core algorithm execution module: This module executes the core algorithm for incrementally retrieving the parent volume search sequence, determines the matching status of the parent volume, and generates main / sub-sequence identifiers. The stored procedure is an adaptation of this module for a steel mill scenario. Global uniqueness verification module: used to perform real-time retrieval and duplicate adjustment to ensure that the identifier is globally unique; Storage and traceability module: Used to store identifiers and related configuration information, and combined with highly readable coding to achieve dual high-efficiency traceability.