联合主键删除错误，注意删除触发器触发的插入
今天用streaming写数据，删数据，报错：主键重复！
原来是主键第二次插入后 报主键重复，修改主键后删除正常，下面贴代码
-- insert `MaxRisk_org_stream3`(msgDate,left_from,right_from,left_flightno,right_flightno)
-- value ("2000-01-01","Jerusalem","Jerusalem","XYZ456","JQX789")
-- delete from MaxRisk_org_stream3
-- where msgDate='2000-01-01'
-- and left_from="Jerusalem"
-- and right_from="Jerusalem"
-- and left_flightno='XYZ456'
-- and right_flightno='JQX789'
--第二次插入没问题
-- insert `MaxRisk_org_stream3`(msgDate,left_from,right_from,left_flightno,right_flightno)
-- value ("2000-01-01","Jerusalem","Jerusalem","XYZ456","JQX789")
-- 第二次删除报错！ 主键重复！
-- delete from MaxRisk_org_stream3
-- where msgDate='2000-01-01'
-- and left_from="Jerusalem"
-- and right_from="Jerusalem"
-- and left_flightno='XYZ456'
-- and right_flightno='JQX789'
-- 更新主键后再删除 好的
-- update MaxRisk_org_stream3
-- set left_from='erha'
-- where msgDate='2000-01-01'
-- and left_from="Jerusalem"
-- and right_from="Jerusalem"
-- and left_flightno='XYZ456'
-- and right_flightno='JQX789'
delete from MaxRisk_org_stream3
where msgDate='2000-01-01'
and left_from="erha"
and right_from="Jerusalem"
and left_flightno='XYZ456'
and right_flightno='JQX789'
注意注意：我的主键是：(`msgDate`,`left_from`,`right_from`,`left_flightno`,`right_flightno`),而且表MaxRisk_org_stream3是有触发器的，
删除的记录会插入MaxRisk_org_stream3_delete表，MaxRisk_org_stream3_delete表的主键重复！！
-- 表定义如下
CREATE TABLE `MaxRisk_org_stream3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`height_diff` double DEFAULT NULL,
`distance_diff` double DEFAULT NULL,
`angle_diff` double DEFAULT NULL,
`left_angle_absDiff_sum` double DEFAULT NULL,
`right_angle_absDiff_sum` double DEFAULT NULL,
`heightDiffRiskIndicate` int(11) DEFAULT NULL,
`distanceRiskIndicate` int(11) DEFAULT NULL,
`approachIndicator` int(11) DEFAULT NULL,
`crossIndicator` int(11) DEFAULT NULL,
`riskIndicatorSum` int(11) DEFAULT NULL,
`earliestMsgTime` varchar(20) DEFAULT NULL,
`latestMsgTime` varchar(20) DEFAULT NULL,
`endureTimeSecond` int(11) DEFAULT NULL,
`msgDate` varchar(20) NOT NULL,
`left_msgTime` varchar(20) DEFAULT NULL,
`timestamp` bigint(20) DEFAULT NULL,
`left_from` varchar(20) NOT NULL,
`left_flightno` varchar(20) NOT NULL,
`left_depDrome` varchar(20) DEFAULT NULL,
`left_arrDrome` varchar(20) DEFAULT NULL,
`left_longi` int(11) DEFAULT NULL,
`left_lati` int(11) DEFAULT NULL,
`left_C_modeHight` double DEFAULT NULL,
`left_speedToGround` double DEFAULT NULL,
`left_flyAngle` double DEFAULT NULL,
`left_ssrCode` varchar(20) DEFAULT NULL,
`left_upOrDownSpeed` double DEFAULT NULL,
`left_triple_A_modeCode` varchar(20) DEFAULT NULL,
`left_wake_type` varchar(20) DEFAULT NULL,
`right_msgTime` varchar(20) DEFAULT NULL,
`right_timeSecond` bigint(20) DEFAULT NULL,
`right_from` varchar(20) NOT NULL,
`right_flightno` varchar(20) NOT NULL,
`right_depDrome` varchar(20) DEFAULT NULL,
`right_arrDrome` varchar(20) DEFAULT NULL,
`right_longi` int(11) DEFAULT NULL,
`right_lati` int(11) DEFAULT NULL,
`right_C_modeHight` double DEFAULT NULL,
`right_speedToGround` double DEFAULT NULL,
`right_flyAngle` double DEFAULT NULL,
`right_ssrCode` varchar(20) DEFAULT NULL,
`right_upOrDownSpeed` double DEFAULT NULL,
`right_triple_A_modeCode` varchar(20) DEFAULT NULL,
`right_wake_type` varchar(20) DEFAULT NULL,
PRIMARY KEY (`msgDate`,`left_from`,`right_from`,`left_flightno`,`right_flightno`),
KEY `increse` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4797 DEFAULT CHARSET=utf8;
CREATE TRIGGER `sentMsgInsert3` AFTER INSERT ON `MaxRisk_org_stream3` FOR EACH ROW BEGIN insert into ChangedId (maxRiskDStreamId,changeType,createTime) VALUES ( new.id,'INSERT',now()); END;
CREATE TRIGGER `sentMsgUpdate3` AFTER UPDATE ON `MaxRisk_org_stream3` FOR EACH ROW BEGIN insert into ChangedId (maxRiskDStreamId,changeType,createTime) VALUES ( new.id,'UPDATE',now()); END;
CREATE TRIGGER `sentMsgDelete3` BEFORE DELETE ON `MaxRisk_org_stream3` FOR EACH ROW BEGIN insert into ChangedId (maxRiskDStreamId,changeType,createTime) VALUES ( old.id,'DELETE',now()); INSERT into MaxRisk_org_stream3_deleted values ( old.id, old.height_diff, old.distance_diff, old.angle_diff, old.left_angle_absDiff_sum, old.right_angle_absDiff_sum, old.heightDiffRiskIndicate, old.distanceRiskIndicate, old.approachIndicator, old.crossIndicator, old.riskIndicatorSum, old.earliestMsgTime, old.latestMsgTime, old.endureTimeSecond, old.msgDate, old.left_msgTime, old.timestamp, old.left_from, old.left_flightno, old.left_depDrome, old.left_arrDrome, old.left_longi, old.left_lati, old.left_C_modeHight, old.left_speedToGround, old.left_flyAngle, old.left_ssrCode, old.left_upOrDownSpeed, old.left_triple_A_modeCode, old.left_wake_type, old.right_msgTime, old.right_timeSecond, old.right_from, old.right_flightno, old.right_depDrome, old.right_arrDrome, old.right_longi, old.right_lati, old.right_C_modeHight, old.right_speedToGround, old.right_flyAngle, old.right_ssrCode, old.right_upOrDownSpeed, old.right_triple_A_modeCode, old.right_wake_type ); END;
