数据库表结构设计：设备信息表、设备分组表、设备认证日志表、策略表

这一章我们聊聊数据库表结构。说实话，很多物联网项目死在第一步——表没设计好。我见过一个项目，设备信息表里塞了三十多个字段，查询慢得像蜗牛。后来重构时发现，一半字段根本用不上。

设备注册与身份管理系统，核心就四张表：设备信息表、设备分组表、设备认证日志表、策略表。它们之间的关系，我画了张图，你先感受一下。

设备注册与身份管理系统核心表关系 设备信息表 device_id (PK) product_key, device_secret 设备分组表 group_id (PK) group_name, parent_id 设备认证日志表 log_id (PK) device_id, auth_result 策略表 policy_id (PK) target_type, target_id 关系说明 • 设备可属于多个分组 • 分组支持树形层级 • 策略可绑定设备或分组 • 日志记录每次认证行为

设备信息表：一切的基础

设备信息表是整个系统的基石。每台设备注册时，都会在这里生成一条记录。我个人习惯把核心字段控制在10个以内，太多了反而难维护。

先看表结构：

字段名类型说明
idBIGINT UNSIGNED自增主键
device_idVARCHAR(64)设备唯一标识，全局唯一
product_keyVARCHAR(32)产品型号标识
device_nameVARCHAR(128)设备名称，可读性字段
device_secretVARCHAR(256)设备密钥，BCrypt加密存储
statusTINYINT0=未激活 1=在线 2=离线 3=禁用
firmware_versionVARCHAR(32)当前固件版本
last_online_timeDATETIME最后上线时间
created_atDATETIME注册时间
updated_atDATETIME更新时间

设计要点：

  • device_id 用 VARCHAR 而不是 INT。为什么？设备标识可能包含字母和符号，比如 "SN-2024-A001" 这种格式很常见
  • device_secret 绝对不能明文存储。我见过有人直接存明文，结果数据库泄露后所有设备都被控制了
  • status 用 TINYINT 而不是 ENUM。扩展性更好，以后加个 "维护中" 状态不用改表结构

建表脚本如下：

CREATE TABLE device_info (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL COMMENT '设备唯一标识',
    product_key VARCHAR(32) NOT NULL COMMENT '产品型号标识',
    device_name VARCHAR(128) DEFAULT '' COMMENT '设备名称',
    device_secret VARCHAR(256) NOT NULL COMMENT '设备密钥(BCrypt)',
    status TINYINT DEFAULT 0 COMMENT '0未激活 1在线 2离线 3禁用',
    firmware_version VARCHAR(32) DEFAULT '' COMMENT '固件版本',
    last_online_time DATETIME DEFAULT NULL COMMENT '最后上线时间',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_device_id (device_id),
    INDEX idx_product_key (product_key),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备信息表';

注意：device_id 一定要建唯一索引。我曾经遇到过一个bug，因为没加唯一约束，同一台设备注册了两次，导致认证时出现冲突。排查了整整一天才发现是数据重复了。

设备分组表：树形结构怎么设计

设备分组是个经典问题。你想想看，一个工厂可能有几千台设备，不分组根本没法管理。分组表的核心是支持树形结构，也就是分组下面还能有子分组。

我推荐用 parent_id 方式实现树形，简单直接：

字段名类型说明
idBIGINT UNSIGNED自增主键
group_idVARCHAR(32)分组唯一标识
group_nameVARCHAR(64)分组名称
parent_idVARCHAR(32)父分组ID，根节点为NULL
descriptionVARCHAR(256)分组描述
sort_orderINT排序序号
created_atDATETIME创建时间

建表脚本：

CREATE TABLE device_group (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_id VARCHAR(32) NOT NULL COMMENT '分组唯一标识',
    group_name VARCHAR(64) NOT NULL COMMENT '分组名称',
    parent_id VARCHAR(32) DEFAULT NULL COMMENT '父分组ID',
    description VARCHAR(256) DEFAULT '' COMMENT '分组描述',
    sort_order INT DEFAULT 0 COMMENT '排序序号',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_group_id (group_id),
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备分组表';

避坑指南：parent_id 允许为 NULL，表示根节点。查询时用递归CTE（MySQL 8.0+）或者业务层递归。我建议如果分组层级不超过3层，业务层递归就够了，SQL写起来简单，也容易维护。

设备和分组是多对多关系，所以还需要一张关联表：

CREATE TABLE device_group_rel (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(64) NOT NULL COMMENT '设备ID',
    group_id VARCHAR(32) NOT NULL COMMENT '分组ID',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_device_group (device_id, group_id),
    INDEX idx_group_id (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备分组关联表';

设备认证日志表：审计的命脉

认证日志表，说白了就是记录设备每次登录的痕迹。安全审计、故障排查都靠它。我见过一个项目，设备出问题了查不到日志，最后只能一台一台去现场看——太痛苦了。

字段名类型说明
idBIGINT UNSIGNED自增主键
log_idVARCHAR(32)日志唯一ID，UUID格式
device_idVARCHAR(64)设备ID
auth_typeTINYINT1=密钥认证 2=证书认证 3=Token认证
auth_resultTINYINT0=失败 1=成功
fail_reasonVARCHAR(128)失败原因，成功时为NULL
client_ipVARCHAR(45)客户端IP，支持IPv6
auth_timeDATETIME认证时间

建表脚本：

CREATE TABLE device_auth_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    log_id VARCHAR(32) NOT NULL COMMENT '日志唯一ID',
    device_id VARCHAR(64) NOT NULL COMMENT '设备ID',
    auth_type TINYINT NOT NULL COMMENT '1密钥 2证书 3Token',
    auth_result TINYINT NOT NULL COMMENT '0失败 1成功',
    fail_reason VARCHAR(128) DEFAULT NULL COMMENT '失败原因',
    client_ip VARCHAR(45) DEFAULT '' COMMENT '客户端IP',
    auth_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '认证时间',
    UNIQUE KEY uk_log_id (log_id),
    INDEX idx_device_id (device_id),
    INDEX idx_auth_time (auth_time),
    INDEX idx_auth_result (auth_result)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='设备认证日志表';

性能提醒：日志表的数据量增长很快。一台设备每小时认证一次，1000台设备一年就是876万条记录。建议按月份做分区，或者定期归档。我习惯用 auth_time 做 RANGE 分区，查询最近一个月的日志时效率很高。

策略表：灵活的权限控制

策略表是系统的"大脑"。它决定了设备能做什么、不能做什么。策略可以绑定到单个设备，也可以绑定到整个分组。这样设计的好处是——灵活。

字段名类型说明
idBIGINT UNSIGNED自增主键
policy_idVARCHAR(32)策略唯一标识
policy_nameVARCHAR(64)策略名称
target_typeTINYINT1=设备 2=分组
target_idVARCHAR(64)设备ID或分组ID
policy_contentJSON策略内容，JSON格式
effectTINYINT0=拒绝 1=允许
priorityINT优先级，数字越小优先级越高
enabledTINYINT0=禁用 1=启用
created_atDATETIME创建时间
updated_atDATETIME更新时间

建表脚本：

CREATE TABLE policy (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    policy_id VARCHAR(32) NOT NULL COMMENT '策略唯一标识',
    policy_name VARCHAR(64) NOT NULL COMMENT '策略名称',
    target_type TINYINT NOT NULL COMMENT '1设备 2分组',
    target_id VARCHAR(64) NOT NULL COMMENT '目标ID',
    policy_content JSON NOT NULL COMMENT '策略内容',
    effect TINYINT NOT NULL DEFAULT 1 COMMENT '0拒绝 1允许',
    priority INT DEFAULT 100 COMMENT '优先级',
    enabled TINYINT DEFAULT 1 COMMENT '0禁用 1启用',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_policy_id (policy_id),
    INDEX idx_target (target_type, target_id),
    INDEX idx_enabled (enabled)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='策略表';

关于JSON字段：MySQL 5.7+ 支持 JSON 类型，可以直接在数据库里做 JSON 查询。比如 policy_content 里存了 {"actions": ["read", "write"], "topics": ["/data/#"]}，查询时可以用 JSON_CONTAINS 函数。不过要注意，JSON 字段不适合做频繁更新的场景。

四张表的关系总结

嗯，到这里四张表都讲完了。我最后总结一下它们之间的关系：

  • 设备信息表是核心，其他表都直接或间接引用它
  • 设备分组表通过关联表与设备建立多对多关系
  • 设备认证日志表记录设备的行为，用于审计和排查
  • 策略表通过 target_type + target_id 灵活绑定到设备或分组
← 上一章 📖 返回目录 下一章 →