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);
                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`) "即可。

转载于:https://www.cnblogs.com/yubin-jia/p/6816011.html