SQL insert statement realizes self-increment

Demand background

1 Table structure

The bside_ev_stktypesubjectmap table structure is as follows

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;

The primary keys of the bside_ev_stktypesubjectmap table structure are actionType, sortserial, and productnum.

2 Deploy multiple copies of the program

In different environments, sortserial is different under the same actionType and productnum restrictions.

data preparation

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);

Statement of needs:

Add a new function, need to consider the self-increment of sortserial in different environments

solution

Before inserting

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:[email protected]+1, -1, 1, '公允价值变动-转融通出借A股', 'InvestAppreciation_ShareLendStock', 3, 'fee_value_add_tax_pricediff_accured', 'value_add_tax_pricediff_accured', '11022601^11023801');

After insertion

sortserial realizes self-increment

PS: This type of solution is only applicable to the situation where the database already exists. When the database does not exist, @rownum will be set to null, causing SQL execution to fail, so SQL needs to be modified.

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:[email protected]+1, -1, 1, '利息计提-转融通出借A股', 'InterestRevenue_ShareLendStock', 3, 'fee_value_add_tax_interest', 'value_add_tax_interest', '');