记录一个按月分组后创建分表并插入数据的存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `creatTable`( )
BEGIN
#设备操作日志按月建表并写入数据（注意：最后一次循环会执行两遍）
#声明结束标识
	DECLARE
		end_flag INT DEFAULT 0;
	DECLARE
		albumId BIGINT;
		#声明游标 album_curosr
	DECLARE
		album_curosr CURSOR FOR 
			SELECT DISTINCT DATE_FORMAT( Time, '%Y%m' ) FROM t_000_macoptlog GROUP BY DATE_FORMAT( Time, '%Y%m' );
		#设置终止标志
	DECLARE
		CONTINUE HANDLER FOR NOT 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 
	END REPEAT;
	#关闭游标
	CLOSE album_curosr;
END