四、数据转换
Electronic supplementary material The online version of this chapter (doi:10.1007/978-1-4842-1293-6_4) contains supplementary material, which is available to authorized users.
无论您有一个现有的 IOT 解决方案，您正在开发一个新的 IOT 解决方案的想法，或者您正在完善一个现有的 IOT 解决方案，数据在某些时候将是一个主要焦点。拥有一个拥有近乎完美的硬件、通信甚至显示能力的优秀 IOT 解决方案，并不能保证数据不会存储不当或处理不当。获得正确的数据可能是最困难的任务。事实上，我见过 IOT 的解决方案很好，但却悲惨地失败了，因为数据要么不容易呈现、理解，要么不完整。
当您考虑正在生成的数据类型以及如何解释这些数据时，问题就来了。由于大多数 IOT 解决方案都是为了观察我们周围的世界而设计的，因此很自然地，生成的大多数数据都是某种形式的传感器数据。当然，可能还有其他数据，如来自相机的图像或视频、用户输入，甚至来自社交媒体应用的数据。幸运的是，这些数据很容易理解。存储可能仍然是一个挑战，但通常很容易解决。
然而，传感器数据可能更难得到正确的。这是因为，正如你在第 2 章中看到的，传感器可以产生模拟(例如电压)或数字值，这些值代表观察值。例如，您可能会发现想要使用的传感器产生-5.0 到+5.0 之间的值。但传感器可能测量的是通过管道(软管)的流量，您可能希望看到的是升或加仑每分钟。诀窍是将传感器的值转换成预期的速率。
您可能需要对数据进行两类转换。您可能需要添加额外的信息，如测量单位、事件注释、日期或时间等。这些附加数据是一种注释形式。您可能还需要对数据执行更大规模的操作，将来自多个来源(传感器)甚至多个节点的数据进行组合。这些操作是数据的聚合，可能需要更多的工作来实现。
在本章中，您将发现许多处理这些和类似转换的技术。这些示例从两个角度展示:用于低成本计算机节点的 Arduino 或 Python 脚本草图的代码摘录，以及如何在数据库中实现该技术的建议。提供内部和外部使用的两种技术 1 数据库服务器为您提供更广泛的知识，从而为您开发 IOT 解决方案提供更多工具。
数据库示例将提供建议，而不是具体的示例。重点是展示它是如何在代码中完成的，然后是数据库实现的建议，这样当您阅读接下来的两章时，您将看到如何实现其中的一些建议。我将在第 8 章中更详细地介绍这些建议。接下来，您将看到使用数据库服务器可以获得多大的能力。
Note
一些示例可能没有数据库替代方案。在这些情况下，我提到了存储数据的注意事项。此外，一些例子使用了我们已经讨论过的技术。对于这些例子，重点应该放在所说明的概念上。
我们首先简要讨论和回顾什么是 IOT 数据以及理解数据的策略，即从数据中获取知识。
理解 IOT 数据
在我们进入如何实现数据的注释和聚集的例子之前，让我们讨论和回顾一些理解数据的策略。关注这个主题非常重要，这样我们才能了解如何成功设计我们的数据存储。在接下来的几节中，我会以数据库设计过程中应该问的问题的形式介绍一些重要的概念。
观察到了什么？
首先要考虑的是你在观察什么。不要专注于传感器产生的数据(这也很重要)，但是知道你在观察什么可以让你计划如何使用数据。我喜欢从问题的角度或在考虑传感器本身时，列出我想观察的事情。也就是说，如果我使用气体探测器，我会列出它可以测量的气体，并考虑如何使用这些观察结果。虽然数据的形式(来自传感器的数据类型)可能需要转换，但更重要的是专注于您正在观察的内容。有时候这并不明显。
例如，如果您使用传感器监控敏感设备 Shell 内的温度，温度会告诉您什么？假设您决定使用传感器来捕捉温度何时达到某个阈值，因为您知道设备无法承受超过该阈值的温度。因此，通过保存测量的温度以及事件的时间和日期来存储这些事件似乎是很自然的。
但是，如果您想知道其他温度范围是否会影响器件，该怎么办呢？在这种情况下，如果设备在某个温度下运行一段时间，该温度可能对设备有害。那么，仅仅存储温度并在温度达到初始阈值时发出信号或发出警报是否足够？可能不是。一段时间后，您可能会发现某些温度会对设备的功能或精度产生负面影响。如果这是真的，记录超过阈值的事件是不够的，因为数据(随时间变化的温度)已经丢失，有利于存储事件。
因此，你不仅要考虑你正在观察的东西，还要考虑观察结果可以被解读的任何其他方式，并计划好你需要为这些用途储存什么。例如，您可能会发现记录某个事件对于一些使用观察来说已经足够了，但是您可能会从一段时间内收集的数据中学到比单个事件更多的东西。
有没有另一种观察方法？
与你期望观察的密切相关的是如何进行观察。有时用某些传感器进行直接观察是不可能的(或者成本太高)。例如，假设您有一个花园池塘，并想确定过滤器何时需要清洗。
在一些花园池塘中，过滤器用于去除水中漂浮的碎片(如树叶、废物等)。随着时间的推移，这些过滤器可能会充满碎屑，从而失去水流过过滤器的能力。此外，让我们假设没有简单的机制来测量通过过滤器的水流量。也就是说，过滤器在容纳泵的 Shell 内，因此过滤器在水流入泵井之前净化水。此外，没有设计传感器来测量 Shell 中过滤器的流速。你如何观察到过滤器需要清洗？
如果你拥有并维护了一个使用这种过滤系统的花园池塘，你会知道有一个因果关系，你可以观察到，以确定过滤器是否需要清洗。更具体地说，随着过滤器充满碎屑，泵井中的水降低(流量降低，蓄水池中的水减少)。因此，您可以确定当泵井中的水位降低到一定水平时，过滤器需要清洗。
然而，这种观察结果并不理想，因为如果池塘出现渗漏或出现大量蒸发，抽水井中的水位也可能会变低。因此，我们必须把这些事件加入到我们从观察中可能学到的东西的列表中。在这种情况下，我们有一个主要的和两个次要的事情可以从抽水井水位的观察中得知。
Know Your Domain
这提出了一个很好的观点。如果你被要求为一个花园池塘制作一个 IOT 解决方案，但你不知道或没有任何相关的经验，你可能无法理解如何观察过滤器的状态。因此，了解或研究你工作的领域是很重要的，这样你就可以探索观察的替代方法。
Tip
你应该考虑观察的替代方法，包括观察因果事件，而不是实际或物理现象。
你需要多久记录一次观察结果？
您还应该考虑您想要记录观察值的频率，也就是说，您想要记录多少次来自传感器的值。一些传感器可能具有计时器电路或最小阈值，用于何时可以进行测量。开关等简单传感器可以是瞬时的，而气体、水的盐度或氧气等传感器可能具有显著的阈值，从而减少了在给定时间框架内可以进行的观察次数。
也有可能观察的频率与你期望获得的知识没有什么关系。例如，如果我们想测量建筑物或房间的温度，如果每三秒钟记录一次温度值，我们能学到什么吗？一分钟两次怎么样？一小时一次或者六小时一次怎么样？
答案取决于我们想学什么。是的，我们又回到那个话题了。更具体地说，我们是否希望能够跟踪温度何时变化到一天中特定的分钟、小时或时间？此外，我们是否想对变化率进行分析？
例如，如果我们在一个没有受控气候的建筑或房间(例如，谷仓或廊桥)中测量温度，我们是否有兴趣了解温度如何随时间变化？如果是，我们希望数据有多精确？也就是说，我们希望能够检测到温度变化有多快吗？如果我们这样做了，存储更频繁地进行的测量可以允许更准确地检测这些变化以及更准确的变化率。另一方面，如果我们只想能够确定一天中不同时段(例如，早晨、中午、晚上)的平均温度，每隔几个小时进行一次测量可能就足够了。
因此，我们必须考虑我们想要观察什么，以及随着时间的推移变化是否有益。也就是说，使用短时间间隔进行的测量可以比以更长时间间隔进行的测量更准确地检测变化和趋势。事实上，如果间隔足够大，测量中的波动可能会被忽略。
Tip
根据您可能从一段时间的变化中学到的东西，考虑您希望记录观察结果的频率。
传感器产生什么类型的数据？
接下来要考虑的是传感器产生的数据类型。抵制跳到如何解释数据的诱惑——我们接下来会这么做。现在，记下数据类型，以便在实现解决方案时可以引用它。
来自传感器的数据的数据类型可能与我们观察到的完全不同。对于以电压形式产生模拟数据的传感器，我们已经看到了这一点。电压的正负可能并不清楚它所代表的意义。记下每个传感器产生的数据将有助于您编写代码来读取、分析和存储观察结果。
Tip
列出传感器产生的数据类型。设计解决方案时，请将此列表放在手边。
观察数据需要解释吗？
一旦我们知道了我们在观察什么，我们能从观察中学到什么，我们需要多频繁地进行观察，以及数据类型是什么，我们现在必须考虑是否有任何需要进行的解释。
回到模拟传感器的讨论，大多数模拟传感器都有特定的解释，而且这些解释确实是有文档记录的。也就是说，传感器的文档将告诉您如何解释产生的值。这是通过一种称为数据手册的通用机制呈现的。
Read The Data Sheet
关于如何解读传感器数据手册中的数据，您可以找到更多信息。大多数制造商都会对其设备进行简短描述，包括工作参数(电压、电流等)以及传感器产生的数据类型和如何解释这些数据。如果您不熟悉传感器或其分立元件，请参考数据手册。
一旦理解了数据类型是如何解释的，就要记下如何执行解释。我喜欢把这些数据和伪代码一起记在笔记本上，以便进行观察。它不仅可以帮助您编写代码来解释数据，还可以帮助您考虑将解释包含在数据库中。
此外，您还应该考虑如何呈现数据，也就是说，您希望如何向用户显示数据。例如，读取-4.12 伏的值对用户没有帮助，但是如果该值被表示为诸如“正常”、“潮湿”或“干燥”的类别，则对用户有帮助
Tip
为如何解释和展示数据制定一个计划。把它记录在笔记本上以供参考。
你需要什么样的精确度？
这可能有些令人惊讶，但传感器并不总是 100%准确。事实上，大多数面向业余爱好者和发烧友的传感器只有 90%到 95%的精确度。也就是说，100 次中有 5 到 10 次，传感器不会产生准确的值。
如果你的传感器只有 95%的准确性，你只能期望你的数据是相同的。因此，在选择或分析传感器数据时，必须确定解决方案所需的精度。幸运的是，如果您需要更高的精度，通常可以找到符合您期望的传感器。然而，根据我的经验，传感器越精确，它们就越贵。
Tip
平衡传感器的精确度和数据的预期精确度。
数据的生命周期有多长？
最后，考虑您希望或需要保留数据多长时间。一些 IOT 解决方案，尤其是那些使用云的解决方案， 2 似乎只保留数据几个小时、几天或几周。这不仅显得武断，而且还确保您可能永远无法对历史数据执行任何数据分析。也就是说，你可能会因为样本集太小而丢失知识。
在考虑数据的生命周期时，您应该考虑前面的所有问题，并为您希望保留数据的时间制定计划，更具体地说，对更长时间内的数据进行数据分析是否有益。
要考虑的一个因素是大量数据是否会影响解决方案的效率。在这种情况下，更多的数据会使搜索甚至某些代码功能变慢，因为它必须花更多的时间来读取和比较数据。也有可能是您的存储解决方案对可以存储的数据量有限制。因此，在确定数据生命周期时，您应该考虑硬件和软件的限制。
一旦确定了数据的生命周期，就应该确定是否需要数据可访问。如果您不需要从您的解决方案或其应用中访问它，您可以考虑删除旧数据以便安全保存，如文件存档或备份。如果它仍然必须是可访问的，您可能要考虑使用替代或补充的存储机制。例如，如果您将数据存储在一个文件中，您可以简单地打开一个新文件。
Archive, Don’T Delete
如果您确定可以清除某个日期之前的数据，请不要删除该数据。取而代之的是，将它存档，以便在需要访问旧数据时可以使用。对于数据库，数据库备份可能就足够了，或者您可以使用相同模式的表来存储较旧的数据。对于基于文件的存储，请在可移动媒体或其他设备上制作文件的副本。
一旦您对数据的生命周期以及如何处理旧数据有了计划，请将此计划记录在笔记本上，以便您可以编写代码或实施正确的流程来定期清除数据。
Tip
为您希望存储数据的时间制定一个计划。将任何清除事件实施为备份或存档，而不是删除。
现在我们已经看到了理解数据需要考虑的一些事情，让我们从注释开始，看看实现这些策略的几种技术。
以下部分中的一些代码示例是为基于文件的存储而编写的。其他介质形式类似，但实际写入方法可能不同。我还模拟从传感器收集数据。您通常会使用以类似方式编写的方法来检索数据。
我还介绍了使用数据库添加注释或实现聚合的注意事项。我用这几节来介绍数据库概念，为您在本书的其余部分学习更深入的 MySQL 教程做准备。
让我们先来看看您可能想要做的一些更常见的注释。
注释
为您的 IOT 解决方案注记数据只是您添加、组合或计算数据的任何附加数据。例如，您可能希望存储一个字符串来描述事件、传感器、节点等。或者，您可能希望对数据执行一些转换，并将其与原始数据一起保存。
Tip
保存原始值始终是一个好的做法。
我在本章中演示了其中的一些转换，提供了一个代码示例，您可以在您的数据聚合器或数据节点上使用它，例如 Arduino、Raspberry Pi 或类似的低成本计算机节点上托管的数据聚合器或数据节点。因此，在适当的地方，我会给出一个 Arduino 草图摘录和一个使用 Python 的例子。虽然您可能不熟悉这两种语言，但是您可以使用这些示例来帮助指导您使用您选择的语言进行写作。
Can You Have Too Much Annotation?
注释应该是为了用户的利益，或者使数据更具信息性。因此，您必须考虑注释是否会添加任何内容，但更重要的是，注释不应该使数据复杂化或模糊化。本章中的一些例子接近于利益与损害的界限，但这是为了说明的目的。使用您自己的判断，多少注释足够满足您的需求。太多会有模糊知识的风险，太少会使数据更难使用。
现在您知道了什么是注释，让我们看几个例子，从最简单的转换形式开始——添加文本和记录注释。
Note
下面的代码示例是为了简洁而摘录的。完整的草图或脚本可以从书籍源代码下载网站获得。
记录传感器名称或添加注释
最简单的注释形式是向存储的数据添加一个短字符串。您可能希望这样做，以确保所存储的值能够被理解，例如正在观察什么事件，或者哪个数据收集器(传感器)生成了数据。您可能还想为存储的数据添加任何注释，比如用户输入或者主观观察，比如“猫睡在花盆里”添加的数据在以后读取数据时会很有帮助，特别是当您将数据作为日志或数据存储写入文件时。
但是，您必须小心使用这项技术。添加过多的文本可能会使数据更难解释，或者在将来需要时更难解析。因为添加字符串很容易，所以我将给出一些例子和简短的解释。
代码实现
向存储在文件中的数据添加文本并不困难。清单 4-1 展示了如何在 Arduino 草图中将文本添加到一行数据中。在这种情况下，我添加了来自两个(模拟的)传感器读数的数据以及对每个数据的解释。我还添加了用于调试的语句(Serial.print语句)。
Listing 4-1.Simple Annotation (Arduino)
/**
Example of simple annotation.
This project demonstrates how to save data to a
microSD card as a log file with sting annotation.
*/
#include <SPI.h>
#include <SD.h>
#include <String.h>
// Pin assignment for Arduino Ethernet shield
//#define SD_PIN 4
// Pin assignment for Sparkfun microSD shield
#define SD_PIN 8
// Pin assignment for Adafruit Data Logging shield
//#define SD_PIN 10
File log_file; // file handle
String strData; // storage for string
// Simulated sensor reading method
float read_sensor(int sensor_num) {
if (sensor_num == 1) {
return 90.125 + random(20)/10.00; // sensor #1
} else if (sensor_num == 2) {
return 14.512313 + random(100)/100.00; // sensor #2
} else {
if (random(25) >= 5) {
return (float)random(14)/10.00;
} else {
return -(float)random(14)/10.00;
}
}
}
void setup() {
Serial.begin(115200);
while (!Serial);
Serial.print("Initializing SD card...");
if (!SD.begin(SD_PIN)) {
Serial.println("ERROR!");
return;
}
Serial.println("ready.");
if (SD.remove("data_log.txt")) {
Serial.println("file removed");
}
// initiate random seed
randomSeed(analogRead(0));
}
void loop() {
delay(2000);
log_file = SD.open("data_log.txt", FILE_WRITE);
if (log_file) {
Serial.println("Log file open.");
strData = String("Temperature sensor: ");
strData += read_sensor(1);
strData += " ";
delay(1000);
strData += ", Barometric sensor: ";
strData += read_sensor(2);
log_file.println(strData);
Serial.print("Data written: ");
Serial.println(strData);
log_file.close();
Serial.println("Log file closed.");
} else {
Serial.println("ERROR: Cannot open file for reading.");
}
}
在这个例子中，我使用String类将字符串与传感器读数连接起来。虽然还有其他方法，但这种方法似乎更清楚地展示了注释。请注意，我在第二个传感器之前的字符串中放置了一个逗号。这个逗号可以用来帮助解析数据，如果您将来需要这样做的话。例如，您可以拆分逗号上的数据，然后将数据值放在冒号后的字符串末尾。以下是输出示例，或者说是日志文件的摘录:
Temperature sensor: 90.82, Barometric sensor: 15.00
Temperature sensor: 91.43, Barometric sensor: 15.09
Temperature sensor: 91.13, Barometric sensor: 15.23
虽然本例显示了如何将传感器名称与数据一起保存，但是通过简单地附加字符串，在输出行的末尾添加更多的文本也同样容易。例如，您可以在字符串末尾添加一个注释，如下所示。这里我添加了一个方法，它从提示(或 web 表单)返回用户的输入。
...
strData += ", Barometric sensor: ";
strData += read_sensor(2);
strData += " Notes: ";
strData += read_user_input();
log_file.println(strData);
要在 Python 中实现类似的注释，可以使用清单 4-2 中所示的代码。在这里，我重现了 Arduino 代码生成的结果。也就是说，我写的是同样的数据，但是我用的是完全不同的方法。为了帮助理解一些格式，我包含了整个代码。
Listing 4-2.Simple Annotation (Python)
from random import random, randint
import string
def read_sensor(sensor_num):
if (sensor_num == 1):
return 90.125 + random()*10 # sensor #1
elif (sensor_num == 2):
return 14.512313 + random() # sensor #2
else:
if (randint(0,25) >= 5):
return randint(0,14)/10.00
else:
return -randint(0,14)/10.00
strData = "Temperature sensor: {0:.2f}, Barometric sensor: {1:.2f}\n"
file_log = open("data_log_python.txt", 'a')
file_log.write(strData.format(read_sensor(1), read_sensor(2)))
file_log.close()
这里你可以看到一个使用format()方法格式化字符串的例子。注意我是如何使用名为strData的字符串中的格式化代码将输出限制为两个小数点，比如{0:.2f}和{1:.2f}。这些代码告诉format()方法替换第一个和第二个参数(read_sensor(1)和read_sensor(2))，将数据格式化为带两位小数的浮点数。
虽然这个例子是基本的，只是一个例子，但是大多数开发者可能不会保存每行的事件或传感器的名称。更有可能的是，您将传感器的名称作为标题行写在文件的开头。这样，您总是知道数据列的含义。事实上，这正是您在数据库解决方案中使用这样的注释的方式。
数据库注意事项
在数据库中用文本进行注释很容易。如果我们想要包括传感器的名称或类型，我们可以简单地相应地命名表格的列。这样，列名描述了数据。例如，如果我们有一个表来存储本节示例中的行，该表可能类似于清单 4-3 。不要担心知道命令的所有部分是什么；只关注例子的可读性。
简而言之，表是我们用来存储数据的数据库结构。它形成了数据的布局。我们将在下一章学习更多关于创建表格的知识。现在，请注意这些列。这里我们看到有两列的名称与我们正在收集的数据相对应。事实上，如果我们输入前面例子中的数据，它看起来会像清单末尾的结果。我使用一个SELECT语句(从数据库中检索行的方法)来显示示例条目。
Listing 4-3.Sample Database Table and Sample Results
CREATE TABLE simple_annotation_test (
``id int(11) NOT NULL AUTO_INCREMENT,
``temperature float NOT NULL,
``barometric float NOT NULL,
``notes char(128) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
mysql> SELECT * FROM simple_annotation_test;
+----+-------------+------------+-------+
| id | temperature | barometric | notes |
+----+-------------+------------+-------+
| 1 | 90.82 | 15 | NULL |
| 2 | 91.43 | 15.09 | NULL |
| 3 | 91.13 | 15.23 | NULL |
+----+-------------+------------+-------+
3 rows in set (0.01 sec)
请注意，还有一个注释栏。这表明我们还可以处理保存任何额外的注释或用户输入以及采集的样本。为此，我们在保存数据时将其包括在内。
Descriptive Column Names
对于糟糕的数据库设计习惯，这是我最讨厌的事情之一。也就是说，在命名对象(表)、列、索引等时，应该始终使用描述性名称。这并不意味着您需要为每个列名使用 200 个字符(那将是荒谬的)，但是您应该避免使用单个字符名称的诱惑，例如a、b、c等等。
如您所见，数据库中数据的注释是通过命名列、创建存储文本的列，甚至创建特殊的数据类型来帮助注释数据来完成的。例如，您可以定义一个包含一组值(称为枚举)的列，这样我们就可以在保存数据时指定枚举的数值。事实上，我们有很多方法可以在数据库中进行注释。我们将在后面的章节中看到更多的例子。
记录日期和时间
除了以列名、注释等形式向数据添加文本之外，我们经常需要存储观察到一个事件或一系列事件的日期和时间。也就是说，我们希望保存传感器被读取的时间。然而，大多数微控制器板甚至一些低成本计算机板都没有实时时钟(RTC)电路。例如，Arduino 没有 RTC，也没有 Raspberry Pi。
需要 RTC 来确保精确的时间保持，因为用于在处理器(微控制器)中推进指令的时钟(产生脉冲的特殊晶体或类似机制)不会以可用于精确计算时间的频率脉动或循环。此外，RTC 电路具有为少量存储器供电的电池，以存储时间值(有时是从特定时期开始的秒)。因此，没有 RTC 的电路板必须被编程为具有开始日期和时间，或者被指示从互联网上的时间服务器获取日期和时间。
幸运的是，有几款出色的产品性能良好，包括一个板载电池，即使在电路板掉电时也能为时钟供电。Adafruit 的 DS1307 实时时钟分线板套件( www.adafruit.com/products/264 )是添加到您的项目中的优秀模块。Sparkfun 还有一个名为实时时钟模块( www.sparkfun.com/products/99 )的产品，它使用与 Adafruit 产品相同的 DS1307 芯片和接口。您可以配合 Arduino、Raspberry Pi 或任何具有 IC2 接口的主板使用。
使用实时时钟模块
RTC 模块使用易于连接到 Arduino 的 I2C 接口。只需将 5V 电源连接到 5V 引脚，将 GND 引脚接地，将 Arduino 上的 SDA 引脚连接到引脚 4，将 Arduino 上的 SCL 引脚连接到引脚 5。图 4-1 显示了将 RTC 模块连接到 Arduino 的接线图。
图 4-1。
Arduino with an Ethernet shield and RTC module Note
如果您使用的是莱昂纳多板，您必须使用新的 SCL/SCA 引脚旁边的 AREF，GND，和引脚 13 最接近 USB 连接器。
初始化 RTC 需要一点代码，但并不困难。首先，我们必须下载 RTC 库。你可以在 https://github.com/adafruit/RTClib 找到一个优秀的库使用。通过单击 Download Zip 按钮下载库，解压缩归档文件，然后将文件夹复制到您的Arduino/Library文件夹中。您可能需要重命名该文件夹。我推荐使用RTCLib作为文件夹名。
Tip
如果您在 Arduino IDE 打开时复制了该库，您可能需要重新启动 IDE 以识别该库。
一旦库准备好了，我们就可以添加代码来使用库了。我们需要包含库头并定义 RTC 类的一个实例。以下是所需的代码行:
#include "RTClib.h"
...
RTC_DS1307 rtc; // real time clock
接下来，我们需要用begin()方法初始化 RTC 类，如果模块之前没有用过或者你换了电池，我们还需要用adjust()方法设置时间。以下是可用于完成这两项任务的代码:
...
rtc.begin();
if (!rtc.begin()) {
Serial.println("Couldn't find the RTC module.");
while (1);
}
if (!rtc.isrunning()) {
Serial.println("ERROR: The RTC module is not working.");
} else {
// Comment out this line after the first run, it is only needed for setting
// the date and time for the first time.
rtc.adjust(DateTime(F(__DATE__), F(__TIME__)));
}
注意传递给方法rtc.adjust()的参数。这将当前日期和时间的值转换为一个DateTime类，该方法需要用它来为模块设置日期和时间。__DATE__和__TIME__是编译草图时从系统中检索日期和时间的宏。因此，只有在第一次开始使用 RTC 时，才需要调用这个方法。您不需要每次运行草图时都调用它。如果这样做，您将每次都将 RTC 设置为相同的值。这不是你想要的。因此，我编写了代码，以便您可以在第一次编译后注释掉该方法。
现在让我们看看如何在草图中使用 RTC 来捕捉 Arduino 上的日期和时间。
代码实现
将日期和时间添加到写入文件的行中的代码所需的工作可能比您预期的要多一些。RTC 模块提供了获取日期和时间的独立元素(如月、年、小时等)的原语。因此，我们需要获取这些值，并将它们组合成我们期望看到的格式。在这种情况下，我们希望看到月/日/年小时:分钟:秒的格式。清单 4-4 显示了获取和格式化日期和时间的代码。
Listing 4-4.Date and Time Annotation (Arduino)
String get_datetime() {
DateTime now = rtc.now();
String dateStr = String(now.day());
dateStr += "/";
dateStr += now.month();
dateStr += "/";
dateStr += now.year();
dateStr += " ";
dateStr += String(now.hour());
dateStr += ":";
dateStr += String(now.minute());
dateStr += ":";
dateStr += String(now.second());
return dateStr;
}
void loop() {
delay(2000);
log_file = SD.open("data_log.txt", FILE_WRITE);
if (log_file) {
Serial.println("Log file open.");
strData = String("Temperature sensor: ");
strData += read_sensor(1);
strData += " ";
delay(1000);
strData += ", Barometric sensor: ";
strData += read_sensor(2);
strData += " ";
strData += get_datetime();
log_file.println(strData);
Serial.print("Data written: ");
Serial.println(strData);
log_file.close();
Serial.println("Log file closed.");
delay(2000);
} else {
Serial.println("ERROR: Cannot open file for reading.");
}
}
如您所见，代码与前面的示例几乎相同。唯一的区别是添加了名为get_datetime()的方法来获取并格式化日期和时间，返回一个字符串，然后我们将该字符串追加到正在写入文件的行中。此代码中的示例行如下所示:
Temperature sensor: 92.33, Barometric sensor: 15.23 Datetime: 11/07/2015 22:36:32
Temperature sensor: 90.72, Barometric sensor: 15.32 Datetime: 11/07/2015 22:36:48
Temperature sensor: 94.38, Barometric sensor: 15.13 Datetime: 11/07/2015 22:36:50
Temperature sensor: 96.74, Barometric sensor: 14.95 Datetime: 11/07/2015 22:36:50
如果您想用 Python 做类似的事情，并且您的平台上有一个 RTC，那么代码相当容易。您只需添加日期和时间，如清单 4-5 所示。我在这里包含了完整的代码，这样您就可以看到读取日期和时间的支持代码。
Listing 4-5.Date and Time Annotation (Python)
from datetime import datetime
from random import random, randint
import string
def read_sensor(sensor_num):
if (sensor_num == 1):
return 90.125 + random()*10 # sensor #1
elif (sensor_num == 2):
return 14.512313 + random() # sensor #2
else:
if (randint(0,25) >= 5):
return randint(0,14)/10.00
else:
return -randint(0,14)/10.00
def get_datetime():
return datetime.strftime(datetime.now(), "%m/%d/%Y %H:%M:%S")
strData = "Temperature sensor: {0:.2f}, Barometric sensor: {1:.2f} Datetime: {2}\n"
file_log = open("data_log_python.txt", 'a')
file_log.write(strData.format(read_sensor(1), read_sensor(2), get_datetime()))
file_log.close()
请注意，我们使用了一种方法来获取和格式化日期和时间，其方式与我们在 Arduino 代码中所做的类似。然而，Python 有更高级的方法(当然是从 C 语言借鉴来的)，我们可以使用包含格式代码的字符串来格式化日期和时间。注意get_datetime()方法。我们使用来自名为strftime()的datetime类的方法，通过datetime.now()方法从当前日期和时间创建一个字符串。然后格式字符串被strftime()方法使用。这段代码的输出类似于 Arduino 代码。
Temperature sensor: 92.33, Barometric sensor: 15.23 Datetime: 11/07/2015 22:36:32
Temperature sensor: 90.72, Barometric sensor: 15.32 Datetime: 11/07/2015 22:36:48
Temperature sensor: 94.38, Barometric sensor: 15.13 Datetime: 11/07/2015 22:36:50
Temperature sensor: 96.74, Barometric sensor: 14.95 Datetime: 11/07/2015 22:36:50
如果您的低成本计算机主板没有 RTC，您可以添加一个，如果主板有 I2C 接口，并且操作系统对 RTC 有足够的支持。例如，您可以向 Raspberry Pi 添加 RTC。Adafruit 在 https://learn.adafruit.com/adding-a-real-time-clock-to-raspberry-pi/overview 有一个很好的教程。一旦添加了时钟，Python(或其他语言)脚本将从操作系统原语中获得正确的日期和时间。
数据库注意事项
有两种方法可以保存数据库行中的日期和时间:您可以向类型为datetime的表中添加一列，并在与数据库服务器通信的代码中提供日期和时间(您发出一个INSERT语句向表中添加数据)，或者您可以使用一个timestamp列，这是一个特殊的列，当行被插入时，数据库服务器会为您填充该列。让我们来看看这些选项中的每一个。
您可以通过指定datetime数据类型向表中添加日期和时间列。像添加或更新任何其他列一样，添加或更新该值。清单 4-6 展示了一个包含一个日期和时间列的示例表模式。为了便于阅读，我突出显示了该列。
Listing 4-6.Database Table with a datetime Column
CREATE TABLE date_annotation_test (
``id int(11) NOT NULL AUTO_INCREMENT,
``temperature float NOT NULL,
``barometric float DEFAULT NULL,
``date_saved datetime DEFAULT NULL,
``notes char(128) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1
使用datetime列要求您以与 MySQL 兼容的格式提供值。幸运的是，我们到目前为止使用的相同格式工作得很好(其他人也一样——参见 MySQL 在线参考手册以获得更多示例)。下面显示了如何将一行保存到表中，为新列提供日期和时间值。
INSERT INTO date_annotation_test VALUES (null, 91.34,15.04,``'11/7/15 23:16:30'
现在让我们看一个添加了示例数据的表的输出示例。在本例中，我添加了几行，为每一行指定日期和时间。
mysql> SELECT * FROM date_annotation_test;
+----+-------------+------------+---------------------+----------------+
| id | temperature | barometric | date_saved | notes |
+----+-------------+------------+---------------------+----------------+
| 1 | 90.82 | 15 | 2011-07-15 23:15:10 | NULL |
| 2 | 91.34 | 15.04 | 2011-07-15 23:16:30 | Test datetime. |
+----+-------------+------------+---------------------+----------------+
2 rows in set (0.00 sec)
添加日期和时间注释的另一种形式是使用一个timestamp列。这是一种特殊的数据类型，由数据库服务器自动更新。每个表我们只能使用一个timestamp列。下面显示的是摘自一张CREATE声明的表格。我用下面的ALTER语句将这个列添加到前面的例子中。
ALTER TABLE date_annotation_test ADD COLUMN data_changed TIMESTAMP AFTER notes;
注意DEFAULT选项后的附加选项。这些是默认添加的，它显示每当行被创建或更新时，该值都被更改。所以，是的，一旦设置了时间戳列，它就不是固定的。让我们看一个用新列添加行的例子。
INSERT INTO date_annotation_test VALUES (null, 91.34,15.04, null, 'Test timestamp.',null);
这里我传入了NULL值，它告诉数据库服务器使用默认值，正如我们在前面的列定义中看到的，这是对列的更新。还要注意，我将日期和时间列(date_saved)留空，这意味着不要更新该列。因此，该行不应该显示日期和时间列的值，而应该显示为timestamp列(data_changed)插入该行的日期和时间。下表显示了累积数据:
mysql> SELECT * FROM date_annotation_test;
+----+-------------+------------+---------------------+-----------------+---------------------+
| id | temperature | barometric | date_saved | notes | data_changed |
+----+-------------+------------+---------------------+-----------------+---------------------+
| 1 | 90.82 | 15 | 2011-07-15 23:15:10 | NULL | 2015-11-08 15:55:50 |
| 2 | 91.34 | 15.04 | 2011-07-15 23:16:30 | Test datetime. | 2015-11-08 15:55:50 |
| 3 | 91.34 | 15.04 | NULL | Test timestamp. | 2015-11-08 15:57:27 |
+----+-------------+------------+---------------------+-----------------+---------------------+
3 rows in set (0.00 sec)
注意最后两行有相同的数据。我故意这样做，这样您就可以看出添加带有datetime列的行和添加带有timestamp列的行之间的区别。
因此，使用timestamp列意味着数据库为您完成了工作，因此您可以跟踪数据是何时添加或更改的。但是，使用 timestamp 数据类型有一些限制。首先，每张桌子只能有一个。其次，对行的任何更改都会覆盖这些值。因此，如果您想要保存一个特定的日期和时间，但是稍后想要更新该行(比如添加注释)，您可能想要重新考虑使用timestamp或者添加一个或多个datetime列来存储静态日期和时间值。
但是等等，为什么前两行的data_changed值是一样的？您可能已经注意到前两行的data_changed列具有相同的值。这是因为我将新列添加到了一个现有的表中。在内部，数据库服务器必须更新所有行以适应这种变化，因此表中已经存在的每一行都会发生变化；由于该列使用了ON UPDATE CURRENT TIMESTAMP(默认情况下)，新列被更新。在更改现有表的模式时，请记住这一点。
Tip
您应该考虑向现有表中添加时间戳列的影响，以及如何更新时间戳列，以确保保存您想要的数据。
数据类型转换
更改数据元素的类型是您应该花些时间仔细考虑和计划的事情。这是因为如果没有正确执行转换，很容易丢失数据。这不仅仅是将一个浮点数转换成一个字符串，然后再转换回来(尽管如果去掉太多的小数，也会有问题)。您必须考虑所需的存储量以及值的范围。例如，整数和无符号整数可能大小相同【3】(2 字节)，但是值的范围不同，因为整数允许将值的符号限制(+/-)到范围-32768 到 32767，而无符号整数存储范围 0 到 65535 的值。
您还需要考虑数据的精度。浮点数是有若干小数的实数。大多数平台都非常明确地声明浮点数据类型是估计的，并不是精确的——同样是因为小数部分。因此，在转换数据类型时，您必须考虑这一点。例如，如果将浮点数更改为整数，可能会出现舍入问题。当然，将整数改为浮点数也可能会引入舍入问题。类似地，将类型从较大的存储空间(字节数)更改为较小的存储空间(字节数)，会有使目标内存溢出并生成无效值的风险。
另一个考虑是执行算术。也就是说，结果值或中间值必须“适合”该数据类型的值范围。例如，如果将两个无符号整数相加，并将结果赋给另一个无符号整数，则必须确保该值在 0 到 65535 的范围内。否则，该值可能溢出并导致许多问题，最明显的是不正确的值。
Note
一些编程语言有严格的类型检查，并且可以在可能溢出时检测或警告，但是这不能代替谨慎的编程(使用正确的数据类型)。
你可能想知道溢出是什么。观察以下简短的 Arduino 草图。这里有两个变量，一个长整型和一个以 65535 开始的无符号整数。看看会发生什么。
void setup() {
unsigned int uint_val = 65535;
long long_val = 65535;
Serial.begin(9600);
while (!Serial); // Wait until Serial is ready - Leonardo
Serial.print("1> ");
Serial.print(uint_val);
Serial.print(" ");
Serial.println(long_val);
Serial.print("2> ");
uint_val++;
long_val++;
Serial.print(uint_val);
Serial.print(" ");
Serial.println(long_val);
Serial.print("3> ");
uint_val++;
long_val++;
Serial.print(uint_val);
Serial.print(" ");
Serial.println(long_val);
}
void loop() {
}
以下是输出(来自串行监控器):
1> 65535 65535
2> 0 65536
3> 1 65537
我将输出的值打印在第一行，然后给每一行加一，再打印在第二行。注意当我给变量加 1 并打印它们时会发生什么。您可能希望无符号整数是 65536，但它是 0，因为表示该数字的位数超过了变量的最大大小(2 个字节)，所以出现了溢出。65536 的值需要 17 位(10000000000000000)，两个字节中只有 16 位，前 16 位从右边开始取(00000000000000)。似乎令人担忧的是，没有任何溢出的警告。第三行显示了当我再次添加 1 时发生的情况，但是我们看到无符号整数变量已经重置。
因此，您应该努力使用尽可能最小的数据类型，尤其是在使用微控制器或其他内存有限的设备时。要练习这种做法，您需要了解每种数据类型可以安全存储的值的范围。为了了解它对 Arduino 平台的意义，表 4-1 列出了一些常见的数据类型、内存大小和值的范围。注意浮点变量比整数大多少。
表 4-1。
Arduino Data Types
| 数据类型 | 以字节为单位 | 值的范围 | | --- | --- | --- | | `boolean` | one | 逻辑真/假 | | `byte` | one | 从 0 到 255 的无符号数 | | `char` | one | 从-128 到 127 的有符号数字，通常是 ASCII 字符 | | `unsigned char` | one | 字符值 0 到 255 | | `word` | Two | 从 0 到 65535 的无符号数 | | `unsigned int` | Two | 从 0 到 65535 的无符号数 | | `int` | Two | 从-32768 到 32767 的签名号码 | | `unsigned long` | four | 0-4，294，967，295 之间的无符号数 | | `long` | four | 签名号码从-2，147，483，648 到 2，147，483，647 | | `float` | four | 从-3.4028235E+38 到 3.4028235E+38 的有符号数字 |尽可能使用最小的数据类型还有一个好处。某些数据类型会导致草图运行速度变慢。例如，在算术运算中使用浮点数可能比使用整数慢 16 倍。如果精度不是问题，而性能是问题，您可能需要考虑将浮点值舍入到整数(提示:乘以 100 以在转换前保留 2 个小数点)。请确保使用尽可能大的整数。
下一节不是给出一组代码示例，而是讨论转换和使用数据类型的一些细节。
代码实现
虽然大多数有经验的程序员会告诫不要转换数据类型，除非绝对必要，但是在一些情况下，如果小心行事，是有可能证明这一点的。例如，如果您正在使用一种具有松散类型检查的语言，并且希望进行一些算术运算来产生浮点值，但是无意中在公式中使用了整数，则您可能会得到整数结果。为了克服这一点，您可能需要使用一种称为强制转换(也称为造型)的特殊操作将整数转换为浮点数。Arduino C 中的强制转换是通过将新的数据类型放在括号中实现的。这里显示了一个示例:
int shift_value = 100;
val_shifted = (float)shift_value * 95.0675;
使用某些数据类型转换的另一个理由是节省内存。这在处理微控制器和其他对变量的内存有限的处理器时最为常见。那些刚接触内存有限的设备的人可能不认为在这里或那里保存一个字节会有什么不同，但它确实会产生巨大的影响。
例如，如果您的草图使用大量变量进行计算，您可能需要考虑减少变量的类型和大小。例如，考虑一下，如果每个单元都是双精度的，那么一个相对较小的 20×20 值的矩阵将消耗多少字节。对，就是 20×20×4 = 1600 字节。如果你的 Arduino 只有 2Kb 的内存，你可能会遇到问题。
如果您遇到类似这样的问题，并且您正在处理的值的范围小于您选择的数据类型，您可以转换它们。例如，如果矩阵中的值从未超过+/-320，并且精度只允许使用两位小数，则可以将这些值转换为整数，如下所示:
int new_val;
new_val = (int)(orig_val * 100.00);
这段代码产生一个值，比如 222.66 到 22266，这个值足够小，适合一个整数数据类型，因此可以在内存中节省 800 个字节。对于旧的 Arduino 主板来说，这是相当大的节省！但是，我应该注意，这些值将被截断，而不是四舍五入。因此，虽然您可以通过除以 100 将数据恢复为浮点型，但您不会恢复原始值的任何额外精度。
您可能遇到的另一种可能的数据类型转换是将字节转换为字符，或者从一系列字节中检索更大的数值。这很可能在设备之间通信时发生。也就是说，大多数通信机制以字节流的形式发送数据。在这种情况下，我们可能需要从字节流中提取整数、浮点数甚至字符串(文本)等数据。
将字节转换成字符很简单，因为它们在代码中被视为相同的(每个都是一个字节)。然而，它们可以有不同的解释。例如，一个 ASCII 字符只使用前七位，而一个字节被视为八位。
如果您必须从字节流中提取数字，您将需要知道这些值是如何编码的。例如，一个适合一个字节的小值将需要一个字节。较大的值可能是一个整数，需要两个字节，长整数可能需要四个字节。除了字节数，您还必须知道各个字节的存储顺序。大多数微处理器和微控制器平台首先用最小的字节存储它们。 5
因此，要从字节流中检索一个值，您必须一次读取一个字节，并将每个字节移位。也就是说，先读取第一个字节，然后读取第二个字节，将其向左移位，并添加第一个字节。可以这样想:一个整数有两个字节，你要把它们分开。要重建整数，必须取第一个字节(最左边的位)并将其左移 8 位。这使得最右边的字节为空。下面是一个例子。我使用十六进制值来使这个例子易于阅读。
Integer: 0x5AFE
Left-most byte: 0x5A
Right-most byte: 0xFE
Left-most byte shifted: 0x5A << 8 = 0x5A00
Adding in the right most byte: 0x5A00 + 0x00FE = 0x5AFE
类似地，存储整数需要将最左边的字节向右移动以保留值。我把这个留给你作为练习来探索。清单 4-7 显示了在字节缓冲区中读取和存储整数值的两种方法。如果您需要在字节流中读取或存储整数，请花些时间通读这些内容。
Listing 4-7.Reading and Storing Integers in a Byte Array
/**
* get_lcb_len - Retrieves the length of a length coded binary value
*
* This reads the first byte from the offset into the buffer and returns
* the number of bytes (size) that the integer consumes. It is used in
* conjunction with read_int() to read``length
* from the buffer.
*
* Returns integer - number of bytes integer consumes
*/
int get_lcb_len(byte buffer[], int offset) {
int read_len = buffer[offset];
if (read_len > 250) {
// read type:
byte type = buffer[offset+1];
if (type == 0xfc)
read_len = 2;
else if (type == 0xfd)
read_len = 3;
else if (type == 0xfe)
read_len = 8;
}
return 1;
}
/**
* read_int - Retrieve an integer from the buffer in size bytes.
*
* This reads an integer from the buffer at offset position indicated for
* the number of bytes specified (size).
*
* buffer[in] byte stream in memory
* offset[in] offset from start of buffer
* size[in] number of bytes to use to store the integer
*
* Returns integer - integer from the buffer
*/
int read_int(byte buffer[], int offset, int size) {
int value = 0;
int new_size = 0;
if (size == 0)
new_size = get_lcb_len(offset);
if (size == 1)
return buffer[offset];
new_size = size;
int shifter = (new_size - 1) * 8;
for (int i = new_size; i > 0; i--) {
value += (byte)(buffer[i-1] << shifter);
shifter -= 8;
}
return value;
}
/**
* store_int - Store an integer value into a byte array of size bytes.
*
* This writes an integer into the``buffer
* buffer. It will transform an integer of size to a length coded binary
* form where 1-3 bytes are used to store the value (set by size).
*
* buff[in] pointer to location in internal buffer where the
* integer will be stored
* value[in] integer value to be stored
* size[in] number of bytes to use to store the integer
*/
void store_int(byte *buff, long value, int size) {
memset(buff, 0, size);
if (value < 0xff)
buff[0] = (byte)value;
else if (value < 0xffff) {
buff[0] = (byte)value;
buff[1] = (byte)(value >> 8);
} else if (value < 0xffffff) {
buff[0] = (byte)value;
buff[1] = (byte)(value >> 8);
buff[2] = (byte)(value >> 16);
} else if (value < 0xffffff) {
buff[0] = (byte)value;
buff[1] = (byte)(value >> 8);
buff[2] = (byte)(value >> 16);
buff[3] = (byte)(value >> 24);
}
}
Note
这段代码是如何编码和解码的一个例子。如果您想在自己的解决方案中使用它，您可能需要修改它以适合您的代码。
注意，一种方法使用循环来遍历字节，而另一种方法使用更常见的方法，即使用条件语句。我两者都包括，但是你可以使用最容易理解的。请注意，这两种方法都需要整数的大小。这是必要的，因为整数的长度可以是 1、2 或 4 个字节。
数据类型用法的另一个概念涉及从不改变的值。在这种情况下，您可以将变量声明为常量，这将告诉编译器用常量值替换变量，从而节省一点内存。对于较大的数据类型，这样做可以节省大量内存。考虑下面的代码片段。第一行代码使用 2 个字节，而第二行代码强制编译器在变量出现的任何地方替换为 10。
int multiplier = 10;
const int multiplier = 10;
处理数据类型有相当多的技巧和技术——如此之多，以至于已经写了整本多卷的书来探索每种可能的技术的细微差别和优缺点。如果您在您的 IOT 解决方案中使用 Arduino，并希望确保尽可能小心高效地对其进行编程，请参阅 Simon Monk 博士的著作《Arduino 后续步骤编程》(McGraw Hill，2014)。
如果您正在用 Python 编写低成本的计算机主板，这些技术中有许多是相同的或者有类似的应用。Wolfram Donat 的书《用 Python 学习 Raspberry Pi 编程》(Apress，2014)是一本关于在 Raspberry Pi 上进行高效 Python 编程的优秀书籍。如果你想超越 Python 编程的基础，可以看看 J. Burton Browning 和 Marty Alchin 的书 Pro Python (Apress，2014)。
数据库注意事项
幸运的是，在数据库中存储数据时，您不太可能遇到与微控制器或类似的小内存设备相同的内存限制。这是因为数据库擅长以特定格式存储数据，并且在某些情况下可以优化所使用的存储(尤其是文本)。也许更重要的是，数据库服务器比微控制器有更多的存储空间。
因此，对于一个数据库来说，以正确的类型存储数据确实没有问题，只是有一些小的例外。也就是说，在一些可能的边界情况下，复杂的数据类型可能在数据库服务器上不可用，但是肯定有足够的原语，您可以保存您需要的任何类型。
Note
MySQL 支持许多数据类型。有关更多详细信息，请参见 MySQL 在线参考手册中标题为“数据类型”的部分( http://dev.mysql.com/doc/refman/5.7/en/ )。
此外，处理规模的问题不是存储数据的问题。这是因为，再一次，数据库真的很擅长这个。因此，浮点数的舍入或限制小数更多的是表示，而不是其他。但是，如果你需要做这样的事情，你可以在数据库服务器。
例如，MySQL 为浮点数据类型提供了额外的控件。也就是说，您可以设置整数位数和小数位数。您可以使用float(m,d)语法来实现这一点，其中m是最大位数，d是小数位数。当您需要限制 MySQL 中浮点数的大小或显示时，请使用此语法。
添加导出或计算的数据
另一个常见的注释是向行中添加额外的数据，这些数据是原始值的派生或计算。这包括转换以更改度量单位(华氏温度到摄氏温度)的值、基于范围的枚举或以某种方式(小数位数、精度)转置的枚举，或者结果有意义的计算列(算术运算)。
例如，可能存在为一组事件或传感器生成的传感器数据，这些数据与其他传感器一起使用以提供结果，或者可能存在您想要缩放、分割、将数据转换为新的测量单位或修改值的情况。在这些情况下，我们将新的派生或计算数据添加到行中。假设您有一个以英寸为单位测量距离的传感器，但您需要以毫米为单位的数据。 6 你可以用下面的公式轻松地将数据转换成英寸: 7
value_millimeters = value_inches * 25.4;
回想一下，我们从不想丢弃原始值，所以我们会保存以英寸为单位的值以及派生值。因此，我们将在现在熟悉的 Python 脚本摘录中编写如下数据:
strData = "Distance milimeters: {0:.2f}, Distance inches: {1:.2f} Datetime: {2}\n"
file_log = open("data_log_python.txt", 'a')
dist_inches = read_sensor(1);
dist_mm = dist_inches * 25.4;
file_log.write(strData.format(dist_mm, dist_inches))
file_log.close()
另一个常见的派生列是使用一个列来存储自上次读取以来值的变化量。虽然可以从保存的数据中计算出这个值，但是添加一个额外的列来存储该值可以使读取和理解数据更加容易。为此，我们必须保存旧值，并通过从新值中减去旧值来计算变化。一个积极的结果是价值增加的数量；负值是值减少的量。
让我们仔细看看一个常见的派生列和计算列的示例。
代码实现
派生和计算的数据(列)将以特定的方式实现。也就是会有一个精确的公式或者翻译需要进行。在下面的示例中，我们将看到我在自己和他人的 IOT 解决方案中遇到的三个派生和计算列的示例。
第一个是校准的推导。有时，传感器需要校准，校准结果将决定一个值(如果差值不是线性的，有时是一个公式)，必须加上或减去该值才能获得更精确的值。
第二个例子是一个简单的计算，我们有一个传感器，可以测量盘子或浅盘上许多物体的重量。在本例中，对象的数量是固定的，但在大多数情况下，这也是一个变量。精明的 IOT 爱好者和爱好者会注意到计算不规则物体的平均重量可能并不有趣，所以我们假设这个例子中的物体在大小和组成上都是相似的。
第三个是派生值的另一个例子，我们希望存储从上一次读取的值中读取的值的差异。这代表了大量的注释，包括平均值、运行总数等等，以及数字数据。
虽然所有这些都是技术上的变化或添加新数据，但我们保留原始值，以确保我们可以从推导或计算中的任何变化中恢复。此外，我们存储这些值以使读取和处理数据更容易。
清单 4-8 显示了一个 Arduino 草图的摘录，该草图模拟并实现了三个传感器的读数:一个需要校准的血氧传感器、8一个测量几个物体重量的重量传感器，以及一个我们用来保存自上次读数以来的差值的电压传感器。
Listing 4-8.Derived and Calculated Annotations (Arduino)
/**
Example of derived or calculated columns annotation.
This project demonstrates how to save data to a
microSD card as a log file with additional column annotation.
*/
#include <SPI.h>
#include <SD.h>
#include <String.h>
// Pin assignment for Arduino Ethernet shield
//#define SD_PIN 4
// Pin assignment for Sparkfun microSD shield
#define SD_PIN 8
// Pin assignment for Adafruit Data Logging shield
//#define SD_PIN 10
File log_file; // file handle
String strData; // storage for string
float blood_oxygen;
// Simulated sensor reading method
float read_sensor(int sensor_num) {
if (sensor_num == 1) {
return 90.125 + random(20)/10.00; // sensor #1
} else if (sensor_num == 2) {
return 94.512313 + random(100)/100.00; // sensor #2
} else if (sensor_num == 3) {
return 45.6675 + random(100)/100.00; // sensor #3
} else {
if (random(25) >= 5) {
return (float)random(14)/10.00;
} else {
return -(float)random(14)/10.00;
}
}
}
void setup() {
Serial.begin(115200);
while (!Serial);
Serial.print("Initializing SD card...");
if (!SD.begin(SD_PIN)) {
Serial.println("ERROR!");
return;
}
Serial.println("ready.");
if (SD.remove("data_log.txt")) {
Serial.println("file removed");
}
// initiate random seed
randomSeed(analogRead(0));
}
float oldVal = 67.123;
float newVal = 0.00;
float weight = 0.00;
void loop() {
delay(2000);
log_file = SD.open("data_log.txt", FILE_WRITE);
if (log_file) {
strData = String("Blood oxygen: ");
blood_oxygen = read_sensor(2);
strData += blood_oxygen;
strData += ", Calibrated: ";
// calculated column adjusting for calibration
strData += String(blood_oxygen + 0.785, 2);
strData += ", Weight: ";
weight = read_sensor(3);
strData += weight;
// calculated column for number of objects
strData += ", Avg weight: ";
strData += String((weight/4.0), 4);
// Calculating change since last read
strData += ", Volts: ";
newVal = oldVal+read_sensor(4);
strData += String(newVal, 2);
strData += ", Delta: ";
strData += String(newVal-oldVal,3);
oldVal = newVal;
log_file.println(strData);
log_file.close();
} else {
Serial.println("Cannot open file for reading.");
}
}
Note
以下代码不代表实际的解决方案；相反，它旨在展示生成派生列和计算列的概念。
请注意，我们再次添加新数据作为列，用逗号分隔它们。下面显示了此代码中的一组示例行:
Blood oxygen: 94.88, Calibrated: 95.67, Weight: 46.07, Avg weight: 11.5169, Volts: 68.42, Delta: 1.300
Blood oxygen: 95.23, Calibrated: 96.02, Weight: 46.56, Avg weight: 11.6394, Volts: 68.52, Delta: 0.100
Blood oxygen: 94.97, Calibrated: 95.76, Weight: 46.42, Avg weight: 11.6044, Volts: 68.62, Delta: 0.100
Blood oxygen: 95.46, Calibrated: 96.25, Weight: 46.49, Avg weight: 11.6219, Volts: 69.62, Delta: 1.000
Blood oxygen: 94.96, Calibrated: 95.75, Weight: 46.18, Avg weight: 11.5444, Volts: 70.12, Delta: 0.500
Blood oxygen: 94.62, Calibrated: 95.41, Weight: 46.11, Avg weight: 11.5269, Volts: 70.52, Delta: 0.400
请注意，这里我们看到的是血氧传感器，其校准值与四个物体的重量和平均重量一起存储，然后存储电压和自上次读取值以来的变化。现在让我们看看数据库服务器如何使这些注释变得更容易。
数据库注意事项
到目前为止，您应该认为数据库服务器是一个强大的工具，事实也的确如此。事实上，它非常擅长像前面展示的那样进行推导和计算。此外，根据所需的计算，对于如何实现推导或计算，您有几种选择。本节讨论三种常见的替代方法。还有其他的选择， 9 但这些都是最常见的方法。
您可以添加一个触发器，这是一个在添加数据时执行(触发)的特殊过程；要在一个特殊的列中添加新数据，您可以将计算放在SELECT语句(检索行的方法)中，这样新数据就可以动态生成；或者您可以将计算放在INSERT语句中(保存数据的方法)。让我们从为什么你会选择一个而不是另一个开始来看看每一个。
您应该考虑的第一件事是，是动态地生成新数据，还是以列的形式存储在表中。动态生成新值将意味着使用更少的存储，但在检索行时可能会带来额外的好处。也就是说，检索大量的行可能需要更长的时间，因为计算被推迟并一次完成。因此，在表中保存新数据意味着检索行花费的时间更少，但是需要更多的存储空间。幸运的是，存储空间通常不是问题。请注意，SELECT语句中指定的计算仍然在数据库服务器上执行，尽管在检索每一行时只执行一次。
至于是使用触发器还是作为INSERT语句的一部分在数据库中生成数据，我们应该考虑希望在哪里执行计算。如果放在触发器中，计算将在数据库服务器上执行。但是如果放在INSERT语句中，计算是在客户机(发送数据的那个)上执行的。因此，如果您有复杂的计算或有限的处理能力，您可能希望选择触发器选项。
在我们查看数据库示例之前，下面显示了执行示例所需的表。我在这里包括它，以便您可以自己测试这些示例，并且您可以看到唯一添加的新列是 calibrated 列，它由触发器示例使用。清单 4-9 显示了测试表。
Listing 4-9.Table Schema for Derived and Calculated Columns Example
CREATE TABLE derived_annotation_test (
``id int(11) NOT NULL AUTO_INCREMENT,
``blood_oxygen float NOT NULL,
``blood_oxygen_corrected float DEFAULT NULL,
``weight float DEFAULT NULL,
``volts float DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
请注意，我们添加了一列用于血氧示例的校准，但没有添加其他两个值的校准。那些我们将在运行中生成的。现在让我们看看如何用代码实现这三个选项。
使用触发器的派生列和计算列
触发器是对数据库进行编程以填充列的好方法。在这种情况下，我们希望编写一个触发器，在每次向表中添加一行时执行，并在添加时使用一个简单的公式计算校准值。触发器可以以多种方式执行(或触发)，例如在插入或删除之前或之后。一个触发器与一个特定的表相关联，但是一个表可以有多个触发器。我们将在第 5 章中更详细地讨论触发器，但现在观察以下内容:
CREATE TRIGGER calibrate_blood_oyxgen BEFORE INSERT ON derived_annotation_test
FOR EACH ROW SET NEW.blood_oxygen_corrected = NEW.blood_oxygen + 0.785;
我们在这里看到的是一个触发器，设置为在名为derived_annotation_tes t 的样本表的插入之前执行。注意，第二行非常清楚地显示，将用blood_oxygen值加上0.785的值为blood_oxygen_corrected列设置一个新值。
现在让我们看看当我们插入一行时会发生什么。以下代码在表格中插入一行。注意，我指定了一个列列表，后跟一个值列表。如果不指定列列表，则必须为表中的所有列提供值。
INSERT INTO derived_annotation_test (blood_oxygen, weight, volts) VALUES (94.88, 46.07, 68.42);
现在让我们看看数据是什么样的。回想一下，为了从表中获取数据，我们使用如下的SELECT命令:
mysql> SELECT * FROM derived_annotation_test;
+----+--------------+------------------------+--------+-------+
| id | blood_oxygen | blood_oxygen_corrected | weight | volts |
+----+--------------+------------------------+--------+-------+
| 5 | 94.88 | 95.665 | 46.07 | 68.42 |
+----+--------------+------------------------+--------+-------+
1 row in set (0.00 sec)
在这里，我们看到修正值已保存在blood_oxygen_corrected栏中。最棒的是，我们添加数据时什么都不用做。事实上，触发器的美妙之处在于，你设置一次，它就能对所有插入的数据起作用。
Note
一些 Python 代码示例使用一个称为数据库连接器的特殊库来连接 MySQL。在本例中，它是来自 Oracle 的连接器/Python 连接器库( http://dev.mysql.com/downloads/connector/python/ )。你会在第 6 章和第 8 章中看到这个连接器的更多细节。
使用 SELECT 语句的派生列和计算列
回想一下，我们还可以在读取数据时动态生成派生列或计算列。更具体地说，我们将该操作作为SELECT语句的一部分。让我们插入一些数据来看看这是如何做到的。在这个例子中，我们将展示如何计算从最后一次读取volts列的值以来的差值。回想一下示例表，我们没有存储增量的列。下面显示了一个插入多行的示例INSERT。这也称为大容量插入。
INSERT INTO derived_annotation_test (blood_oxygen, weight, volts)
VALUES (94.88, 46.07, 68.42), (95.23, 46.56, 68.52), (94.97, 46.42, 68.62);
注意，我们只是提供了一个逗号分隔的值列表，每个值代表一个新的数据行。在这种情况下，我们使用与之前 Arduino 草图中所示相同的数据。插入数据后，我们可以按如下方式查看它:
mysql> SELECT * FROM derived_annotation_test;
+----+--------------+------------------------+--------+-------+
| id | blood_oxygen | blood_oxygen_corrected | weight | volts |
+----+--------------+------------------------+--------+-------+
| 5 | 94.88 | 95.665 | 46.07 | 68.42 |
| 7 | 94.88 | 95.665 | 46.07 | 68.42 |
| 8 | 95.23 | 96.015 | 46.56 | 68.52 |
| 9 | 94.97 | 95.755 | 46.42 | 68.62 |
+----+--------------+------------------------+--------+-------+
4 rows in set (0.00 sec)
为了计算增量，我们编写一个 Python 脚本来连接数据库并从表中检索行。我们首先保存读取的第一个值的值，然后在后续行中进行比较，计算自读取最后一个值以来的变化。清单 4-10 显示了一个使用 MySQL 连接器/Python 库从服务器读取数据的 Python 脚本。
Listing 4-10.Calculated Columns Using SELECT (Python)
import mysql.connector;
cnx = mysql.connector.connect(user="root", password="SECRET")
cur = cnx.cursor()
cur.execute("SELECT * FROM test.derived_annotation_test")
old_value = 0;
for row in cur.fetchall():
if old_value > 0:
print "{0}, {1}, {2}, {3}, {4}".format(row[1], row[2], row[3], row[4], row[4] - old_value)
else:
print "{0}, {1}, {2}, {3}".format(row[1], row[2], row[3], row[4])
old_value = row[4]
虽然可能有更有效的方法来读取列值，但我编写了代码来访问行中的每一列，以向您展示结果如何作为列表返回(就像数组一样)。我只需访问从 0 开始的列号，就可以得到该列的值。注意，volts列的值是表中的第五列，因此用[4]来引用。下面显示了运行该脚本的输出:
$ python ./derived_select_example.py
94.88, 95.665, 46.07, 68.42,
94.88, 95.665, 46.07, 68.42, 0.0
95.23, 96.015, 46.56, 68.52, 0.1
94.97, 95.755, 46.42, 68.62, 0.1
这里我们看到代码打印出了blood_oxygen、blood_oxygen_corrected、weight和volts的数据。第一行没有值，因为我们没有以前的值来计算增量。剩余的行将最后一个值之后的增量或变化显示为第五个值。
使用 INSERT 语句的派生列和计算列
现在我们来看一下INSERT方法。我们将使用先前获得的电压数据。在这种情况下，我们已经在代码中的某个地方存储了旧值，这样当我们保存新值时，我们就包括了那里的计算。下面的代码是一个例子，说明如何形成包含计算的INSERT语句。虽然公式很简单，但该示例显示了在插入数据时如何填充派生列或计算列。
回想一下，计算是在客户机上执行的，而触发器示例是在服务器上执行的。我离开去做一个练习，把这个例子转换成一个触发器。在我们看到 SQL 代码之前，让我们将计算列添加到表中。有一个名为ALTER TABLE的强大命令，可以用来添加或删除列，等等。我们使用它来添加新列，如下所示:
ALTER TABLE derived_annotation_test ADD COLUMN avg_weight float AFTER weight;
现在我们添加新数据。回想一下 Arduino 示例，我们想简单地将重量值除以 4，得到传感器测得的每件物品的平均重量。下面是另一个 bulk insert 语句。请注意，我们只是在“平均体重”栏的空白处加入了这个公式。
INSERT INTO derived_annotation_test (blood_oxygen, weight, avg_weight, volts)
VALUES (95.46, 46.49, (46.49/4.00), 69.62), (94.96, 46.18, (46.18/4.00), 70.12),
(94.62, 46.11, (46.11/4.00), 70.52);
快速检查表中的数据，确认行中插入了正确的值。
mysql> SELECT * FROM derived_annotation_test;
+----+--------------+------------------------+--------+------------+-------+
| id | blood_oxygen | blood_oxygen_corrected | weight | avg_weight | volts |
+----+--------------+------------------------+--------+------------+-------+
| 5 | 94.88 | 95.665 | 46.07 | NULL | 68.42 |
| 7 | 94.88 | 95.665 | 46.07 | NULL | 68.42 |
| 8 | 95.23 | 96.015 | 46.56 | NULL | 68.52 |
| 9 | 94.97 | 95.755 | 46.42 | NULL | 68.62 |
| 10 | 95.46 | 96.245 | 46.49 | 11.6225 | 69.62 |
| 11 | 94.96 | 95.745 | 46.18 | 11.545 | 70.12 |
| 12 | 94.62 | 95.405 | 46.11 | 11.5275 | 70.52 |
+----+--------------+------------------------+--------+------------+-------+
7 rows in set (0.00 sec)
这里我们看到只有最后三行有平均重量列。回想一下，我们将新列添加到已经包含数据的表中。除非添加了触发器，否则现有行的新列的值将保持为空(技术上来说是NULL)。
数据解释
有时数据以不可用的形式生成，或者需要一些翻译或解释才能使用。我们已经看到了一个数据解释的例子。回想一下第 1 章中的工厂监控示例，我们有一个传感器，根据产生的值，它可以指示几种状态中的一种。因此，我们可以将数据值解释为这些状态之一，并因此存储该状态。在这种情况下，我们从一系列值中创建一个值，而不是计算一个新值。
让我们看一个类似的例子，但这次我们将看到从不同类型的传感器生成的数据。在这种情况下，被模拟的传感器是诸如由 Sparkfun ( http://sparkfun.com/products/10221 )出售的液位传感器。这是一种测量电阻的传感器，可用于确定从传感器顶部到液体表面的距离。更具体地说，输出与液位成反比。液体越低，测得的电阻越高。液体越低，测得的电阻越小。
现在，假设我们在一个池塘监控解决方案中使用这种传感器，想要测量过滤池内的水位。此外，我们从经验和观察中得知，根据水位的不同，池塘的状态会发生如下变化:
- 如果水位低至 6 英寸或低于固定点，过滤器可能需要清洗。
- 如果水位在 3-6 英寸之间，则池塘水位较低。
- 如果水位在 1-3”之间，则池塘处于正常状态。
- 如果水位高于 1 英寸，则说明池塘中的水过多，水溢出了过滤元件。
因此，我们需要检查特定范围内的电阻值，并存储该范围的枚举值。我们将命名范围(CLEAN、LOW、NORMAL、HIGH)。现在让我们看看如何使用 Python 脚本存储一行数据。请记住，我们总是希望存储原始值，这样，如果枚举需要调整(范围改变)，我们可以改变现有的数据，而不会使其不可用。
代码实现
在这个例子中，我们使用 Python 代码来确定使用哪个枚举。以下代码节选自另一个使用连接器/Python 库的 Python 脚本。不要太担心库的机制；相反，请注意我是如何使用 Python 代码来实现枚举的。
从传感器读取的值以欧姆为单位，在 300 到 1500 +/-10%的范围内。那么我们如何知道使用什么范围呢？我们安装传感器并进行一些测量吗？我们可以这么做。事实上，我们可以使用一个高容器，慢慢地往里面注水，同时观察传感器的数值。这是一种有效的测试传感器的好方法，但是还有一种更快的方法。
大多数制造商提供所谓的数据表来描述设备的性能。在这种情况下，制造商会提供一个图表，指示特定液位的预期值( http://cdn.sparkfun.com/datasheets/Sensors/ForceFlex/eTape%20Datasheet%2012110215TC-8_040213.pdf )。根据这些数据，我们可以确定以下范围与观察到的水位相对应。表 4-2 显示了完整的数据。
表 4-2。
Liquid-Level Enumerated Values for Pond Monitoring
| 传感器顶部的深度，单位为英寸 | 值范围 | 结果 | | --- | --- | --- | | 0 < 1 | 1500 及以上 | 高的 | | 1 < n < 3 | 1150 到 1500 | 标准 | | 3 < n < 6 | 1150 到 700 | 低的 | | 6 < n | 700 及以下 | 干净的 |请注意一些范围是如何重叠的。只有使用或实验才能确定实际值，但这应该是一个良好的开端。现在让我们看看 Python 代码(参见清单 4-11 )。这里我们正在构建一个INSERT语句，它包含了从执行INSERT语句之前的代码中得到的数据。
Listing 4-11.Derived Values Example (Python)
import mysql.connector;
from random import random, randint
def read_sensor():
return 500 + randint(0,1500)
strInsert = "INSERT INTO pond VALUES (null, {0}, '{1}')"
cnx = mysql.connector.connect(user="root", password="SECRET", database="test")
cur = cnx.cursor()
# Calculate enumerated value for sensor.
water_level = read_sensor()
if water_level > 1500:
state = 'CLEAN'
elif water_level > 1150:
state = 'LOW'
elif water_level > 700:
state = 'NORMAL'
else:
state = 'HIGH'
cur.execute(strInsert.format(water_level, state))
cnx.commit()
cur.close()
cnx.close()
花点时间通读代码。不要太担心连接器库。相反，请关注用于设置状态值的代码。这相当简单，确定的值通过参数替换用于INSERT语句。
为了测试代码，我们需要创建一个测试表，如下所示。注意，我添加了我们将需要的两列以及一个自动递增的列，以便于识别行。
CREATE TABLE pond (
``id int(11) NOT NULL AUTO_INCREMENT,
``water_level int NOT NULL,
``state char(12) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
接下来，我们可以多次运行脚本来创建几行。精明的 Python 开发者会看到，我用随机生成的值模拟了传感器读取代码。显然，这将创建相当多的值变化很大的行。也就是说，在实际的池塘中，水位不会从一个时刻到另一个时刻如此剧烈地波动。然而，代码足以测试和演示枚举值的技术。插入几行后，我们可以看到如下结果:
mysql> SELECT * FROM pond;
+----+-------------+--------+
| id | water_level | state |
+----+-------------+--------+
| 8 | 1054 | NORMAL |
| 10 | 1117 | NORMAL |
| 11 | 1278 | LOW |
| 12 | 1316 | LOW |
| 13 | 1451 | LOW |
| 14 | 1688 | CLEAN |
+----+-------------+--------+
6 rows in set (0.00 sec)
花点时间确保这些值与基于表 4-2 中数据的枚举值相匹配。我鼓励您多次运行该脚本，直到您看到每个州的示例。提示:您可能希望修改read_sensor()方法，为每个范围返回一个随机数，而不是从 0 到 1500 的范围。
数据库注意事项
有几种方法可以处理数据库中的数据解释。我将讨论两种这样的方法:在保存数据时使用枚举值来确定状态(仍然需要一点代码)，以及在保存或更新数据时使用一种高级技术来自动确定状态。是的，另一个触发器！
到目前为止，我们知道触发器是非常强大的，在这种情况下，我们可以包含一组类似的代码来确定来自传感器的状态值。事实上，它类似于 Python 的例子。让我们看看触发器内部的代码，重点看解释是如何完成的。下面显示了触发代码:
DELIMITER //
CREATE TRIGGER set_pond_state BEFORE INSERT ON pond FOR EACH ROW
BEGIN
IF NEW.water_level > 1500 THEN
SET NEW.state = 'CLEAN'
ELSEIF NEW.water_level > 1150 THEN
SET NEW.state = 'LOW'
ELSEIF NEW.water_level > 700 THEN
SET NEW.state = 'NORMAL'
ELSE
SET NEW.state = 'HIGH'
END IF;
END //
DELIMITER ;
现在让我们使用批量插入测试触发器。这里，我们将插入四个新行，它们的值是专门为产生四种状态之一而选择的。
INSERT INTO pond VALUES (null, 1501, null), (null, 1151, null), (null, 701, null), (null, 600, null);
MySQL 中有一个有趣的系统变量。它被命名为last_insert_id，存储自动递增字段使用的最后一个值。如果您练习了 Python 示例，您将已经创建了表并插入了几行。我们可以使用last_insert_id来查找自动递增机制的最后一个值，而不是显示(返回)所有的行。在这种情况下，该值是最后一次插入所使用的最后一个值，但是由于这是一次大容量插入，因此该值是大容量插入中的第一个值(在这种情况下为 15)。你自己试试。
mysql> select @@last_insert_id;
+------------------+
| @@last_insert_id |
+------------------+
| 15 |
+------------------+
1 row in set (0.00 sec)
下面显示了插入的行:
mysql> SELECT * FROM pond WHERE id >= 15;
+----+-------------+--------+
| id | water_level | state |
+----+-------------+--------+
| 15 | 1501 | CLEAN |
| 16 | 1151 | LOW |
| 17 | 701 | NORMAL |
| 18 | 600 | HIGH |
+----+-------------+--------+
4 rows in set (0.01 sec)
请注意，我们看到插入的四行，并且已经选择了正确的枚举值。在这一点上，我还应该注意到有一个名为enum的数据类型，您可以使用它来存储表本身中值的字符串。我们将在第 5 章的中看到这个选项的实际应用；但是，如果您熟悉使用enum，我鼓励您更改前面显示的表格和触发器，使用enum代替字符串。
现在，您已经看到了几个注释示例，让我们讨论一个更复杂的聚合来自多个传感器或节点的数据的应用，以及您可能希望在 IOT 解决方案中实现的聚合操作类型。
聚合
为您的 IOT 解决方案聚合数据可能比标注数据更复杂。聚合可以有几种形式。最容易实现的形式是聚合来自多个传感器的数据。从多个节点(其他数据收集器)聚合数据在概念上相似，但在实现上有很大不同。最后，更复杂的聚合形式是对数据集执行操作，如统计或计数函数。
在本节中，我们将从较高的层次来探讨这些聚合形式。因为每一个的实现都可能很复杂，所以我只给出一般的概述，而不是具体的代码解决方案。也就是说，我们将在第 8 章中看到这些聚合形式的实现。因此，以下部分讨论了每种形式的策略、实践和一般描述。
来自多个传感器的数据
IOT 解决方案从多个来源或传感器收集数据是正常的。有时，传感器用于观察不同的事物(事件、物体等)，但大多数情况下，几个源或传感器用于观察单个事物或对象。也就是说，您可能有一个 Arduino 板形式的数据收集节点，从几个监控某些东西的传感器读取数据。或者，您可能有一个 Arduino 读取多个传感器来监控多个对象。
接下来的问题是如何存储这些数据？您应该像在本章前面的示例中看到的那样将传感器值存储为一个集合，还是应该单独存储这些值？有两种方法:基于传感器定时存储数据和基于特定时间段存储数据。
传感器驱动的数据
基于传感器读取时间生成的数据意味着数据以数据可用性所决定的时间间隔存储。此外，在收集数据时，独立地使用数据(显示、列表或挖掘信息等)。也就是说，使用数据的时间对数据没有意义。
虽然存储一组对应于单个对象的传感器值是很自然的，但是如果传感器以不同的时间间隔产生值，您可能必须考虑一次存储一个值，而不是等待所有传感器报告。事实上，不同的传感器在不同的时间间隔被读取并不罕见。这可能是因为你所观察的事物的性质，以及观察的时机有助于产生知识。
例如，考虑工厂监控解决方案。您可能希望每小时读取一次温度(甚至更频繁)，因为室外温度在一小时内可能会在某些区域快速变化，或者您可能希望在室内受控环境中每隔几小时读取一次温度。然而，对于土壤湿度，您可能希望每天读取两次数值，因为土壤湿度可能不会快速变化，尤其是在受控气候下。
假设您决定每小时读取一次温度值，每六小时读取一次土壤湿度值。给定这些时间间隔，你如何处理这六个温度值？每个土壤湿度值有六个温度值。你是平均温度值还是扔掉其中的五个？
显然，丢弃五个传感器读数是潜在的信息损失。在这种情况下，您可能会丢失温度变化的数据。例如，如果温度在第一个小时变化了 4 度(在我所在的地区并不罕见)，但在接下来的五个小时内只变化了 1 度，保存最后一个值会模糊温度变化的时间，更重要的是会丢失温度快速变化的时间事件。即使取平均值也会丢失数据和模糊知识。知识的丧失可能不明显，需要一点思考。表 4-3 显示了我们可以收集的数据类型示例。
表 4-3。
Sensor Data Frequency and Loss of Knowledge
| 小时 | 温度 | 土壤湿度 | | --- | --- | --- | | one | Twenty-four point five | | | Two | Twenty-four point seven | | | three | Twenty-four point nine | | | four | Twenty-five point two | | | five | Twenty-five point four | | | six | Twenty-five point six | Four hundred and twenty-six | | seven | Twenty-five point eight | | | eight | Twenty-seven point nine | | | nine | Thirty point one | | | Ten | Twenty-nine point three | | | Eleven | Twenty-eight point nine | | | Twelve | Twenty-eight point six | Four hundred and ten |请注意，这里我们看到的是温度值(以摄氏度为单位)，但每六小时只有一个土壤湿度值。如果我们只在读取土壤湿度时存储温度读数，我们将会看到数值的巨大变化，并且不知道温度何时变化——只知道自六小时前最后一次读取温度以来温度发生了变化。
例如，注意第 6 小时的温度和土壤湿度。这里我们看到我们分别存储了值(25.6，426)。请注意第 12 小时的值。这里我们存储了值(28.9，410)。虽然土壤湿度没有太大变化，但我们看到温度发生了变化(28.9–25.6 = 3.0)。然而，我们已经失去了在第 7 小时和第 10 小时之间温度变化最大的时刻，甚至失去了第 9 小时温度最高的事实。
相反，如果我们对读取的温度值进行平均，我们将保存第 6 小时(25.05，426)和第 12 小时(28.43，410)的数据。虽然随着时间的推移，我们已经考虑了这些价值，但我们并没有获得更多的信息。是的，我们仍然能探测到间隔期间温度上升的趋势，但是温度最高的那个小时仍然不知道。你也可以说我们已经失去了变化率的知识，甚至失去了准确性，因为我们存储的温度值在读取值时是不准确的。
当您遇到将传感器数据存储为单行会模糊知识的情况时，您需要将数据划分到两个表中，而不是一个表中。图 4-2 显示了一个解决方案的示例，我们可以以不同的速率保存传感器数据，但仍将其与单一事物相关联。
图 4-2。
Collecting data from multiple sensors at different rates
注意这里我们有三张桌子。基表或包含核心信息的表被命名为plants。我们看到一个单独的表存储土壤湿度数据，另一个存储温度。这些表共享一个公共列id，用于唯一识别读取传感器数据的工厂。也就是说，我们可以检索给定植物的所有温度或土壤湿度值(列id是temperature和soil_moisture表的外键)。请注意，我们还为每一行存储了时间戳，因此我们知道传感器数据是何时存储的，因此可以绘制一段时间内的数据或确定变化率。
Note
图 4-2 中的图取自 MySQL Workbench 中的可视化数据库编辑器( http://dev.mysql.com/downloads/workbench/ )。
通过制作三个表而不是一个表，我们保留了从数据中获取知识的潜力，这些数据在不同的时间间隔被读取。事实上，这个例子显示了数据库的强大。当然，您可以在基于文件的解决方案中实现这个解决方案，但是数据库使它更容易查看，甚至更容易设置。
区间驱动数据
在一些解决方案中，以固定或特定的时间间隔存储数据更为重要。在这些解决方案中，重点是收集在特定时间使用的数据。因此，该解决方案很可能不会存储数据，除非或直到数据被请求。一些解决方案使数据动态可用，但是通常直到用户或一些其他机制发起请求时才存储数据。
例如，考虑一个气象站。如果您自己正在构建一个气象站，您可能会加入一个显示元素来实时显示天气观测的当前值，如温度、气压等。在这种情况下，来自传感器的数据被频繁读取，并且显示元素(例如，仪表)被频繁更新。然而，这些值的数据不太可能被存储。
相反，对于气象站来说，保存的数据通常是特定时间段的一组观测数据。你可能会看到气象站每小时存储一次数据，甚至每小时存储几次。这是因为观测有一个共同的数据元素——收集时间。因此，间隔驱动数据可能需要您构建解决方案，以特定的间隔存储大量数据，而不是以特定传感器或事件的间隔存储。
对于这些解决方案，我们通常将信息存储为一个条目(行)。然而，没有任何东西说我们必须这样做。如果有必要，我们可能仍然希望将数据存储在单独的表(文件)中，但是通常不会出现与获得的数据或知识相关的问题。同样，这是因为数据保存为传感器之间的特定间隔，而不是在传感器数据可用时驱动。
来自多个节点的数据
与来自多个传感器的数据密切相关的是从几个节点收集的数据。该节点不是直接读取传感器，而是从其他设备获取数据。例如，您可能使用 XBee 模块将一个 Arduino 无线连接到几个模块。因为 XBee 模块只能发送数据，所以您需要 Arduino 节点来收集数据并将其存储在本地文件或数据库中。
同样的考虑适用于我们从多个传感器发现的数据。很可能每个 XBee 模块都定期发送数据，并且可能包括多条数据(多个传感器数据)。因此，如何保存数据具有相同的含义——将数据作为一行存储在一起，还是分别存储数据。
到目前为止，原则上与上一种形式没有什么不同。然而，从多个节点收集数据还有另一个可以利用的功能——批量保存数据。回想一下前面的例子，MySQL INSERT SQL 语句允许在一个命令中存储多行数据。通过从多个节点收集数据，我们可以轻松地构建这样的语句。
清单 4-12 显示了一个 Arduino 草图的摘录，该草图从多个 XBee 模块获取数据并将数据保存在 MySQL 数据库中。 10 在这个例子中，我让事情变得相当简单，我们读取从多个数据节点生成的传感器数据，这些数据节点被表示为带有温度传感器的 XBee 模块。该代码旨在向数据库中批量插入数据。我已经将包含的行数设置为 3，但是您可以很容易地将这段代码扩展到您有多少内存来存储数据就有多少行。
那么我们如何知道哪个 XBee 被读取了呢？我们保存模块的地址——所有 XBee 模块都有唯一的地址，因此我们知道温度数据的来源。我们还看到一个在数据节点上执行计算的例子，其中我们以原始传感器形式以及摄氏度和华氏度存储温度。我还应该注意，在连接多个 XBee 模块的情况下，根据 XBee 模块发送数据的时间，可以从同一个模块收集两次数据。
Listing 4-12.Getting Data from Multiple Nodes (Arduino)
String get_data(ZBRxIoSampleResponse *ioSample) {
// Received data from address of data node
int address = (ioSample->getRemoteAddress64().getMsb() << 8) +
ioSample->getRemoteAddress64().getLsb();
// Get and calculate the temperature in C and F
float temp_raw = ioSample->getAnalog(3);
float temp_c = ((temp_raw * 1200.0 / 1024.0) - 500.0) / 10.0;
float temp_f = ((temp_c * 9.0)/5.0) + 32.0;
String strRow = String("(");
strRow += String(address);
strRow += ",";
strRow += String(temp_raw);
strRow += ",";
strRow += String(temp_c);
strRow += ",";
strRow += String(temp_f);
strRow += ")";
return strRow;
}
...
void loop() {
String row[3];
int i;
i = 0;
//attempt to read a packet
xbee.readPacket();
if (xbee.getResponse().isAvailable()) {
// XBee module is communicating, check for IO packet
if (xbee.getResponse().getApiId() == ZB_IO_SAMPLE_RESPONSE) {
// Get the packet
xbee.getResponse().getZBRxIoSampleResponse(ioSample);
row[i] = get_data(``&
i++;
}
else {
Serial.print("Expected I/O Sample, but got ");
Serial.print(xbee.getResponse().getApiId(), HEX);
}
} else if (xbee.getResponse().isError()) {
Serial.print("Error reading packet. Error code: ");
Serial.println(xbee.getResponse().getErrorCode());
}
// Store the data once 3 entries are retrieved.
if (i == 3) {
i = 0;
String strINSERT = String("INSERT INTO test.room_temperatures VALUES ");
strINSERT += row[0];
strINSERT += ",";
strINSERT += row[1];
strINSERT += ",";
strINSERT += row[2];
Serial.println(strINSERT);
// Create an instance of the cursor passing in the connection
MySQL_Cursor *cur = new MySQL_Cursor(``&
cur->execute(strINSERT.c_str());
delete cur;
}
}
哇，这么多代码，而且只是节选！我将代码的三个部分加粗，以突出我所演示的概念。注意get_data()方法。这里我们看到了从通信包中读取数据并产生一个形式为(N,F,F,F)的字符串的代码，其中N是一个整数，F是一个浮点数。我们将在后面的代码中使用这个字符串。第二部分是对get_data()的调用，在这里我们保存在数组中创建的字符串。在前面的部分中，我们检测到三个数据收集器已经发送了数据，我们用一个INSERT语句将信息保存到数据库中。
有一点，使用这样的数据聚合器会使事情变得更复杂。回想一下，有时我们希望存储数据被读取的日期和时间。如果我们实现一个像前面所示的数据聚合器，其中我们不捕获读取值的日期和时间，那么在数据库中设置时间戳列可能会导致日期和时间值不准确。
例如，如果读取所有三组数据需要 10 秒，那么时间戳值不仅会延迟大约 10 秒，而且这三行的时间戳几乎相同。对于某些解决方案来说，这可能没问题，但如果延迟更像是 10 分钟甚至一个小时，这种延迟可能是不可接受的。
因此，如果您想要为数据收集器存储日期和时间信息，您将必须在传感器被数据收集器读取或被数据收集器在接收数据时设置时收集数据。
汇总计算
您可能遇到的最后一种聚合形式包括需要对一组数据进行一些计算的情况。这可以简单到计算总和的平均值，找到最小和最大值，或者执行任何这样的公式或运算。您可以编写代码来处理这些操作，这是一个有效的解决方案。然而，这是数据库服务器擅长的另一个领域。
例如，考虑清单 4-13 中所示的 Python 脚本的代码摘录。这里我们看到了从包含多列数据的文件中读取多行的代码。我们使用 Python 的能力来解密文件，然后对数据执行操作。
Listing 4-13.Aggregate Calculations (Python)
file_log = open("data_log_python.txt", 'a')
temp_tot = 0;
temp_min = 999;
temp_max = 0;
for i in range(0,20):
# read sensors
temp = read_sensor(1)
baro = read_sensor(2)
# add to total
temp_tot = temp_tot + temp
# find min/max
if (temp < temp_min):
temp_min = temp
if (temp > temp_max):
temp_max = temp
print(temp, baro)
file_log.write(strData.format(temp, baro))
# display aggregate values
print "Average Temperature:", temp_tot/20.00
print "Min Temperature:", temp_min
print "Max Temperature:", temp_max
file_log.close()
请注意，我们计算读取值的平均值(20)以及最小值和最大值。除了我们必须计数、合计和检测最小值/最大值之外，代码中没有任何神奇之处。虽然代码并不复杂，但它远不止一行代码。这里显示了一个输出示例:
Average Temperature: 94.0704303696
Min Temperature: 90.2774251101
Max Temperature: 99.8600782018
现在让我们看看如何使用一个叫做函数的 MySQL 特性在数据库中进行同样的操作。在本例中，我对前面显示的 Python 代码使用了相同的数据，将其存储在一个简单的表中。正如您将看到的，对数据库中的数据进行聚合操作很容易。
清单 4-14 显示了特殊函数的用法，您可以在SELECT语句中使用AVG、MIN和MAX函数。这些函数完全符合您的预期。这样的功能还有很多。有关可用函数的完整列表，请参见在线 MySQL 参考手册( http://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html )。
Listing 4-14.Aggregate Calculations (SQL)
mysql> SELECT AVG(temperature), MIN(temperature), MAX(temperature) FROM aggregation_test;
+------------------+-------------------+-------------------+
| AVG(temperature) | MIN(temperature) | MAX(temperature) |
+------------------+-------------------+-------------------+
| 94.0704303741455 | 90.27742767333984 | 99.86007690429688 |
+------------------+-------------------+-------------------+
1 row in set (0.00 sec)
请注意，我们使用了一个命令来获取这些数据。还有什么比这更简单的呢？另请注意，最终值与 Python 示例输出略有不同。这是因为浮点值的舍入不完美，从更高精度的小数中可以看出这一点。例如，如果我们只想显示两个十进制值，Python 和数据库的结果将是相同的:分别是 94.07、90.28 和 99.86。
显然，数据库中的语句更容易阅读和使用。您所要做的就是用正确的函数形成查询。在接下来的两章中，你会看到更多关于这些陈述的内容。
摘要
数据转换不仅仅是将一种数据类型转换成另一种数据类型。正如您在本章中看到的，在决定如何进行转换之前，您需要考虑许多事情。我们不仅需要考虑我们正在观察的东西，还需要考虑我们期望学到的东西，以及如何解释这些数据以获得进一步的知识。我们需要考虑原始数据以及使数据更具信息性和相关性所需的任何转换。
解释数据之外的另一个考虑是如何注释数据。过多的注释会掩盖我们想要学习的东西，过少的注释会产生错误的解释或导致错过获取知识的机会。类似地，如果我们需要组合来自多个传感器或多个数据收集器的数据，或者需要对数据执行统计或计数操作，那么数据的聚合可能很重要。
在本章中，我们学习了在考虑数据时要问的几个实际问题，以及在 Arduino 和 Python 代码中注释和聚合的几个例子。本章还介绍了在数据库中存储 IOT 数据的注意事项。事实上，我们看到许多示例注释和聚合在数据库中比在代码中更容易实现。
在下一章，我们将深入探讨 MySQL 的数据库存储。您将了解什么是 MySQL，如何安装它，以及如何开始为您的 IOT 数据构建数据库。
Footnotes 1
数据库存储前和数据库存储期间。
并非总是如此，但这是我注意到的一种趋势。
我说可能是相同的大小，因为可变的大小取决于处理器和平台。在考虑数据类型的内存分配之前，最好查阅您的平台的文档。
我自己的经验和训练迫使我保持严格的类型坚持，但我也承认在某些情况下这可能是必要的。即超越琐碎。
叫做小端。参见 https://en.wikipedia.org/wiki/Endianness 。
或者更可能是几英寸到几厘米。
我避免了古怪但过度使用的温度例子。不客气
在这种情况下，我使用一种简单的校准方法，将值移动一个固定的增量。这种性质的校准很少。校准可能基于线性标度(随着数值的增加，误差变得更大或更小)，或者可能需要更复杂的公式来校正数值。
例如存储事件、存储过程等等。
是的，你可以用 Arduino 做到这一点！
五、MySQL 优先
Electronic supplementary material The online version of this chapter (doi:10.1007/978-1-4842-1293-6_5) contains supplementary material, which is available to authorized users.
因此，您正在规划您的 IOT 解决方案，并决定在您的解决方案中内置一个数据库服务器。也许您以前从未使用过数据库系统，或者您作为用户使用过一个数据库系统，但从未需要从头开始建立一个。或者，您可能已经决定发现数据库系统有什么大惊小怪的。无论是哪种情况，你都有了入门所需的核心知识 1 :你知道你要存储什么，数据是什么样子的。
回想一下，我们讨论并看到了一些 IOT 数据的示例，以及在 IOT 解决方案中如何最好地存储这些数据。回想一下，我们想要存储的每种类型的数据都有其利弊。您还了解了如何扩充数据，使其在您的 IOT 解决方案中更有用。如您所见，并不总是数据的发起者(传感器节点或平台)需要进行这种增强。正如您所了解的，拥有更多计算资源的数据聚合器更适合这种操作。但是，您还会看到数据库服务器(有时称为数据节点，因为您可以有多个数据节点)的功能更强大，它可以自动执行数据聚合和注释。
在本章中，您还将看到如何将这些技术应用到实践中，将数据存储到数据库中。更具体地说，您将学习如何使用 MySQL，并在 IOT 解决方案中利用这些知识。我们从一个简短的讨论开始，讨论如何获得 MySQL，安装它，并创建您的第一个数据库。本章的其余部分将通过例子介绍如何使用 MySQL 的简短入门。
入门指南
MySQL 是世界上最受欢迎的开源数据库系统，原因有很多。首先，它是开源的，这意味着任何人都可以免费使用它来完成各种各样的任务。 2 最棒的是，MySQL 被包含在许多平台仓库中，使其易于获取和安装。如果你的平台在资源库中没有包含 MySQL(比如 aptitude)，你可以从 MySQL 网站( http://dev.mysql.com )下载。
甲骨文公司拥有 MySQL。Oracle 通过收购 Sun Microsystems 获得了 MySQL，Sun Microsystems 从其原始所有者 MySQL AB 获得了 MySQL。尽管担心会出现相反的情况，但 Oracle 通过继续投资于新功能的演进和开发以及忠实地维护其开源遗产，表现出了对 MySQL 的出色管理。尽管 Oracle 也提供 MySQL 的商业许可——就像它以前的所有者过去做的那样——MySQL 仍然是开源的，每个人都可以使用。
What Is Open Source? Is It Really Free?
开源软件是从对公司财产心态的有意识抵制中成长起来的。在为麻省理工工作时，自由软件运动之父理查德·斯托尔曼抵制了软件私有(封闭)的趋势，离开了麻省理工，创办了 GNU (GNU 非 Unix)项目和自由软件基金会(FSF)。
斯托曼的目标是重建一个合作的开发者社区。然而，他有先见之明，意识到这个系统需要版权许可来保证某些自由。(有些人把斯托曼对版权的理解称为“左版权”，因为它保障了自由，而不是限制了自由。)为了解决这个问题，斯托曼创建了 GNU 公共许可证(GPL)。GPL 是一个巧妙的法律许可作品，它允许代码不受限制地被复制和修改，规定衍生作品(修改后的副本)必须在与原始版本相同的许可下发布，没有任何附加限制。
自由软件运动有一个问题。自由一词旨在保证使用、修改和发布的自由；这并不意味着“没有成本”或“免费到一个好的家。”为了消除这种误解，开放源码倡议(OSI)成立了，后来采用并推广了“开放源码”一词来描述 GPL 许可证所保证的自由。有关开源软件的更多信息，请访问 www.opensource.org 。
我如何使用 MySQL？
MySQL 在您的系统上作为后台进程运行(或者作为前台进程，如果您从命令行启动它)。像大多数数据库系统一样，MySQL 支持结构化查询语言(SQL)。您可以使用 SQL 创建数据库和对象(使用数据定义语言[DDL])，写入或更改数据(使用数据操作语言[DML])，以及执行各种命令来管理服务器。
要发出这些命令，必须首先连接到数据库服务器。MySQL 提供了一个名为 mysql 3 的客户端应用，使您能够连接到服务器并在其上运行命令。客户端接受 SQL 命令以及一些特定于客户端本身的命令。分号必须终止所有命令。
Tip
要查看客户端中可用命令的列表，请在提示符下键入help并按 Enter 键。
要连接到服务器，必须指定用户帐户和要连接的服务器。如果您连接到同一台机器上的服务器，您可以省略服务器信息(主机和端口)，因为这些默认为端口3306上的localhost。使用--user(或-u)选项指定用户。您可以在命令上为用户指定密码，但更安全的做法是指定--password(或-p)，并且客户端会提示您输入密码。如果您确实在命令行上指定了密码，您将会得到一个警告提示，鼓励您不要这样做。
在没有--host(或-h)和--port选项的同一台机器上使用 mysql 客户端不使用网络连接。如果您想要使用网络连接进行连接，或者想要使用不同的端口进行连接，则必须使用环回地址。例如，要连接到同一台机器上端口 13001 上运行的服务器，使用命令mysql -uroot -p –h127.0.0.1 --port=13001。
清单 5-1 展示了几个使用 mysql 客户端的 SQL 命令的例子。
Listing 5-1.Commands Using the mysql Client
$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.8-rc-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE DATABASE plant_monitoring;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE TABLE plant_monitoring.plants (plant_name char(50), sensor_value int, sensor_event timestamp);
Query OK, 0 rows affected (0.06 sec)
mysql> INSERT INTO plant_monitoring.plants VALUES ('living room', 23, NULL);
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM plant_monitor.plants;
+-------------+--------------+---------------------+
| plant_name | sensor_value | sensor_event |
+-------------+--------------+---------------------+
| living room | 23 | 2015-09-22 19:54:01 |
+-------------+--------------+---------------------+
1 row in set (0.01 sec)
mysql> SET @@global.server_id = 111;
Query OK, 0 rows affected (0.00 sec)
mysql>
在本例中，您看到 DML 以CREATE DATABASE和CREATE TABLE语句的形式出现，DDL 以INSERT和SELECT语句的形式出现，还有一个简单的管理命令来设置全局服务器变量。接下来，您将看到创建一个数据库和一个表来存储数据，在表中添加一行，最后检索表中的数据。注意我是如何用大写字母表示 SQL 命令关键字的。这是一种常见的做法，有助于使 SQL 命令更容易阅读，也更容易找到用户提供的选项或数据。
您可以通过键入命令quit退出 MySQL 客户端。在 Linux 和 Unix 系统上，您可以按 Ctrl+D 退出客户端。
MySQL 中有很多可用的命令。幸运的是，你只需要掌握几个比较常见的。以下是您最常使用的命令。<>中包含的部分表示用户提供的命令组件，而[...]表示需要额外的选项。
CREATE DATABASE <database_name>:创建数据库USE <database>:设置默认数据库(不是 SQL 命令)CREATE TABLE <table_name> [...]:创建一个表格或结构来存储数据INSERT INTO <table_name> [...]:向表格中添加数据UPDATE [...]:更改特定行的一个或多个值DELETE FROM <table_name> [...]:从表格中删除数据SELECT [...]:从表格中检索数据(行)SHOW [...]:显示对象列表
虽然这个列表只是一个简短的介绍，并不像一个完整的语法指南，但有一个很好的在线参考手册，它非常详细地解释了每个命令(以及更多)。当你对 MySQL 有任何疑问时，你应该参考在线参考手册。你可以在 http://dev.mysql.com/doc/ 找到它。
显示的一个更有趣的命令允许您查看对象列表。例如，您可以看到带有SHOW DATABASES的数据库，带有SHOW TABLES的表列表(一旦您更改为数据库)，甚至带有SHOW GRANTS的用户权限。我发现自己经常使用这些命令。
Tip
如果使用 mysql 客户端，必须用分号(;)或\G结束每个命令。
如果您认为 MySQL 不仅仅是几个简单的命令，那么您绝对是正确的。尽管 MySQL 易于使用且启动时间快，但它是一个成熟的关系数据库管理系统(RDBMS)。比你在这里看到的要多得多。有关 MySQL 的更多信息，包括所有高级特性，请参见参考手册。
Mysql—What Does It Mean?
MySQL 这个名字是一个专有名称和一个缩写的组合。SQL 是结构化查询语言。“我的部分”不是所有格形式——它是一个名称。在这种情况下，My 是创始人女儿的名字。至于发音，MySQL 专家发音为“My-S-Q-L”而不是“my sequel”
如何获取和安装 MySQL
MySQL 服务器可用于多种平台，包括大多数 Linux 和 Unix 平台、Mac OS X 和 Windows。要下载 MySQL 服务器，请访问 http://dev.mysql.com/downloads/ ，点击社区，然后点击 MySQL 社区服务器。这是 MySQL 的 GPLv2 许可证。 4 页面会自动检测你的操作系统。如果您想为另一个平台下载，可以从下拉列表中选择。
下载页面将列出几个可供下载的文件。根据您的平台，您可能会看到几个选项，包括压缩文件、源代码和安装包。大多数人会选择在笔记本电脑或台式电脑上安装安装包。图 5-1 显示了 Debian 和 Ubuntu 平台的 APT 库的一个例子。
图 5-1。
Download page for Ubuntu Linux
最受欢迎的平台之一是微软视窗系统。Oracle 为 Windows 提供了一个名为 Windows Installer 的特殊安装包。这个包包括社区许可下所有可用的 MySQL 产品，包括 MySQL 服务器、工作台、实用程序、Fabric 和所有可用的连接器(用于连接 MySQL 的程序库)。这使得在 Windows 上安装成为一站式、一次安装的事情。图 5-2 显示了 Windows installer 的下载页面。以下几段演示如何在 Windows 10 上安装 MySQL。你将在下一章看到如何在像 Raspberry Pi 这样的单板计算机上安装 MySQL。
图 5-2。
Download page for Windows Installer
首先选择与您的 Windows 版本匹配的 Windows Installer 32 位或 64 位安装包。下载文件后，单击文件开始安装。请注意，一些浏览器(如新的 Edge 浏览器)可能会询问您是否要启动安装。您可能需要回复允许安装的对话框。
第一步是同意许可。图 5-3 显示了安装对话框的许可协议面板。
图 5-3。
License agreement
显示的许可证是社区版的 GPLv2 许可证。阅读许可 5 并同意后，选择“我接受许可条款”复选框，然后单击“下一步”。
下一个面板显示设置或安装类型。大多数人会选择 developer 选项，因为它会安装所有的 MySQL 组件和应用，并设置在本地机器上运行 MySQL 的默认设置。您可以选择不同的选项，并在右侧的文本中了解更多信息。图 5-4 显示了设置类型面板。做出选择后，单击下一步。
图 5-4。
Setup type
下一个面板将检查任何必需的组件。例如，如果您在不包含 Python 或 Visual Studio 的机器上安装，您将得到一个警告，如图 5-5 所示。要继续，您必须解决每个问题。也就是说，在解决需求之前，按钮将不可用。解决这些问题后，单击下一步。
图 5-5。
Check Requirements page
下一个面板将显示所有可供安装的软件包。如果您不想安装其中的一个或多个，您可以单击每个并选择不安装它们。图 5-6 显示了所有包装都标记为待安装的示例。一旦您对安装选项感到满意，请单击执行。
图 5-6。
Preparing for installation
面板会随着每次安装的进度进行更新，如图 5-7 所示。
图 5-7。
Installing packages
随着安装的进行，每个软件包旁边会出现不同的符号。在某些情况下，安装程序可能会下载额外的软件包。你可以在图 5-7 中看到这一点。注意，下载了 Connector/Python 2.1.2。您可以单击“显示详细信息”按钮来查看安装的更多详细信息。
一旦所有选中的包都被安装，你会看到配置面板，如图 5-8 所示。该面板根据您选择的软件包显示不同配置选项的列表。如果您选择安装所有内容，您将看到一个类似下图的面板。单击下一步继续。
图 5-8。
Product configuration
每种产品的配置过程都不同。比如配置 MySQL 服务器，从联网开始有几个步骤，如图 5-9 所示。此面板允许您选择配置类型(服务器如何启动和运行)以及网络细节。在此页面上，您可以选择服务器将监听连接的 TCP/IP 端口。如果要配置其他参数，请选择“显示高级选项”复选框。做出选择后，单击“下一步”进入下一步。
图 5-9。
Type and Networking page
下一个面板是账户和角色面板，如图 5-10 所示。此面板允许您设置初始用户帐户以及 root 密码。强烈建议您为 root 帐户选择一个强密码。您也可以通过点按“添加用户”按钮来设置具有不同角色的附加用户帐户。选择设置后，单击“下一步”进入下一步。
图 5-10。
Accounts and Roles page
下一个面板允许您控制 MySQL 在 Windows 机器上的启动方式。图 5-11 显示了细节。请注意，您可以将服务器配置为作为 Windows 服务启动，在启动时自动启动 MySQL(或不启动)，以及服务器将使用什么类型的帐户。我强烈建议保留默认设置，除非您知道如何设置帐户来运行服务。单击“下一步”进入下一步。
图 5-11。
Windows Service page
如果您在第一个配置面板中检查了高级设置，您将看到高级选项面板，如图 5-12 所示。此面板允许您打开常规日志(用于记录服务器反馈语句)、查询日志(用于记录所有查询)和二进制日志(用于复制和备份)。如果你计划在复制设置中使用服务器(我将在第 7 章的中讨论)，你应该打开二进制日志。在复制设置中，服务器 ID 在所有服务器中必须是唯一的，您可以在此面板上进行设置。选择设置后，单击下一步。
图 5-12。
Advanced Options panel
图 5-13 所示的下一个面板显示了配置的进度。
图 5-13。
Server configuration execution
如果您选择将 MySQL 作为 Windows 服务启动，您将会看到列出的第二组语句，如图 5-14 所示。最后，如果您选择安装示例和示例数据库，您将看到另一个对话框面板，显示安装示例数据库的进度。所有步骤完成后，单击完成。
图 5-14。
Configuring examples
除了作为一站式安装机制之外，我最喜欢 Windows Installer 的一点是能够再次使用安装程序进行更改。也就是说，您可以在另一个时间运行安装程序来安装不同的软件包，甚至删除您不再需要的软件包。这是在 Windows 上安装和配置 MySQL 的一种便捷方式。
Note
其他平台的安装程序采用了与 Windows Installer 类似的机制。例如，大多数安装包中都有配置步骤。
现在你知道了如何设置 MySQL，让我们来讨论如何使用 MySQL 存储和检索数据。
如何存储和检索数据
既然您已经知道了 MySQL 是什么以及它是如何使用的，那么在开始构建您的第一个数据库服务器之前，您需要对 RDBMSs 和 MySQL 有更多的了解。本节讨论 MySQL 如何存储数据(以及存储在哪里)，它如何与其他系统通信，以及管理新的 MySQL 服务器所需的一些基本管理任务。
Note
我将在第 6 章中向你展示如何在 Raspberry Pi 和类似的板上安装 MySQL。
What Is A Relational Database Management System?
RDBMS 是一种基于数据关系模型的数据存储和检索服务，由 E. F. Codd 于 1970 年提出。这些系统是结构化数据的标准存储机制。大量的研究致力于改进 Codd 提出的基本模型，正如 C. J. Date 在《数据库关系模型:回顾和分析》中所讨论的那样。理论和实践的这种演变最好地记录在第三个宣言中。 7
关系模型是存储库(数据库)的直观概念，可以通过使用一种称为查询语言的机制来检索、更新和插入数据，从而方便地查询存储库。许多供应商已经实现了关系模型，因为它具有完善的系统理论、坚实的数学基础和简单的结构。最常用的查询机制是 SQL，它类似于自然语言。虽然关系模型中不包括 SQL，但它提供了关系模型在 RDBMSs 中的实际应用的一个组成部分。
数据表示为关于某个事件或实体的相关信息(属性或列，有时称为字段)。属性值集以元组的形式形成(有时称为记录或行)。元组存储在具有相同属性集的表中。然后，表可以通过键、属性和元组的约束与其他表相关。
表可以有称为索引的列的特殊映射，允许您以特定的顺序读取数据。索引对于快速检索与索引列的值相匹配的行也很有用。
MySQL 存储数据的方式和位置
MySQL 数据库系统通过一种有趣的编程隔离机制存储数据，这种机制称为存储引擎，由处理程序接口控制。处理程序接口允许在 MySQL 服务器中使用可互换的存储组件，以便解析器、优化器和各种组件可以使用公共机制在磁盘上存储数据时进行交互。这也称为可插拔存储引擎。
Note
MySQL 支持几种存储引擎。默认情况下，大多数都被设计为将数据写入磁盘。但是，内存存储引擎将数据存储在内存中，但不是永久性的。也就是说，当计算机重新启动时，数据会丢失。您可以使用内存存储引擎进行快速查找表。事实上，一种优化技术是在启动时使用内存存储引擎创建查找表的副本。
这对你意味着什么？这意味着您可以选择不同的数据存储机制。您可以在下面的代码示例所示的表CREATE语句中指定存储引擎。请注意命令中的最后一行:这是如何指定存储引擎的。去掉这个子句会导致 MySQL 使用默认的存储引擎。对于本书中的例子，MySQL 5.5 默认使用 MyISAM 存储引擎。
Tip
在 MySQL 版本 5.6 中，默认存储引擎从 MyISAM 更改为 InnoDB。
CREATE DATABASE bvm;
CREATE TABLE bvm.books (
``ISBN varchar(15) DEFAULT NULL,
``Title varchar(125) DEFAULT NULL,
``Authors varchar(100) DEFAULT NULL,
``Quantity int(11) DEFAULT NULL,
``Slot int(11) DEFAULT NULL,
``Thumbnail varchar(100) DEFAULT NULL,
``Description text
) ENGINE=MyISAM;
太好了。现在，MySQL 上存在哪些存储引擎？您可以通过发出SHOW STORAGE ENGINES命令来发现支持哪些存储引擎，如清单 5-2 所示。如你所见，有很多可供选择。我将介绍一些可能与规划 IOT 解决方案相关的内容。
Note
下面几节展示了如何在典型的类 Linux(实际上是类 Unix)平台上使用 MySQL。我发现大多数 IOT 解决方案将使用这些平台的形式，而不是 Windows 10，但这可能会在未来发生变化。目前，我主要在这些平台上探索 MySQL，而不是 Windows。然而，所示的许多示例可以在 Windows 上执行，尽管使用的是不同的命令集。
Listing 5-2.Available Storage Engines
mysql> SHOW STORAGE ENGINES \G
*************************** 1\. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
*************************** 2\. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3\. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4\. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5\. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6\. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 7\. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8\. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9\. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
9 rows in set (0.00 sec)
mysql>
从 5.6 版本开始，MySQL 默认使用 InnoDB 存储引擎。以前的版本默认使用 MyISAM。InnoDB 是一个完全事务性的、ACID 8 存储引擎。事务是一批语句，在将任何更改写入磁盘之前，这些语句必须全部成功。典型的例子是银行转账。如果您考虑一个需要从一个帐户中扣除一笔金额，然后将该金额存入另一个帐户以完成资金转移的系统，您不会希望第一个帐户成功而第二个帐户失败，或者相反！
将语句包装在一个事务中可以确保在所有语句都正确无误地完成之前，不会将任何数据写入磁盘。在这种情况下，事务用一个BEGIN语句指定，并以一个COMMIT保存更改或一个ROLLBACK撤销更改结束。InnoDB 将其数据存储在一个文件中(还有一些用于管理索引和事务的附加文件)。
MyISAM 存储引擎针对读取进行了优化。MyISAM 作为默认引擎已经有一段时间了，并且是第一批可用的存储引擎之一。事实上，服务器的很大一部分是专用于支持 MyISAM 的。它与 InnoDB 的不同之处在于，它不支持事务，并且以索引顺序访问方法格式存储数据。这意味着它支持快速索引。如果您不需要事务，并且希望能够移动或备份单个表，那么您应该选择 MyISAM 而不是 InnoDB。
您可能需要考虑的另一个存储引擎是归档，尤其是对于传感器网络。该引擎不支持删除(但是您可以删除整个表),并且针对磁盘上的最小存储进行了优化。很明显，如果你在一个像 Raspberry Pi 这样的小系统上运行 MySQL，小系统总是更好！无法删除数据可能会限制更高级的应用，但大多数传感器网络只是存储数据，很少删除数据。在这种情况下，您可以考虑使用归档存储引擎。
还有 CSV 存储引擎(其中 CSV 代表逗号分隔值)。此存储引擎创建文本文件，以纯文本形式存储数据，其他应用(如电子表格应用)可以读取这些数据。如果您将传感器数据用于统计分析，CSV 存储引擎可能会使获取数据的过程更容易。
那么，这些数据都在哪里呢？如果您查询 MySQL 服务器并发出命令SHOW VARIABLES LIKE "datadir";，您会看到所有存储引擎用来存储数据的磁盘位置的路径。对于 InnoDB，这是位于数据目录中的磁盘上的一个文件。InnoDB 也创建一些管理文件，但是数据存储在单个文件中。对于除了NDB和MEMORY之外的大多数其他存储引擎，表的数据存储在 data 目录下的一个以数据库名称命名的文件夹中。清单 5-3 展示了一个来自 Mac OS X 机器的例子。您可能需要在自己的机器上使用不同的路径。
Listing 5-3.Finding Where Your Data Is Located
mysql> SHOW VARIABLES LIKE 'datadir'
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.00 sec)
mysql> quit;
bye
$ sudo ls -lsa /usr/local/mysql/data
rwxr-x--- 58 _mysql wheel 1972 Feb 6 15:05 .
drwxr-xr-x 17 root wheel 578 Jan 20 16:38 ..
-rw-rw---- 1 _mysql wheel 0 Feb 6 15:04 Chucks-iMac.local.err
-rw-rw---- 1 _mysql wheel 5 Feb 6 15:00 Chucks-iMac.local.pid
drwx------ 6 _mysql wheel 204 Oct 17 15:16 bvm
-rw-rw---- 1 _mysql wheel 5242880 Feb 6 15:00 ib_logfile0
-rw-rw---- 1 _mysql wheel 5242880 Feb 6 15:00 ib_logfile1
-rw-rw---- 1 _mysql wheel 815792128 Feb 1 17:16 ibdata1
-rw-rw---- 1 _mysql wheel 52428800 Feb 1 17:16 ibdata2
drwxr-x--- 77 _mysql wheel 2618 Jan 8 15:24 mysql
drwx------ 38 _mysql wheel 1292 Nov 27 08:46 sakila
drwx------ 192 _mysql wheel 6528 Oct 22 12:17 test
drwx------ 6 _mysql wheel 204 Dec 18 17:05 world_innodb
$ sudo ls -lsa /usr/local/mysql/data/bvm
drwx------ 6 _mysql wheel 204 Oct 17 15:16 .
drwxr-x--- 58 _mysql wheel 1972 Feb 6 15:05 ..
-rw-rw---- 1 _mysql wheel 5056 Oct 17 15:24 books.MYD
-rw-rw---- 1 _mysql wheel 1024 Oct 17 15:25 books.MYI
-rw-rw---- 1 _mysql wheel 8780 Oct 17 15:16 books.frm
-rw-rw---- 1 _mysql wheel 65 Oct 17 15:15 db.opt
该示例首先向数据库服务器查询数据目录的位置(它位于该计算机上受保护的文件夹中)。如果您发出一个列表命令，您可以看到由前缀ib和ibd标识的 InnoDB 文件。您还可以看到许多目录，所有这些目录都是该服务器上的数据库。之后是一个数据库文件夹的列表。注意扩展名为.MY?的文件:这些是 MyISAM 文件(数据和索引)。.frm文件是服务器创建和维护的配置文件。
Tip
如果您想通过复制文件将数据从一个服务器复制到另一个服务器，请确保也复制了.frm文件！这对于 MyISAM 和 Archive 来说很容易，但对于 InnoDB 来说要困难得多。在 InnoDB 的情况下，你必须复制所有的数据库文件夹和 InnoDB 文件，以确保你得到所有的东西。
虽然在您的 IOT 解决方案中，您不太可能需要一个数据库节点的事务存储引擎，比如运行 MySQL Server 的 Raspberry Pi，但是 MySQL 5.6 有一个，并且默认情况下是打开的。更有可能的情况是对表使用 MyISAM 或归档引擎。
有关存储引擎及其选择和特性的更多信息，请参见在线 MySQL 参考手册“存储引擎”( http://dev.mysql.com/doc/ )一节。
MySQL 配置文件
MySQL 服务器可以使用与配置 Raspberry Pi 类似的配置文件进行配置。在 Windows 上，MySQL 配置文件位于安装文件夹中，命名为my.ini。在其他系统上，它位于/etc/mysql文件夹中，并被命名为my.cnf。该文件包含几个部分，其中一部分被标记为[mysqld]。此列表中的项目是键值对；等号左边的名称是选项，右边是它的值。以下是一个典型的配置文件(为简洁起见，省略了许多行):
[mysqld]
port = 3306
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
server_id = 5
general_log
如您所见，这是配置系统的简单方法。该示例设置 TCP 端口、基目录(MySQL 安装的根目录，包括数据以及二进制文件和辅助文件)、数据目录和服务器 ID(用于复制，稍后会讨论)，并打开常规日志(当包含布尔开关时，它会打开日志)。您可以为 MySQL 设置许多这样的变量。有关使用配置文件的详细信息，请参阅在线 MySQL 参考手册。当您在 Raspberry Pi 上设置 MySQL 时，您将更改这个文件。
如何在 Windows 上启动、停止和重启 MySQL
在计算机上使用数据库和配置 MySQL 时，您可能需要控制 MySQL 服务器的启动和关闭。安装 MySQL 的默认模式是在启动时自动启动，在关机时自动停止，但是您可能希望更改这一模式，或者您可能需要在更改参数后停止并启动服务器。此外，当您更改配置文件时，需要重新启动服务器才能看到更改的效果。
您可以使用通知程序托盘应用或通过 Windows 服务控制面板来启动、停止和重启 MySQL 服务器。只需选择 MySQL 服务，右键单击即可停止或启动该服务。这将执行一个受控的关机和启动，如果你需要这样做。
创建用户和授予访问权限
在使用 MySQL 之前，您需要了解另外两个管理操作:创建用户帐户和授予数据库访问权限。MySQL 可以用GRANT语句执行这两项操作，如果用户不存在，它会自动创建一个用户。但是更迂腐的方法是首先发出一个CREATE USER命令，然后是一个或多个GRANT命令。例如，下面显示了名为sensor1的用户的创建，并授予该用户对数据库room_temp的访问权限:
CREATE USER 'sensor1'@'%' IDENTIFIED BY 'secret'
GRANT SELECT, INSERT, UPDATE ON room_temp.* TO 'sensor1'@'%'
第一个命令创建名为sensor1的用户，但是该名称也有一个@后跟另一个字符串。第二个字符串是与用户相关联的机器的主机名。也就是说，MySQL 中的每个用户都有一个用户名和一个主机名，以user@host的形式来唯一地标识他们。这意味着用户和主机sensor1@10.0.1.16以及用户和主机sensor1@10.0.1.17是不同的。但是，%符号可以用作通配符，将用户与任何主机关联起来。IDENTIFIED BY子句为用户设置密码。
A Note About Security
为您的应用创建一个对 MySQL 系统没有完全访问权限的用户总是一个好主意。这是为了最大限度地减少任何意外更改，也是为了防止被利用。对于传感器网络，建议您创建一个只能访问存储(或检索)数据的数据库的用户。您可以使用以下命令更改 MySQL 用户密码:
SET PASSWORD FOR sensor1@"%" = PASSWORD("secret");
对于主机使用通配符%也要小心。虽然创建单个用户并让用户从任何主机访问数据库服务器变得更加容易，但这也使得恶意用户更容易访问您的服务器(一旦他们发现了密码)。
另一个考虑是连接性。与 Raspberry Pi 一样，如果您将一个数据库连接到您的网络，而该网络又连接到 Internet，那么您的网络或 Internet 上的其他用户就有可能访问该数据库。不要让他们轻易得逞——更改您的 root 用户密码，并为您的应用创建用户。
第二个命令允许访问数据库。您可以授予用户许多权限。该示例显示了您最有可能向传感器网络数据库用户提供的集合:读取(SELECT)、添加数据(INSERT)和更改数据(UPDATE)。有关安全性和帐户访问权限的更多信息，请参见在线参考手册。
该命令还指定要授予权限的数据库和对象。因此，可以给用户一些表的读(SELECT)权限，给另一些表的写(INSERT、UPDATE)权限。这个例子让用户可以访问room_temp数据库中的所有对象(表、视图等等)。
如上所述，您可以将这两个命令合并成一个命令。你可能会在文献中更经常地看到这种形式。下面显示了组合语法。在这种情况下，您需要做的就是将IDENTIFIED BY子句添加到GRANT语句中。酷！
GRANT SELECT, INSERT, UPDATE ON room_temp. * TO 'sensor1'@'%' IDENTIFIED BY 'secret'
常见的 MySQL 命令和概念
学习和掌握数据库系统需要训练、经验和极大的毅力。精通所需的主要知识是如何使用常见的 SQL 命令和概念。本节通过介绍最常见的 MySQL 命令和概念来完成 MySQL 入门。
Note
本节介绍高级命令和概念，而不是重复引用《参考手册》、 9 。如果您决定使用任何命令或概念，请参考在线参考手册，了解更多详细信息、完整的命令语法和其他示例。
MySQL 命令
本节回顾了最常见的 SQL 和 MySQL 特有的命令，您需要了解这些命令，以便充分利用您的 IOT 数据库。虽然您已经看到了其中的一些功能，但是本节提供了一些额外的信息来帮助您使用它们。
Note
用户提供的变量的大小写敏感性(例如，last_name对Last_Name)在不同的平台上是不一致的。例如，Windows 上的区分大小写行为与 Mac OS X 上的不同。MySQL 遵循该平台的区分大小写策略。查看您的平台的在线参考手册，了解区分大小写如何影响用户提供的变量。
创建数据库和表
您需要学习和掌握的最基本的命令是CREATE DATABASE和CREATE TABLE命令。回想一下，MySQL 之类的数据库服务器允许您创建任意数量的数据库，您可以用逻辑方式添加表和存储数据。
要创建一个数据库，使用CREATE DATABASE后跟一个数据库名称。如果您正在使用 MySQL 客户端，您必须使用USE命令切换到特定的数据库。客户端焦点是在启动时(在命令行上)或通过USE命令指定的最新数据库。您可以通过首先引用数据库名称来覆盖它。例如，SELECT * FROM db1.table1将执行，而不管默认的数据库设置。但是，省略数据库名称会导致 mysql 客户端使用默认数据库。下面显示了创建和更改数据库焦点的两个命令:
mysql> CREATE DATABASE plant_monitoring;
mysql> USE plant_monitoring;
Tip
回想一下，如果您想查看服务器上的所有数据库，可以使用SHOW DATABASES命令。
创建表格需要 yes，CREATE TABLE命令。该命令有许多选项，不仅允许您指定列及其数据类型，还允许您指定附加选项，如索引、外键等。还可以使用CREATE INDEX命令创建一个索引(参见下面的代码)。以下显示了如何创建一个简单的表格来存储工厂传感器数据。
CREATE TABLE plant_monitoring.plants (
``plant_name char(30) NOT NULL,
``sensor_value float DEFAULT NULL,
``sensor_event timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
``sensor_level char(5) DEFAULT NULL,
PRIMARY KEY plant_name (plant_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
注意这里我指定了表名(plants)和四列(plant_name、sensor_value、sensor_event和sensor_level)。我使用了几种数据类型。对于plant_name，我使用了一个最多包含 30 个字符的字符字段，一个浮点数据类型用于sensor_value，一个时间戳值用于sensor_event，另一个字符字段用于包含 5 个字符的sensor_level。
TIMESTAMP数据类型在 IOT 解决方案或任何您想要记录事件或行动的日期和时间的时候特别有用。例如，知道何时读取传感器值通常是有帮助的。通过向表中添加一个TIMESTAMP列，您不需要计算、读取或格式化传感器甚至聚合节点上的日期和时间。
还要注意，我指定将sensor_name列定义为一个键，这将创建一个索引。在这种情况下，它也是主键。PRIMARY KEY短语告诉服务器确保表中存在且只有一行匹配列的值。通过重复关键字，可以指定几个要在主键中使用的列。注意，所有主键列都不允许空值(NOT NULL)。
如果您不能确定唯一标识一行的一组列(并且您想要这样的行为——有些人喜欢没有这种限制的表，但是一个好的 DBA 不会),那么您可以为 integer 字段使用一个称为AUTO INCREMENT的人工数据类型选项。当用于某一列(必须是第一列)时，服务器会为插入的每一行自动增加该值。这样，它就创建了一个默认主键。有关自动递增列的更多信息，请参见联机参考手册。
Tip
最佳实践表明，在某些情况下，在字符字段上使用主键并不是最佳选择，例如表中的每一列都有很大的值或者有许多唯一值。这可能会降低搜索和索引的速度。在这种情况下，您可以使用 auto increment 字段来人工添加一个更小的主键(但有点神秘)。
可用的数据类型比上一个示例中显示的多得多。您应该查阅联机参考手册，以获得数据类型的完整列表。请参见“数据类型”一节如果你想知道一个表格的布局或“模式”,使用SHOW CREATE TABL E 命令。
像数据库一样，您也可以使用SHOW TABLES命令获得数据库中所有表的列表。
获得结果
您需要知道的最常用的基本命令是从表中返回数据的命令(也称为结果集或行)。为此，您可以使用SELECT语句。这个 SQL 语句是数据库系统的核心。所有对数据的查询都将使用该命令执行。 10
SELECT语句允许您指定想要从数据中选择哪些列。该列表作为语句的第一部分出现。第二部分是FROM子句，它指定了要从中检索行的表。
Note
FROM子句可以用来用JOIN操作符连接表。在后面的小节中，您将看到一个简单的连接示例。
指定列的顺序决定了结果集中的显示顺序。如果你想要所有的列，使用星号(*)代替。清单 5-4 展示了生成相同结果集的三条语句。也就是说，在每个的输出中将显示相同的行。事实上，为了简单起见，我使用了一个只有四行的表。
Listing 5-4.Example SELECT Statements
mysql> SELECT plant_name, sensor_value, sensor_event, sensor_level FROM plant_monitoring.plants;
+------------------------+--------------+---------------------+--------------+
| plant_name | sensor_value | sensor_event | sensor_level |
+------------------------+--------------+---------------------+--------------+
| fern in den | 0.2319 | 2015-09-23 21:04:35 | NULL |
| fern on deck | 0.43 | 2015-09-23 21:11:45 | NULL |
| flowers in bedroom1 | 0.301 | 2015-09-23 21:11:45 | NULL |
| weird plant in kitchen | 0.677 | 2015-09-23 21:11:45 | NULL |
+------------------------+--------------+---------------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM plant_monitoring.plants;
+------------------------+--------------+---------------------+--------------+
| plant_name | sensor_value | sensor_event | sensor_level |
+------------------------+--------------+---------------------+--------------+
| fern in den | 0.2319 | 2015-09-23 21:04:35 | NULL |
| fern on deck | 0.43 | 2015-09-23 21:11:45 | NULL |
| flowers in bedroom1 | 0.301 | 2015-09-23 21:11:45 | NULL |
| weird plant in kitchen | 0.677 | 2015-09-23 21:11:45 | NULL |
+------------------------+--------------+---------------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT sensor_value, plant_name, sensor_level, sensor_event FROM plant_monitoring.plants;
+--------------+------------------------+--------------+---------------------+
| sensor_value | plant_name | sensor_level | sensor_event |
+--------------+------------------------+--------------+---------------------+
| 0.2319 | fern in den | NULL | 2015-09-23 21:04:35 |
| 0.43 | fern on deck | NULL | 2015-09-23 21:11:45 |
| 0.301 | flowers in bedroom1 | NULL | 2015-09-23 21:11:45 |
| 0.677 | weird plant in kitchen | NULL | 2015-09-23 21:11:45 |
+--------------+------------------------+--------------+---------------------+
4 rows in set (0.00 sec)
请注意，前两条语句以相同的顺序生成相同的行和相同的列，但是第三条语句虽然生成相同的行，但以不同的顺序显示列。
您还可以使用列列表中的函数来执行计算和类似操作。一个特殊的例子是使用COUNT()函数来确定结果集中的行数，如下所示。关于 MySQL 提供的函数的更多例子，请参阅在线参考手册。
SELECT COUNT(*) FROM plant_monitoring.plants;
SELECT语句中的下一个子句是WHERE子句。您可以在这里指定用于限制结果集中行数的条件。也就是说，只有那些符合条件的行。这些条件基于列，可能相当复杂。也就是说，您可以基于计算、连接结果等来指定条件。但是为了回答一个问题，大多数条件将是一列或多列上的简单等式或不等式。例如，假设您想要查看传感器读数小于 0.40 的植物。在这种情况下，我们发出以下查询并接收结果。注意，我只指定了两列:工厂名称和从传感器读取的值。
mysql> SELECT plant_name, sensor_value FROM plant_monitoring.plants WHERE sensor_value < 0.40;
+---------------------+--------------+
| plant_name | sensor_value |
+---------------------+--------------+
| fern in den | 0.2319 |
| flowers in bedroom1 | 0.301 |
+---------------------+--------------+
2 rows in set (0.01 sec)
您还可以使用其他子句，包括用于对行进行分组以进行聚合或计数的GROUP BY子句，以及用于对结果集进行排序的ORDER BY子句。让我们从聚合开始，快速地看一下每一个。
假设您想要计算每个传感器在表中读取的传感器值的平均值。在这种情况下，我们有一个包含各种传感器随时间变化的传感器读数的表。虽然该示例只包含四行(因此可能没有统计信息)，但是该示例非常清楚地展示了聚合的概念，如清单 5-5 所示。请注意，我们收到的只是四个传感器读数的平均值。
Listing 5-5.GROUP BY Example
mysql> SELECT plant_name, sensor_value FROM plant_monitoring.plants WHERE plant_name = 'fern on deck'
+--------------+--------------+
| plant_name | sensor_value |
+--------------+--------------+
| fern on deck | 0.43 |
| fern on deck | 0.51 |
| fern on deck | 0.477 |
| fern on deck | 0.73 |
+--------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT plant_name, AVG(sensor_value) as avg_value FROM plant_monitoring.plants WHERE plant_name = 'fern on deck' GROUP BY plant_name;
+--------------+-------------------+
| plant_name | avg_value |
+--------------+-------------------+
| fern on deck | 0.536750003695488 |
+--------------+-------------------+
1 row in set (0.00 sec)
注意，我在列列表中指定了 average 函数AVG()，并传入了我想要求平均值的列的名称。MySQL 中有许多这样的函数可以用来执行一些强大的计算。显然，这是数据库服务器中存在多少功率的另一个示例，这将需要网络中典型的轻量级传感器或聚合器节点上的更多资源。
还要注意，我用关键字AS重命名了平均值列。您可以使用它来重命名任何指定的列，这将更改结果集中的名称，如清单所示。
子句的另一个用途是计数。在这种情况下，我们用COUNT()替换了AVG()，得到了与WHERE子句匹配的行数。更具体地说，我们想知道每个工厂存储了多少传感器值。
mysql> SELECT plant_name, COUNT(sensor_value) as num_values FROM plant_monitoring.plants GROUP BY plant_name;
+------------------------+------------+
| plant_name | num_values |
+------------------------+------------+
| fern in den | 1 |
| fern on deck | 4 |
| flowers in bedroom1 | 1 |
| weird plant in kitchen | 1 |
+------------------------+------------+
4 rows in set (0.00 sec)
现在，假设我们想要查看按传感器值排序的结果集的结果。我们将使用为面板上的蕨类植物选择行的相同查询，但是我们使用ORDER BY子句按照传感器值以升序和降序对行进行排序。清单 5-6 显示了每个选项的结果。
Listing 5-6.ORDER BY Examples
mysql> SELECT plant_name, sensor_value FROM plant_monitoring.plants WHERE plant_name = 'fern on deck' ORDER BY sensor_value ASC;
+--------------+--------------+
| plant_name | sensor_value |
+--------------+--------------+
| fern on deck | 0.43 |
| fern on deck | 0.477 |
| fern on deck | 0.51 |
| fern on deck | 0.73 |
+--------------+--------------+
4 rows in set (0.00 sec)
mysql> SELECT plant_name, sensor_value FROM plant_monitoring.plants WHERE plant_name = 'fern on deck' ORDER BY sensor_value DESC;
+--------------+--------------+
| plant_name | sensor_value |
+--------------+--------------+
| fern on deck | 0.73 |
| fern on deck | 0.51 |
| fern on deck | 0.477 |
| fern on deck | 0.43 |
+--------------+--------------+
4 rows in set (0.00 sec)
正如我提到的,SELECT陈述比这里显示的要多得多，但是我们在这里看到的会让你走得更远，尤其是在处理大多数中小型 IOT 解决方案的典型数据时。
添加数据
现在您已经创建了一个数据库和表，您将希望向表中加载或插入数据。您可以使用INSERT INTO语句来实现。这里我们指定表格和行的数据。下面是一个简单的例子:
INSERT INTO plant_monitoring.plants (plant_name, sensor_value) VALUES ('fern in den', 0.2319);
在这个例子中，我通过指定名称和值为我的一个工厂插入数据。你想知道其他的柱子呢？在这种情况下，其他列包括一个时间戳列，它将由数据库服务器填充。所有其他列(只有一列)将被设置为NULL，这意味着没有值可用、值缺失、值不为零或值为空。 11
请注意，我在该行的数据之前指定了列。当您希望插入的列数少于表中包含的列数时，这是必要的。更具体地说，关闭列列表意味着您必须为表中的所有列提供数据(或NULL)。此外，列出的列的顺序可以不同于它们在表中的定义顺序。关闭列列表将导致根据列数据在表中的显示方式对其进行排序。 12
您也可以使用逗号分隔的行值列表，使用相同的命令插入几行，如下所示:
INSERT INTO plant_monitoring.plants (plant_name, sensor_value) VALUES ('flowers in bedroom1', 0.301), ('weird plant in kitchen', 0.677), ('fern on deck', 0.430);
这里我用相同的命令插入了几行。请注意，这只是一种简化机制，除了自动提交之外，与发出单独的命令没有什么不同。 十三
更改数据
有时，您需要更改或更新数据。您可能需要更改一列或多列的值，替换多行的值，或者更正数字数据的格式甚至比例。为了更新数据，我们使用UPDATE命令。
您可以更新特定的列，更新一组列，对一列或多列执行计算，等等。我通常不太需要更改 IOT 解决方案中的数据，但有时在传感器读取代码出错或类似数据输入问题的情况下，这可能是必要的。
更有可能的是，您或您的用户想要重命名数据库中的对象。例如，假设我们确定甲板上的植物实际上不是蕨类植物，而是一种外来开花植物。 14 在本例中，我们希望将所有植物名称为“fern on deck”的行改为“flowers on deck”以下命令执行更改:
UPDATE plant_monitoring.plants SET plant_name = 'flowers on deck' WHERE plant_name = 'fern on deck'
注意这里的关键操作符是SET操作符。这告诉数据库为指定的列分配一个新值。您可以在命令中列出多个 set 操作。
注意，我在这里使用了一个WHERE子句来将UPDATE限制到一组特定的行。这就是你在SELECT语句中看到的同一个WHERE子句，它做同样的事情；它允许您指定限制受影响的行的条件。如果不使用WHERE子句，更新将应用于所有行。
Caution
别忘了WHERE条款！发出不带WHERE子句的UPDATE命令将影响表中的所有行！
删除数据
有时，您最终会得到需要删除的表中的数据。也许您使用了测试数据，并希望去除虚假的行，或者也许您希望压缩或清除您的表，或者希望消除不再适用的行。要删除行，使用DELETE FROM命令。
我们来看一个例子。假设您有一个正在开发的工厂监控解决方案，您发现您的一个传感器或传感器节点读取的值太低，原因是编码、布线或校准错误。在这种情况下，我们希望删除传感器值小于 0.20 的所有行。以下命令可以做到这一点:
DELETE FROM plants WHERE sensor_value < 0.20;
Caution
别忘了WHERE条款！发出不带WHERE子句的DELETE FROM命令将永久删除表中的所有行！
注意我在这里使用了一个WHERE子句。也就是说，一个条件语句来限制被操作的行数。您可以使用您想要的任何列或条件；只要确保你有正确的！我喜欢在SELECT语句中首先使用相同的WHERE子句。例如，我将首先发出下面的命令来检查我是否要删除我想要的行，并且只删除那些行。请注意，这是同一个WHERE子句。
SELECT * FROM plants WHERE sensor_value < 0.20;
MySQL 概念
除了前面显示的命令之外，您还可以考虑使用其他概念。虽然每个都有一个 SQL 命令，但我在这里将它们作为一个概念列出，因为它们比简单的对象创建或数据检索更复杂。
索引
创建表时不使用任何排序。也就是说，表是无序的。虽然 MySQL 每次都会以相同的顺序返回数据，但是没有隐含的(或可靠的)顺序，除非您创建一个索引。我这里所指的排序并不像你在排序时所想的那样(在SELECT语句的ORDER BY子句中是可能的)。
相反，索引是服务器在执行查询时用来读取数据的映射。例如，如果一个表上没有索引，并且希望选择某列中值大于某个值的所有行，则服务器必须读取所有行来查找所有匹配项。但是，如果我们在该列上添加了一个索引，服务器将只能读取那些符合标准的行。
我应该注意到有几种形式的索引。这里我指的是一个聚集索引，索引中列的值存储在索引中，允许服务器只读取索引，而不读取行来测试标准。
要创建索引，您可以在CREATE TABLE语句中指定索引，或者发出一个CREATE INDEX命令。下面是一个简单的例子:
CREATE INDEX plant_name ON plants (plant_name);
该命令在plant_name列上添加一个索引。观察这对表格的影响。
CREATE TABLE plants (
``plant_name char(30) DEFAULT NULL,
``sensor_value float DEFAULT NULL,
``sensor_event timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
``sensor_level char(5) DEFAULT NULL,
KEY plant_name (plant_name)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
像这样创建的索引不会影响表中行的唯一性，换句话说，确保存在且只有一行可以被特定列的特定值访问。我所指的是主键(或主索引)的概念，这是在创建表时使用的一个特殊选项，如前所述。
视图
视图是一个或多个表的结果的逻辑映射。它们可以像查询中的表一样被引用，这使它们成为创建数据子集的强大工具。您用CREATE VIEW创建一个视图，并给它起一个类似于表格的名字。下面显示了一个简单的例子，其中我们创建了一个测试视图来从表中读取值。在这种情况下，我们限制了视图的大小(行数)，但是您可以为视图使用各种各样的条件，包括组合来自不同表的数据。
CREATE VIEW test_plants AS SELECT * FROM plants LIMIT 5;
在中小型数据库解决方案中通常不会遇到视图，但是我将它们包括在内是为了在您决定进行额外的分析并希望将数据组织成更小的组以便于阅读时让您了解它们。
扳机
另一个高级概念(以及相关的 SQL 命令)是使用事件驱动的机制，当数据发生变化时会“触发”该机制。也就是说，您可以创建一组简短的 SQL 命令(过程),这些命令将在插入或更改数据时执行。
触发器将在几种事件或条件下执行。您可以在更新、插入或删除操作之前或之后设置触发器。触发器与单个表相关联，其主体是一个特殊的构造，允许您对受影响的行进行操作。下面是一个简单的例子:
DELIMITER //
CREATE TRIGGER set_level BEFORE INSERT ON plants FOR EACH ROW
BEGIN
IF NEW.sensor_value < 0.40 THEN
SET NEW.sensor_level = 'LOW'
ELSEIF NEW.sensor_value < 0.70 THEN
SET NEW.sensor_level = 'OK'
ELSE
SET NEW.sensor_level = 'HIGH'
END IF;
END //
DELIMITER ;
该触发器将在每次插入表之前执行。在复合语句(BEGIN中可以看到…END)，我们根据sensor_value的值将名为sensor_level的列设置为LOW、OK或HIGH。要了解这一点，请考虑下面的命令。FOR EACH ROW语法允许触发器作用于事务中的所有行。
INSERT INTO plants (plant_name, sensor_value) VALUES ('plant1', 0.5544);
由于我们提供的值小于中间值(0.70)，我们期望触发器为我们填充sensor_level列。下面显示了触发器触发时发生的情况:
+-------------+--------------+---------------------+--------------+
| plant_name | sensor_value | sensor_event | sensor_level |
+-------------+--------------+---------------------+--------------+
| plant1 | 0.5544 | 2015-09-23 20:00:15 | OK |
+-------------+--------------+---------------------+--------------+
1 row in set (0.00 sec)
这展示了一种有趣而强大的方法，可以利用数据库服务器的能力创建派生列，并节省传感器或聚合器节点的处理能力。我鼓励您考虑这个以及类似的强大概念，以利用数据库服务器的强大功能。
简单连接
数据库系统最强大的概念之一是在数据之间建立关系的能力(因此得名关系型)。也就是说，一个表中的数据可以引用另一个(或多个)表中的数据。最简单的形式称为主从关系，其中一个表中的一行引用或关联到另一个表中的一行或多行。
一个常见的(也是经典的)主从关系的例子来自订单跟踪系统，其中一个表包含订单的数据，另一个表包含订单的行项目。因此，我们只存储一次订单信息，如客户号和发货信息，并在检索订单时合并或“连接”这些表。
让我们看一个来自示例数据库的名为world的例子。你可以在 MySQL 网站上找到这个数据库( http://dev.mysql.com/doc/index-other.html )。请随意下载它和任何其他示例数据库。它们都展示了数据库系统的各种设计。您还会发现练习查询数据很方便，因为它包含了许多简单的行。
Note
如果要运行以下示例，需要安装示例文档中描述的世界数据库( http://dev.mysql.com/doc/world-setup/en/world-setup-installation.html )。
清单 5-7 展示了一个简单连接的例子。这里发生了很多事情，所以花点时间检查一下SELECT语句的各个部分，尤其是我是如何指定JOIN子句的。您可以忽略LIMIT选项，因为它只是限制了结果集中的行数。
Listing 5-7.Simple JOIN Example
mysql> SELECT Name, Continent, Language FROM Country JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode LIMIT 10;
+-------------+---------------+------------+
| Name | Continent | Language |
+-------------+---------------+------------+
| Aruba | North America | Dutch |
| Aruba | North America | English |
| Aruba | North America | Papiamento |
| Aruba | North America | Spanish |
| Afghanistan | Asia | Balochi |
| Afghanistan | Asia | Dari |
| Afghanistan | Asia | Pashto |
| Afghanistan | Asia | Turkmenian |
| Afghanistan | Asia | Uzbek |
| Angola | Africa | Ambo |
+-------------+---------------+------------+
10 rows in set (0.00 sec)
这里我使用了一个JOIN子句，它接受两个指定的表，这样第一个表使用特定的列及其值连接到第二个表(ON指定匹配)。数据库服务器所做的是从表中读取每一行，并只返回那些列中的值指定匹配的行。一个表中不在另一个表中的任何行都不会被返回。
Tip
但是您可以检索那些具有不同连接的行。有关更多详细信息，请参见内部和外部连接的在线参考手册。
还要注意，我只包括了几个专栏。在本例中，我从Country表中指定了国家名称和大陆，从CountryLanguage表中指定了语言列。如果列名不是惟一的(相同的列出现在每个表中)，我就必须用表名来指定它们，比如Country.Name。事实上，总是以这种方式限定列被认为是一种好的做法。
这个例子中有一个有趣的异常，我觉得有必要指出来。事实上，有些人会认为这是一个设计缺陷。注意，在JOIN子句中，我指定了表格和每个表格的列。这是正常且正确的，但是请注意，两个表中的列名并不匹配。虽然这真的没有关系，并且只需要一点额外的输入，但是一些 DBA 会认为这是错误的，并且希望在两个表中使用相同的公共列名。
连接的另一个用途是检索公共数据、存档数据或查找数据。例如，假设您有一个表，其中存储了不变(或很少变)的事物的详细信息，如与邮政编码相关联的城市或与标识号相关联的名称(例如，SSN)。您可以将这些信息存储在一个单独的表中，并在需要时将数据连接到一个公共列(和值)上。在这种情况下，公共列可以用作外键，这是另一个高级概念。
外键用于维护数据完整性(也就是说，如果一个表中的数据与另一个表相关，但这种关系需要保持一致)。例如，如果您想确保在删除主行时所有的细节行也被删除，您可以在主表中声明一个外键，指向细节表的一列(或多列)。有关外键的更多信息，请参见联机参考手册。
关于连接的讨论只涉及最基本的内容。事实上，连接可以说是数据库系统中最困难和最容易混淆的领域之一。如果您发现您想要使用联接来组合几个表或扩展数据，以便从几个表提供数据(外部联接)，您应该花一些时间来深入研究数据库概念，如 Clare Churcher 的书《数据库设计入门》(Apress，2012)。
其他高级概念
MySQL 中有更多可用的概念和命令，但有两个可能会引起人们的兴趣，那就是PROCEDURE和FUNCTION，它们有时被称为例程。我在这里介绍这些概念，以便如果您想探索它们，您可以理解它们是如何在高层次上使用的。
假设您需要运行几个命令来更改数据。也就是你需要在计算的基础上做一些复杂的改变。对于这些类型的操作，MySQL 提供了存储过程的概念。存储过程允许您在调用该过程时执行复合语句(一系列 SQL 命令)。存储过程有时被认为是一种主要用于定期维护的高级技术，但它们在更简单的情况下也很方便。
例如，假设您想要开发您的 IOT 解决方案，但是由于您正在开发它，您需要定期重新开始，并且想要首先清除所有数据。如果只有一个表，存储过程不会有太大帮助，但是假设有几个表分布在几个数据库中(对于较大的 IOT 解决方案来说并不少见)。在这种情况下，存储过程可能会有所帮助。
Tip
在 MySQL 客户端中输入带有复合语句的命令时，您需要临时更改分隔符(分号),以便行尾的分号不会终止命令条目。例如，在用复合语句编写命令之前使用DELIMITER //，使用//结束命令，用DELIMITER ;将分隔符改回来。这仅在使用客户端时。
由于存储过程可能相当复杂，如果您决定使用它们，在尝试开发自己的存储过程之前，请阅读联机参考手册的“创建过程和创建函数语法”一节。创建存储过程的内容远不止这一部分。
现在假设您想执行一个复合语句并返回一个结果—您想将它用作一个函数。您可以使用函数通过执行计算、数据转换或简单的翻译来填充数据。因此，函数可用于提供值来填充列值、提供聚合、提供日期操作等等。
您已经看到了几个函数(COUNT、AVG)。这些被认为是内置函数，在线参考手册中有一整节专门介绍它们。但是，您也可以创建自己的函数。例如，您可能希望创建一个函数来对您的数据执行一些数据规范化。更具体地说，假设您有一个传感器，它产生一个特定范围内的值，但是根据该值和来自不同传感器或查找表的另一个值，您想要对该值进行加、减、平均等操作来校正它。您可以编写一个函数来实现这一点，并在触发器中调用它来填充计算列的值。
Tip
对计算值使用新列，以便保留原始值。
What About Changing Objects?
您可能想知道当您需要修改表、过程、触发器等时该怎么做。放心吧，你不必从头开始！MySQL 为每个对象提供了一个ALTER命令。也就是说，有一个ALTER TABLE、ALTER PROCEDURE等等。关于每个ALTER命令的更多信息，参见在线参考手册“数据定义语句”一节。
规划 IOT 数据的数据库存储
既然您已经知道了如何获取、安装和使用 MySQL，那么是时候关注如何应用您在前面章节中学到的知识，并为存储 IOT 数据建立一个数据库了。召回 IOT 数据可以是任何形式的传感器数据、个人信息、代码、事件日期、设备标识等。
我以举例的方式提出这个主题。更具体地说，我觉得最好是演示数据库设计，而不是通过花言巧语来规定实践和政策。我认为这些例子展示了许多您在为您的 IOT 解决方案设计自己的数据库时可能会遇到的概念和构造。让我们深入了解工厂监控解决方案的完整设计。
由于我在前几节中使用了这些表的一些原语，下面的内容可能看起来很熟悉。但是，本节包含一个完全开发的数据库设计。这只是我可能用过的一种设计。如果您考虑实施自己的工厂监控解决方案，我建议您考虑替代方案。
Correct Database Design: Am I Doing This Right?
设计数据库没有任何错误的方法。虽然有些 DBA 会对这种说法感到畏缩，但是只要您能够以合理的性能实现所有的目标，并且不会丢失数据，您就应该认为您的设计是可行的。毕竟，即使是最复杂和专业设计的数据库也会经历常规的和进化的变化。也就是说，你不必一开始就做对。您可以随时调整您的数据库，以满足您不断增长和成熟的 IOT 解决方案需求。
示例 1:工厂监控系统
让我们探索一种 IOT 解决方案，用于监控植物的环境温度和土壤湿度。在这种情况下，解决方案被设计为支持任意数量的工厂(目标数据对象)。这种 IOT 解决方案的一个关键组成部分是，所有收集的数据都存储在所使用的传感器中，大约每小时读取一次。
在下面的例子中，我向您展示了设计数据库时我喜欢使用的四个基本步骤。你可能会发现其他哲学有更多的步骤和更严格的过程(这很好)，但是对于爱好者和业余爱好者，我推荐使用这些简化的步骤。这并不意味着你不能设计任何其他方式，只是这种方法应该为大多数工作。事实上，如果你有设计数据库的经验，你应该会看到与你自己的方法的相似之处。
步骤 1:描述数据
设计数据库时，您应该做的第一件事是尽可能完整地描述数据。你应该用英语来描述数据，甚至可以把它写在一张纸上。这样做有助于您从概念上定义数据的样子，以便您可以确定要存储多少个对象，它们由什么组成，以及如何组织它们。
植物监控系统应该存储关于植物的信息。具体来说，我们希望存储告诉我们植物何时需要浇水以及浇水频率的信息。了解植物的名称、位置以及是在室内还是室外也很重要。因此，我们需要存储的数据包括植物名称、位置、室内室外、土壤湿度传感器值、温度传感器值和传感器读数时间。我们还决定要量化土壤湿度的值，以便于编写应用来检测植物何时需要浇水。事实上，我们甚至可以在未来增加自动浇水功能！
因为植物名称总是相同的，所以我们不需要多次存储该信息。因此，我们将创建一个表来存储关于我们正在监控的工厂的信息，并创建另一个表来存储传感器读数。这样，如果你需要改变一个植物的名字(就像我们之前看到的)或者你想改变它的位置，你只需要在一个地方改变它。因为我们将传感器读数存储在不同的表中，所以我们必须选择一个列来连接这些表。因为我们没有为每个工厂分配任何合理的数值，所以我们可以使用自动增量特性来添加一个唯一的键(在本例中是主键)。
步骤 2:设计数据库对象
现在让我们看看这些表是什么样子的。我们将主表(存储工厂信息的表)命名为plants，将明细表(传感器读数)命名为readings。我们将这些表放在名为plant_monitoring的数据库中。清单 5-8 显示了数据库中每个表的布局或模式。
Listing 5-8.Plant-Monitoring Schema
-- A database for storing plant soil moisture and ambient temperature
CREATE DATABASE plant_monitoring;
USE plant_monitoring;
-- This table stores information about a plant.
CREATE TABLE plants (
``id int(11) NOT NULL AUTO_INCREMENT,
``name char(50) DEFAULT NULL,
``location char(30) DEFAULT NULL,
``climate enum ('inside','outside') DEFAULT 'inside',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-- This table stores values read from sensors. The sensors are read
-- periodically by a sensor node and the values stored as a single row
-- with the date and time added by the database server.
CREATE TABLE readings (
``id int(11) NOT NULL,
``moisture float DEFAULT NULL,
``temperature float DEFAULT NULL,
``event_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
``soil_status enum ('DRY', 'OK', 'WET') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DELIMITER //
CREATE TRIGGER set_status BEFORE INSERT ON readings FOR EACH ROW
BEGIN
IF NEW.moisture < 250 THEN
SET NEW.soil_status = 1;
ELSEIF NEW.moisture < 400 THEN
SET NEW.soil_status = 2;
ELSE
SET NEW.soil_status = 3;
END IF;
END //
DELIMITER ;
注意readings表上定义的触发器。回想一下上一节，触发器可以用来为计算列提供数据，这就是我们想要存储在soil_status列中的数据。在这种情况下，我们将干土壤的低阈值设为 250，湿土壤的低阈值设为 400 以上。因此，在 250 和 399 之间，植物土壤被认为是正常的(Ok)。
另外，请注意 SQL 代码中包含的注释。双破折号是 MySQL 客户端会忽略的注释。虽然这个示例数据库相当简单，但添加对数据库对象(表等)的简短描述也无妨，这样您就可以在忘记时记住每个对象的作用，或者作为其他人使用数据库或您的 IOT 解决方案(如定制应用)。
还要注意plants表中的location列。这是一个使用枚举值的例子，这样您就不必为添加到表中的每个工厂重复输入字符串。值从 1 开始，因此您可以为内部指定 1，为外部指定 2，如下所示:
mysql> INSERT INTO plants VALUES(NULL, 'Jerusalem Cherry', 'deck', 2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from plants;
+----+---------------------+----------+---------+
| id | name | location | climate |
+----+---------------------+----------+---------+
| 1 | Jerusalem Cherry | deck | outside |
+----+---------------------+----------+---------+
1 row in set (0.00 sec)
Note
我尽可能为这些列选择描述性的名称。我还加入了一些模糊的东西。你能说出哪些可以用一个更好的名字吗？提示:气候对你意味着什么？显然，为列选择有效的、有意义的名称是一项挑战，也是一种艺术形式。
最后，请注意，虽然我向plants表添加了一个主键，在本例中为AUTO_INCRMENT数据类型，但是我没有向readings表添加一个主键。这样我们可以在readings表中存储任意数量的行，并且数据完全有可能不是唯一的。更准确地说，从传感器读取的值可能与两个或多个读数相同。因此，我在readings表的定义中忽略了唯一性因素。
我还应该注意使用AUTO_INCREMENT数据类型来惟一标识行的结果。虽然从概念上讲，在plants表中的同一位置不能有多于一种植物被命名为相同的植物(即使有三种蕨类植物，你很可能会给它们起不同的名字)，但事实上AUTO_INCREMENT是一种人工的唯一性机制，这意味着你很可能两次输入相同的数据，从而产生不同的AUTO_INCREMENT值。因此，在与AUTO_INCREMENT合作时，谨慎一点是明智的。
在设计表格时，有两个方便的工具可以使用。首先，您可以使用SHOW CREATE TABLE命令查看实际的 SQL 命令来重新创建表。事实上，SHOW CREATE可以用于任何物体，如SHOW CREATE TRIGGER。其次，您可以使用EXPLAIN命令，如清单 5-9 所示。
Listing 5-9.Using EXPLAIN
mysql> explain plants;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
| location | char(30) | YES | | NULL | |
| plant_type | char(30) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> explain readings;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | | NULL | |
| moisture | float | YES | | NULL | |
| temperature | float | YES | | NULL | |
| event_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| soil_status | enum('DRY', | YES | | NULL | |
| | ‘好’，‘湿’)| | | | |
+-------------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
这里您可以看到一个结果集，它显示了所有列及其数据类型，以及plant monitoring数据库中每个表的选项。注意我们为时间戳字段获得的额外信息。这里它告诉我们，当行被更新时，时间戳将被更新，但它也适用于新行。
但是等等！每个传感器如何知道每个植物的 ID 是什么？嗯，这有点本末倒置。如果您认为每个传感器都可以通过一个小的微控制器板读取和发送数据，那么代码(Arduino 行话中的草图)必须知道来自plants表的 ID。因为我们在表中使用了一个AUTO_INCREMENT列，所以我们必须首先插入关于工厂的数据，然后查询表中的 ID。下面显示了一个示例:
mysql> INSERT INTO plant_monitoring.plants VALUES (NULL, 'fern', 'beside picnic table', 2);
Query OK, 1 row affected (0.01 sec)
mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
| 9 |
+------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM plant_monitoring.plants WHERE id = 9;
+----+------+---------------------+---------+
| id | name | location | climate |
+----+------+---------------------+---------+
| 9 | fern | beside picnic table | outside |
+----+------+---------------------+---------+
1 row in set (0.00 sec)
但是请注意，我实际上并不需要查询plants表。相反，我使用了LAST_INSERT_ID()函数，它返回最后生成的AUTO_INCREMENT值。酷！
一旦我们有了这些信息，我们就可以对传感器读取器进行编程，将这些信息发送到数据库，以填充读数表，从而建立与植物表的关系。这意味着当我们查询特定工厂的所有传感器读数时，join 将返回正确的信息。因此，如果我想用id = 9存储关于工厂的数据，我将使用如下的INSERT语句:
INSERT INTO plant_monitoring.readings VALUES (9, 233, 38.4, NULL, NULL);
Database Design First? What About The Application?
我曾经为一个认为应用和用户界面比数据库设计更重要的组织工作过。我想这就是为什么他们最终雇佣了更多的 DBA 而不是开发者。在应用的开发过程中，他们不得不多次重新设计数据库，这导致了交付的重大延迟和一些有趣而严重的错误。
在定义了高级需求之后、实现源代码之前，一定要设计好数据库，包括您想问的问题。这样，您的应用将基于具有良好定义的查询的全功能数据库系统，而不是努力使查询适合某些代码实现。
现在让我们看看如何从数据库中获取信息。
步骤 3:为数据库设计查询或问题
每当设计一个数据库时，我都把它作为一个要点，如果不是一个必需的里程碑的话，来确定我想对数据运行什么查询(换句话说，我想问的问题和我期望的答案)。这些问题是为向用户呈现信息而设计的应用会遇到的问题。请这样想:如果您有一个工厂监控系统，您希望在用户界面上看到什么功能或结果？在设计应用之前定义这些将使应用开发进行得更加顺利。
我们可能希望对工厂监控解决方案执行的查询包括但不限于以下内容。正如您将看到的，其中一些简单易行，而另一些可能需要更复杂的SELECT语句才能实现。我将在接下来的小节中演示其中的一些，剩下的留给您自己去探索。
- 哪些植物需要浇水？
- 哪些植物正在经历最高的温度？
- 每天每株植物的平均土壤湿度是多少？
- 特定植物在白天能忍受的温度范围是多少？
- 有多少工厂受到监控？
- 外面有多少植物？
- 工厂内部或外部的平均温度是多少？
下面几节将向您介绍如何为其中的三个问题创建查询。我首先给出没有验证的查询，然后将通过相同的逻辑过程向您展示如何用已知的测试数据测试每个查询。然而，对于一些更复杂的查询，我将向您展示它们是如何在上下文中工作的，以便您可以看到各个部分是如何组合在一起的。
示例 1:一个简单的查询
让我们从一个简单的查询开始。这一个包括做一些聚集。回想一下我们想问的一个问题是，“植物内部或外部的平均温度是多少？”在这种情况下，让我们简单一点，只查询室外的植物。正如您将看到的，更改查询来查找内部植物是很简单的。
像所有好的查询设计一样，我们从分解问题开始。对于这个查询，我们需要知道每株植物的平均温度。回想一下，我们需要在AVG()函数中使用一个GROUP BY子句。但是，如果我们希望将行限制为只显示某一天(比如今天)的读数，该怎么办呢？也就是说，我们的应用可能会提供每家工厂当天平均温度的周期性状态。我们可以在一个时间范围内查询数据，但这更多的是一种分析操作——并不是说你不能这样做。为了简单起见，我们将使用今天的阅读材料。
那么，如何查询这类信息呢？显然，我们需要使用某种形式的日期检查。幸运的是，MySQL 提供了许多这样的功能。为此，我们将使用DATE()和CURRENT_DATE()函数。它们执行使用event_time TIMESTAMP列确定今天采集的样本的操作。数据库的一个非常酷和强大的特性是，如果我们使用这些函数，我们可以创建查询、视图、函数等等，自动确定今天记录了哪些行。因此，当使用这些函数进行查询时，我们不需要读取、存储和传输日期。酷！
下面的WHERE子句演示了如何做到这一点。注意，我们将event_time列传递给函数，然后由函数确定日期。我们将其与当前日期进行比较，从而只找到传感器读数是在今天获得的那些行。
WHERE DATE(event_time) = CURRENT_DATE()
现在我们只需要做平均。我们之前已经看过一个例子，所以下面的 SQL 命令应该看起来很熟悉——至少在概念上是这样的:
SELECT id, AVG(temperature) as avg_temp FROM readings WHERE DATE(event_time) = CURRENT_DATE() GROUP BY id;
这将为我们提供平均温度和今天读取的每株植物的 ID。剩下的工作就是将这些信息与plants表中的信息相结合，以获得名称、位置和平均温度。以下 SQL 语句显示了如何做到这一点:
SELECT name, location, AVG(temperature) as avg_temp
FROM plants JOIN readings ON plants.id = readings.id
WHERE DATE(event_time) = CURRENT_DATE()
GROUP BY plants.id;
哇，对于这样一个简单的查询，这里发生了很多事情！为了便于阅读，我把每一个子句放在单独的一行上。让我们逐一回顾一下。
首先，我们看到选择的列是工厂name和location以及平均值temperature。回想一下GROUP BY子句是控制向函数提供哪些行的。在这种情况下，我们按工厂 ID 分组。注意，FROM子句执行了plants表和readings表之间的连接。这样我们就可以从plants表中获得工厂信息，但对与plants表相匹配的readings表中的数据进行计算(平均温度)。因此，一个简单的连接！最后，我们使用前面描述的WHERE子句将数据限制为今天采集的样本。
但我们还没完。我们还没有完全回答这个问题。也就是说，我们还想知道室外设备的平均温度。我们需要做的就是将该条件添加到WHERE子句中，如下所示:
SELECT name, location, AVG(temperature) as avg_temp
FROM plants JOIN readings ON plants.id = readings.id
WHERE DATE(event_time) = CURRENT_DATE() AND plants.climate = 2
GROUP BY plants.id;
现在，我们完成了！要查询内部工厂，只需将climate值更改为 1。回想一下，这是climate列的枚举数据类型的值。
现在让我们看一个更复杂的查询。乍一看，这看起来很简单，但是你会看到它有很多层。
Note
下面只是如何形成查询的一个例子。还有几个其他的，一些更好的，但是我想展示你可以用来解决这个问题的逻辑思维过程。请随意试验和改进下面的例子。
示例 2:复杂的查询
现在让我们看一个更复杂的查询。或者更确切地说，是一种形成简单但实施起来并非微不足道的方法。在这种情况下，考虑查询“哪些植物需要浇水？”为此，我们需要知道哪些植物的水分值低于我们的干/好/湿阈值。因为我们使用计算列，所以我们不必看到实际的传感器值。也就是说，我们可以查询readings表，发现哪些被标记为 DRY。
您可能认为我们只需要查询readings表，以获得今天传感器读数的 DRY 值。这将让你接近，但它可能会导致一些行，但如果你或自动植物浇水系统浇水，或者如果下雨怎么办？你可能只有几个干燥的读数，但有更多的正常或潮湿的读数。在这种情况下，植物可能根本不需要浇水。
您也可以考虑为每个工厂选择最新的传感器读数。事实上，这是大多数人都会做的事情。但这并不包括传感器读取的数值处于边界或产生虚假读数的情况。例如，业余水平的土壤湿度传感器(换句话说，负担得起的传感器)不是 100%准确，可能会产生轻微的不一致的读数。只要你明白你应该回顾几个读数，或者取平均值，或者考虑一个范围的读数相对于另一个范围的读数的百分比，它们对于一个“广泛”的读数来说是非常好的。
我们需要的是一种方法来确定那些比其他值更频繁的植物干旱读数，这需要一点数学知识。你认为这很简单。如果你把它分解成更小的部分，这就是为什么我选择这个例子。让我们把它分成几个部分。我们从我们想要的最基本的陈述开始。
更具体地说，我们想知道所有植物当天的土壤状态。我们将计算soil_status的值，并根据当天的总读数确定发生的百分比。我们可以利用这些信息来决定哪些植物需要浇水。
为了做到这一点，我们将在 MySQL 中使用一个叫做视图的概念。Recall 视图是结果集的逻辑表示，可以像其他SELECT语句中的表一样对待。下图显示了检索当天土壤状态的视图:
CREATE VIEW soil_status_today AS
SELECT id, soil_status, count(soil_status) as num_events FROM plant_monitoring.readings
WHERE DATE(event_time) = CURRENT_DATE() GROUP BY id, soil_status;
还要注意，我使用了一个GROUP BY子句来聚合值，计算每个状态值的数量。让我们来看一个示例结果。注意，我像查询表一样查询视图。
mysql> SELECT * FROM plant_monitoring.soil_status_today;
+----+-------------+------------+
| id | soil_status | num_events |
+----+-------------+------------+
| 1 | DRY | 10 |
| 2 | OK | 10 |
| 3 | DRY | 4 |
| 3 | OK | 4 |
| 3 | WET | 2 |
| 4 | OK | 6 |
| 4 | WET | 4 |
| 5 | OK | 10 |
+----+-------------+------------+
8 rows in set (0.01 sec)
所以，这告诉我们，今天，植物 IDs 1 和 3 是干的。但我们还没完！考虑到一天中采集了多个样本。一些植物可能处于稍微干燥的阈值，但没有干燥到传感器读数一致的程度。所以，我们希望植物持续干旱，干旱事件比正常或潮湿多。我们可以使用另一个视图来获取这些信息，但是让我们看看如何使用一个存储函数来实现这一点。下面创建了一个函数，该函数返回给定工厂今天收集的最大样本数:
DELiMITER //
CREATE FUNCTION plant_monitoring.max_samples_today (in_id int)
RETURNS int DETERMINISTIC READS SQL DATA
BEGIN
DECLARE num_samples int;
SELECT COUNT(*) into num_samples FROM plant_monitoring.readings
WHERE DATE(event_time) = CURRENT_DATE() AND readings.id = in_id;
RETURN num_samples;
END //
DELIMITER ;
让我们看看这个函数是如何工作的。让我们使用视图创建一个查询，并计算每个植物的每个值的出现百分比。下面的SELECT语句用一点数学知识完成了这个任务。我包括这些行是为了向您展示它是有效的。
mysql> SELECT *, max_samples_today(id) as max_samples, (num_events/max_samples_today(id)) as percent_occurrence FROM plant_monitoring.soil_status_today;
+----+-------------+------------+-------------+--------------------+
| id | soil_status | num_events | max_samples | percent_occurrence |
+----+-------------+------------+-------------+--------------------+
| 1 | DRY | 10 | 10 | 1.0000 |
| 2 | OK | 10 | 10 | 1.0000 |
| 3 | DRY | 4 | 10 | 0.4000 |
| 3 | OK | 4 | 10 | 0.4000 |
| 3 | WET | 2 | 10 | 0.2000 |
| 4 | OK | 6 | 10 | 0.6000 |
| 4 | WET | 4 | 10 | 0.4000 |
| 5 | OK | 10 | 10 | 1.0000 |
+----+-------------+------------+-------------+--------------------+
8 rows in set (0.01 sec)
在这种情况下，我今天正好有十个传感器读数。这是因为我的测试数据(我将在后面的小节中展示)是固定的。当我在家里的实时植物监控解决方案上运行时，我的传感器读数平均每小时一个，每天 18 到 24 个。
注意，我添加了函数调用来获取最大样本数(读数),然后添加了另一列来计算总出现次数的百分比。但是这是很大的工作量。既然我们知道了如何在查询中进行数学运算，我们就可以轻松一点了。
我们现在需要做的就是添加一个百分比检查，比方说超过 50 %,并将行限制在那些带有soil_statusDRY 的行。因此，我们采用前面的查询，并添加几个条件。我们还会将结果中的列限制为仅包含id。以下查询显示了一个示例SELECT语句，用于确定哪些读数指示哪棵或哪些植物今天需要浇水:
mysql> SELECT id FROM soil_status_today WHERE ((num_events/max_samples_today(id)) > 0.50) AND soil_status = 1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
我们快到了。同样，我们将使用前面的查询并连接到plant表来确定哪些工厂需要监控。
SELECT name, location FROM plants JOIN (SELECT id FROM soil_status_today WHERE ((num_events/max_samples_today(id)) > 0.50) AND soil_status = 1) as sub_query on plants.id = sub_query.id;
现在我们知道了今天需要浇水的植物。请注意，我们只是简单地使用了如何从最基本的开始向外查找数据的逻辑分解(就像缩小我们的结果一样)。正如您所看到的，这个查询并没有那么难，因为我们创建了一个函数和一个视图来帮助我们，所以编写类似于这个查询的查询会容易得多。
步骤 4:测试数据库
既然我们已经设计、实现了数据库，并决定了查询(或者至少是我们能想到的所有查询)，我们就可以开始构建传感器网络并编写应用了，对吗？没有。我们需要测试这些查询，不仅要确保它们能够工作(也就是说，具有正确的语法)，还要确保我们得到了我们期望的结果。这需要有一个已知的数据集来处理。如果您发现在您的应用像病毒一样传播之后，有一个查询没有返回正确的数据，这将对您没有什么好处。
此时，数据可能不完全准确，也不需要实际的实时数据。您可以使用虚构的数据，只要您制作的数据代表表中每列的值范围。也就是说，确保您知道传感器可以读取的最小值和最大值。清单 5-10 显示了我为测试工厂监控解决方案的查询而创建的一些样本数据。
Listing 5-10.Sample Data
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Jerusalem Cherry', 'deck', 2);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Moses in the Cradle', 'patio', 2);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Peace Lilly', 'porch', 1);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'Thanksgiving Cactus', 'porch', 1);
INSERT INTO plant_monitoring.plants VALUES (NULL, 'African Violet', 'porch', 1);
INSERT INTO plant_monitoring.readings VALUES (1, 235, 39.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 235, 38.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 230, 38.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 230, 39.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 215, 39.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 215, 39.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 225, 39.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 220, 38.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 222, 38.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (1, 218, 37.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 355, 38.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 350, 38.6, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 366, 38.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 378, 38.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 361, 38.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 348, 37.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 343, 39.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 342, 38.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 358, 36.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (2, 377, 36.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 155, 33.6, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 150, 33.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 166, 33.6, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 278, 32.3, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 261, 31.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 248, 32.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 313, 33.6, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 342, 32.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 458, 31.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (3, 470, 33.4, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 333, 33.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 345, 33.6, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 360, 34.4, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 380, 34.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 395, 33.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 385, 33.4, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 425, 32.3, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 420, 31.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 422, 33.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (4, 418, 32.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 335, 39.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 335, 38.7, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 330, 38.8, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 330, 39.1, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 315, 39.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 315, 39.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 325, 39.2, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 320, 38.9, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 322, 38.5, NULL, NULL);
INSERT INTO plant_monitoring.readings VALUES (5, 318, 37.1, NULL, NULL);
注意，我在最后两列中使用了NULL。这是我做 DBA 时养成的习惯，我总是为表中的每个字段提供一个值。你不必那样做。事实上，只使用您知道的值来指定 SQL 命令也是正确的，但是请记住使用列列表，如下所示:
INSERT INTO plant_monitoring.readings (id, moisture, temperature) VALUES (7,418,32.5);
作为一个简单的测试，让我们做一个简单的连接来找到特定植物的所有读数。例如，我们问这样一个问题，“甲板上植物的湿度读数是多少？”以下查询返回结果:
mysql> SELECT name, location, moisture FROM plants JOIN readings ON plants.id = readings.id WHERE location LIKE '%deck%'
+------+----------+----------+
| name | location | moisture |
+------+----------+----------+
| fern | deck | 235 |
| fern | deck | 235 |
| fern | deck | 230 |
| fern | deck | 230 |
| fern | deck | 215 |
| fern | deck | 215 |
| fern | deck | 225 |
| fern | deck | 220 |
| fern | deck | 222 |
| fern | deck | 218 |
+------+----------+----------+
10 rows in set (0.00 sec)
注意，我在WHERE子句中使用了一个LIKE函数。我用它是因为我不确定甲板上是否有不止一种植物。例如，可能有一种植物的位置是“桌子旁边的甲板”或“在树下的甲板上”在每一侧使用带有通配符%的LIKE，将返回 location 列值中包含“deck”的所有行。酷！花些时间看看清单 5-10 中的样本数据，以确保你看到的是正确的数据。
现在我们有了一些样本数据，让我们看看示例查询的结果。我按照解释上一个查询的顺序遍历结果。
测试示例 1
回想一下这个查询，我们只需要室外植物的平均温度。当我测试一个查询时，我喜欢把它分解成最简单的部分——就像我开发它时一样。这样，我可以验证我得到了每个部分的正确结果。让我们再看一下那个查询。
SELECT name, location, AVG(temperature) as avg_temp
FROM plants JOIN readings ON plants.id = readings.id
WHERE DATE(event_time) = CURRENT_DATE() AND plants.climate = 2
GROUP BY plants.id;
让我们从最基本的数据开始——生活在室外的植物的名称和位置。
Note
我将按部分输入查询，这样您可以更好地阅读结果。如您所见，这种方式在 MySQL 客户端中读取要容易得多，客户端会“等待”直到您键入分号来执行查询。
mysql> SELECT * FROM plants
-> WHERE climate = 2;
+----+---------------------+----------+---------+
| id | name | location | climate |
+----+---------------------+----------+---------+
| 1 | Jerusalem Cherry | deck | outside |
| 2 | Moses in the Cradle | patio | outside |
+----+---------------------+----------+---------+
2 rows in set (0.00 sec)
所以，我们看到外面有两株植物，一株在甲板上，另一株在院子里。现在，每株植物今天的平均温度是多少？
mysql> SELECT id, AVG(temperature)
-> FROM readings
-> WHERE DATE(event_time) = CURRENT_DATE() GROUP BY id;
+----+--------------------+
| id | AVG(temperature) |
+----+--------------------+
| 1 | 38.89000015258789 |
| 2 | 38.12999954223633 |
| 3 | 32.859999656677246 |
| 4 | 33.21000003814697 |
| 5 | 38.89000015258789 |
+----+--------------------+
5 rows in set (0.00 sec)
让我们将两者结合起来，以确保我们从测试数据中得到我们所期望的。我们应该首先检查数据，并试图确定我们应该看到什么。在这种情况下，我们应该看到一个工厂的平均温度为 38.89，另一个工厂的平均温度为 38.13(四舍五入)。
mysql> SELECT name, location, AVG(temperature) as avg_temp
-> FROM plants JOIN readings ON plants.id = readings.id
-> WHERE DATE(event_time) = CURRENT_DATE() AND plants.climate = 2
-> GROUP BY plants.id;
+---------------------+----------+-------------------+
| name | location | avg_temp |
+---------------------+----------+-------------------+
| Jerusalem Cherry | deck | 38.89000015258789 |
| Moses in the Cradle | patio | 38.12999954223633 |
+---------------------+----------+-------------------+
2 rows in set (0.00 sec)
没错。现在我们知道查询得到了我们想要的结果。另一个复杂的查询呢？
测试复杂查询
对于复杂的查询，我们想知道今天需要浇水的植物。您已经看到了如何将这个查询分解成几个部分来构造 SQL 语句。让我们看看数据库如何生成每个部分的结果，然后组合它们来验证查询。
让我们从我们创建的视图开始。回想一下，这个视图返回今天获取的那些读数的 ID、soil_status和每个soil_status值的计数。也就是说，我们应该看到今天有多个土壤状态值的植物的几行。
mysql> SELECT *
-> FROM soil_status_today;
+----+-------------+------------+
| id | soil_status | num_events |
+----+-------------+------------+
| 1 | DRY | 10 |
| 2 | OK | 10 |
| 3 | DRY | 4 |
| 3 | OK | 4 |
| 3 | WET | 2 |
| 4 | OK | 6 |
| 4 | WET | 4 |
| 5 | OK | 10 |
+----+-------------+------------+
8 rows in set (0.00 sec)
太好了。现在我们知道了今天每株植物的土壤湿度状况。注意一些植物有不止一个值，因为它们的土壤湿度在一天中不断变化。我们要的是那些比其他价值更有“干”价值的植物。但是让我们慢一点。
回想一下，我们使用了一个函数来计算今天采集的给定植物的最大样本。让我们使用这个函数来关注前面结果中的工厂 id。在这种情况下，我们看到几行的soil_status值都较高。让我们使用函数返回找到的每个值的出现百分比。在这种情况下，我将查询所有的植物，计算百分比，以便我们可以更容易地看到所有的数据。我们应该看到的是与之前相同的行，只是添加了平均样本。
mysql> SELECT id, soil_status, num_events, (num_events/max_samples_today(id)) as percent_occurrence
-> FROM soil_status_today;
+----+-------------+------------+--------------------+
| id | soil_status | num_events | percent_occurrence |
+----+-------------+------------+--------------------+
| 1 | DRY | 10 | 1.0000 |
| 2 | OK | 10 | 1.0000 |
| 3 | DRY | 4 | 0.4000 |
| 3 | OK | 4 | 0.4000 |
| 3 | WET | 2 | 0.2000 |
| 4 | OK | 6 | 0.6000 |
| 4 | WET | 4 | 0.4000 |
| 5 | OK | 10 | 1.0000 |
+----+-------------+------------+--------------------+
8 rows in set (0.00 sec)
太好了，七排！现在，我们有进展了。现在，让我们将该结果的输出限制为那些出现率高于 50%的结果。
mysql> SELECT id, soil_status, num_events, (num_events/max_samples_today(id)) as percent_occurrence
-> FROM soil_status_today
-> WHERE (num_events/max_samples_today(id)) > 0.50;
+----+-------------+------------+--------------------+
| id | soil_status | num_events | percent_occurrence |
+----+-------------+------------+--------------------+
| 1 | DRY | 10 | 1.0000 |
| 2 | OK | 10 | 1.0000 |
| 4 | OK | 6 | 0.6000 |
| 5 | OK | 10 | 1.0000 |
+----+-------------+------------+--------------------+
4 rows in set (0.00 sec)
请注意，我们所做的只是添加了一个WHERE子句。现在我们已经得到了今天获得的读数的行，这些行中有超过 50%的soil_status的单个值出现。让我们再一次扩展查询并添加soil_status = 'DRY'的条件。
Tip
你注意到那里有什么东西吗？看soil_status = 'DRY'。注意到有什么奇怪的吗？是的，这是一个枚举列，我使用了其中的一个值，而不是像前面的例子那样使用一个数值。如您所见，只要枚举中列出了值，您就可以使用数值或文本值。如果文本与枚举值之一不匹配，将会出现错误。
mysql> SELECT id, soil_status, num_events, (num_events/max_samples_today(id)) as percent_occurrence -> FROM soil_status_today
-> WHERE (num_events/max_samples_today(id)) > 0.50 AND soil_status = 'DRY'
+----+-------------+------------+--------------------+
| id | soil_status | num_events | percent_occurrence |
+----+-------------+------------+--------------------+
| 1 | DRY | 10 | 1.0000 |
+----+-------------+------------+--------------------+
1 row in set (0.00 sec)
完美！我们快到了。现在，我们想知道那株植物的名称和位置。为此，我们添加了一个新的连接来从plants表中获取信息。请注意，我使用土壤状态列的数值。
mysql> SELECT plants.id, name, location, soil_status, num_events, (num_events/max_samples_today(plants.id)) as percent_occurrence
-> FROM soil_status_today JOIN plants ON soil_status_today.id = plants.id
-> WHERE (num_events/max_samples_today(plants.id)) > 0.50 AND soil_status = 1;
+----+------------------+----------+-------------+------------+--------------------+
| id | name | location | soil_status | num_events | percent_occurrence |
+----+------------------+----------+-------------+------------+--------------------+
| 1 | Jerusalem Cherry | deck | DRY | 10 | 1.0000 |
+----+------------------+----------+-------------+------------+--------------------+
1 row in set (0.01 sec)
啊哈！现在，我们有了。嗯，差不多了。我们有太多的信息。但至少我们可以看到信息是正确的。现在，我们只想知道工厂的名称和位置。让我们稍微限制一下输出。
mysql> SELECT name, location
-> FROM soil_status_today JOIN plants ON soil_status_today.id = plants.id
-> WHERE (num_events/max_samples_today(plants.id)) > 0.50 AND soil_status = 1;
+------------------+----------+
| name | location |
+------------------+----------+
| Jerusalem Cherry | deck |
+------------------+----------+
1 row in set (0.00 sec)
就在那里！很简单，对吧？嗯，如果这是您的第一次尝试，就不会太多，但是请注意，当我们使用视图、函数和聚合特性等工具时，编写查询要容易得多！
现在我们已经看到了一个完整的工作示例，让我们考虑一下为 IOT 解决方案设计数据库的建议和最佳实践。 十五
建议和最佳做法
让我们回顾一下最佳实践和建议，看看您应该如何为您的 IOT 解决方案构建数据库。本节介绍了一些设计数据库的技巧。也就是说，本节不能涵盖所有关于数据库设计的知识。事实上，还有很多，特别是对于企业级的数据库设计和管理。因此，它需要几个章节，许多页，以及一整本书的许多倍于这一节的大小来公正地对待它。但是，我觉得在结束关于学习在 IOT 解决方案中使用 MySQL 和数据库的讨论时，有必要提供一份参考资料，供您在规划 IOT 数据库设计时参考。我没有特别的顺序列出它们。
- 使用正确的数据类型:做好准备工作，确定每列使用的最佳数据类型。避免使用所有字符字段的诱惑，这可能会使比较返回错误的结果，或者使您在试图找出视图或函数失败的原因时感到沮丧。
- 使用最小的数据类型:尽量为你的数据使用最小的数据类型。例如，避免使用宽字符字段。如果需要，您可以随时增加大小(如果您开始截断数据)。同样，使用与您将存储的最大值相匹配的二进制类型。例如，如果不需要高精度或大数值，就不要使用双精度或长整型数据类型。
- 使用索引:对于超过几十行的数据查询，考虑在经常查询的列上添加索引。如果正在比较或计算的列上有索引，索引可以极大地改善连接和复杂查询(如范围)。
- 不要存储冗余数据:尽量避免在多个表中存储相同值的诱惑。这可能会使您的查询更容易编写，但会使更新变得困难。也就是说，对于较大的数据库，您如何知道该列的每次出现在哪里？使用主从关系来简化数据。
- 计划您的查询:在设计表时，始终包括您想向数据库提出的问题。提前准备查询将有助于以后的开发工作。
- 避免使用 SELECT*:虽然获取一个表的所有列很容易，但是在列规范中使用*对于大型表或有很多列的表来说可能不是最佳选择。我们在前面的复杂示例中看到了这一点。唯一需要的列是名称和位置。因此，您应该抵制获取所有列的诱惑，而是指定您想要的列。
- 对固定或很少更改的数据使用查找表:与冗余数据类似，使用静态(或很少更新的)表有助于减少在结果集中传递和存储的额外数据量。存储一个较短的整数或类似的键比在整个表中存储一个或多个字段要有效得多。
- 尽可能使用数据库服务器的能力:设法将聚合、数学函数的处理，尤其是计算开销大的任务(如日期和字符串操作)卸载到数据库服务器上。不能低估视图、函数、触发器等的力量。精通这些概念可能需要一些时间，但回报可能意味着在您的 IOT 网络节点中使用更小、更便宜的组件。
- 使用好的、连贯的名称:尽量在数据库对象中使用描述性的、连贯的名称。抵制使用 a、b、c 等作为对象或列名来节省编码击键的诱惑。如果除了你之外的其他人试图弄清楚这些数据描述了什么，他们将会完全迷失。稍微啰嗦比简洁好。最后，避免使用首字母缩写或非标准缩写，因为它们会混淆意思。比如什么是 kdxprt？儿童专家？没有。我不得不问设计师——它的意思是(以一种政治上正确的方式)，“前父母。”别这样。
- 在主表上使用主键:对于任何可以唯一标识的数据，使用可以唯一标识每一行的主键。主键中可以使用多个列。对于列不唯一标识行的表，可以使用
AUTO_INCREMENT整数数据类型添加代理或人工主键。 - 避免宽表:如果你的表包含 20 个或更多的列，很可能它的设计很差。更具体地说，用数据库术语来说，它不是规范化的。再次查看数据，确定所使用的列是否可以移动到另一个表中。寻找本质上被引用、不经常改变或冗余的列。
- 不要丢弃数据:您应该始终保留表中的原始数据。如果您的数据库存储传感器读数，请存储原始值。如果您需要一个计算列来使阅读或查询更容易，可以像我展示的那样将它们存储在工厂监控数据库中，但要尽量使这些列简单，并使用触发器来设置值，而不是在您的数据或传感器节点上编写代码。这将代码隔离到一个单独的位置，并且一旦经过测试就可以依赖。拥有原始数据将允许您在将来计划查询，而您可能没有考虑过需要原始数据。
- 避免存储二进制数据:虽然数据库系统允许您存储大型的二进制数据(在 MySQL 中是 BLOB 或二进制大型对象)，但对于以这种方式存储许多行的表来说，它们效率不高，也不是一个好的选择，尤其是在它们没有变化的情况下。例如，如果您想要存储与数据项(主表中的一行)相关联的照片，您应该考虑创建一个字段来存储图像的路径，并将其存储在数据库服务器上。虽然这产生了一个新问题——改变路径——但是它从表中删除了 BLOB，并且可以使查询更有效。
- 规范化您的数据库:规范化对于许多数据库专家来说是一件大事，这是理所当然的，但是对于爱好者和业余爱好者来说，将您的数据库转换成一种更高级的范式(根据关系数据库理论)可能是太多的工作了。也就是说，我鼓励你去争取第三范式， 16 但不要为了达到那里而自毁前程。试图过度规范化可能会很快毁掉一个大型数据库。如果为了获得简单性而不损失效率，少量的欠标准化是允许的。
- 编码前设计数据库:总是在定义了高级需求之后、实现源代码之前设计数据库。这可能听起来很落后，但这是一个很好的习惯。
- 测试测试测试！我怎么强调这一点都不为过。花时间测试数据库，更重要的是用已知(测试)数据进行查询，这将为您以后开发查询和显示数据的应用省去很多麻烦。
- 备份数据:一旦您的解决方案开始运行，就对数据进行备份。如果您没有大量数据，mysqldump、MySQL Utilities、 17 等工具可以以 SQL 形式对您的数据进行逻辑备份，您可以在需要时恢复这些数据。如果您的数据较大，比如千兆字节或更多，您应该考虑商业备份解决方案，如 MySQL Enterprise Backup。
- 记录您的数据库:如果您的数据库只包含一个或少数几个表和少量数据，这可能看起来是额外的工作，但是想象一下，如果您的解决方案运行多年而没有出现问题，然后有一天您需要添加新功能或解决问题，会发生什么情况。如果您不知道数据库存储了什么或者它是如何产生结果的(视图、触发器等等)，您可能会花很多时间去挖掘错误的线索。您可以用多种方式记录数据库。我喜欢将 SQL 语句存储在一个文件中，并为每个对象编写简短的描述。有关示例，请参见本章的示例代码。
摘要
MySQL 数据库服务器是一个强大的工具。鉴于 MySQL 作为互联网数据库服务器在市场上的独特地位，IOT 开发者(以及许多初创公司和类似的互联网公司)选择 MySQL 作为他们的 IOT 解决方案也就不足为奇了。该服务器不仅功能强大且易于使用，还可以作为免费的社区许可证获得，您可以使用它来将您的初始投资控制在预算之内。
在本章中，您了解了使用数据库服务器的一些功能，数据库服务器如何存储数据，如何发出创建数据库和存储数据的表的命令以及检索数据的命令。虽然这一章只介绍了 MySQL 的一个小入门，但是您已经通过示例 IOT 解决方案学习了如何开始使用您自己的 IOT 数据。您自己的 IOT 解决方案很可能在范围上是相似的(但可能不是相同的数据库对象或表布局[模式])。
在下一章中，您将看到如何使用 Raspberry Pi 构建一个数据库节点。您将看到如何利用本章中关于数据存储方式和位置的示例，使用低成本的计算机来构建健壮的 MySQL 服务器。在其他主板上安装 MySQL 的过程是相似的。
Footnotes 1
即使是对数据及其形式的初步了解，对于成功的数据库配置也是至关重要的。
根据 GNU ( http://gnu.org/philosophy/free-sw.html )，“自由软件是自由的问题，不是价格的问题。为了理解这个概念，你应该把“免费”理解为“言论自由”，而不是“免费啤酒”。"
有时称为 MySQL 监控器、终端监控器，甚至是 MySQL 命令窗口。
如果您是 Oracle 的付费客户，并且拥有 MySQL 的订阅或支持协议，请联系您的销售代表了解详细信息。
你读过这些，是吗？
C.数据库关系模型:回顾与分析。
C.J. Date 和 H. Darwen,《未来数据库系统的基础:第三宣言》(雷丁，麻省，Addison-Wesley，2000 年)。
http://en.wikipedia.org/wiki/ACID
现在，这是一个专业回流可能做不到的技巧。 https://en.wikipedia.org/wiki/Stevie_Starr见。
不包括直接的引擎级查询，如 NoSQL 使用 NDB。
https://en.wikipedia.org/wiki/Null_%28SQL%29
如果你像我一样是关系数据库专家，那么在数据库系统中，尤其是 SQL 中，像这样的排序概念会让我毛骨悚然。“无序”的概念到此为止！
有关多行插入命令的附加条件和差异，请参见联机参考手册。
嘿，这是常有的事。虽然这是虚构的，但我和妻子发现了一种我们认为是一回事的植物，当它开始开花时，结果却是另一回事。
实际上，遵循任何数据库设计的最佳实践。
扫一扫
621
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
