需求背景
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', '');