SQL自增字段按天/月/年递增策略与定制生成示例

在IT行业中，有时候我们需要在非主键字段上实现自增控制，例如按照日期（每天、每月或每年）进行递增，并且允许定制前缀、后缀以及填充位数。这种需求在设计数据库表结构和编写SQL语句时尤为常见。以下是一个针对MySQL数据库实现的示例，展示了如何创建一个名为`man_busi_code`的表，并定义一个存储过程`f_generator_busi_code`来生成按照指定条件自增的业务代码。 首先，我们创建一个`man_busi_code`表，包含以下字段： 1. `busi_type`：整型（int，11位），用于存储类型ID，不能为空，注释为类型标识。 2. `busi_desc`：可变长度字符串（varchar，255位），默认为空，描述性文本。 3. `prefix`：可变长度字符串（varchar，255位），默认为空，用于添加前缀。 4. `suffix`：可变长度字符串（varchar，255位），默认为空，用于添加后缀。 5. `granularity`：整型（int，11位），默认为0，表示是否按照时间粒度自增，1为每日，2为每月，3为每季度，4为每年。 6. `gen_time`：日期类型，用于记录生成时间，默认为空。 7. `gen_val`：整型（int，11位），初始值为1，表示自增序列。 8. `extent`：整型（int，11位），默认为1，控制填充位数。 接下来，我们定义了一个存储过程`f_generator_busi_code`，该过程接受一个输入参数`i_busi_type`（业务类型ID）和一个输出参数`v_code`（生成的业务代码）。这个过程的主要逻辑如下： - 初始化变量v_cnt为0，用于计数已存在的相同类型的记录。 - 查询当前类型ID下是否存在记录，如果存在，获取`granularity`值。 - 根据`granularity`值决定生成规则： - 如果`granularity`为0，则直接使用Lpad函数对当前自增值（gen_val+1）进行填充，确保与`extent`指定的位数一致，并拼接前缀和后缀。 - 如果`granularity`非0，则根据具体的时间粒度（每日、每月等）更新`gen_val`并生成代码。 最后，如果发现没有冲突记录，存储过程会生成新的业务代码并将其赋值给输出参数`v_code`。如果存在冲突，存储过程会捕获事务，确保数据的一致性。完成操作后，可以使用这个存储过程来生成新的、符合自定义规则的业务代码。 总结来说，这个示例展示了一种在MySQL中实现非主键自增字段的策略，通过自定义表结构和存储过程，我们可以灵活地控制自增的规则，满足不同场景的需求。这对于数据库设计和业务逻辑管理非常有用，尤其是在需要有序、唯一标识的场景中。