项目代码与报告:https://github.com/yicongl1/Vehicle-Share-Database

上一篇讲的是 vehicleShare 的关系模型:18 张表,保险、发票、评论各自独立。课程的最后一个交付要求做一版 NoSQL 演进,我们选择保留 PostgreSQL,只把三类“整块读写”的数据改成 JSON 列:

  • Vehicule.contrat_assurance
  • Contrat_location.commentaire
  • Contrat_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:

  1. 一对一或“一父一子”:一份合同只有一张发票、一条评论;一辆车只有一份保险。
  2. 整块读写:前端总是把发票作为一个整体展示,不会单独按“支付方式”做连接。
  3. 不需要跨行约束:字段之间没有“必须引用另一张表”的关系,或者可以接受应用层校验。

反例也很清楚:

  • 如果评论要按时间、评分、举报状态做多维筛选,独立表 + 索引更合适;
  • 如果发票要参与财务对账、按月份汇总、和合同/车辆/车主连接,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