关系库里的 JSON 列:什么时候值得把发票和评论塞进数据库
项目代码与报告:https://github.com/yicongl1/Vehicle-Share-Database
上一篇讲的是 vehicleShare 的关系模型:18 张表,保险、发票、评论各自独立。课程的最后一个交付要求做一版 NoSQL 演进,我们选择保留 PostgreSQL,只把三类“整块读写”的数据改成 JSON 列:
Vehicule.contrat_assuranceContrat_location.commentaireContrat_location.facture
对应的 Contrat_assurance、Commentaire、Facture 三张表被删掉。表少了,查询写法变了,代价也随之出现。这篇把这笔账算清楚。
原来的三张表长什么样
关系版里,保险合同、发票、评论都是独立实体:
CREATE TABLE Contrat_assurance (
id_assurance SERIAL PRIMARY KEY,
nom_assurance VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL,
vehicule VARCHAR(255) NOT NULL,
FOREIGN KEY (vehicule) REFERENCES Vehicule(immatriculation)
);
CREATE TABLE Facture (
id_facture SERIAL PRIMARY KEY,
date DATE NOT NULL,
kilometrage INT NOT NULL,
carburant FLOAT NOT NULL,
moyen_paiement VARCHAR(255) NOT NULL,
montant FLOAT NOT NULL,
contrat_location INT NOT NULL,
FOREIGN KEY (contrat_location) REFERENCES Contrat_location(id_contrat)
);
它们的共同点:和父记录是一对一或“一合同一份”的关系,读取时几乎总是整块取出,很少单独按发票字段做连接。 这正是 JSON 列的适用场景。
改完之后
保险变成车辆行里的一列:
CREATE TABLE Vehicule (
immatriculation VARCHAR(255) PRIMARY KEY,
-- ...
contrat_assurance JSON NOT NULL,
FOREIGN KEY (proprietaire) REFERENCES Proprietaire(pseudo)
);
发票和评论挂到合同上:
CREATE TABLE Contrat_location (
id_contrat SERIAL PRIMARY KEY,
-- ...
commentaire JSON,
facture JSON,
-- ...
);
查询用 PostgreSQL 的 ->> 取文本字段:
-- 所有“全险”车辆
SELECT immatriculation, marque, modele,
Contrat_assurance->>'nom_assurance' AS nom_assurance,
Contrat_assurance->>'type' AS type
FROM Vehicule
WHERE Contrat_assurance->>'type' = 'Tous risques';
-- 一条合同对应的发票金额与评分
SELECT id_contrat,
facture->>'montant' AS montant,
commentaire->>'note' AS note
FROM Contrat_location
WHERE id_contrat = 2;
-- 评论里标记了 signaler 的合同
SELECT * FROM Contrat_location
WHERE commentaire->>'signaler' = 'true';
注意 ->> 返回的是 text。所以 commentaire->>'note' = '5' 是字符串比较;如果要按数值排序或做范围查询,需要显式转换:
SELECT id_contrat, (commentaire->>'note')::int AS note
FROM Contrat_location
ORDER BY note DESC;
JSON 列省掉了什么
省掉的:
- 三张表、三个
SERIAL主键、三组外键; - 一对一关系的连接;
- 为“发票/评论/保险”分别写 CRUD 的模板代码;
- 结构变化时的
ALTER TABLE:加一个字段只需要改 JSON 的写入逻辑。
一起省掉的还有:
- 约束。 JSON 里的
note没有CHECK (note BETWEEN 0 AND 5);montant没有CHECK (montant > 0);signaler可以是字符串、数字或不存在。数据库不会替你拒绝脏数据。 - 外键。 发票里如果存了
contrat_id,它只是 JSON 的一个字段,不会阻止指向不存在的合同。 - 索引。 默认情况下
->>查询要扫全表。可以建表达式索引:
CREATE INDEX idx_contrat_note
ON Contrat_location (((commentaire->>'note')::int));
CREATE INDEX idx_assurance_type
ON Vehicule ((Contrat_assurance->>'type'));
但每多一种查询模式,就多一个索引;这跟关系表里“按列建索引”没有本质区别,只是索引定义更啰嗦。
- 聚合与连接。 “按保险类型统计车辆数”仍然能写,但 “发票金额与合同、车辆、车主一起连接分析”会变得别扭:JSON 字段要先展开,类型要转换,查询计划也更难优化。
什么时候值得
我的判断标准是三条,全部满足才考虑 JSON:
- 一对一或“一父一子”:一份合同只有一张发票、一条评论;一辆车只有一份保险。
- 整块读写:前端总是把发票作为一个整体展示,不会单独按“支付方式”做连接。
- 不需要跨行约束:字段之间没有“必须引用另一张表”的关系,或者可以接受应用层校验。
反例也很清楚:
- 如果评论要按时间、评分、举报状态做多维筛选,独立表 + 索引更合适;
- 如果发票要参与财务对账、按月份汇总、和合同/车辆/车主连接,JSON 会让查询变复杂;
- 如果字段需要唯一约束、外键或频繁更新单个子字段,JSON 的“整块读写”反而成为负担。
Python 应用里的一个裂缝
关系版和 NoSQL 版的命令行应用都用 psycopg2。大部分函数写得很规范:
cursor.execute(
"SELECT * FROM Utilisateur WHERE pseudo = %s AND mot_de_passe = %s",
(username, password),
)
但预订函数 Rerserver_Voiture 里,日期和公告号是直接拼进 SQL 的:
requete = f"INSERT INTO Contrat_location(...) VALUES ('{option_franchise}',{seuil_kilometrage},'{date_debut}', ...)"
这里的 date_debut 来自 input(),虽然前面用 strptime 校验了格式,但同一条代码路径里混用参数化和字符串拼接,是一个不该留下的裂缝。参数化查询的意义不是“防住已经想到的输入”,而是“让所有输入都走同一条安全通道”。重写时应该统一成:
cursor.execute(
"""INSERT INTO Contrat_location
(option_franchise, seuil_kilometrage, debut, fin, proprietaire, vehicule, locataire)
VALUES (%s, %s, %s, %s, %s, %s, %s)""",
(option_franchise, seuil_kilometrage, date_debut, date_fin, proprietaire, vehicule, locataire),
)
这也是我把仓库整理成作品集时最想标出来的一类问题:功能能跑,不代表写法可以照搬。
小结
JSON 列不是“关系库过时了”,而是把一部分结构约束换成了写入自由度。在 vehicleShare 里,保险、发票、评论恰好是“整块读写、弱跨行关系”的数据,所以这笔交换划算;如果换成订单明细、库存或需要强一致性的财务流水,同样的做法就会把问题推到应用层。
关系模型和文档模型的分界线,往往不在“数据复不复杂”,而在“你准备把哪些不变量交给数据库”。
源码、关系版与 NoSQL 版 SQL:https://github.com/yicongl1/Vehicle-Share-Database
相关技能
- SQL
- Python