mysql存储过程执行ddl语句

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); SELECT table_month; END IF; IF table_day < 10 THEN SET table_day = CONCAT("0",table_day); SELECT table_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 NULL auto_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) SELECT 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 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 NULL auto_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; EXECUTE c_day_stmt; -- 执行插入数据语句 PREPARE i_parameter_day_stmt FROM @insert_data_sql; EXECUTE i_parameter_day_stmt; -- 执行删除语句 PREPARE d_parameter_day_stmt FROM @delete_data_sql; EXECUTE d_parameter_day_stmt; END;

此处需要注意的地方就是

　　1、PREPARE c_day_stmt FROM @creat_table_sql;  FROM后边只能跟上用户变量。

　　2、还有一个问题是从navicat张贴过来的DDL语句是不正确的。这里是针对mysql

　　　　　　

CREATE TABLE `parameter20170228` (
  `id` int(11) NOT NULL auto_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`),
  CONSTRAINT `parameter20170228_ibfk_1` FOREIGN KEY (`electric_meter_id`) REFERENCES `t_sg_electric_meter` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5335200 DEFAULT CHARSET=utf8;

这一段是copy的DDL语句，然而，这样直接运行是不行的，需要把" CONSTRAINT `parameter20170228_ibfk_1` FOREIGN KEY (`electric_meter_id`) REFERENCES `t_sg_electric_meter` (`id`)  "。

开头的  CONSTRAINT `parameter20170228_ibfk_1` 去除  "FOREIGN KEY (`electric_meter_id`) REFERENCES `t_sg_electric_meter` (`id`) "即可。

mysql存储过程执行ddl语句

标签：else   去除   param   parameter   not   时间设置   key   where   select