CREATE PROCEDURE parameter_split(in table_name VARCHAR(30),in start_time BIGINT(16),in end_time BIGINT(16))BEGIN
--得到时间
DECLARE table_year VARCHAR(5);DECLARE table_month VARCHAR(3);DECLARE table_day VARCHAR(3);--按开始时间设置年月日,并补齐单月为 05
SET table_year = YEAR(FROM_UNIXTIME(start_time/1000));SET table_month = MONTH(FROM_UNIXTIME(start_time/1000));SET table_day = DAY(FROM_UNIXTIME(start_time/1000));IF table_month < 10 THEN
SET table_month = CONCAT("0",table_month);SELECTtable_month;END IF;IF table_day < 10 THEN
SET table_day = CONCAT("0",table_day);SELECTtable_day;END IF;--设置表名
SET table_name =CONCAT(table_name,table_year,table_month,table_day);--创建表语句
IF table_name="t_sg_electric_meter_parameter_data" THEN
SET @creat_parameter_table_sql = CONCAT("CREATE TABLE IF NOT EXISTS",table_name,
" (`id`int(11) NOT NULLauto_increment,",
"`data_type`varchar(255) default NULL,",
"`freq`double default NULL,",
"`lineVoltage_A`double default NULL,",
"`lineVoltage_B`double default NULL,",
" `lineVoltage_C`double default NULL,",
"`phaseCurrent_A`double default NULL,",
"`phaseCurrent_B`double default NULL,",
"`phaseCurrent_C`double default NULL,",
" `phaseVoltage_A`double default NULL,",
"`phaseVoltage_B`double default NULL,",
"`phaseVoltage_C`double default NULL,",
"`timestamp` datetime default NULL,",
"`electric_meter_id`int(11) default NULL,",
"`timestamp_long`bigint(14) default NULL,",
"PRIMARY KEY(`id`),",
"KEY`FK_6xa5d3kb1j2cnhw4skdg9nlq9` (`electric_meter_id`),",
"KEY`index_name` (`timestamp_long`),",
"FOREIGN KEY (`electric_meter_id`) REFERENCES`t_sg_electric_meter` (`id`)",
") ENGINE=InnoDB AUTO_INCREMENT=5184520 DEFAULT CHARSET=utf8;"
);--创建插入语句
SET @insert_data_sql = CONCAT("INSERT INTO",table_name," (data_type,freq,lineVoltage_A,lineVoltage_B,lineVoltage_C,phaseCurrent_A,phaseCurrent_B,",
"phaseCurrent_C,phaseVoltage_A,phaseVoltage_B,phaseVoltage_C,timestamp,electric_meter_id,timestamp_long) SELECTdata_type,freq,lineVoltage_A,lineVoltage_B,lineVoltage_C,phaseCurrent_A,phaseCurrent_B,",
"phaseCurrent_C,phaseVoltage_A,phaseVoltage_B,phaseVoltage_C,timestamp,electric_meter_id,timestamp_long from t_sg_electric_meter_parameter_data WHERE timestamp_long BETWEEN",
start_time,"AND",end_time);--创建删除数据的语句
SET @delete_data_sql = CONCAT("DELETE FROM t_sg_electric_meter_parameter_data WHERE timestamp_long BETWEEN ",start_time," AND",end_time);
ELSEIF table_name="t_sg_electric_meter_demand_data" THEN
SET @creat_demand_table_sql = CONCAT("CREATE TABLE IF NOT EXISTS",table_name,
" (`id`int(11) NOT NULLauto_increment,",
"`active_demand`double default NULL,",
"data_type`varchar(255) default NULL,",
"`reactive_demand`double default NULL,",
"`timestamp` datetime default NULL,",
"`total_demand`double default NULL,",
"`electric_meter_id`int(11) default NULL,",
"`timestamp_long`bigint(14) default NULL,",
"PRIMARY KEY(`id`),",
"KEY`index_electric_meter_id` (`electric_meter_id`),",
"KEY`index_timestamp_long` (`timestamp_long`),",
"FOREIGN KEY (`electric_meter_id`) REFERENCES`t_sg_electric_meter` (`id`)",
") ENGINE=InnoDB AUTO_INCREMENT=2017227 DEFAULT CHARSET=utf8;"
);END IF;--执行创建表语句
PREPARE c_day_stmt FROM @creat_table_sql;EXECUTEc_day_stmt;--执行插入数据语句
PREPARE i_parameter_day_stmt FROM @insert_data_sql;EXECUTEi_parameter_day_stmt;--执行删除语句
PREPARE d_parameter_day_stmt FROM @delete_data_sql;EXECUTEd_parameter_day_stmt;END;