CREATEDEFINER=`root`@`%`PROCEDURE`creatTable`()BEGIN#设备操作日志按月建表并写入数据(注意:最后一次循环会执行两遍)#声明结束标识DECLARE
end_flag INTDEFAULT0;DECLARE
albumId BIGINT;#声明游标 album_curosrDECLARE
album_curosr CURSORFORSELECTDISTINCT DATE_FORMAT(Time,'%Y%m')FROM t_000_macoptlog GROUPBY DATE_FORMAT(Time,'%Y%m');#设置终止标志DECLARECONTINUEHANDLERFORNOT FOUND
SET end_flag =1;#打开游标OPEN album_curosr;#遍历游标REPEAT#获取当前游标指针记录,取出值赋给自定义的变量FETCH album_curosr INTO albumId;#利用取到的值进行数据库的操作(建表,写入数据)SET@createSql= CONCAT("CREATE TABLE IF NOT EXISTS t_000_macoptlog_", albumId,"(
`Id` bigint(0) NOT NULL AUTO_INCREMENT,
`UserId` int(0) NULL DEFAULT NULL,
`OptFunction` varchar(128) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Time` datetime(0) NOT NULL,
`Memo` varchar(8888) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '请求参数',
`MacNo` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`Name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`ErrCode` int(0) NOT NULL DEFAULT -404 COMMENT '错误码',
`FactoryId` bigint(0) NOT NULL,
`IsSendMsg` bit(1) NOT NULL COMMENT '是否发送短信',
`Response` varchar(10000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '响应参数或错误详情',
PRIMARY KEY (`Id`) USING BTREE,
INDEX `detail_index`(`Time`, `MacNo`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4877170 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
");PREPARE stmt
FROM@createSql;EXECUTE stmt;SET@addData=
CONCAT("INSERT INTO t_000_macoptlog_",albumId," SELECT NULL,UserId,OptFunction,Time,Memo,MacNo,NAME,ErrCode,FactoryId,IsSendMsg,Response FROM t_000_macoptlog WHERE DATE_FORMAT( Time, '%Y%m' ) = ",albumId,";");PREPARE doAdd
FROM@addData;EXECUTE doAdd;# 根据 end_flag 判断是否结束
UNTIL end_flag
ENDREPEAT;#关闭游标CLOSE album_curosr;END