如何在数据库中存储通用数据与客户端特定数据？

一、MySQL 8+ 结合SQL与NoSQL的实现方式 #

MySQL 8及以上版本支持JSON类型，可以完美结合结构化通用数据与半结构化的客户端特定数据：

• 保留通用信息的结构化表，以示例中的shapes为例：

  CREATE TABLE shapes (
      id INT PRIMARY KEY AUTO_INCREMENT,
      Name VARCHAR(50) NOT NULL,
      number_of_sides INT NOT NULL,
      area DECIMAL(10,2) NOT NULL,
      client_specific JSON -- 存储客户端/类型专属的特定属性
  );
  

• 插入数据时，将特定属性以JSON格式存入client_specific列，比如正方形的示例数据：

  INSERT INTO shapes (Name, number_of_sides, area, client_specific)
  VALUES ('Square', 4, 25, '{"height":5, "rotation":0}');
  

• 查询时可通过JSON函数直接提取特定属性，比如筛选旋转角度为0的正方形：

  SELECT * FROM shapes
  WHERE Name = 'Square' AND client_specific->>'$.rotation' = 0;
  

这种方式既保留了SQL对通用数据的高效查询能力，又通过JSON字段灵活存储不同客户端的特定数据，避免了多表关联或键值对表的弊端。

二、PostgreSQL 面向对象+结构化存储的实现方式 #

PostgreSQL的特性可以通过多种方式满足需求：

1. JSON/JSONB类型（轻量灵活） #

PostgreSQL支持JSON和JSONB（二进制存储，查询效率更高），用法与MySQL类似，将特定属性存入JSONB字段，同时保留通用数据的结构化列，适合快速落地需求。

2. 继承表（面向对象特性） #

利用PostgreSQL的表继承特性，创建通用父表，再为每个客户端/类型创建子表：

• 创建通用父表shapes：

  CREATE TABLE shapes (
      id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      Name VARCHAR(50) NOT NULL,
      number_of_sides INT NOT NULL,
      area DECIMAL(10,2) NOT NULL
  );
  

• 创建square子表继承父表并添加特定属性：

  CREATE TABLE square (
      height DECIMAL(10,2) NOT NULL,
      rotation INT NOT NULL
  ) INHERITS (shapes);
  

• 创建rectangle子表：

  CREATE TABLE rectangle (
      height DECIMAL(10,2) NOT NULL,
      width DECIMAL(10,2) NOT NULL,
      rotation INT NOT NULL
  ) INHERITS (shapes);
  

这种方式严格区分不同类型的数据结构，同时继承通用字段，查询时可直接查询父表获取所有形状数据，也可单独查询子表获取特定类型的数据，完美契合面向对象的设计思路。

3. 自定义类型（结构严谨） #

还可以创建自定义类型存储特定属性，示例如下：

CREATE TYPE square_attr AS (height DECIMAL(10,2), rotation INT);
CREATE TYPE rectangle_attr AS (height DECIMAL(10,2), width DECIMAL(10,2), rotation INT);

CREATE TABLE shapes (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    Name VARCHAR(50) NOT NULL,
    number_of_sides INT NOT NULL,
    area DECIMAL(10,2) NOT NULL,
    square_attr square_attr,
    rectangle_attr rectangle_attr
);

不过这种方式灵活性稍弱，适合属性固定、对类型约束要求高的场景。

三、方案选型建议 #

• MySQL JSON方案：实现简单，灵活性高，适合快速迭代、对特定属性类型约束要求不高的场景。
• PostgreSQL JSONB方案：兼顾灵活性与查询效率，支持更丰富的JSON操作函数，适合需要对特定属性做复杂查询的场景。
• PostgreSQL继承表方案：数据结构严谨，类型明确，维护性强，适合对数据规范有严格要求、业务类型相对固定的场景。

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