SQL insert 语句实现自增

需求背景 

1  表结构

bside_ev_stktypesubjectmap 表结构如下

CREATE TABLE `bside_ev_stktypesubjectmap` (
  `actiontype` varchar(40) NOT NULL COMMENT '操作业务分类',
  `sortSerial` int(4) NOT NULL DEFAULT '0' COMMENT '业务序号',
  `ProductNum` int(9) NOT NULL DEFAULT '-1' COMMENT '产品序号',
  `assetFlag` int(1) DEFAULT NULL COMMENT '业务方向',
  `description` varchar(40) DEFAULT NULL COMMENT '业务说明',
  `exchid` varchar(2) DEFAULT NULL COMMENT '市场',
  `ev_stktype` varchar(20) DEFAULT NULL COMMENT '证券品种',
  `BusinessType` varchar(100) DEFAULT NULL COMMENT '业务标志',
  `subjectAttrId` varchar(100) NOT NULL COMMENT '科目性质',
  `Subjectlevel` int(1) NOT NULL COMMENT '核对科目级别(1: 1级，2：2级，3：3级，4：4级)',
  `fee_subjectAttrId` varchar(100) DEFAULT NULL COMMENT '费用科目性质',
  `tax_subjectAttrId` varchar(100) DEFAULT NULL COMMENT '负债应交税科目性质',
  `costSubjectIdList` varchar(100) DEFAULT NULL COMMENT '对应成本科目列表',
  `memo` varchar(200) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`actiontype`,`sortSerial`,`ProductNum`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

bside_ev_stktypesubjectmap 表结构的主键为 actionType,sortserial,productnum  .

2 程序部署多份

不同环境下，同actionType和productnum限定下，sortserial是不同的。

数据准备

INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`, `exchid`, `ev_stktype`, `BusinessType`, `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`, `memo`) VALUES ('value_add_tax_accured', 1, -1, 1, '公允价值变动-债券', NULL, '', '', 'InvestAppreciation_Bond', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11030101^11033101', NULL);
INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`, `exchid`, `ev_stktype`, `BusinessType`, `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`, `memo`) VALUES ('value_add_tax_accured', 2, -1, -1, '公允价值变动-创业板', NULL, '', '', 'InvestAppreciation_GemStock', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11024101', NULL);
INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`, `exchid`, `ev_stktype`, `BusinessType`, `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`, `memo`) VALUES ('value_add_tax_accured', 3, -1, 1, '公允价值变动-信用创业板', NULL, '', '', 'InvestAppreciation_GemStock_Credit', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11026801', NULL);
INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`, `exchid`, `ev_stktype`, `BusinessType`, `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`, `memo`) VALUES ('value_add_tax_accured', 4, -1, 1, '公允价值变动-港股通', NULL, '', '', 'InvestAppreciation_HKThroughStock', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11028101^11029101', NULL);
INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`, `exchid`, `ev_stktype`, `BusinessType`, `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`, `memo`) VALUES ('value_add_tax_accured', 5, -1, 1, '公允价值变动-A股', NULL, '', '', 'InvestAppreciation_Stock', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11020101^11023101', NULL);
INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`, `exchid`, `ev_stktype`, `BusinessType`, `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`, `memo`) VALUES ('value_add_tax_accured', 6, -1, 1, '公允价值变动-信用A股', NULL, '', '', 'InvestAppreciation_Stock_Credit', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11025101^11026101', NULL);

 

需求说明：

新增一个功能，需要考虑不同环境下的  sortserial的自增

 

解决方案

插入前

set @rownum=0;
select @rownum:=MAX(sortSerial) FROM bside_ev_stktypesubjectmap WHERE actiontype ='value_add_tax_accured' AND ProductNum =-1;

INSERT INTO`bside_ev_stktypesubjectmap`(`actiontype`, `sortSerial`, `ProductNum`, `assetFlag`, `description`,  `subjectAttrId`, `Subjectlevel`, `fee_subjectAttrId`, `tax_subjectAttrId`, `costSubjectIdList`) VALUES ('value_add_tax_accured', @rownum:=@rownum+1, -1, 1, '公允价值变动-转融通出借A股', 'InvestAppreciation_ShareLendStock', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11022601^11023801');

 

插入后

sortserial 实现自增

PS:此类解决方案仅适用于数据库已经存在的情况，当数据库中不存在时，@rownum 会被设置为null,导致SQL执行失败，故需要对SQL进行改造一下。

SET @rownum = 0;
SELECT
	( CASE WHEN  MAX(sortSerial) >0 THEN @rownum := MAX( sortSerial ) ELSE @rownum := 0 END ) 
FROM
	bside_ev_stktypesubjectmap 
WHERE
	actiontype = 'value_add_tax_interest' 
	AND ProductNum =- 1;

INSERT INTO bside_ev_stktypesubjectmap(actiontype, sortSerial, ProductNum, assetFlag, description,  subjectAttrId, Subjectlevel, fee_subjectAttrId, tax_subjectAttrId, costSubjectIdList) VALUES ('value_add_tax_interest', @rownum:=@rownum+1, -1, 1, '利息计提-转融通出借A股', 'InterestRevenue_ShareLendStock', 3, 'fee_value_add_tax_interest', 'value_add_tax_interest', '');