DevOpsWaterOS-水务物联网设备运维管理系统通讯篇之Mysql,通讯协议配置-输出结果5

马铃薯_

已于 2024-06-12 14:34:29 修改

阅读量797

点赞数 19

分类专栏：水务运维系统 MYSQL 文章标签： mysql 数据库运维智能水表

于 2024-06-12 13:21:01 首次发布

本文链接：https://blog.csdn.net/guanyonggui/article/details/139623807

版权

水务运维系统同时被 2 个专栏收录

15 篇文章 1 订阅

订阅专栏

MYSQL

6 篇文章 0 订阅

订阅专栏

在MYSQL中创建SumCRC（）函数用于计算１６进制效验码

CREATE DEFINER=`root`@`localhost` FUNCTION `SumCRC`(hex_str VARCHAR(255)) RETURNS varchar(255) CHARSET gb2312
DETERMINISTIC
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE hex_pair VARCHAR(2);
DECLARE dec_val BIGINT DEFAULT 0;
   DECLARE HEX1016 INT;
DECLARE cur CURSOR FOR SELECT SUBSTRING(hex_str, num*2-1, 2)
FROM (SELECT 1 num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
                                                                   UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16
UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL SELECT 20
                                                                   UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28
                                                                   UNION ALL SELECT 29 UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32
UNION ALL SELECT 33 UNION ALL SELECT 34 UNION ALL SELECT 35 UNION ALL SELECT 36
                                                                   UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39
                                                               UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44 UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49
                                                                   UNION ALL SELECT 50 UNION ALL SELECT 51 UNION ALL SELECT 52 UNION ALL SELECT 53 UNION ALL SELECT 54 UNION ALL SELECT 55 UNION ALL SELECT 56 UNION ALL SELECT 57 UNION ALL SELECT 58 UNION ALL SELECT 59
                                                                   UNION ALL SELECT 60 UNION ALL SELECT 61 UNION ALL SELECT 62 UNION ALL SELECT 63 UNION ALL SELECT 64 UNION ALL SELECT 65 UNION ALL SELECT 66 UNION ALL SELECT 67 UNION ALL SELECT 68 UNION ALL SELECT 69
                                                                   UNION ALL SELECT 70 UNION ALL SELECT 71 UNION ALL SELECT 72 UNION ALL SELECT 73 UNION ALL SELECT 74 UNION ALL SELECT 75 UNION ALL SELECT 76 UNION ALL SELECT 77 UNION ALL SELECT 78 UNION ALL SELECT 79
) t
WHERE num * 2 <= CHAR_LENGTH(hex_str);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cur;

read_loop: LOOP
FETCH cur INTO hex_pair;
IF done THEN
LEAVE read_loop;
END IF;

SET dec_val = dec_val + CONV(hex_pair, 16, 10);

END LOOP;

CLOSE cur;

RETURN SUBSTRING( CONV(dec_val, 10, 16), LENGTH( CONV(dec_val, 10, 16)) - 1, 2);
END

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

运行结果

SELECT
FunctionalName,
FunctionalCoding,
CONCAT(FrameCodeS, HexSum,16) AS MergedValue, -- 合并FrameCodeS和HexSum列
direction
FROM (
SELECT
FunctionalName,
FunctionalCoding,
REPLACE(
GROUP_CONCAT(
CASE
WHEN FrameCode = 'data' THEN (
SELECT Framd
FROM (
SELECT
PFF_id,
GROUP_CONCAT(FrameCode ORDER BY FrameSorting) AS Framd
FROM
protocoldataframe
GROUP BY
PFF_id
) AS subquery
WHERE subquery.PFF_id = protocolframefunctional.id
)
ELSE FrameCode
END
ORDER BY FrameSorting
),
',', '' -- 替换逗号为空字符串
) AS FrameCodeS,
SumCRC(REPLACE(
GROUP_CONCAT(
CASE
WHEN FrameCode = 'data' THEN (
SELECT Framd
FROM (
SELECT
PFF_id,
GROUP_CONCAT(FrameCode ORDER BY FrameSorting) AS Framd
FROM
protocoldataframe
GROUP BY
PFF_id
) AS subquery
WHERE subquery.PFF_id = protocolframefunctional.id
)
ELSE FrameCode
END
ORDER BY FrameSorting
),
',', '' -- 替换逗号为空字符串
)) AS HexSum,
direction
FROM
protocolframefunctional
WHERE
CRCflag = 1
GROUP BY
FunctionalName,
FunctionalCoding,
direction
) AS subquery;

以上结果完全由protocoldataframe，protocolframefunctional配置完成通过MYSQL查询语法输出通讯指令集，使用了SumCRC（）自定义函数；

做这个目的，可以管理不同通讯协议．也可人根据任务需求输出相关通讯指令集．可向串口工具，可向SOCKET透传通道输出相关指令集，解决后续运维人员检修设备等操作．只需在通讯指令结构体中添加帧结构体及顺序，哪些字段参与CRC计算，哪些是固定指令集不能修改，哪些帧可变

`FrameLength` int NULL DEFAULT NULL COMMENT '帧长度',
`FrameSorting` int NULL DEFAULT NULL COMMENT '顺序',
`direction` varchar(255) CHARACTER SET gb2312 COLLATE gb2312_chinese_ci NULL DEFAULT NULL COMMENT '上下行协议',
`CRCflag` bit(1) NULL DEFAULT NULL COMMENT '参与校验标记',
`Frameflag` bit(1) NULL DEFAULT NULL COMMENT '是否引用当前伪帧的代码',

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

使用步骤

select * from protocolgroup; -- 第一步查找数据库中有哪些通讯协议记录id=3
select * from protocolframe where PG_id=?; -- 第二步查找数据库中有哪些通讯协议以PG_id=3为例确定地址域和数据域ID688,691
select * from protocolframefunctional where FunctionalCoding='901F' and direction='下行协议' and Frameflag=0 ; -- and Frameflag=0 Frameflag输出哪些帧值可被替换
select * from protocoldataframe where PFF_id=?;

说明protocolframefunctional表中要提前定义哪些字段是可被修改哪些不不可被修改rameflag=0可修改rameflag=１不可修改．只要更新FrameCode字段即可，如果设置通讯指令集会涉及protocoldataframe的DATA结构．该表一般用于存储设置类数据道理同样