Mysql 存储过程实现订单流水号

存储过程实现流水号

• 存储过程

实现效果：SHHP201806080002
前缀编码--prefixStr 'SHHP'
后缀编码长度-- suffixCodeLength 长度4 (0002)
日期字符-- 20180608

先准备一张表 express_code 
需要一个字段 product_code

CREATE PROCEDURE `generate_code`(IN prefixStr varchar(10), IN suffixCodeLength int(10), OUT newCode varchar(50))
BEGIN
	DECLARE currentDate VARCHAR(14); 
	-- 当前日期字符串
	DECLARE suffixCode INT DEFAULT 0;
	-- 后缀编码
	DECLARE oldCode VARCHAR(30) DEFAULT '';
    -- 上一个编码
	SELECT DATE_FORMAT(NOW(), '%Y%m%d') INTO currentDate ;
    -- 格式化日期,如'20180608'
	SELECT IFNULL(product_code, '') INTO oldCode
		FROM express_info    
	WHERE SUBSTRING(product_code, 1, LENGTH(currentDate) + LENGTH(prefixStr)) = CONCAT(prefixStr,currentDate)   
		AND LENGTH(product_code) =  LENGTH(prefixStr) + LENGTH(currentDate) + suffixCodeLength
		ORDER BY product_code DESC LIMIT 1 ; 
    -- 查询上一个code
	IF oldCode != '' 
	THEN     
		SET suffixCode = CONVERT(SUBSTRING(oldCode, -suffixCodeLength), DECIMAL) ;  
	END IF ; 
    -- 后缀编码转换为数字，如果前面有0005，则去除前面的0
	SELECT CONCAT(prefixStr, currentDate,  LPAD((suffixCode + 1), suffixCodeLength, '0')) INTO newCode ;
    -- 后缀编码加1，并进行拼接
	SELECT newCode ; 

END

• mybatis、后端调用

    ////  只有一个输出参数时：
    
    <select id="getExpressCode" resultType="string"  statementType="CALLABLE">
    
      call generate_productCode(?)
    
    </select>
  
    // mapper 接口
    String getExpressCode();
    // service 
    public String getExpressCode() {
    	
    	String expressCode = expressInfoMapper.getExpressCode();
    	return expressCode;
    }

    //// 带输入、输出参数时
    
    // 参数封装到实体类
    @Data
    public class CodeRule {
    	private String prefixString;
    	private String code;
    	private int suffixCodeLength;
    }

    <select id="getExpressCode" resultType="string" parameterType="com.peng.express.entity.CodeRule"  statementType="CALLABLE">
    
      call generate_code(
          #{prefixString,mode=IN,jdbcType=VARCHAR},
          #{suffixCodeLength,mode=IN,jdbcType=INTEGER},
          #{code,mode=OUT,jdbcType=VARCHAR}
          )
    </select>	
    
    String getExpressCode(CodeRule codeRule);
    
    public String getExpressCode() {
    	CodeRule codeRule = new CodeRule();
    	codeRule.setPrefixString("XSBH");
    	codeRule.setSuffixCodeLength(4);
    	// 查询结束后，结果自动回填到codeRule的code属性
    	String expressCode = expressInfoMapper.getExpressCode(codeRule);
    	return expressCode;
    }