将以下存储过程改写为适用于高斯数据库的：
drop procedure if exists single_log_generate_partition;
DELIMITER //
CREATE PROCEDURE `single_log_generate_partition`(begin_date_in varchar(10))
BEGIN
DECLARE stmt VARCHAR(200);
DECLARE countNum LONG;
DECLARE countNumStart LONG;
DECLARE countNumEnd LONG;
DECLARE tableExists TINYINT DEFAULT 0;
/***********************************************
生成分区表
***********************************************/
set @yearIn = SUBSTRING(begin_date_in FROM 1 FOR 4);
set @monthIn = SUBSTRING(begin_date_in FROM 6 FOR 2);
set @dayIn = SUBSTRING(begin_date_in FROM 9 FOR 2);
set @yearMonth = CONCAT(@yearIn,@monthIn);
set @monthLastDay = SUBSTRING(LAST_DAY(STR_TO_DATE(begin_date_in, '%Y-%m-%d')) FROM 9 FOR 2);
set @tableName = CONCAT('event_log_alarm',@yearIn,@monthIn);
SELECT 1 INTO tableExists FROM information_schema.`TABLES` WHERE TABLE_NAME=@tableName;
#select tableExists;
IF tableExists = 0 THEN
set @sql1 = "CREATE TABLE `";
set @sql2 = "` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`DEVICE_CODE` varchar(64) COLLATE utf8_bin NOT NULL COMMENT '设备编码',
`DEVICE_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '设备名称',
`UNIT_TYPE` tinyint(3) NOT NULL DEFAULT '0' COMMENT '报警单元类型',
`UNIT_SEQ` tinyint(3) DEFAULT '0',
`CHANNEL_SEQ` int(10) NOT NULL DEFAULT '0' COMMENT '通道号',
`CHANNEL_NAME` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '通道名称',
`ALARM_STAT` tinyint(3) unsigned NOT NULL COMMENT '报警状态；1：报警产生，2：报警消失',
`ALARM_TYPE` int(16) unsigned NOT NULL COMMENT '报警类型',
`ALARM_DATE` datetime NOT NULL COMMENT '报警时间',
`ALARM_GRADE` smallint(5) unsigned NOT NULL COMMENT '报警等级',
`HANDLE_USER` varchar(50) CHARACTER SET gbk DEFAULT NULL COMMENT '处理用户的登录名',
`HANDLE_DATE` datetime DEFAULT NULL COMMENT '处理时间',
`HANDLE_MESSAGE` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '处理内容',
`HANDLE_STAT` smallint(5) unsigned DEFAULT NULL COMMENT '处理状态',
`ALARM_PICTURE` varchar(500) COLLATE utf8_bin DEFAULT NULL COMMENT '报警图片路径',
`ALARM_PICTURE_SIZE` bigint(10) unsigned DEFAULT NULL COMMENT '报警图片大小',
`ALARM_CODE` varchar(50) COLLATE utf8_bin NOT NULL COMMENT '报警编号',
`NODE_TYPE` tinyint(1) DEFAULT NULL COMMENT '节点类型（1设备，2通道）',
`NODE_CODE` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '设备编码或通道编码',
`MEMO` varchar(64) COLLATE utf8_bin DEFAULT NULL COMMENT '备注',
`ORG_CODE` varchar(90) CHARACTER SET utf8 DEFAULT NULL COMMENT '所属组织编码',
`ORG_NAME` varchar(50) CHARACTER SET gbk DEFAULT NULL COMMENT '组织编码',
`DEVICE_CATEGORY` tinyint(3) DEFAULT NULL COMMENT '设备种类',
`ALARM_POSITION` varchar(64) CHARACTER SET gbk COLLATE gbk_bin NOT NULL COMMENT '设备名称或者通道名称',
`LINK_NAMES` varchar(512) CHARACTER SET utf8 DEFAULT NULL COMMENT '联动能力',
`IS_EVENT` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否事件',
`SUB_SYSTEM` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT '来源子系统',
`INSERT_FLAG` tinyint(1) DEFAULT NULL COMMENT '是否insert',
`EXTEND` varchar(2048) CHARACTER SET utf8 DEFAULT NULL COMMENT '扩展字段',
PRIMARY KEY (`ID`,`ALARM_DATE`),
KEY `idx_time_org` (`ALARM_DATE`,`ORG_CODE`) USING BTREE,
KEY `idx_alarm_code` (`ALARM_CODE`) USING BTREE,
KEY `idx_type_org` (`ALARM_TYPE`,`ORG_CODE`) USING BTREE,
KEY `idx_log_alarm_position` (`ALARM_POSITION`) USING BTREE,
KEY `idx_log_alarm_handle_user` (`HANDLE_USER`) USING BTREE,
KEY `idx_log_alarm_handle_date` (`HANDLE_DATE`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=";
set @sql3 = " DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='报警信息表' partition by range columns(`ALARM_DATE`)(";
set @sql4 = ");";
set @stmt = 'SELECT COUNT_NUM_END into @count2 FROM event_table_cut_condition ORDER BY TABLE_TIME DESC,ID DESC LIMIT 1 ;';
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
deallocate prepare stmt1;
select @count2;
IF @count2 IS NULL THEN
set @stmt = 'SELECT COUNT(1) into @count1 FROM event_log_alarm ;';
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
deallocate prepare stmt1;
set countNum = @count1;
INSERT INTO event_table_cut_condition (TABLE_NAME,COUNT_NUM_START,COUNT_NUM_END,ENABLE,TABLE_TIME) VALUES ('event_log_alarm',0,countNum,1,@yearMonth);
set countNumStart = countNum + 1000000;
set countNumEnd = countNum + 21000000;
INSERT INTO event_table_cut_condition (TABLE_NAME,COUNT_NUM_START,COUNT_NUM_END,ENABLE,TABLE_TIME) VALUES (@tableName,countNumStart,countNumEnd,1,@yearMonth);
ELSE
set countNum = @count2;
set countNumStart = countNum + 1000000;
set countNumEnd = countNum + 21000000;
INSERT INTO event_table_cut_condition (TABLE_NAME,COUNT_NUM_START,COUNT_NUM_END,ENABLE,TABLE_TIME) VALUES (@tableName,countNumStart,countNumEnd,1,@yearMonth);
END IF;
set @i=0;
#select @monthLastDay;
#select @dayIn;
#select @monthLastDay-@dayIn;
loop1: WHILE @i<=@monthLastDay-@dayIn DO
set @dateStart=DATE_ADD(begin_date_in,INTERVAL @i DAY);
set @dateIN=DATE_ADD(begin_date_in,INTERVAL (@i+1) DAY);
set @sql3 = CONCAT(@sql3,'PARTITION p',REPLACE(@dateStart,'-','')," VALUES LESS THAN ('",DATE_FORMAT(@dateIN, '%Y-%m-%d'),"'),");
set @i=@i+1;
END WHILE loop1;
set @sql3 = SUBSTRING(@sql3 FROM 1 FOR CHAR_LENGTH(@sql3)-1);
#select @sql3;
set @sql = CONCAT(@sql1,@tableName,@sql2,countNumStart,@sql3,@sql4);
#select @sql;
set @stmt = @sql;
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
deallocate prepare stmt1;
END IF;
END;
//
DELIMITER ;
扫一扫
4490
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
