家庭IoT网络本地SQL与云端SQL双向同步及数据留存功能咨询

问题1：本地SQL仅留存最近几天数据并与云端SQL同步 #

SQL本身没有直接集成“自动留存指定天数数据+同步”的一站式功能，但可以通过数据库基础操作+定时任务组合实现，步骤如下：

• 第一步：确保本地数据库表包含时间戳字段（比如created_at，记录数据生成时间），用来精准筛选旧数据。
• 第二步：定时清理本地旧数据：用树莓派的cron系统定时任务，执行SQL删除语句。示例SQL（适配MySQL/SQLite）：

  -- 删除3天前的数据，可根据需求修改天数
  DELETE FROM sensor_data WHERE created_at < DATETIME('now', '-3 days');
  

  关键注意点：清理操作必须在数据同步到云端之后执行，避免本地数据被删除后无法同步到云端。
• 第三步：先完成本地到云端的数据同步（参考问题2方案），再执行本地数据清理，确保云端保留全量数据，本地仅留存最近几天的内容。

问题2：局域网新数据同步至云端数据库 #

根据你选择的数据库类型，有两种实用方案：

方案1：数据库内置复制功能（适合MySQL/PostgreSQL等客户端-服务端型数据库） #

如果本地部署MySQL，可配置主从复制：将树莓派的本地数据库设为主节点，云端数据库设为从节点，本地新增/修改的数据会自动同步到云端。这种方式无需额外写代码，依赖数据库原生的同步机制，稳定性较高。

方案2：自定义脚本同步（适合SQLite等轻量文件型数据库） #

SQLite是文件型数据库，没有内置远程复制功能，可通过简单的Python脚本配合定时任务实现：

1. 脚本核心逻辑：查询本地数据库中上次同步时间之后的新增数据（通过created_at字段筛选）。
2. 将查询到的数据批量插入云端数据库。
3. 用cron设置每分钟执行一次脚本，保证数据同步的及时性。
   示例伪代码逻辑：

# 连接本地SQLite数据库
local_db = sqlite3.connect('/home/pi/local_iot.db')
# 连接云端MySQL数据库
cloud_db = mysql.connector.connect(host='云端地址', user='用户名', password='密码', database='iot_db')

# 获取上次同步时间（可存在本地文件或单独的配置表中）
last_sync_time = load_last_sync_time()
# 查询本地新数据
cursor_local = local_db.cursor()
cursor_local.execute("SELECT * FROM sensor_data WHERE created_at > ?", (last_sync_time,))
new_data = cursor_local.fetchall()

# 批量插入云端
cursor_cloud = cloud_db.cursor()
cursor_cloud.executemany("INSERT INTO sensor_data (id, value, created_at) VALUES (?, ?, ?)", new_data)
cloud_db.commit()

# 更新并保存本次同步时间
save_last_sync_time(datetime.now())

问题3：云端新数据同步至局域网数据库 #

这是反向同步需求，同样有两种适配方案：

方案1：双向复制（适合MySQL/PostgreSQL） #

配置双向主从复制，让云端和本地数据库互相同步。需提前定义冲突处理规则：比如当两边同时修改同一条数据时，以本地数据或云端数据的时间戳为准，避免数据混乱。

方案2：定时拉取脚本 #

用Python脚本定时从云端拉取新增数据（比如下发的指令），插入到本地数据库，逻辑与问题2的脚本方向相反：

cloud_db = mysql.connector.connect(host='云端地址', user='用户名', password='密码', database='iot_db')
local_db = sqlite3.connect('/home/pi/local_iot.db')

last_sync_time = load_last_sync_time()
# 从云端拉取新指令数据
cursor_cloud = cloud_db.cursor()
cursor_cloud.execute("SELECT * FROM commands WHERE created_at > ?", (last_sync_time,))
new_commands = cursor_cloud.fetchall()

# 插入本地数据库
cursor_local = local_db.cursor()
cursor_local.executemany("INSERT INTO commands (id, content, created_at) VALUES (?, ?, ?)", new_commands)
local_db.commit()

save_last_sync_time(datetime.now())

新手友好建议 #

• 本地优先选SQLite：轻量、无需运行服务端、资源占用低，完全适配树莓派的性能；云端可选择云服务商托管的MySQL或PostgreSQL，运维成本低。
• 同步脚本加入异常处理：比如捕获网络连接失败的异常，添加重试逻辑，避免单次网络波动导致同步中断。
• 给同步表加唯一键：比如id设为自增主键，插入时用INSERT ... ON DUPLICATE KEY UPDATE语句，避免重复插入数据。

内容的提问来源于stack exchange，提问作者eSlavko