mysql实现表单号：表单编码+时间+表单号

背景：mysql+mybatis实现表单号；
表单规则为：表单编码+生成时间+随机号
例如：xft201911030056
1、新建表：base_coderole和base_codenumber

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `base_coderole`;
CREATE TABLE `base_coderole`  (
  `vcoderole` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '编码规则',
  `prefix` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '前缀',
  `nlength` double DEFAULT NULL COMMENT '长度',
  `meno` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '描述'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `base_codenumber`;
CREATE TABLE `base_codenumber`  (
  `Vcodetype` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '表单编码类型',
  `nmonth` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '月份',
  `maxnum` int(11) DEFAULT NULL COMMENT '目前最大号码'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

2、新建函数

CREATE DEFINER = CURRENT_USER PROCEDURE `GetFormCode`(IN codetype  varchar(40),OUT ERRNUM    VARCHAR(10) character set utf8,  OUT ERRMSG   VARCHAR(500) character set utf8)
BEGIN
	declare VARQZ varchar(500)  character set utf8;
  declare INTLENN INT;
  declare INTMAXN  INT;
  declare VMONTH VARCHAR(500);
  declare NROWS INT;
  declare 	LROWS INT;
	select count(1) into NROWS FROM  base_coderole where vcoderole = codetype ;
	IF (NROWS = 0)  THEN
      set ERRNUM = 1;
      set ERRMSG = '未配置该类型的表单';      
	ELSE
		  SELECT prefix,nlength INTO VARQZ, INTLENN FROM base_coderole WHERE vcoderole = codetype;
			select CONCAT(YEAR(CURDATE()),MONTH(CURDATE())) into VMONTH;
	    SELECT COUNT(1) INTO LROWS FROM base_codenumber WHERE Vcodetype = codetype and nmonth = VMONTH FOR UPDATE ;
	    IF (LROWS > 0) THEN
			   SELECT maxnum + 1 INTO INTMAXN FROM base_codenumber WHERE Vcodetype = codetype and nmonth = VMONTH;
				 SET ERRNUM =0;
			   SET ERRMSG = CONCAT(CONCAT(VARQZ,VMONTH),right(concat('000000000000',INTMAXN),INTLENN));
				 UPDATE base_codenumber SET maxnum = maxnum + 1 WHERE Vcodetype = codetype and nmonth = VMONTH; 
      ELSE
			   INSERT INTO base_codenumber(Vcodetype,nmonth,maxnum) VALUES(codetype,VMONTH,1);
				 SET ERRNUM =0;
			   SET ERRMSG = CONCAT(CONCAT(VARQZ,VMONTH),right(concat('000000000000',1),INTLENN));
      END IF;		    
  END IF; 
END

3、系统调用

{call GetFormCode(
        #{codetype ,mode=IN ,jdbcType=VARCHAR},
        #{ERRNUM   ,mode=OUT,jdbcType=VARCHAR},
        #{ERRMSG   ,mode=OUT,jdbcType=VARCHAR}
        )}

注意：前台页面传入在base_coderole配置的PXF字段，返回报错信息或者表单编码