3. 数据存储设计：SQLite数据库表结构

好，咱们接着聊。传感器数据采集上来了，总得有个地方存吧？

我个人习惯用 SQLite。为什么？轻量、免安装、一个文件搞定。在嵌入式设备或者 PC 上做原型验证，它都是首选。你想想看，要是为了存几个温度值就去装个 MySQL，是不是有点大炮打蚊子？

3.1 核心表：sensor_data

这是最核心的一张表。所有传感器采集到的历史数据，最终都会落在这里。

我在项目中遇到过一个问题：一开始只设计了「温度」和「湿度」两个字段，后来客户要加「光照强度」和「CO2浓度」，结果只能改表结构。嗯，这其实挺麻烦的。所以我的建议是——把传感器类型和数值拆开。

来看我推荐的表结构：

CREATE TABLE sensor_data (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_id   TEXT    NOT NULL,       -- 传感器编号，比如 'temp_01'
    sensor_type TEXT    NOT NULL,       -- 传感器类型，比如 'temperature'
    value       REAL    NOT NULL,       -- 采集到的数值
    unit        TEXT    NOT NULL,       -- 单位，比如 '°C', '%RH'
    timestamp   TEXT    NOT NULL,       -- 采集时间，ISO 8601 格式
    quality     INTEGER DEFAULT 1      -- 数据质量标记：1=正常，0=异常
);

为什么要这么设计？说白了，就是为了灵活。以后加任何新传感器，都不需要改表结构，直接往里插数据就行。

3.2 辅助表：sensor_info

光有数据还不够。你总得知道每个传感器装在哪个位置、量程是多少、校准日期是什么时候吧？

所以我通常会再加一张辅助表：

CREATE TABLE sensor_info (
    sensor_id       TEXT PRIMARY KEY,   -- 传感器编号
    sensor_type     TEXT NOT NULL,      -- 传感器类型
    location        TEXT,               -- 安装位置，比如 '温室A区-3号架'
    range_min       REAL,               -- 量程下限
    range_max       REAL,               -- 量程上限
    install_date    TEXT,               -- 安装日期
    calibration_date TEXT,              -- 最近校准日期
    notes           TEXT                -- 备注
);

这张表平时查询不多，但做数据分析或者排查故障时特别有用。我曾经有一次发现某个温度值异常偏高，查了 sensor_info 才发现那个传感器装在暖气管道旁边——嗯，位置信息帮了大忙。

3.3 索引设计

数据量大了以后，查询速度会变慢。这时候索引就派上用场了。

我建议至少建这两个索引：

CREATE INDEX idx_sensor_data_time 
ON sensor_data(timestamp);

CREATE INDEX idx_sensor_data_sensor_time 
ON sensor_data(sensor_id, timestamp);

第一个索引用于按时间范围查询。第二个索引用于查某个传感器在某个时间段的数据。这两个查询场景在实际项目中出现频率最高。

3.4 数据归档策略

数据会越积越多。一年下来，几百万条记录很正常。

我的做法是：按月份分表。比如 sensor_data_2024_01、sensor_data_2024_02……查询时根据时间范围自动路由到对应的表。

当然，如果你不想搞那么复杂，也可以保留一张大表，定期删除旧数据。比如只保留最近 90 天的数据。

3.5 完整建表脚本

最后，给你一个可以直接用的完整脚本：

-- 传感器信息表
CREATE TABLE IF NOT EXISTS sensor_info (
    sensor_id       TEXT PRIMARY KEY,
    sensor_type     TEXT NOT NULL,
    location        TEXT,
    range_min       REAL,
    range_max       REAL,
    install_date    TEXT,
    calibration_date TEXT,
    notes           TEXT
);

-- 传感器数据表
CREATE TABLE IF NOT EXISTS sensor_data (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    sensor_id   TEXT    NOT NULL,
    sensor_type TEXT    NOT NULL,
    value       REAL    NOT NULL,
    unit        TEXT    NOT NULL,
    timestamp   TEXT    NOT NULL,
    quality     INTEGER DEFAULT 1,
    FOREIGN KEY (sensor_id) REFERENCES sensor_info(sensor_id)
);

-- 索引
CREATE INDEX IF NOT EXISTS idx_sensor_data_time 
ON sensor_data(timestamp);

CREATE INDEX IF NOT EXISTS idx_sensor_data_sensor_time 
ON sensor_data(sensor_id, timestamp);

嗯，这个结构我用了好几年，在好几个项目里都验证过。你直接拿去用，基本不会出大问题。

下一章咱们聊聊怎么用 Python 操作这个数据库，把数据写进去、读出来。