mysql - 使用mysql工作台自动生成自定义ID？

Question

我希望它的格式为“YYYY-###”，其中给出从 1 开始的年份和编号 ###。例如:2016-001、2016-002、2016-003，.. .

Answer 1

如果您愿意，可以使用数字/计数表。我使用 UDF 动态生成数字范围。正如您在函数调用中看到的，我将范围设置为 1 到 12，增量为 1

Declare @Table table (SomeField int)
Insert into @Table values 
(2015),(2016)


Select StringValue =cast(SomeField as varchar(25))+'-'+right('000'+cast(RetVal as varchar(25)),3)
      ,SomeField
      ,RetVal
 From  @Table A
 Join (Select RetVal=cast(RetVal as int) from [dbo].[udf-Create-Range-Number](1,12,1)) B
   on (1=1)  

返回

StringValue SomeField   RetVal
2015-001    2015        1
2015-002    2015        2
2015-003    2015        3
2015-004    2015        4
2015-005    2015        5
2015-006    2015        6
2015-007    2015        7
2015-008    2015        8
2015-009    2015        9
2015-010    2015        10
2015-011    2015        11
2015-012    2015        12
2016-001    2016        1
2016-002    2016        2
2016-003    2016        3
2016-004    2016        4
2016-005    2016        5
2016-006    2016        6
2016-007    2016        7
2016-008    2016        8
2016-009    2016        9
2016-010    2016        10
2016-011    2016        11
2016-012    2016        12

UDF

CREATE FUNCTION [dbo].[udf-Create-Range-Number] (@R1 money,@R2 money,@Incr money)

-- Syntax Select * from [dbo].[udf-Create-Range-Number](0,100,2)

Returns 
@ReturnVal Table (RetVal money)

As
Begin
    With NumbTable as (
        Select NumbFrom = @R1
        union all
        Select nf.NumbFrom + @Incr
        From NumbTable nf
        Where nf.NumbFrom < @R2
    )
    Insert into @ReturnVal(RetVal)

    Select NumbFrom from NumbTable Option (maxrecursion 0)

    Return
End