如何设计SQLite数据库表以减少城市历史温度数据的Null值占用空间？

这是个典型的反范式宽表设计踩坑场景——把日期作为列名的思路，在不同城市记录起始时间差异巨大的情况下，必然会产生海量冗余空值，严重浪费存储空间。咱们直接用符合第一范式的行式存储结构来彻底解决这个问题：

推荐的表结构 #

CREATE TABLE city_temperatures (
    city_name TEXT NOT NULL,
    record_date DATE NOT NULL,
    temperature REAL NOT NULL,
    PRIMARY KEY (city_name, record_date)
);

为什么这个结构更优？ #

• 完全消除Null值：每条记录只存储某个城市某一天的实际温度数据，没有任何冗余空值，存储空间利用率达到最高。
• 扩展性极强：不需要因为新增日期而修改表结构，直接插入新记录即可，不管是1600年还是2024年的数据都能无缝存储。
• 查询更灵活：比如要查伦敦1600-1700年的温度，直接用WHERE条件过滤就行：

  SELECT record_date, temperature 
  FROM city_temperatures 
  WHERE city_name = 'London' AND record_date BETWEEN '1600-01-01' AND '1700-12-31';
  

• 数据唯一性保障：联合主键(city_name, record_date)确保同一个城市不会出现同一天的重复温度记录。

SQLite专属优化建议 #

1. 用DATE类型存日期：比你原来用的2018_04_04字符串格式更节省空间，还能直接使用SQLite的日期函数（比如strftime('%Y', record_date)提取年份）。
2. 索引优化：由于主键已经包含了city_name和record_date，查询特定城市或时间段的数据时，SQLite会自动利用主键索引，速度非常快。
3. 超大规模数据的可选方案：如果你的数据量达到千万级以上，可以考虑按城市拆分表（比如london_temperatures、new_york_temperatures），进一步提升查询效率，但绝大多数场景下单表足够用。

对比你最初的宽表设计，这个结构不仅彻底解决了Null值占用空间的问题，还让数据的维护和查询变得更加高效。

内容的提问来源于stack exchange，提问作者J.R.