利用MySql函数生成不重复的流水号

添加存储流水号的表

CREATE TABLE `t_code_ident`  (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `currentvalue` int(10) NULL DEFAULT 1,
  `increaseby` int(10) NULL DEFAULT 1,
  `maxvalue` int(10) NULL DEFAULT 999999999,
  `minvalue` int(10) NULL DEFAULT 1,
  `cycle` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dateStr` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '2020-05-09',
  UNIQUE INDEX `id`(`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

MySql函数创建

#数据传入和返回值函数
create function getSeqNo(in_strType varchar(30)) 
returns varchar(10) charset utf8
BRGIN
  DECLARE v_intValue NUMERIC(20,0);
  DECLARE v_strSn VARCHAR(30);
  #返回值
  DECLARE v_strTmp VARCHAR(30);
  #将表明传入处理数据的函数
  SET v_intValue = getSeqNextValue(in_strType);
  select LPAD(v_intValue,10,"0") INTO v_strTmp FROM dual;
  RETURN v_strTmp;
  END

#逻辑处理函数
CREATE DEFINER=`itr`@`%` FUNCTION `getSeqNextValue`(arg varchar(30)) RETURNS int(11)
    DETERMINISTIC
BEGIN
	DECLARE str VARCHAR(50);
	DECLARE intResult NUMERIC(20,0);
	DECLARE intCount NUMERIC(20,0);
	DECLARE intMax NUMERIC(20,0);
	DECLARE intMin NUMERIC(20,0);
	DECLARE strlsCycle CHAR(1);
	DECLARE dateStrs VARCHAR(50);
	set intResult=0;
	set intCount=0;
	set intMax=0;
	set intMin=0;
	select count(ID), dateStr into intCount,dateStrs from t_code_ident WHERE ID=arg;
	IF intCount = 0 then 
		 insert into t_code_ident(id,dateStr) VALUES(arg, DATE_FORMAT(now(),'%Y-%m-%d'));
		 set intResult = 1;
	ELSE
	   SELECT DATE_FORMAT(now(),'%Y-%m-%d') into str FROM DUAL;
	   IF dateStrs != str THEN
				update t_code_ident SET currentvalue = 1, dateStr = str WHERE ID=arg;
				set intResult = 1;
		 ELSE
				 select s.currentvalue + s.increaseby, s.maxValue,s.minvalue,s.cycle into intResult, intMax,intMin,strlsCycle from t_code_ident s where id=arg;
				 IF intResult<intMax then 
						update t_code_ident SET currentvalue = intResult WHERE ID=arg;
						set intResult = intResult;
				 ELSE
						update t_code_ident SET currentvalue = intMin WHERE ID=arg;
						set intResult = intMin;
				 END IF;
		 END IF;	
	END IF;
	RETURN intResult;
END

封装获取流水号的方法

import io.netty.util.internal.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import javax.annotation.PostConstruct;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import java.util.concurrent.Callable;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.FutureTask;

@Component
public class GetCaseNoUtil {
    @Autowired
    private CaseMasterMapperExt caseMasterMapperExt;
    private static GetCaseNoUtil getCaseNoUtil;
    private static SimpleDateFormat sd = new SimpleDateFormat("yyyyMMdd");
    //private static ThreadLocal<StringBuffer> threadLocal = new ThreadLocal<StringBuffer>();

    @PostConstruct
    public void init(){
        getCaseNoUtil = this;
        getCaseNoUtil.caseMasterMapperExt = this.caseMasterMapperExt;
    }

    public static  String getCaseNo(String tableName){
        if(StringUtil.isNullOrEmpty(tableName)){
            ResultUtils.fail4Unauthorized("数据为空！请核查");
        }
        StringBuffer buffer = new StringBuffer();
        String number = null;
        String number1 = getNumber(tableName);
        //number = getCaseNoUtil.caseMasterMapperExt.getSeqNo(tableName);
        Date date = new Date();
        buffer.append("F").append(sd.format(date)).append(number1.substring(number1.length()-4,number1.length()));
        //threadLocal.set(buffer);
        return buffer.toString();
    }

    private static String getNumber(String tableName) {
        //Set<String> set = new HashSet<String>();
        FutureTask<String> task = null;
        long startTime = System.currentTimeMillis();
        Callable<String> callable = new Callable<String>() {
            @Override
            public String call() throws Exception {
                return getCaseNoUtil.caseMasterMapperExt.getSeqNo(tableName);
            }
        };
        task = new FutureTask<String>(callable);
        new Thread(task).start();
        String number = null;
        try {
            number = task.get();
        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (ExecutionException e) {
            e.printStackTrace();
        }
        return number;
    }
}

Mapper

@Repository
public interface GetSeqNoMapper {
	String getSeqNo(String tableName);
}

mapper.xml

<select id="getSeqNo" parameterType="java.lang.String" resultType = "java.lang.String">
	select getSeqNo(#{tableName}) from dual
</select>

结语

通过把表名传入函数中，进行逻辑判断生成流水号，在代码中可以对获取的流水号进一步进行处理，再添加一些其他规则可以得到自己想要的流水号，如果要应对高并发，自己可以在Util中添加锁进行处理。
欢迎大佬补充和斧正。