Shulou(Shulou.com)06/01 Report--

This article mainly introduces "how to store messages in MySQL database". In daily operation, I believe many people have doubts about how to store messages in MySQL database. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to store messages in MySQL database". Next, please follow the editor to study!

Scene introduction

This scenario requires that messages that meet the criteria under the topic specified by EMQ X be stored in the MySQL database. In order to facilitate subsequent analysis and retrieval, the message content needs to be split and stored.

In this scenario, the information reported by the device end is as follows:

Report topic: cmd/state/:id, in which id represents the vehicle client identification number

Message body:

{"id": "NXP-058659730253-963945118132721-22", / / client identification code "speed": 32.12, / / vehicle speed "direction": 198.33212, / / driving direction "tachometer": 3211, / / engine speed When the value is greater than 8000, you need to store "dynamical": 8.93, / / instantaneous fuel consumption "location": {/ / GPS longitude and latitude data "lng": 116.296011, "lat": 40.005091}, "ts": 1563268202 / / reporting time}

When the reported data engine speed value is greater than 8000, the current information is stored for subsequent analysis of the user's vehicle usage.

Prepare for work to create a database

Create an iot_data database to store message data. Here, specify the database encoding as utf8mb4 to avoid coding problems:

CREATE DATABASE `emqx_rule_engine_ output` CHARACTER SET utf8mb4; creates a data table

According to the requirements of the scenario, the use_statistics structure and field annotations of the data table are created as follows:

CREATE TABLE `use_ statistics` (`id` int (11) NOT NULL AUTO_INCREMENT, `msg_ id` varchar (100) DEFAULT NULL COMMENT 'client identification number', `speed`float unsigned DEFAULT '0.00' COMMENT' current speed', `tachometer` int (11) unsigned DEFAULT'0' COMMENT 'engine speed', `ts`int (11) unsigned DEFAULT'0' COMMENT 'reporting timestamp', `msg_ id` varchar (50) DEFAULT NULL COMMENT 'MQTT message ID', PRIMARY KEY (`id`) KEY `client_id_ index` (`client_ id`) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

After successful creation, confirm the existence of the datasheet through the MySQL command:

Database changedmysql > desc use_statistics +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | int (11) | NO | PRI | NULL | auto_increment | | client_id | varchar | YES | MUL | NULL | | speed | float unsigned | YES | | 0 | tachometer | int (11) unsigned | YES | | 0 | | ts | int (11) unsigned | YES | | 0 | | msg_id | varchar (50) | YES | | NULL | | +-+-| -+ 6 rows in set (0.01sec) configuration instructions for creating resources

Open EMQ X Dashboard, go to the resources page of the left menu, click the New button, and type the MySQL server information to create resources.

The network environment of the nodes in the EMQ X cluster may be different from each other. Click the status button in the list after resource creation to view the connection status of each node resource. If the resources on the node are not available, check whether the configuration and network connectivity are correct, and click the reconnect button to reconnect manually.

Create a rule

Go to the rules page of the left menu and click the New button to create the rules. Here, we choose to trigger the event message release, and trigger the rule for data processing when the message is released.

After the trigger event is selected, we can see the optional fields and sample SQL on the interface:

Filter the required fields

The rule engine uses SQL statements to process rule conditions. In this business, we need to select all the fields in payload separately and select them in payload.fieldName format. We also need the topic, qos and id information of the message context. The current SQL is as follows:

SELECT payload.id as client_id, payload.speed as speed, payload.tachometer as tachometer, payload.ts as ts, idFROM "message.publish" WHERE topic = ~ 'tUniverse'to establish the screening conditions

Use the SQL statement WHERE sentence for conditional filtering. In this business, we need to define two conditions:

Only deal with cmd/state/:id topics and filter topic with the topic wildcard = ~: topic = ~ 'cmd/state/+'

Only messages with tachometer > 8000 are processed and tachometer is filtered using comparators: payload.tachometer > 8000

The SQL obtained from the previous step is as follows:

SELECT payload.id as client_id, payload.speed as speed, payload.tachometer as tachometer, payload.ts as ts, idFROM "message.publish" WHERE topic = ~ 'cmd/state/+' AND payload.tachometer > 8000 use the SQL test function for output testing

With the SQL testing function, we can view the current SQL processed data output in real time, which requires us to specify payload and other simulation raw data.

The payload data is as follows. Be careful to change the size of the tachometer value to meet the SQL condition:

{"id": "NXP-058659730253-963945118132721-22", "speed": 32.12, "direction": 198.33212, "tachometer": 9001, "dynamical": 8.93, "location": {"lng": 116.296011, "lat": 40.005091}, "ts": 1563268202}

Click the SQL test switch button, change topic and payload to the information in the scenario, and click the test button to view the data output:

The test output data is:

{"client_id": "NXP-058659730253-963945118132721-22", "id": "589A429E9572FB44B0000057C0001", "speed": 32.12, "tachometer": 9001, "ts": 1563268202}

The test output is as expected, and we can take the next steps.

Add response actions to store messages to MySQL

After the SQL conditional input and output is correct, we continue to add the appropriate actions, configure to write the SQL statement, and store the filter results in MySQL.

Click the add button in the response action, select Save data to the MySQL action, select the resource just selected, we populate the SQL statement with ${fieldName} syntax, insert the data into the database, and finally click the New button to complete the rule creation.

The SQL configuration for the action is as follows:

INSERT INTO `use_ statistics` (`client_ id`, `speed`, `tachometer`, `ts`, `msg_ id`) VALUES (${client_id}, ${speed}, ${tachometer}, ${ts}, ${id})

Test expected results

We successfully created a rule that contains a processing action, and the desired effect of the action is as follows:

The device reports a message to the cmd/state/:id topic that when the tachometer value in the message exceeds 8000, the SQL will be hit, and the hit number in the rule list will be increased by 1.

A piece of data is added to the use_statistics table of the MySQL iot_data database, and the value is the same as the current message.

Test using the Websocket tool in Dashboard

Switch to the tool-> Websocket page, use any information client to connect to EMQ X, and send the following information on the message card after the connection is successful:

Topic: cmd/state/NXP-058659730253-963945118132721-22

Message body:

{"id": "NXP-058659730253-963945118132721-22", "speed": 32.12, "direction": 198.33212, "tachometer": 9002, "dynamical": 8.93, "location": {"lng": 116.296011, "lat": 40.005091}, "ts": 1563268202}

Click the send button to view the rule statistics after it has been successfully sent. A statistical value of 1 indicates that the rule has been successfully hit. The data recorded in the data table in the MySQL command line is as follows:

So far, we have realized the business development of using the rule engine to store messages to the MySQL database.

At this point, the study on "how to store messages in the MySQL database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.