mysql 执行ddl_mysql存储过程执行ddl语句

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;