转载

EAV or JSON

MongoDB 之类的 NoSQL 之所以流行，很大程度上是因为其 schema 设计相对自由，不管数据量多大，可以随时在线上环境添加新字段来保存新数据，而这种能力恰恰是传统的关系数据库所欠缺的，不过别担心，传统关系数据库有自己的应对之道。我们今天就讨论一下其中最具代表性的几种方法，看看它们的优劣。

在讨论前，我们不妨虚拟一个业务场景：假设我们要做一个类似汽车之家的产品库，首当其冲的是如何保存汽车的各种属性，比如说：长度、宽度、高度、GPS 导航系统、倒车影像、上坡辅助、陡坡缓降等等，最传统的方法是每一个属性都用一个独立的字段来保存，不过这样有问题：汽车属性有可能非常多，如此一来我们的表需要创建几十甚至上百个字段，而且以后还可能想要不断的添加新字段。最要命的是不同的汽车拥有的属性大不相同，比如有的汽车有 GPS 导航系统、倒车影像，但是却没有上坡辅助、陡坡缓降，如果把这些属性统统作为字段存在的话，那么就会导致表出现很多空值。

传统的 EAV 方法

所谓 EAV ，实际上是 Entity–Attribute–Value 的缩写。它的核心思想是把原本按列保存的数据转换成按行保存。不同的项目设计在表结构上可能会有些许差异，不过核心通常就是 entity，attribute，value 三张表，下面看看采用 EAV 如何解决问题：

CREATE TABLE eav_entities (     id INT UNSIGNED NOT NULL AUTO_INCREMENT,     entity_name VARCHAR(100) NOT NULL,     PRIMARY KEY(id) );  INSERT INTO `eav_entities`     (`id`, `entity_name`) VALUES     (1,'普拉多 3.5L TX-L'),     (2,'帕杰罗 3.0L 豪华版');  CREATE TABLE eav_attributes (     id INT UNSIGNED NOT NULL AUTO_INCREMENT,     attribute_name VARCHAR(100) NOT NULL,     PRIMARY KEY(id) );  INSERT INTO `eav_attributes`     (`id`, `attribute_name`) VALUES     (1,'长度'),     (2,'宽度'),     (3,'高度'),     (4,'GPS 导航系统'),     (5,'倒车影像'),     (6,'上坡辅助'),     (7,'陡坡缓降');  CREATE TABLE eav_values (     id INT UNSIGNED NOT NULL AUTO_INCREMENT,     entity_id INT UNSIGNED NOT NULL,     attribute_id INT UNSIGNED NOT NULL,     attribute_value VARCHAR(100) NOT NULL,     PRIMARY KEY(id) );  INSERT INTO `eav_values`     (`id`, `entity_id`, `attribute_id`, `attribute_value`) VALUES     (1,1,1,'4780'),     (2,1,2,'1885'),     (3,1,3,'1890'),     (4,1,6,'有'),     (5,1,7,'有'),     (6,2,1,'4900'),     (7,2,2,'1875'),     (8,2,3,'1900'),     (9,2,4,'有'),     (10,2,5,'有');

在 EAV 模型中查询相当麻烦，通常需要执行条件的查询就要执行多少次表链接：

EAV

BTW：前些天途牛撰文介绍他们自己的运维系统就是利用 EAV 设计的。

新锐的 JSON 方法

现在的关系数据库已不在是单纯的关系数据库了，很多时候视情况可以打破三范式的束缚，主流的 MySQL 和 PostgreSQL 都已经支持 JSON 数据类型，以 MySQL 为例：

CREATE TABLE entities (     id INT UNSIGNED NOT NULL AUTO_INCREMENT,     name VARCHAR(100) NOT NULL,     data JSON NOT NULL,     PRIMARY KEY(id) );  INSERT INTO `entities`     (`id`, `name`, `data`) VALUES     (1,'普拉多 3.5L TX-L','{       "Length": 4780,       "Width": 1885,       "Height": 1890,       "HAC": "有",       "HDC": "有"     }'),  (2,'帕杰罗 3.0L 豪华版','{       "Length": 4900,       "Width": 1875,       "Height": 1900,       "GPS": "有",       "ReverseImage": "有"     }');

和 EAV 的三个表相比，使用 JSON 后我们只需一个表即可：