概述
mysql存储过程和触发器常用sql
#查看当前数据库中所有触发器
SHOW TRIGGERS
#查看当前数据库中某个触发器的定义
SHOW CREATE TRIGGER 触发器名
#删除触发器
DROP TRIGGER IF EXISTS 触发器名称;
#查看当前数据库中所有存储过程
show procedure status where db='traffic_monitor';
#查看当前数据库中存储过程sql
show create procedure 存储过程名称;
#删除当前数据库的存储过程
drop procedure if exists 存储过程名称;
drop procedure if exists CalculateDensityByPass;
drop procedure if exists CalculateEvent;
drop procedure if exists CalculateStatsEvent;
#删除当前数据库的触发器
drop TRIGGER if exists 触发器名称;
drop TRIGGER if exists density_after_insert;
drop TRIGGER if exists event_after_insert;
drop TRIGGER if exists event_before_update;
drop TRIGGER if exists pass_after_insert;
drop TRIGGER if exists realtime_before_insert;
drop TRIGGER if exists realtime_before_update;
触发器和存储过程实例：
CREATE TABLE `data_pass` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`device_code` varchar(32) NOT NULL COMMENT '设备编码',
`event_type` varchar(32) NOT NULL COMMENT '事件类型;tms.event.type',
`event_code` varchar(32) NOT NULL COMMENT '事件代码；tms.event.code',
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
`vehicle_id` varchar(32) DEFAULT NULL COMMENT '车辆ID',
`file_url` varchar(256) DEFAULT NULL COMMENT '图片文件url',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7822 DEFAULT CHARSET=utf8 COMMENT='过车表';
#创建触发器pass_after_insert关联表data_pass，在插入的每一行后执行
CREATE TRIGGER `pass_after_insert` AFTER INSERT ON `data_pass` FOR EACH ROW BEGIN
# 取得设备的relation
select relation into @_relation from base_device where code= new.device_code;
SET @_time = DATE_FORMAT(new.event_time,'%H');
SET @_day = DATE_FORMAT(new.event_time,'%Y-%m-%d');
case @_time
#根据索引值，存在就更新，不存在就插入初始值
when '00' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_00,c_00_length,c_00_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_00 = c_00 + 1,c_00_length = c_00_length+1,c_00_avg = c_00_length/c_00;
when '01' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_01,c_01_length,c_01_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_01 = c_01 + 1,c_01_length = c_01_length+1,c_01_avg = c_01_length/c_01;
when '02' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_02,c_02_length,c_02_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_02 = c_02 + 1,c_02_length = c_02_length+1,c_02_avg = c_02_length/c_02;
when '03' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_03,c_03_length,c_03_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_03 = c_03 + 1,c_03_length = c_03_length+1,c_03_avg = c_03_length/c_03;
when '04' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_04,c_00_length,c_04_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_04 = c_04 + 1,c_04_length = c_04_length+1,c_04_avg = c_04_length/c_04;
when '05' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_05,c_05_length,c_05_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_05 = c_05 + 1,c_05_length = c_05_length+1,c_05_avg = c_05_length/c_05;
when '06' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_06,c_06_length,c_06_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_06 = c_06 + 1,c_06_length = c_06_length+1,c_06_avg = c_06_length/c_06;
when '07' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_07,c_07_length,c_07_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_07 = c_07 + 1,c_07_length = c_07_length+1,c_07_avg = c_07_length/c_07;
when '08' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_08,c_08_length,c_08_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_08 = c_08 + 1,c_08_length = c_08_length+1,c_08_avg = c_08_length/c_08;
when '09' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_09,c_09_length,c_09_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_09 = c_09 + 1,c_09_length = c_09_length+1,c_09_avg = c_09_length/c_09;
when '10' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_10,c_10_length,c_10_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_10 = c_10 + 1,c_10_length = c_10_length+1,c_10_avg = c_10_length/c_10;
when '11' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_11,c_11_length,c_11_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_11 = c_11 + 1,c_11_length = c_11_length+1,c_11_avg = c_11_length/c_11;
when '12' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_12,c_12_length,c_12_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_12 = c_12 + 1,c_12_length = c_12_length+1,c_12_avg = c_12_length/c_12;
when '13' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_13,c_13_length,c_13_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_13 = c_13 + 1,c_13_length = c_13_length+1,c_13_avg = c_13_length/c_13;
when '14' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_14,c_14_length,c_14_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_14 = c_14 + 1,c_14_length = c_14_length+1,c_14_avg = c_14_length/c_14;
when '15' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_15,c_15_length,c_15_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_15 = c_15 + 1,c_15_length = c_15_length+1,c_15_avg = c_15_length/c_15;
when '16' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_16,c_16_length,c_16_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_16 = c_16 + 1,c_16_length = c_16_length+1,c_16_avg = c_16_length/c_16;
when '17' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_17,c_17_length,c_17_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_17 = c_17 + 1,c_17_length = c_17_length+1,c_17_avg = c_17_length/c_17;
when '18' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_18,c_18_length,c_18_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_18 = c_18 + 1,c_18_length = c_18_length+1,c_18_avg = c_18_length/c_18;
when '19' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_19,c_19_length,c_19_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_19 = c_19 + 1,c_19_length = c_19_length+1,c_19_avg = c_19_length/c_19;
when '20' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_20,c_20_length,c_20_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_20 = c_20 + 1,c_20_length = c_20_length+1,c_20_avg = c_20_length/c_20;
when '21' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_21,c_21_length,c_21_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_21 = c_21 + 1,c_21_length = c_21_length+1,c_21_avg = c_21_length/c_21;
when '22' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_22,c_22_length,c_22_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_22 = c_22 + 1,c_22_length = c_22_length+1,c_22_avg = c_22_length/c_22;
when '23' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_23,c_23_length,c_23_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_23 = c_23 + 1,c_23_length = c_23_length+1,c_23_avg = c_23_length/c_23;
End case;
#如果是20903
if @_relation = 20903 then
# 取得过车设备的上级设备
select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = new.device_code);
# 计算上级设备的密度,进入的减去出来的
select sum(value) into @_inport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20902) and event_code = '0';
select sum(value) into @_outport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20903) and event_code = '0';
set @_fvalue = @_inport - @_outport;
#出来的为空或者密度大于0，就更新实时数据表
if @_outport is null or @_fvalue > 0 then
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation ,1,new.id)
ON DUPLICATE KEY UPDATE event_time=new.event_time,value=value+1,data_id=new.id;
end if;
#如果不是20903，就直接更新时数据表
else
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation ,1,new.id)
ON DUPLICATE KEY UPDATE event_time=new.event_time,value=value+1,data_id=new.id;
end if;
#调用存储过程
#根据过车计算密度
Call CalculateDensityByPass(new.device_code,new.event_time);
# 计算上级设备的事件数
Call CalculateStatsEvent(new.device_code,new.event_code,@_day);
END;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CalculateDensityByPass`(IN `d_code` varchar(32),IN `e_time` timestamp)
COMMENT '根据过车计算密度'
BEGIN
# 取得过车设备的上级设备
select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = d_code);
# 计算上级设备的密度
select sum(value) into @_inport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20902) and event_code = '0';
select sum(value) into @_outport from data_realtime where device_code in (select code from base_device where f_id=@_fid and relation = 20903) and event_code = '0';
IF @_inport is null THEN
set @_fvalue = 0;
ELSEIF @_outport is null THEN
set @_fvalue = @_inport;
ELSEIF @_inport<@_outport THEN
set @_fvalue = 0;
ELSE
set @_fvalue = @_inport - @_outport;
END IF;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'2',e_time,20901,@_fvalue)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20902,@_inport)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_inport;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20903,@_outport)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_outport;
# 计算上上级设备的密度
repeat #循环执行找上级
# 取得上上级设备
select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = @_fdeviceCode);
# 计算上上级设备密度
select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and event_code = '2';
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'2',e_time,20901,@_fvalue)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
# 计算上上级设备入口车辆数
select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and relation = 20902;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20902,@_fvalue)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
# 计算上上级设备出口车辆数
select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and relation = 20903;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,'0',e_time,20903,@_fvalue)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
#直到@_ftype满足条件停止
UNTIL @_ftype=20801
End repeat;
END
CREATE DEFINER=`root`@`%` PROCEDURE `CalculateStatsEvent`(IN `d_code` varchar(32),IN `e_code` varchar(32),IN `e_time` timestamp)
BEGIN
# 计算上级设备的事件数
set @_fdeviceCode = d_code;
select relation into @_relation FROM base_device where code = d_code;
repeat #循环执行找上级
# 取得上级设备
select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = @_fdeviceCode);
# 计算上级设备事件数
select SUM(c_all), SUM(c_all_length), SUM(c_all_avg), SUM(c_00), SUM(c_00_length), SUM(c_00_avg), SUM(c_01), SUM(c_01_length), SUM(c_01_avg),
SUM(c_02), SUM(c_02_length), SUM(c_02_avg), SUM(c_03), SUM(c_03_length), SUM(c_03_avg), SUM(c_04), SUM(c_04_length), SUM(c_04_avg),
SUM(c_05), SUM(c_05_length), SUM(c_05_avg), SUM(c_06), SUM(c_06_length), SUM(c_06_avg), SUM(c_07), SUM(c_07_length), SUM(c_07_avg),
SUM(c_08), SUM(c_08_length), SUM(c_08_avg), SUM(c_09), SUM(c_09_length), SUM(c_09_avg), SUM(c_10), SUM(c_10_length), SUM(c_10_avg),
SUM(c_11), SUM(c_11_length), SUM(c_11_avg), SUM(c_12), SUM(c_12_length), SUM(c_12_avg), SUM(c_13), SUM(c_13_length), SUM(c_13_avg),
SUM(c_14), SUM(c_14_length), SUM(c_14_avg), SUM(c_15), SUM(c_15_length), SUM(c_15_avg), SUM(c_16), SUM(c_16_length), SUM(c_16_avg),
SUM(c_17), SUM(c_17_length), SUM(c_17_avg), SUM(c_18), SUM(c_18_length), SUM(c_18_avg), SUM(c_19), SUM(c_19_length), SUM(c_19_avg),
SUM(c_20), SUM(c_20_length), SUM(c_20_avg), SUM(c_21), SUM(c_21_length), SUM(c_21_avg), SUM(c_22), SUM(c_22_length), SUM(c_22_avg),
SUM(c_23), SUM(c_23_length), SUM(c_23_avg)
into @_c_all, @_c_all_length, @_c_all_avg, @_c_00, @_c_00_length, @_c_00_avg, @_c_01, @_c_01_length, @_c_01_avg,
@_c_02, @_c_02_length, @_c_02_avg, @_c_03, @_c_03_length, @_c_03_avg, @_c_04, @_c_04_length, @_c_04_avg,
@_c_05, @_c_05_length, @_c_05_avg, @_c_06, @_c_06_length, @_c_06_avg, @_c_07, @_c_07_length, @_c_07_avg,
@_c_08, @_c_08_length, @_c_08_avg, @_c_09, @_c_09_length, @_c_09_avg, @_c_10, @_c_10_length, @_c_10_avg,
@_c_11, @_c_11_length, @_c_11_avg, @_c_12, @_c_12_length, @_c_12_avg, @_c_13, @_c_13_length, @_c_13_avg,
@_c_14, @_c_14_length, @_c_14_avg, @_c_15, @_c_15_length, @_c_15_avg, @_c_16, @_c_16_length, @_c_16_avg,
@_c_17, @_c_17_length, @_c_17_avg, @_c_18, @_c_18_length, @_c_18_avg, @_c_19, @_c_19_length, @_c_19_avg,
@_c_20, @_c_20_length, @_c_20_avg, @_c_21, @_c_21_length, @_c_21_avg, @_c_22, @_c_22_length, @_c_22_avg,
@_c_23, @_c_23_length, @_c_23_avg
from stats_event where device_code in (select code from base_device where f_id=@_fid) and event_code = e_code and day = e_time;
INSERT INTO stats_event
(device_code, event_code, day, relation, c_all, c_all_length, c_all_avg, c_00, c_00_length,
c_00_avg, c_01, c_01_length, c_01_avg, c_02, c_02_length, c_02_avg, c_03, c_03_length,
c_03_avg, c_04, c_04_length, c_04_avg, c_05, c_05_length, c_05_avg, c_06, c_06_length,
c_06_avg, c_07, c_07_length, c_07_avg, c_08, c_08_length, c_08_avg, c_09, c_09_length,
c_09_avg, c_10, c_10_length, c_10_avg, c_11, c_11_length, c_11_avg, c_12, c_12_length,
c_12_avg, c_13, c_13_length, c_13_avg, c_14, c_14_length, c_14_avg, c_15, c_15_length,
c_15_avg, c_16, c_16_length, c_16_avg, c_17, c_17_length, c_17_avg, c_18, c_18_length,
c_18_avg, c_19, c_19_length, c_19_avg, c_20, c_20_length, c_20_avg, c_21, c_21_length,
c_21_avg, c_22, c_22_length, c_22_avg, c_23, c_23_length, c_23_avg)
VALUES (@_fdeviceCode,e_code,e_time,@_relation,@_c_all, @_c_all_length, @_c_all_avg, @_c_00, @_c_00_length,
@_c_00_avg, @_c_01, @_c_01_length, @_c_01_avg,
@_c_02, @_c_02_length, @_c_02_avg, @_c_03, @_c_03_length, @_c_03_avg, @_c_04, @_c_04_length, @_c_04_avg,
@_c_05, @_c_05_length, @_c_05_avg, @_c_06, @_c_06_length, @_c_06_avg, @_c_07, @_c_07_length, @_c_07_avg,
@_c_08, @_c_08_length, @_c_08_avg, @_c_09, @_c_09_length, @_c_09_avg, @_c_10, @_c_10_length, @_c_10_avg,
@_c_11, @_c_11_length, @_c_11_avg, @_c_12, @_c_12_length, @_c_12_avg, @_c_13, @_c_13_length, @_c_13_avg,
@_c_14, @_c_14_length, @_c_14_avg, @_c_15, @_c_15_length, @_c_15_avg, @_c_16, @_c_16_length, @_c_16_avg,
@_c_17, @_c_17_length, @_c_17_avg, @_c_18, @_c_18_length, @_c_18_avg, @_c_19, @_c_19_length, @_c_19_avg,
@_c_20, @_c_20_length, @_c_20_avg, @_c_21, @_c_21_length, @_c_21_avg, @_c_22, @_c_22_length, @_c_22_avg,
@_c_23, @_c_23_length, @_c_23_avg)
ON DUPLICATE KEY UPDATE c_all=@_c_all, c_all_length=@_c_all_length, c_all_avg=@_c_all_avg, c_00=@_c_00, c_00_length=@_c_00_length,
c_00_avg=@_c_00_avg, c_01=@_c_01, c_01_length=@_c_01_length, c_01_avg=@_c_01_avg, c_02=@_c_02, c_02_length=@_c_02_length, c_02_avg=@_c_02_avg, c_03=@_c_03, c_03_length=@_c_03_length,
c_03_avg=@_c_03_avg, c_04=@_c_04, c_04_length=@_c_04_length, c_04_avg=@_c_04_avg, c_05=@_c_05, c_05_length=@_c_05_length, c_05_avg=@_c_05_avg, c_06=@_c_06, c_06_length=@_c_06_length,
c_06_avg=@_c_06_avg, c_07=@_c_07, c_07_length=@_c_07_length, c_07_avg=@_c_07_avg, c_08=@_c_08, c_08_length=@_c_08_length, c_08_avg=@_c_08_avg, c_09=@_c_09, c_09_length=@_c_09_length,
c_09_avg=@_c_09_avg, c_10=@_c_10, c_10_length=@_c_10_length, c_10_avg=@_c_10_avg, c_11=@_c_11, c_11_length=@_c_11_length, c_11_avg=@_c_11_avg, c_12=@_c_12, c_12_length=@_c_12_length,
c_12_avg=@_c_12_avg, c_13=@_c_13, c_13_length=@_c_13_length, c_13_avg=@_c_13_avg, c_14=@_c_14, c_14_length=@_c_14_length, c_14_avg=@_c_14_avg, c_15=@_c_15, c_15_length=@_c_15_length,
c_15_avg=@_c_15_avg, c_16=@_c_16, c_16_length=@_c_16_length, c_16_avg=@_c_16_avg, c_17=@_c_17, c_17_length=@_c_17_length, c_17_avg=@_c_17_avg, c_18=@_c_18, c_18_length=@_c_18_length,
c_18_avg=@_c_18_avg, c_19=@_c_19, c_19_length=@_c_19_length, c_19_avg=@_c_19_avg, c_20=@_c_20, c_20_length=@_c_20_length, c_20_avg=@_c_20_avg, c_21=@_c_21, c_21_length=@_c_21_length,
c_21_avg=@_c_21_avg, c_22=@_c_22, c_22_length=@_c_22_length, c_22_avg=@_c_22_avg, c_23=@_c_03, c_23_length=@_c_23_length, c_23_avg=@_c_23_avg;
#循环执行找上级
UNTIL @_ftype=20801
End repeat;
END
CREATE DEFINER=`root`@`%` PROCEDURE `CalculateEvent`(IN `d_code` varchar(32),IN `e_code` varchar(32),IN `e_time` timestamp)
BEGIN
# 计算上级设备的事件数
set @_fdeviceCode = d_code;
repeat
# 取得上级设备
select id,code, type into @_fid,@_fdeviceCode,@_ftype from base_device where id = (select f_id from base_device where code = @_fdeviceCode);
# 计算上级设备事件数
select sum(value) into @_fvalue from data_realtime where device_code in (select code from base_device where f_id=@_fid) and event_code = e_code;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value) VALUES (@_fdeviceCode,e_code,e_time,20901,@_fvalue)
ON DUPLICATE KEY UPDATE event_time=e_time,value=@_fvalue;
UNTIL @_ftype=20801
End repeat;
END
#车密度表
CREATE TABLE `data_density` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`device_code` varchar(32) NOT NULL COMMENT '设备编码',
`event_type` varchar(32) NOT NULL COMMENT '事件类型;tms.event.type',
`event_code` varchar(32) NOT NULL COMMENT '事件代码；tms.event.code',
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
`vehicle_count` int(11) DEFAULT NULL COMMENT '车辆数量',
`file_url` varchar(256) DEFAULT NULL COMMENT '图片文件url',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5246 DEFAULT CHARSET=utf8 COMMENT='密度表';
#在插入密度事件后执行
CREATE TRIGGER `density_after_insert` AFTER INSERT ON `data_density` FOR EACH ROW BEGIN
# 取得设备的relation
select relation into @_relation from base_device where code= new.device_code;
SET @_time = DATE_FORMAT(new.event_time,'%H');
SET @_day = DATE_FORMAT(new.event_time,'%Y-%m-%d');
case @_time
when '00' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_00,c_00_length,c_00_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_00 = c_00 + 1,c_00_length = c_00_length+new.vehicle_count,c_00_avg = c_00_length/c_00;
when '01' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_01,c_01_length,c_01_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_01 = c_01 + 1,c_01_length = c_01_length+new.vehicle_count,c_01_avg = c_01_length/c_01;
when '02' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_02,c_02_length,c_02_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_02 = c_02 + 1,c_02_length = c_02_length+new.vehicle_count,c_02_avg = c_02_length/c_02;
when '03' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_03,c_03_length,c_03_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_03 = c_03 + 1,c_03_length = c_03_length+new.vehicle_count,c_03_avg = c_03_length/c_03;
when '04' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_04,c_00_length,c_04_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_04 = c_04 + 1,c_04_length = c_04_length+new.vehicle_count,c_04_avg = c_04_length/c_04;
when '05' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_05,c_05_length,c_05_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_05 = c_05 + 1,c_05_length = c_05_length+new.vehicle_count,c_05_avg = c_05_length/c_05;
when '06' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_06,c_06_length,c_06_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_06 = c_06 + 1,c_06_length = c_06_length+new.vehicle_count,c_06_avg = c_06_length/c_06;
when '07' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_07,c_07_length,c_07_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_07 = c_07 + 1,c_07_length = c_07_length+new.vehicle_count,c_07_avg = c_07_length/c_07;
when '08' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_08,c_08_length,c_08_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_08 = c_08 + 1,c_08_length = c_08_length+new.vehicle_count,c_08_avg = c_08_length/c_08;
when '09' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_09,c_09_length,c_09_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_09 = c_09 + 1,c_09_length = c_09_length+new.vehicle_count,c_09_avg = c_09_length/c_09;
when '10' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_10,c_10_length,c_10_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_10 = c_10 + 1,c_10_length = c_10_length+new.vehicle_count,c_10_avg = c_10_length/c_10;
when '11' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_11,c_11_length,c_11_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_11 = c_11 + 1,c_11_length = c_11_length+new.vehicle_count,c_11_avg = c_11_length/c_11;
when '12' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_12,c_12_length,c_12_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_12 = c_12 + 1,c_12_length = c_12_length+new.vehicle_count,c_12_avg = c_12_length/c_12;
when '13' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_13,c_13_length,c_13_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_13 = c_13 + 1,c_13_length = c_13_length+new.vehicle_count,c_13_avg = c_13_length/c_13;
when '14' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_14,c_14_length,c_14_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_14 = c_14 + 1,c_14_length = c_14_length+new.vehicle_count,c_14_avg = c_14_length/c_14;
when '15' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_15,c_15_length,c_15_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_15 = c_15 + 1,c_15_length = c_15_length+new.vehicle_count,c_15_avg = c_15_length/c_15;
when '16' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_16,c_16_length,c_16_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_16 = c_16 + 1,c_16_length = c_16_length+new.vehicle_count,c_16_avg = c_16_length/c_16;
when '17' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_17,c_17_length,c_17_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_17 = c_17 + 1,c_17_length = c_17_length+new.vehicle_count,c_17_avg = c_17_length/c_17;
when '18' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_18,c_18_length,c_18_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_18 = c_18 + 1,c_18_length = c_18_length+new.vehicle_count,c_18_avg = c_18_length/c_18;
when '19' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_19,c_19_length,c_19_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_19 = c_19 + 1,c_19_length = c_19_length+new.vehicle_count,c_19_avg = c_19_length/c_19;
when '20' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_20,c_20_length,c_20_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_20 = c_20 + 1,c_20_length = c_20_length+new.vehicle_count,c_20_avg = c_20_length/c_20;
when '21' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_21,c_21_length,c_21_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_21 = c_21 + 1,c_21_length = c_21_length+new.vehicle_count,c_21_avg = c_21_length/c_21;
when '22' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_22,c_22_length,c_22_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_22 = c_22 + 1,c_22_length = c_22_length+new.vehicle_count,c_22_avg = c_22_length/c_22;
when '23' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_23,c_23_length,c_23_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,new.vehicle_count,new.vehicle_count,1,new.vehicle_count,new.vehicle_count)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+new.vehicle_count,c_all_avg = c_all_length/c_all,c_23 = c_23 + 1,c_23_length = c_23_length+new.vehicle_count,c_23_avg = c_23_length/c_23;
End case;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation,new.vehicle_count,new.id)
ON DUPLICATE KEY UPDATE event_time=new.event_time,value=new.vehicle_count,data_id=new.id;
Call CalculateStatsEvent(new.device_code,new.event_code,@_day);
END;
#事件表
CREATE TABLE `data_event` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`device_code` varchar(32) NOT NULL COMMENT '设备编码',
`event_type` varchar(32) NOT NULL COMMENT '事件类型;tms.event.type',
`event_code` varchar(32) NOT NULL COMMENT '事件代码；tms.event.code',
`event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
`vehicle_id` varchar(32) DEFAULT NULL COMMENT '车辆ID',
`stop_time` datetime DEFAULT NULL COMMENT '停车时间',
`leave_time` datetime DEFAULT NULL COMMENT '离开时间',
`duration` int(11) DEFAULT NULL COMMENT '停车时长(s)',
`state` int(11) DEFAULT '0' COMMENT '状态： 0-告警，1-取消告警,2-手动取消告警',
`description` varchar(256) DEFAULT '' COMMENT '备注',
PRIMARY KEY (`id`),
KEY `device_code` (`device_code`,`vehicle_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1408 DEFAULT CHARSET=utf8 COMMENT='事件表';
#插入后执行触发器
CREATE TRIGGER `event_after_insert` AFTER INSERT ON `data_event` FOR EACH ROW BEGIN
# 取得设备的relation
select relation into @_relation from base_device where code= new.device_code;
SET @_time = DATE_FORMAT(new.event_time,'%H');
SET @_day = DATE_FORMAT(new.event_time,'%Y-%m-%d');
case @_time
when '00' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_00,c_00_length,c_00_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_00 = c_00 + 1,c_00_length = c_00_length+1,c_00_avg = c_00_length/c_00;
when '01' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_01,c_01_length,c_01_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_01 = c_01 + 1,c_01_length = c_01_length+1,c_01_avg = c_01_length/c_01;
when '02' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_02,c_02_length,c_02_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_02 = c_02 + 1,c_02_length = c_02_length+1,c_02_avg = c_02_length/c_02;
when '03' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_03,c_03_length,c_03_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_03 = c_03 + 1,c_03_length = c_03_length+1,c_03_avg = c_03_length/c_03;
when '04' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_04,c_00_length,c_04_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_04 = c_04 + 1,c_04_length = c_04_length+1,c_04_avg = c_04_length/c_04;
when '05' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_05,c_05_length,c_05_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_05 = c_05 + 1,c_05_length = c_05_length+1,c_05_avg = c_05_length/c_05;
when '06' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_06,c_06_length,c_06_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_06 = c_06 + 1,c_06_length = c_06_length+1,c_06_avg = c_06_length/c_06;
when '07' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_07,c_07_length,c_07_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_07 = c_07 + 1,c_07_length = c_07_length+1,c_07_avg = c_07_length/c_07;
when '08' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_08,c_08_length,c_08_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_08 = c_08 + 1,c_08_length = c_08_length+1,c_08_avg = c_08_length/c_08;
when '09' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_09,c_09_length,c_09_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_09 = c_09 + 1,c_09_length = c_09_length+1,c_09_avg = c_09_length/c_09;
when '10' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_10,c_10_length,c_10_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_10 = c_10 + 1,c_10_length = c_10_length+1,c_10_avg = c_10_length/c_10;
when '11' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_11,c_11_length,c_11_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_11 = c_11 + 1,c_11_length = c_11_length+1,c_11_avg = c_11_length/c_11;
when '12' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_12,c_12_length,c_12_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_12 = c_12 + 1,c_12_length = c_12_length+1,c_12_avg = c_12_length/c_12;
when '13' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_13,c_13_length,c_13_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_13 = c_13 + 1,c_13_length = c_13_length+1,c_13_avg = c_13_length/c_13;
when '14' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_14,c_14_length,c_14_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_14 = c_14 + 1,c_14_length = c_14_length+1,c_14_avg = c_14_length/c_14;
when '15' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_15,c_15_length,c_15_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_15 = c_15 + 1,c_15_length = c_15_length+1,c_15_avg = c_15_length/c_15;
when '16' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_16,c_16_length,c_16_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_16 = c_16 + 1,c_16_length = c_16_length+1,c_16_avg = c_16_length/c_16;
when '17' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_17,c_17_length,c_17_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_17 = c_17 + 1,c_17_length = c_17_length+1,c_17_avg = c_17_length/c_17;
when '18' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_18,c_18_length,c_18_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_18 = c_18 + 1,c_18_length = c_18_length+1,c_18_avg = c_18_length/c_18;
when '19' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_19,c_19_length,c_19_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_19 = c_19 + 1,c_19_length = c_19_length+1,c_19_avg = c_19_length/c_19;
when '20' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_20,c_20_length,c_20_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_20 = c_20 + 1,c_20_length = c_20_length+1,c_20_avg = c_20_length/c_20;
when '21' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_21,c_21_length,c_21_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_21 = c_21 + 1,c_21_length = c_21_length+1,c_21_avg = c_21_length/c_21;
when '22' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_22,c_22_length,c_22_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_22 = c_22 + 1,c_22_length = c_22_length+1,c_22_avg = c_22_length/c_22;
when '23' then
INSERT INTO stats_event (day,device_code,event_code,relation,c_all,c_all_length,c_all_avg,c_23,c_23_length,c_23_avg) VALUES(new.event_time,new.device_code,new.event_code,@_relation,1,1,1,1,1,1)
ON DUPLICATE KEY UPDATE c_all = c_all+1,c_all_length = c_all_length+1,c_all_avg = c_all_length/c_all,c_23 = c_23 + 1,c_23_length = c_23_length+1,c_23_avg = c_23_length/c_23;
End case;
INSERT INTO data_realtime(device_code,event_code,event_time,relation,value,data_id) VALUES(new.device_code,new.event_code,new.event_time,@_relation ,1,new.id)
ON DUPLICATE KEY UPDATE event_time=new.event_time,value=value+1,data_id=new.id;
#执行存储过程
Call CalculateEvent(new.device_code,new.event_code,new.event_time);
Call CalculateStatsEvent(new.device_code,new.event_code,@_day);
END;
#更新前执行
CREATE TRIGGER `event_before_update` BEFORE UPDATE ON `data_event` FOR EACH ROW BEGIN
SET @_duration = TIMESTAMPDIFF(SECOND,new.stop_time,new.leave_time);
if(@_duration > 0) then
SET new.duration = @_duration;
SET new.state=1;
end if;
END;
#实时数据表的前置触发器before和after 都是当事件类型是密度事件时，更新密度状态（根据阈值计算）
CREATE TABLE `data_realtime` (
`device_code` varchar(32) NOT NULL COMMENT '设备编码',
`event_code` varchar(32) NOT NULL,
`event_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '事件时间',
`relation` int(11) NOT NULL DEFAULT '0' COMMENT '关联类型；tms.device.relation',
`value` int(11) DEFAULT '0' COMMENT '值',
`state` int(11) DEFAULT NULL COMMENT '密度状态',
`data_id` int(11) DEFAULT NULL COMMENT '数据ID',
PRIMARY KEY (`device_code`,`event_code`,`relation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='实时数据表';
CREATE TRIGGER `realtime_before_insert` BEFORE INSERT ON `data_realtime` FOR EACH ROW BEGIN
SET @_deviceCode = new.device_code;
SET @_eventCode = new.event_code;
SET @_value = new.value;
if @_eventCode='2' then
# 密度事件
select threshold1,threshold2 into @_threshold1,@_threshold2 from base_device where code = @_deviceCode;
if @_value <= @_threshold1 then
set new.state = 0;
end if;
if @_value > @_threshold1 and @_value < @_threshold2 then
set new.state = 1;
end if;
if @_value >= @_threshold2 then
set new.state = 2;
end if;
End if;
END;
CREATE TRIGGER `realtime_before_update` BEFORE UPDATE ON `data_realtime` FOR EACH ROW BEGIN
SET @_deviceCode = new.device_code;
SET @_eventCode = new.event_code;
SET @_value = new.value;
if @_eventCode='2' then
# 密度事件
select threshold1,threshold2 into @_threshold1,@_threshold2 from base_device where code = @_deviceCode;
if @_value <= @_threshold1 then
set new.state = 0;
end if;
if @_value > @_threshold1 and @_value < @_threshold2 then
set new.state = 1;
end if;
if @_value >= @_threshold2 then
set new.state = 2;
end if;
End if;
END;
最后
以上就是内向河马为你收集整理的mysql存储过程和触发器常用sql的全部内容，希望文章能够帮你解决mysql存储过程和触发器常用sql所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复