MySQL 常用存储过程

需求：将老的按”月”存储的数据按照”年月”存储到新的表中(最近12个月)

将按”月”存储的模拟量导入到新的按照”年月”分表的新数据库中

-- 将按月存储的模拟量导入到新的按照"年月"分表的新数据库中
DROP PROCEDURE IF EXISTS insertAnalogValue2NewTable;
DELIMITER;;
CREATE PROCEDURE insertAnalogValue2NewTable() 
BEGIN
    DECLARE i INT;
    SET i = 0;
    WHILE i < 12 DO
        --     存入的表
        SET @table_name1 = CONCAT( 'a_analog_value_', date_format( date_sub( curdate()- 1, INTERVAL i MONTH ), '%Y_%m' ));
        --     查询的表
        SET @table_name2 = CONCAT( 'a_analog_value_', date_format( date_sub( curdate()- 1, INTERVAL i MONTH ), '%m' ));
        SET @sql_where = CONCAT( " FORCE INDEX (createtime) where createtime  like '", date_format( date_sub( curdate()- 1, INTERVAL i MONTH ), '%Y-%m' ), "%'");
        SET @insert_sql = CONCAT( "INSERT INTO ", @table_name1, " ( `id`, `deviceid`, `value`, `createtime`, `channelno`, `partunitloopcode`, `analog_value_type_id`, `analog_status_id`, `companyid`, `systemaddress` ) select `id`, `deviceid`, `value`, `createtime`, `channelno`, `partunitloopcode`, `analog_value_type_id`, `analog_status_id`, `companyid`, `systemaddress` from ", @table_name2, @sql_where );
        PREPARE insert_sql FROM @insert_sql;
        EXECUTE insert_sql;
        SET i = i + 1;
    END WHILE;
END;
;;
DELIMITER;
CALL insertAnalogValue2NewTable();

创建最近12个月的表

DROP PROCEDURE IF EXISTS createAnalogTable;
DELIMITER ;;
CREATE PROCEDURE createAnalogTable()
BEGIN
    declare i int;
    set i=0;
    while i<12 do  
        set @table_name = CONCAT('a_analog_value_',date_format(date_sub(curdate()-1, interval i month),'%Y_%m'));
            SET @sql_begin='CREATE TABLE ';
            SET @sql_end="(`id` int(11) NOT NULL AUTO_INCREMENT,
          `deviceid` int(11) NOT NULL,
          `value` varchar(20) DEFAULT NULL,
          `createtime` datetime DEFAULT NULL,
          `systemtime` datetime DEFAULT NULL,
          `channelno` varchar(5) DEFAULT NULL,
          `partunitloopcode` varchar(10) DEFAULT NULL,
          `analog_value_type_id` int(11) DEFAULT NULL,
          `analog_status_id` int(11) DEFAULT NULL,
          `companyid` int(11) DEFAULT NULL,
          `systemaddress` varchar(2) DEFAULT NULL,
          PRIMARY KEY (`id`) USING BTREE,
          KEY  `group_index` (`companyid`,`deviceid`,`partunitloopcode`,`channelno`,`systemaddress`) USING BTREE
        ) ENGINE=InnoDB AUTO_INCREMENT=140747 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;";
            set @create_sql=CONCAT(@sql_begin,@table_name,@sql_end);
            PREPARE create_table from @create_sql;
            EXECUTE create_table;
        insert into @table_name (`deviceid`, `value`, `createtime`, `channelno`, `partunitloopcode`, `analog_value_type_id`, `analog_status_id`, `companyid`, `systemaddress`) 
        SELECT `deviceid`, `value`, `createtime`, `channelno`, `partunitloopcode`, `analog_value_type_id`, `analog_status_id`, `companyid`, `systemaddress`
        from a_analog_value force index (createtime) where createtime like concat(date_format(date_sub(curdate()-1, interval i month),'%Y_%m'),"%")
    set i=i+1;
    end while;
    END;
;;
DELIMITER ;
CALL createAnalogTable();