实现数据库表中的流水号

实现的流水号样式（字符+日期+流水号）如下

流水号.png

一般自己在写小demo的时候，都是使用的纯数字自增（1,2,3……）这种。相比而言，流水号形式的看起来更正规，而且可以从流水号中了解数据的添加时间及顺序，当然，是不是看着更有逼格。

目前我采用的方法是，利用存储过程来实现（可能还有很多方法），因为这种一般是在插入数据的时候使用，所以把插入数据的操作也放在了存储过程。如果目前不知道存储过程这个概念的小白，可以看我这篇的介绍第一次接触存储过程

<h3>分析</h3>
该字段由三部分组成：QQ字符 + 201706（年月，即当前日期）+ 0001（流水号，自动增长）

1. QQ字符不用管
2. 201706，当然是先获取到系统当前的时间，然后拿到对应的6位数值
3. 流水号，为了确保主键不重复，同一时间内，这部分流水号应该是不同的，所以流水号从 0000~9999 ，即同一段时间最多会有一万个不同的流水号。（目前不考虑9999加1之后的情况）

<h3>实现</h3>
首先建立一个test表，方便讲解

create table test(
     pk_id varchar(12) not null,
     name varchar(10) null
)

实现201706的前一步（拿到当前的年月）

CONVERT(varchar(8), GETDATE(), 112)  --获取对应格式（20170601）的当前系统时间

实现201706，同时把得到的值赋值给一个变量（为了代码看起来清晰）

declare @dateStr varchar(6)  --定义变量
----使用substring()方法截取前6位，同时赋值变量
select @dateStr = (select SUBSTRING((Select CONVERT(varchar(8), GETDATE(), 112)),1,6)) 

实现0001的前一步（这里假设表中已经存在一条数据，那么它的4位流水号应该是0000，完整代码部分中会考虑第一条数据的问题。拿到最近生产的4位流水号，新的流水号是在此基础上得到）

----拿到上一个最近生成的pk_id
select top 1 pk_id from table test order by pk_id desc 

实现0001，这只是开始

declare @maxNo varchar(4)  ----定义变量
----把上诉得到的最近生产的pk_id，通过substring()方法截取最后的4位流水号，然后对其加1
select @maxNo=(Select SUBSTRING( (select top 1 pk_id from test order by pk_id desc),9,4)+1);

虽然新生成了一个流水号，但是不能直接使用，因为如果@maxNo的值不是4位数，那么就会扰乱pk_id的队形，所以需要进行判断

if (@maxNo < 10) begin
    set @maxNo = '000' + @maxNo; --如果是1位数的话，需要在前面加3个0，补够4位
end 
else if (@maxNo < 100) begin
    set @maxNo = '00' + @maxNo; --如果是2位数的话，需要在前面加2个0，补够4位
end
else if (@maxNo < 1000) begin
    set @maxNo = '0' + @maxNo; --如果是3位数的话，需要在前面加1个0，补够4位
end

至此，分割的各个部分都实现了。为了便于插入操作，把所有值结合起来，用一个新的变量来存储

declare @result nvarchar(12)--共计12位，前两位为QQ，中间6位是年月，最后4位是流水号
----把之前得到的@dateStr和maxNo加上，QQ字符直接加上即可
select @result=(Select 'QQ' + @dateStr + @maxNo);

最后，把需要插入表中的数据作为参数传递到存储过程中，在存储过程中执行插入操作

@name -- 需要插入test表中的值
insert into test(pk_id,name) values(@result,@name)

这就是完整的过程，在执行插入数据的操作时，把除了pk_id之外的字段当做参数传入到存储过程中，在存储过程中执行insert操作即可

完整的存储过程代码如下

USE [Test] ----Test是test表所在的数据库名
GO
/****** Object:  StoredProcedure [dbo].[AddData]    Script Date: 06/01/2017 18:51:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddData]  
    @name varchar(10)  ----参数，用于接收传入的name值
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    
    declare @dateStr varchar(6),  ----定义变量,用于存储6位数的时间
    @maxNo varchar(4), ----定义变量，用于存储4位流水号
    @result varchar(12) -----定义变量，用于存储新生成的pk_id
    
    ----获取到系统当前时间，并截取符合条件的6位，同时赋值给@dateStr
    select @dateStr = (select SUBSTRING((Select CONVERT(varchar(8), GETDATE(), 112)),1,6))
    
    ----先判断是否第一次向test表中插入数据（即当前表中是否存在数据）
    if exists (select * from test) begin
        ----获取数据表中最近产生的一个pk_id，并截取最后4位流水号，对4位流水号进行加1，最后赋值给@maxNo
        select @maxNo=(Select SUBSTRING( (select top 1 pk_id from test order by pk_id desc),9,4)+1);
        
        ----对上诉的@maxNo做判断，确保流水号一定是4位，不够的在前面补0
        if (@maxNo < 10) begin
            set @maxNo = '000' + @maxNo; --如果是1位数的话，需要在前面加3个0，补够4位
        end 
        else if (@maxNo < 100) begin
            set @maxNo = '00' + @maxNo; --如果是2位数的话，需要在前面加2个0，补够4位
        end
        else if (@maxNo < 1000) begin
            set @maxNo = '0' + @maxNo; --如果是3位数的话，需要在前面加1个0，补够4位
        end
        
        ----将上诉结果合成一个新的pk_id
        select @result=(Select 'QQ' + @dateStr + @maxNo)
    end 
    else
       ----如实当前插入的数据时第一条数据，那么直接把4位流水号设为0000
       select @result = 'QQ' + @dateStr + '0000'
        
    ----将数据插入到test表中
    insert into test(pk_id,name) values(@result,@name);
END