记录MySQL数据迁移时使用的存储过程、游标以及存储过程中文乱码异常解决

最新推荐文章于 2024-04-10 17:49:50 发布

苏雨丶

最新推荐文章于 2024-04-10 17:49:50 发布

阅读量307

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_38082304/article/details/125988680

版权

存储过程中文乱码异常Incorrect string value: ‘\xE6\xB5\x8B\xE8\xAF\x95…’ for column ‘tmp_name’ at row 1 解决办法
经查阅资料发现，mysql存储过程插入参数涉及中文的要将varchar类型修改为nvarchar
，只需要将变量定义为nvarchar类型就能解决乱码问题。

-- 有用代码	
DROP PROCEDURE if EXISTS p_topology;
delimiter -- 声明分隔符
CREATE PROCEDURE p_topology()
BEGIN
	-- 遍历数据结束标志
  DECLARE done int DEFAULT FALSE;
	-- 定义变量用于接收游标内的记录值
	DECLARE tmp_id bigint(20);
	DECLARE tmp_station_id bigint(20);
	DECLARE tmp_child_types varchar(20);
	DECLARE tmp_type VARCHAR(50);	
	DECLARE t_type INT DEFAULT 1;
	DECLARE tmp_name nvarchar(200); -- 如果不用nvarchar插入时会报字符异常

  -- 定义游标 select后面的字段就是我们需要用到的字段值
  DECLARE cur CURSOR FOR SELECT id ,`name` ,station_id,child_types,metering_point_type FROM t_metering_point where metering_point_type in ('1','5') and is_deleted = 0;
	 

	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

	-- 打开游标
	OPEN cur;
	-- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据
    FETCH cur INTO tmp_id,tmp_name,tmp_station_id,tmp_child_types,tmp_type;
	
	-- 水计量点
		IF tmp_type = '5' THEN
			SET t_type = 2;
			SET tmp_child_types = '5';
		ELSE 
			SET tmp_child_types = CONCAT('1,',tmp_child_types);
		END IF;
		
		-- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
    -- 这里做你想做的循环的事件
		insert into t_equipment_topology(id,station_id,`name`,type,link_entity) values(tmp_id,tmp_station_id,tmp_name,t_type,tmp_child_types);
	END LOOP;
  -- 关闭游标
  CLOSE cur;
END
-- 调用存储过程
call p_topology();





-- 填充节点表数据  t_equipment_topology_node
DROP PROCEDURE if EXISTS p_topology_node;
delimiter -- 声明分隔符
CREATE PROCEDURE p_topology_node()
BEGIN
  DECLARE done int DEFAULT FALSE;
	DECLARE tmp_equipment_id bigint(20);
	DECLARE tmp_capacity DECIMAL(20);
	DECLARE tmp_id  bigint(20);
	DECLARE tmp_type INT(2);	
	DECLARE tmp_nid bigint(20);	
	
 DECLARE cur CURSOR FOR  select  r.equipment_id,r.metering_point_id,r.capacity,t.metering_point_type from r_metering_point_equipment  r inner join t_metering_point t
on r.metering_point_id = t.id 
where r.metering_point_id in (select id from t_equipment_topology);
	
	
 	   -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		  -- 打开游标
	OPEN cur;
	  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据
    FETCH cur INTO tmp_equipment_id,tmp_id,tmp_capacity,tmp_type;
		-- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
			-- 这里做你想做的循环的事件
			insert into t_equipment_topology_node(topology_id,node_type,equipment_id,metering_point_id,metering_type,capacity) values(tmp_id,1,tmp_equipment_id,tmp_id,tmp_type,tmp_capacity);
	END LOOP;
  -- 关闭游标
  CLOSE cur;
END

call p_topology_node();


-- 填充节点表数据  t_equipment_topology_node2
DROP PROCEDURE if EXISTS p_topology_node2;
delimiter -- 声明分隔符
CREATE PROCEDURE p_topology_node2()
BEGIN
  DECLARE done int DEFAULT FALSE;
	DECLARE tmp_topology_id bigint(20);
	DECLARE tmp_parent_id bigint(20);
	DECLARE tmp_equipment_id bigint(20);
	DECLARE tmp_point_id bigint(20);
	DECLARE tmp_point_type int(2);
	DECLARE tmp_capacity DECIMAL(10);
	
 DECLARE cur CURSOR FOR select node_id,point_id 't_id',p.id as 'metering_point_id',p.metering_point_type,e.equipment_id,e.capacity from r_metering_point_equipment e left join t_metering_point p on e.metering_point_id = p.id left join  (select n.id as node_id,t.id 'point_id' from t_equipment_topology_node n left join  t_equipment_topology t on t.id = n.topology_id  where t.type = 1 group by t.id) t
on p.parent_id = t.point_id  where p.metering_point_type not in ('1','5') and node_id is not null;
	
	
 	   -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		  -- 打开游标
	OPEN cur;
	  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据
    FETCH cur INTO tmp_parent_id,tmp_topology_id,tmp_point_id,tmp_point_type,tmp_equipment_id,tmp_capacity;
		-- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF;
			-- 这里做你想做的循环的事件
			insert into t_equipment_topology_node(topology_id,parent_id,node_type,equipment_id,metering_point_id,metering_type,capacity) values(tmp_topology_id,tmp_parent_id,1,tmp_equipment_id,tmp_point_id,tmp_point_type,tmp_capacity);
	END LOOP;
  -- 关闭游标
  CLOSE cur;
END

call p_topology_node2();

第一个存储过程sql脚本如下：

DROP TABLE IF EXISTS `t_metering_point`;
CREATE TABLE `t_metering_point`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `topology_id` bigint(20) NULL DEFAULT NULL COMMENT '拓扑结构id',
  `parent_id` bigint(20) NULL DEFAULT 0 COMMENT '计量点父id,顶层为0',
  `child_types` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '支持的子计量点id,暂定4位 0000，0否 1 是 第一位表示光伏，第二位表示储能，第三位表示风电',
  `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计量点名称',
  `metering_point_type` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '计量点类型 1.国网计量点 2.光伏并网点 3.储能并网点 4风电计量点. 5.水计量点 多个以逗号隔开',
  `bill_day` int(11) NULL DEFAULT NULL COMMENT '账单日 0表示月底',
  `on_grid_price` decimal(10, 4) NULL DEFAULT NULL COMMENT '上网电价',
  `discharge_subsidy` decimal(10, 4) NULL DEFAULT NULL COMMENT '储能放电补贴（元/度）',
  `subsidy_period` decimal(10, 1) NULL DEFAULT NULL COMMENT '补贴年限',
  `station_id` bigint(20) NULL DEFAULT NULL COMMENT '站点id',
  `operator_user_id` bigint(20) NULL DEFAULT NULL COMMENT '修改操作userid',
  `operator_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改者 姓名',
  `is_deleted` smallint(1) NULL DEFAULT 0 COMMENT '是否删除 1.是 0.否',
  `remark` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注',
  `update_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  `create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `energy_storage_discharge_subsidy` decimal(10, 4) NULL DEFAULT NULL COMMENT '储能放电补贴（元/度）',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 195 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '站点计量点' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of t_metering_point
-- ----------------------------
INSERT INTO `t_metering_point` VALUES (41, NULL, 0, '1,2,3,4', '测试系统电拓扑2', '1', 0, 0.6800, 1.9900, 10.0, 60108116124496, NULL, NULL, 0, NULL, '2020-12-25 13:40:01', '2020-12-22 10:35:45', NULL);
INSERT INTO `t_metering_point` VALUES (42, NULL, 0, '2,3', '测试2', '1', 0, 1.0000, 1.0000, NULL, 48271644747600, NULL, NULL, 0, NULL, '2022-07-25 10:59:05', '2020-12-22 10:40:13', NULL);
INSERT INTO `t_metering_point` VALUES (48, NULL, 41, NULL, '光伏1', '2', NULL, NULL, NULL, NULL, 48271644747600, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-22 11:01:20', NULL);
INSERT INTO `t_metering_point` VALUES (49, NULL, 42, NULL, '光伏2', '2', NULL, NULL, NULL, NULL, 48271644747600, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-22 11:01:50', NULL);
INSERT INTO `t_metering_point` VALUES (50, NULL, 42, NULL, '储能2', '3', NULL, NULL, NULL, NULL, 48271644747600, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-22 11:02:02', NULL);
INSERT INTO `t_metering_point` VALUES (51, NULL, 41, NULL, '储能21', '3', NULL, NULL, NULL, NULL, 48271644747600, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-22 11:02:19', NULL);
INSERT INTO `t_metering_point` VALUES (54, NULL, 41, NULL, '储能211', '3', NULL, NULL, NULL, NULL, 738028869742891001, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-24 20:11:53', NULL);
INSERT INTO `t_metering_point` VALUES (57, NULL, 0, '2,3', '国网计量点1', '1', 2, 1.0000, 1.0000, NULL, 48341497332560, 47, '毛晨', 0, NULL, '2022-07-25 10:59:05', '2020-12-25 14:28:17', NULL);
INSERT INTO `t_metering_point` VALUES (58, NULL, 0, '2,3', '国网计量点2', '1', 6, 1.0000, 1.0000, NULL, 48341497332560, 47, '毛晨', 0, NULL, '2022-07-25 10:59:05', '2020-12-25 14:29:11', NULL);
INSERT INTO `t_metering_point` VALUES (59, NULL, 57, NULL, '光伏计量点1', '2', NULL, NULL, NULL, NULL, 48341497332560, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-25 14:33:54', NULL);
INSERT INTO `t_metering_point` VALUES (60, NULL, 57, NULL, '储能计量点1', '3', NULL, NULL, NULL, NULL, 48341497332560, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-25 14:34:39', NULL);
INSERT INTO `t_metering_point` VALUES (61, NULL, 58, NULL, '光伏计量点2', '2', NULL, NULL, NULL, NULL, 48341497332560, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-25 14:35:15', NULL);
INSERT INTO `t_metering_point` VALUES (62, NULL, 58, NULL, '储能计量点2', '3', NULL, NULL, NULL, NULL, 48341497332560, NULL, NULL, 0, NULL, '2022-07-25 10:50:04', '2020-12-25 14:35:31', NULL);
INSERT INTO `t_metering_point` VALUES (67, NULL, 0, '2,3', '测试1计量点', '1', 4, 2.0000, 1.0000, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 10:59:05', '2020-12-30 16:02:04', NULL);
INSERT INTO `t_metering_point` VALUES (68, NULL, 67, NULL, '光伏计量点1', '2', NULL, NULL, NULL, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2020-12-30 16:02:50', NULL);
INSERT INTO `t_metering_point` VALUES (69, NULL, 67, NULL, '储能计量点1', '3', NULL, NULL, NULL, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2020-12-30 16:03:09', NULL);
INSERT INTO `t_metering_point` VALUES (70, NULL, 0, '2,3', '非光伏储能', '1', 1, 4.0000, 3.0000, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 10:59:05', '2020-12-31 10:25:14', NULL);
INSERT INTO `t_metering_point` VALUES (71, NULL, 0, '2,3', '只支持光伏', '1', 3, 2.0000, 2.0000, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 10:59:05', '2020-12-31 10:25:52', NULL);
INSERT INTO `t_metering_point` VALUES (72, NULL, 71, NULL, '光伏', '2', NULL, NULL, NULL, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2020-12-31 10:38:26', NULL);
INSERT INTO `t_metering_point` VALUES (73, NULL, 0, NULL, 'etata', '1', 2, NULL, NULL, NULL, 48451519758928, 47, '毛晨', 0, NULL, '2022-07-25 11:00:56', '2020-12-31 16:36:38', NULL);
INSERT INTO `t_metering_point` VALUES (74, NULL, 0, NULL, 'summerTest1', '1', 1, NULL, NULL, NULL, 48033208024912, 47, '毛晨', 0, NULL, '2022-07-25 11:00:56', '2021-01-06 10:06:17', NULL);
INSERT INTO `t_metering_point` VALUES (75, NULL, 0, '3', '国王', '1', 1, NULL, 1.0000, NULL, 48721348291664, 47, '毛晨', 0, NULL, '2022-07-25 10:59:30', '2021-01-11 18:04:32', NULL);
INSERT INTO `t_metering_point` VALUES (76, NULL, 75, NULL, '储能', '3', NULL, NULL, NULL, NULL, 48721348291664, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-01-11 18:07:49', NULL);
INSERT INTO `t_metering_point` VALUES (77, NULL, 0, NULL, '测试水计量点2', '5', 20, NULL, NULL, NULL, 51551424847440, 57, 'xiaoming', 0, NULL, '2021-05-19 17:00:45', '2021-05-17 16:31:43', NULL);
INSERT INTO `t_metering_point` VALUES (78, NULL, 0, '2,3', '国网', '1', 4, 0.5000, 1.0000, NULL, 51551424847440, 47, '毛晨', 0, NULL, '2022-07-25 10:59:05', '2021-05-19 16:56:24', NULL);
INSERT INTO `t_metering_point` VALUES (79, NULL, 78, NULL, '光伏', '2', NULL, NULL, NULL, NULL, 51551424847440, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-05-19 16:56:36', NULL);
INSERT INTO `t_metering_point` VALUES (80, NULL, 78, NULL, '储能', '3', NULL, NULL, NULL, NULL, 51551424847440, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-05-19 16:56:52', NULL);
INSERT INTO `t_metering_point` VALUES (81, NULL, 78, NULL, '风电', '4', NULL, NULL, NULL, NULL, 51551424847440, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-05-19 16:59:33', NULL);
INSERT INTO `t_metering_point` VALUES (88, NULL, 0, NULL, 'hhh', '5', 4, NULL, NULL, NULL, 50903607121232, 47, '毛晨', 0, NULL, '2021-05-25 16:58:32', '2021-05-25 16:58:32', NULL);
INSERT INTO `t_metering_point` VALUES (89, NULL, 0, '4', '1111', '1', 3, NULL, NULL, NULL, 50903607121232, 47, '毛晨', 0, NULL, '2022-07-25 10:59:41', '2021-05-25 17:01:10', NULL);
INSERT INTO `t_metering_point` VALUES (90, NULL, 0, '2,3,4', 'haoxy国网计量点', '1', 1, 1.0000, 1.0000, NULL, 52455202757456, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-06-29 15:53:18', NULL);
INSERT INTO `t_metering_point` VALUES (91, NULL, 90, NULL, 'haoxy光伏2', '2', NULL, NULL, NULL, NULL, 52455202757456, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-06-29 15:54:00', NULL);
INSERT INTO `t_metering_point` VALUES (92, NULL, 90, NULL, 'haoxy3储能', '3', NULL, NULL, NULL, NULL, 52455202757456, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-06-29 15:54:44', NULL);
INSERT INTO `t_metering_point` VALUES (93, NULL, 90, NULL, 'haoxy4风电计量点', '4', NULL, NULL, NULL, NULL, 52455202757456, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-06-29 15:55:15', NULL);
INSERT INTO `t_metering_point` VALUES (94, NULL, 0, NULL, 'hlh-test-1', '1', 1, NULL, NULL, NULL, 52459747135568, 47, '毛晨', 0, NULL, '2022-07-25 11:00:56', '2021-06-29 18:42:52', NULL);
INSERT INTO `t_metering_point` VALUES (95, NULL, 0, NULL, 'hlh-test-2', '1', 1, NULL, NULL, NULL, 52459747135568, 47, '毛晨', 0, NULL, '2022-07-25 11:00:56', '2021-06-29 18:46:55', NULL);
INSERT INTO `t_metering_point` VALUES (96, NULL, 0, '2,3,4', 'HAO国网计量点', '1', 1, 1.0000, 1.0000, NULL, 52480756155984, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-06-30 17:20:39', NULL);
INSERT INTO `t_metering_point` VALUES (97, NULL, 96, NULL, 'HAO光伏', '2', NULL, NULL, NULL, NULL, 52480756155984, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-06-30 17:21:09', NULL);
INSERT INTO `t_metering_point` VALUES (98, NULL, 96, NULL, 'HAO储能3', '3', NULL, NULL, NULL, NULL, 52480756155984, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-06-30 17:21:40', NULL);
INSERT INTO `t_metering_point` VALUES (99, NULL, 96, NULL, 'HAO风电4', '4', NULL, NULL, NULL, NULL, 52480756155984, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-06-30 17:22:00', NULL);
INSERT INTO `t_metering_point` VALUES (100, NULL, 0, '2,3,4', '国网', '1', 1, 1.0000, 0.5000, NULL, 52499518719312, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-07-01 13:41:55', NULL);
INSERT INTO `t_metering_point` VALUES (101, NULL, 100, NULL, '光伏并网点', '2', NULL, NULL, NULL, NULL, 52499518719312, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-07-01 13:42:18', NULL);
INSERT INTO `t_metering_point` VALUES (102, NULL, 100, NULL, '风电并网点', '4', NULL, NULL, NULL, NULL, 52499518719312, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-07-01 13:42:35', NULL);
INSERT INTO `t_metering_point` VALUES (103, NULL, 100, NULL, '储能并网点', '3', NULL, NULL, NULL, NULL, 52499518719312, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-07-01 13:43:05', NULL);
INSERT INTO `t_metering_point` VALUES (104, NULL, 0, NULL, '水设备', '5', 2, NULL, NULL, NULL, 52499518719312, 47, '毛晨', 0, NULL, '2021-07-02 10:41:51', '2021-07-02 10:41:51', NULL);
INSERT INTO `t_metering_point` VALUES (105, NULL, 0, '2,3,4', '国网2.4', '1', 1, 1.0000, 1.0000, NULL, 53074922614096, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-08-05 20:20:50', NULL);
INSERT INTO `t_metering_point` VALUES (107, NULL, 105, NULL, '风2.4', '4', NULL, NULL, NULL, NULL, 53074922614096, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-08-05 20:21:17', NULL);
INSERT INTO `t_metering_point` VALUES (108, NULL, 105, NULL, '储能2.4', '3', NULL, NULL, NULL, NULL, 53074922614096, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-08-05 20:21:36', NULL);
INSERT INTO `t_metering_point` VALUES (109, NULL, 0, NULL, '水2.4', '5', 3, NULL, NULL, NULL, 53074922614096, 47, '毛晨', 0, NULL, '2021-08-05 20:22:14', '2021-08-05 20:22:14', NULL);
INSERT INTO `t_metering_point` VALUES (110, NULL, 0, '2,4', '国网计量点A', '1', 1, 1.0000, NULL, NULL, 53229042566480, 47, '毛晨', 0, NULL, '2022-07-25 11:12:59', '2021-08-11 14:02:53', NULL);
INSERT INTO `t_metering_point` VALUES (111, NULL, 110, NULL, '风电', '4', NULL, NULL, NULL, NULL, 53229042566480, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-08-11 14:26:55', NULL);
INSERT INTO `t_metering_point` VALUES (126, NULL, 0, '2,3,4', '国网', '1', 2, 1.0000, 0.5000, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-08-18 09:48:25', NULL);
INSERT INTO `t_metering_point` VALUES (128, NULL, 126, NULL, '光伏并网点', '2', NULL, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-08-18 09:49:04', NULL);
INSERT INTO `t_metering_point` VALUES (129, NULL, 126, NULL, '储能并网点', '3', NULL, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-08-18 09:49:17', NULL);
INSERT INTO `t_metering_point` VALUES (141, NULL, 0, '2,3,4', '计量点1', '1', 2, 12.0000, 12.0000, NULL, 53544641592656, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-09-06 19:43:43', NULL);
INSERT INTO `t_metering_point` VALUES (142, NULL, 0, '2,3,4', '计量点2', '1', 2, 12.0000, 12.0000, NULL, 53544641592656, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-09-06 19:50:54', NULL);
INSERT INTO `t_metering_point` VALUES (143, NULL, 0, '2,3,4', '计量点3', '1', 2, 12.0000, 12.0000, NULL, 53544641592656, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-09-06 19:51:51', NULL);
INSERT INTO `t_metering_point` VALUES (144, NULL, 0, '2,3,4', '计量点4', '1', 2, 12.0000, 12.0000, NULL, 53544641592656, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-09-06 19:52:49', NULL);
INSERT INTO `t_metering_point` VALUES (145, NULL, 0, '2,3,4', '计量点5', '1', 2, 12.0000, 12.0000, NULL, 53544641592656, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-09-06 20:02:00', NULL);
INSERT INTO `t_metering_point` VALUES (146, NULL, 0, NULL, '水计量点', '5', 4, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2021-09-16 15:11:20', '2021-09-16 15:11:20', NULL);
INSERT INTO `t_metering_point` VALUES (150, NULL, 126, NULL, '风电计量点002', '4', NULL, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-09-17 09:54:53', NULL);
INSERT INTO `t_metering_point` VALUES (152, NULL, 0, NULL, '水1国网', '5', 2, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2021-09-17 13:50:20', '2021-09-17 13:49:50', NULL);
INSERT INTO `t_metering_point` VALUES (153, NULL, 0, NULL, '水计量点测试', '5', 3, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2021-09-23 14:41:30', '2021-09-23 13:11:12', NULL);
INSERT INTO `t_metering_point` VALUES (156, NULL, 105, NULL, '光4', '2', NULL, NULL, NULL, NULL, 53074922614096, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-09-26 13:37:11', NULL);
INSERT INTO `t_metering_point` VALUES (157, NULL, 0, NULL, '国网1', '1', 1, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 11:00:56', '2021-09-28 10:31:31', NULL);
INSERT INTO `t_metering_point` VALUES (158, NULL, 0, '2,3,4', '国网2', '1', 2, 1.0000, 1.0000, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 10:59:19', '2021-09-28 10:33:48', NULL);
INSERT INTO `t_metering_point` VALUES (159, NULL, 158, NULL, '风电计量点002', '2', NULL, NULL, NULL, NULL, 53557568625744, 47, '毛晨', 0, NULL, '2022-07-25 10:50:04', '2021-09-28 10:40:05', NULL);
INSERT INTO `t_metering_point` VALUES (160, NULL, 0, '2,3', '国网', '1', 1, 1.0000, 1.0000, NULL, 60108116124496, 237, '1666666', 0, NULL, '2022-07-25 10:59:05', '2022-06-10 13:33:34', NULL);
INSERT INTO `t_metering_point` VALUES (161, NULL, 160, NULL, '光伏', '2', 1, NULL, NULL, NULL, 60108116124496, 237, '1666666', 0, NULL, '2022-07-25 10:50:04', '2022-06-10 13:33:54', NULL);
INSERT INTO `t_metering_point` VALUES (162, NULL, 160, NULL, '储能', '3', 1, NULL, NULL, NULL, 60108116124496, 237, '1666666', 0, NULL, '2022-07-25 10:50:04', '2022-06-10 13:34:09', NULL);
INSERT INTO `t_metering_point` VALUES (163, 5, 0, '1,4,2', '测试系统电拓扑', '1', 1, 1.6800, 1.1900, 10.0, 60108116124496, NULL, NULL, 0, NULL, '2022-07-14 16:45:10', '2022-07-14 16:45:10', NULL);
INSERT INTO `t_metering_point` VALUES (164, 5, 0, NULL, '风电并网点', '4', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-25 11:00:56', '2022-07-14 16:45:50', NULL);
INSERT INTO `t_metering_point` VALUES (165, 5, 0, NULL, '储能并网点', '3', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-25 11:00:56', '2022-07-14 16:45:54', NULL);
INSERT INTO `t_metering_point` VALUES (166, 5, 0, NULL, '光伏并网点', '2', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-25 11:00:56', '2022-07-14 16:45:10', NULL);
INSERT INTO `t_metering_point` VALUES (167, 9, 0, '1,4,2', '测试系统电拓扑2', '1', 1, 0.6800, 1.9900, 10.0, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:49', '2022-07-18 10:35:13', NULL);
INSERT INTO `t_metering_point` VALUES (168, 9, 0, NULL, '光伏并网点', '2', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:49', '2022-07-18 10:35:19', NULL);
INSERT INTO `t_metering_point` VALUES (169, 9, 0, NULL, '风电并网点', '4', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:49', '2022-07-18 10:35:20', NULL);
INSERT INTO `t_metering_point` VALUES (170, 9, 0, NULL, '储能并网点', '3', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:49', '2022-07-18 10:35:59', NULL);
INSERT INTO `t_metering_point` VALUES (177, 16, 0, '5', '测试水', '5', 1, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:49', '2022-07-19 10:47:50', NULL);
INSERT INTO `t_metering_point` VALUES (178, 17, 0, '1,4,2', '测试电02', '1', NULL, 1.0000, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:31', '2022-07-19 10:58:31', NULL);
INSERT INTO `t_metering_point` VALUES (179, 17, 0, NULL, '光伏并网点', '2', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:31', '2022-07-19 10:58:31', NULL);
INSERT INTO `t_metering_point` VALUES (180, 17, 0, NULL, '风电并网点', '4', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 10:58:31', '2022-07-19 10:58:31', NULL);
INSERT INTO `t_metering_point` VALUES (181, 18, 0, '5', '测试水01', '5', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 11:08:53', '2022-07-19 11:08:53', NULL);
INSERT INTO `t_metering_point` VALUES (182, 19, 0, '5', '测试水02', '5', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 11:15:17', '2022-07-19 11:15:17', NULL);
INSERT INTO `t_metering_point` VALUES (183, 20, 0, '1,4,2,3', '测试电01', '1', NULL, 10.0000, 10.0000, 10.0, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 11:29:12', '2022-07-19 11:29:12', NULL);
INSERT INTO `t_metering_point` VALUES (184, 20, 0, NULL, '光伏并网点', '2', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 11:29:12', '2022-07-19 11:29:12', NULL);
INSERT INTO `t_metering_point` VALUES (185, 20, 0, NULL, '风电并网点', '4', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 11:29:13', '2022-07-19 11:29:13', NULL);
INSERT INTO `t_metering_point` VALUES (186, 20, 0, NULL, '储能并网点', '3', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 11:29:13', '2022-07-19 11:29:13', NULL);
INSERT INTO `t_metering_point` VALUES (187, 21, 0, '1,4,2,3', '测试电03', '1', NULL, 1.0000, 1.0000, 2.0, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 13:19:40', '2022-07-19 13:19:40', NULL);
INSERT INTO `t_metering_point` VALUES (188, 21, 0, NULL, '光伏并网点', '2', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 13:19:40', '2022-07-19 13:19:40', NULL);
INSERT INTO `t_metering_point` VALUES (189, 21, 0, NULL, '风电并网点', '4', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 13:19:40', '2022-07-19 13:19:40', NULL);
INSERT INTO `t_metering_point` VALUES (190, 21, 0, NULL, '储能并网点', '3', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 13:19:40', '2022-07-19 13:19:40', NULL);
INSERT INTO `t_metering_point` VALUES (191, 22, 0, '1,4,2,3', '测试电009', '1', NULL, 2.0000, 2.0000, 2.0, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 14:23:44', '2022-07-19 14:23:44', NULL);
INSERT INTO `t_metering_point` VALUES (192, 22, 0, NULL, '光伏并网点', '2', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 14:23:44', '2022-07-19 14:23:44', NULL);
INSERT INTO `t_metering_point` VALUES (193, 22, 0, NULL, '风电并网点', '4', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 14:23:44', '2022-07-19 14:23:44', NULL);
INSERT INTO `t_metering_point` VALUES (194, 22, 0, NULL, '储能并网点', '3', NULL, NULL, NULL, NULL, 60108116124496, NULL, NULL, 0, NULL, '2022-07-19 14:23:44', '2022-07-19 14:23:44', NULL);



DROP TABLE IF EXISTS `t_equipment_topology`;
CREATE TABLE `t_equipment_topology`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `station_id` bigint(20) NULL DEFAULT NULL COMMENT '站点id',
  `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '拓扑名称',
  `type` int(2) NULL DEFAULT NULL COMMENT '拓扑类型：1.系统拓扑-电气结构2.系统拓扑-水管网3.空间拓扑',
  `link_entity` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '关联计量点/设备类型：计量点类型/设备类型id 计量点多个按逗号隔开',
  `deleted` int(2) NULL DEFAULT 0 COMMENT '删除状态：0、正常 1、已删除',
  `create_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `update_time` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '编辑时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '设备拓扑结构表' ROW_FORMAT = Dynamic;