# testmerge

**Repository Path**: giteelzw/testmerge

## Basic Information

- **Project Name**: testmerge
- **Description**: 测试merge
- **Primary Language**: Java
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2025-09-04
- **Last Updated**: 2025-09-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 一、制定Topic的规范

```bash
Pattern pattern = Pattern.compile("^iot/deviceId/\d{1,10}/sensorTag/*");
iot/deviceId/+/sensorTag/+/#,home/#

# 举例
iot/deviceId/332244/sensorTag/co2
```



##     正则匹配测试

```java
 // 测试
 String topic = "iot/deviceId/332244/sensorTag/co2";

 Pattern pattern = Pattern.compile("^iot/deviceId/\\d{1,10}/sensorTag/*");
 boolean ok = pattern.matcher(topic).matches();
```


# 二、制定数据库存储结构

## 1. 表结构

```sql
/*
 Navicat Premium Data Transfer

 Source Server         : iot
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : localhost:3306
 Source Schema         : iot_mqtt

 Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

 Date: 08/11/2022 12:53:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_device
-- ----------------------------
DROP TABLE IF EXISTS `tb_device`;
CREATE TABLE `tb_device`  (
                              `device_id` int(32) NOT NULL COMMENT '设备ID 惟一',
                              `device_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                              PRIMARY KEY (`device_id`) USING BTREE,
                              UNIQUE INDEX `deviceId`(`device_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_device
-- ----------------------------
INSERT INTO `tb_device` VALUES (10000001, 'Test1');
INSERT INTO `tb_device` VALUES (10000002, 'Test2');
INSERT INTO `tb_device` VALUES (10000003, 'Test3');
INSERT INTO `tb_device` VALUES (10000004, 'Test4');
INSERT INTO `tb_device` VALUES (10000005, 'Test5');

-- ----------------------------
-- Table structure for tb_sensor
-- ----------------------------
DROP TABLE IF EXISTS `tb_sensor`;
CREATE TABLE `tb_sensor`  (
                              `device_id` int(32) NULL DEFAULT NULL,
                              `sensor_tag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                              `sensor_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                              `sensor_val` float(32, 2) NULL DEFAULT NULL,
                              `record_time` datetime(0) NULL DEFAULT NULL,
                              INDEX `deviceId`(`device_id`) USING BTREE,
                              CONSTRAINT `deviceId` FOREIGN KEY (`device_id`) REFERENCES `tb_device` (`device_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_sensor
-- ----------------------------
INSERT INTO `tb_sensor` VALUES (10000001, 'co', '一氧化碳', 20.10, '2022-11-07 20:23:48');
INSERT INTO `tb_sensor` VALUES (10000001, 'co2', '二氧化碳', 22.10, '2022-11-07 20:24:16');
INSERT INTO `tb_sensor` VALUES (10000001, 'co2', '二氧化碳', 19.20, '2022-11-07 20:24:42');
INSERT INTO `tb_sensor` VALUES (10000002, 'so2', '二氧化硫', 2.30, '2022-11-07 20:25:42');
INSERT INTO `tb_sensor` VALUES (10000002, 'co', '一氧化碳', 18.20, '2022-11-07 20:26:15');
INSERT INTO `tb_sensor` VALUES (10000003, 'so2', '二氧化硫', 3.20, '2022-11-07 20:26:38');
INSERT INTO `tb_sensor` VALUES (10000004, 'temp', '温度', 30.20, '2022-11-07 20:27:07');
INSERT INTO `tb_sensor` VALUES (10000004, 'humity', '湿度', 40.00, '2022-11-07 20:28:15');
INSERT INTO `tb_sensor` VALUES (10000005, 'tmep', '温度', 31.20, '2022-11-07 20:28:41');
INSERT INTO `tb_sensor` VALUES (10000005, 'humity', '湿度', 45.00, '2022-11-07 20:29:02');

SET FOREIGN_KEY_CHECKS = 1;



-- JOIN 查询举例
-- SELECT
--   device.device_id,
--   device.device_name,
--   sensor.sensor_tag,
--   sensor.sensor_val,
--   sensor.sensor_name,
--   sensor.record_time
-- FROM
--   tb_sensor AS sensor
--   RIGHT JOIN tb_device AS device ON sensor.device_id = device.device_id
-- WHERE
--   device.device_id = 10000002;
```

## 2.使用MybatisPlus操作数据库

测试

![1667884209771](imgs/1667884209771.png)



***部分Mapper书写举例而已***

```xml
<select id="getSensorDetailByDeviceId" resultType="com.hua.iot.domain.IOTDevice">
        SELECT device.device_id,device.device_name,
               sensor.sensor_name,sensor.sensor_tag,sensor.sensor_val,sensor.record_time
        FROM tb_sensor as sensor
        INNER JOIN tb_device as device
        ON sensor.device_id = device.device_id
        <where>
            <if test="deviceId!=null">
                device.device_id = #{deviceId}
            </if>
        </where>
    </select>
```



## 3.问题汇总

mysql 的group by 满足的规则要求：ONLY_FULL_GROUP_BY：合法检查

这个会报错，所以尽量关闭  



- 查看mysql是否启用ONLY_FULL_GROUP_BY

  > select @@global.sql_mode
  >
  > 
  >
  >  select @@global.sql_mode;
  > +-------------------------------------------------------------------------------------------------------------------------------------------+
  > | @@global.sql_mode                                                                                                                         |
  > +-------------------------------------------------------------------------------------------------------------------------------------------+
  > | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  > +-------------------------------------------------------------------------------------------------------------------------------------------+
  > 1 row in set (0.00 sec)

- 如果有，删除这个模式

  > set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  > 

-  但是这样的操作只能对当前mysql服务有效，如果重启mysql，则发现还有这样的问题。
  需要在mysql的配置文件中设置sql_mode 

  >  在my.ini中添加 (windows)
  > sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

  8.0 已经没有这个模式了

  >[mysqld] 
  >sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION





sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 三、Redis 设计

## 1.数据保存规则

首先手动插入数据库中的值，也按预设的规则 

key:   iot:deviceId:10000002:sensortag:co2 （  iot+设备id+传感器id)

val:   22.3   (传感器的值) )保存到redis

```bash
LPUSH iot:deviceId:10000001:sensorTag:co		 20.1  3.5  8.1  12.2 13.2 5.5  6.6 

LPUSH iot:deviceId:10000001:sensorTag:co2		 22.1   19.2  20.2

LPUSH iot:deviceId:10000001:sensorTag:temp       28

LPUSH iot:deviceId:10000002:sensorTag:co2		 18.2
LPUSH iot:deviceId:10000002:sensorTag:so2		 19.2

LPUSH iot:deviceId:10000003:sensorTag:so2		 3.2

LPUSH iot:deviceId:10000004:sensorTag:humity     40
LPUSH iot:deviceId:10000004:sensorTag:temp     30.2


LPUSH iot:deviceId:10000005:sensorTag:humity     41
LPUSH iot:deviceId:10000005:sensorTag:temp     31.2
```



## 2.序列化

*注意，redis 存取的时候要加序列化，否则会有乱码问题*

![1667901844323](imgs/1667901844323.png)



![1668059930144](imgs/1668059930144.png)



# 四、服务端设计

## 1.传感器控制器设计

<img src="imgs/1668060937917.png"  style="zoom:50%;" />

- 通过 Mybatis 获取传感器数据

- 通过Redis 获取传感器数据

  

## 2.设备控制器设计

<img src="imgs/1668063016957.png" alt="1668063016957" style="zoom:67%;" />

- 添加设备
- 更新设备
- 删除设备
- 获取设备信息



# 五、测试

## 1. 分页查询指定设备下所有传感器的历史数据 

<img src="imgs/1668061212993.png" alt="1668061212993" style="zoom:75%;" />

## 2.根据设备ID和传感器ID ， 分页查询

<img src="imgs/1668061309779.png" alt="1668061309779" style="zoom:67%;" />

## 3. 简单分页，查询指定设备下某传感器的历史数据 

<img src="imgs/1668061460794.png" alt="1668061460794" style="zoom:67%;" />

## 4. 简单获取指定设备下,所有传感器的最新数据 

![1668061667686](imgs/1668061667686.png)



## 5. 添加设备

<img src="./imgs/1668062003654.png" alert=""  style="zoom:50%; display: flex; margin-left: 20px">
***用数据库查看--添加设备成功***

<img src="./imgs/1668062033771.png" alert=""  style="zoom:50%;display: flex; margin-left: 100px">



## 6.更新设备

<img src="imgs/1668063077544.png" alt="1668063077544" style="zoom:50%;" />



***用数据库查看更新-->成功***

<img src="imgs/1668063093565.png" alt="1668063077544" style="zoom:50%;" />

## 7.删除设备

<img src="imgs/1668063253300.png" alt="1668063253300" style="zoom:67%;" />



## 8.查询设备

<img src="imgs/1668063428258.png" alt="1668063428258" style="zoom:67%;" />



# 六 MQTT+MyBatis+Redis综合测试

发送 MQTT消息到指定主题中，各个设备如果订阅了此主题就可以收到此消息，

TODO: 做心跳连接

举例：

## 1.模拟硬件设备发送消息

发送消息到  iot:deviceId:10000006:calciumion   值为  6.1

![1668065140881](imgs/1668065140881.png)



## 2.查看数据库保存

<img src="imgs/1668065598024.png" alt="1668065598024" style="zoom:67%;" />

##  3.查看Redis 缓存

![1668065688063](imgs/1668065688063.png)

## 4.使用查询传感器数据API ，简单查询

![1668065746502](imgs/1668065746502.png)



## 5.查询设备信息

<img src="imgs/1668065829391.png" alt="1668065829391" style="zoom:67%;" />