1创建表时先分好区，否则数据量大分区卡
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for mon_dev_real
-- ----------------------------
DROP TABLE IF EXISTS `mon_dev_real`;
CREATE TABLE `mon_dev_real` (
`data_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '数据ID',
`factory_id` int(11) NOT NULL COMMENT '企业ID 冗余',
`device_id` varchar(10) NOT NULL COMMENT '设备ID',
`time_type` char(1) NOT NULL DEFAULT 'R' COMMENT '时间类型 枚举。总为“实时”',
`data_time` datetime NOT NULL COMMENT '数据时间',
`zm_time` varchar(20) DEFAULT NULL COMMENT '装煤时间',
`tm_time` varchar(20) DEFAULT NULL COMMENT '推焦时间',
`p1` double(20,6) DEFAULT NULL COMMENT '生产过程参数1 含义因设备而异',
`p2` double(20,6) DEFAULT NULL COMMENT '生产过程参数2 含义因设备而异',
`p3` double(20,6) DEFAULT NULL COMMENT '生产过程参数3 含义因设备而异',
`p4` double(20,6) DEFAULT NULL COMMENT '生产过程参数4 含义因设备而异',
`flow_in` double(20,6) DEFAULT NULL COMMENT '烟气流量-in',
`temp_in` double(20,6) DEFAULT NULL COMMENT '温度-in',
`nox_in` double(20,6) DEFAULT NULL COMMENT 'NOx-in',
`pm_in` double(20,6) DEFAULT NULL COMMENT '颗粒物-in',
`so2_in` double(20,6) DEFAULT NULL COMMENT 'SO2-in',
`flow_out` double(20,6) DEFAULT NULL COMMENT '烟气流量-out',
`temp_out` double(20,6) DEFAULT NULL COMMENT '温度-out',
`nox_out` double(20,6) DEFAULT NULL COMMENT 'NOx-out',
`o2_out` double(20,6) DEFAULT NULL COMMENT '含氧量-out',
`pm_out` double(20,6) DEFAULT NULL COMMENT '颗粒物-out',
`so2_out` double(20,6) DEFAULT NULL COMMENT 'SO2-out',
`flow_in_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`temp_in_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`nox_in_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`pm_in_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`so2_in_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`flow_out_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`temp_out_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`nox_out_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`o2_out_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`pm_out_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`so2_out_flag` char(1) DEFAULT NULL COMMENT '数据标志',
`tlj_vol` double(20,6) DEFAULT NULL COMMENT '脱硫剂使用量',
`txj_vol` double(20,6) DEFAULT NULL COMMENT '脱硝剂使用量',
`so2_cc1` double(20,6) DEFAULT NULL COMMENT 'SO2-除尘1',
`pm_cc1` double(20,6) DEFAULT NULL COMMENT '颗粒物-除尘1',
`wind_elec_cc1` double(20,6) DEFAULT NULL COMMENT '风机电流-除尘1',
`wind_vol_cc1` double(20,6) DEFAULT NULL COMMENT '除尘器风量-除尘1',
`so2_cc2` double(20,6) DEFAULT NULL COMMENT 'SO2-除尘2',
`pm_cc2` double(20,6) DEFAULT NULL COMMENT '颗粒物-除尘2',
`wind_elec_cc2` double(20,6) DEFAULT NULL COMMENT '风机电流-除尘2',
`wind_vol_cc2` double(20,6) DEFAULT NULL COMMENT '除尘器风量-除尘2',
`mq1` double(20,6) DEFAULT NULL COMMENT '煤气参数3 含义因设备而异',
`mq2` double(20,6) DEFAULT NULL COMMENT '煤气参数2 含义因设备而异',
`mq3` double(20,6) DEFAULT NULL COMMENT '煤气参数1 含义因设备而异',
`mq4` double(20,6) DEFAULT NULL COMMENT '煤气参数4 含义因设备而异',
`mq5` double(20,6) DEFAULT NULL COMMENT '煤气参数5 含义因设备而异',
`addi_param` varchar(2000) DEFAULT NULL COMMENT '附加参数 JSON。格式类似：{"pmIn":{"02":12,"03":23},...}',
`update_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`create_time` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '创建时间',
PRIMARY KEY (`data_id`,`data_time`),
KEY `device_id_create_time_idx` (`device_id`,`create_time`) USING BTREE,
KEY `device_id_data_time_idx` (`device_id`,`data_time`) USING BTREE,
KEY `data_time_idx` (`data_time`) USING BTREE,
KEY `create_time_idx` (`create_time`) USING BTREE,
KEY `f_d_time_idx` (`factory_id`,`device_id`,`data_time`) USING BTREE,
KEY `f_d_time` (`factory_id`,`device_id`,`data_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=248973055 DEFAULT CHARSET=utf8 COMMENT='设备数据(有组织)-实时 保存有组织设备的实时数据。p1~pn字段对应各设备的生产过程类参数';
ALTER TABLE mon_dev_real PARTITION BY RANGE (TO_DAYS(data_time))
(
PARTITION p_2105 VALUES LESS THAN (TO_DAYS('2021-06-01')),
PARTITION p_2106 VALUES LESS THAN (TO_DAYS('2021-07-01')),
PARTITION p_2107 VALUES LESS THAN (TO_DAYS('2021-08-01')),
PARTITION p_2108 VALUES LESS THAN (TO_DAYS('2021-09-01')),
PARTITION p_2109 VALUES LESS THAN (TO_DAYS('2021-10-01')),
PARTITION p_2110 VALUES LESS THAN (TO_DAYS('2021-11-01')),
PARTITION p_2111 VALUES LESS THAN (TO_DAYS('2021-12-01')),
PARTITION p_2112 VALUES LESS THAN (TO_DAYS('2022-01-01')),
PARTITION p_2201 VALUES LESS THAN (TO_DAYS('2022-02-01')),
PARTITION p_2202 VALUES LESS THAN (TO_DAYS('2022-03-01')),
PARTITION p_2203 VALUES LESS THAN (TO_DAYS('2022-04-01')),
PARTITION p_2204 VALUES LESS THAN (TO_DAYS('2022-05-01')),
PARTITION p_2205 VALUES LESS THAN (TO_DAYS('2022-06-01')),
PARTITION p_2206 VALUES LESS THAN (TO_DAYS('2022-07-01')),
PARTITION p_2207 VALUES LESS THAN (TO_DAYS('2022-08-01')),
PARTITION p_2208 VALUES LESS THAN (TO_DAYS('2022-09-01')),
PARTITION p_2209 VALUES LESS THAN (TO_DAYS('2022-10-01')),
PARTITION p_2210 VALUES LESS THAN (TO_DAYS('2022-11-01')),
PARTITION p_2211 VALUES LESS THAN (TO_DAYS('2022-12-01')),
PARTITION p_2212 VALUES LESS THAN (TO_DAYS('2023-01-01')),
PARTITION p_2301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
PARTITION p_2302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
PARTITION p_2303 VALUES LESS THAN (TO_DAYS('2023-04-01')),
PARTITION p_2304 VALUES LESS THAN (TO_DAYS('2023-05-01')),
PARTITION p_2305 VALUES LESS THAN (TO_DAYS('2023-06-01')),
PARTITION p_2306 VALUES LESS THAN (TO_DAYS('2023-07-01')),
PARTITION p_2307 VALUES LESS THAN (TO_DAYS('2023-08-01')),
PARTITION p_2308 VALUES LESS THAN (TO_DAYS('2023-09-01')),
PARTITION p_2309 VALUES LESS THAN (TO_DAYS('2023-10-01')),
PARTITION p_2310 VALUES LESS THAN (TO_DAYS('2023-11-01')),
PARTITION p_2311 VALUES LESS THAN (TO_DAYS('2023-12-01')),
PARTITION p_2312 VALUES LESS THAN (TO_DAYS('2024-01-01')),
PARTITION p_Other VALUES LESS THAN MAXVALUE);
