EAV(Entity–Attribute–Value,实体-属性-值)，使用eav建模的好处是可以动态为数据模型增加或移除属性,最早用于医学用途，医生在就诊时需要记录很多病人的参数，如体温，年龄，过敏药等情况，而这些参数并不是每个病人都需要记录的。

由于商品的多样性，用EAV表来描述商品的各种属性也很合适。老牌电子商务应用oscommerce的表设计(为了简洁，我将商品属性名和属性值的关系表略去)：

-- 商品表
CREATE TABLE `products` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `products_name` VARCHAR (50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

-- 商品属性表
CREATE TABLE `products_attributes` (
  `id` INT (11) NOT NULL AUTO_INCREMENT,
  `products_id` INT (11) NOT NULL DEFAULT '0',
  `attribute_name` VARCHAR (50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `products_id_attribute_name` (`products_id`, `attribute_name`)
) ;

-- 属性值
CREATE TABLE `attribute_values` (
  `attribute_id` INT (11) NOT NULL DEFAULT '0',
  `attribute_value` VARCHAR (100) DEFAULT NULL,
  UNIQUE KEY `attribute_id` (
    `attribute_id`,
    `attribute_value`
  )
) ;

EAV表模型带来了数据的灵活性，是的增加对象的属性不需要用增加数据库的字段，有很高的灵活性。但是EAV表也有较大的性能问题。通常，EAV表带来的一个问题是当查找多个字段时，需要进行关联查询join,这样的查询效率比较低。为了提高查询效率，我们可以对商品属性表进行矩阵转积处理(pivoting),

SELECT
  items.item_name,
  ia.attribute_name,
  av.attribute_value
FROM
  attribute_values AS av
  JOIN item_attributes AS ia
    ON (ia.id = av.attribute_id)
  JOIN items AS items
    ON (items.id = ia.item_id) ;

一种方式是在php代码中读出后存入memcache, 当修改attributes表后php触发更新memcache或用cron定期更新;
另一种方法是将关联信息组成一张大的临时表，或者view(mysql 5), 利于warehouse的查询，数据的更新可以用数据库的触发器触发更新。
由于大量数据在php中进行处理带来了DB的额外IO和服务器性能问题，比较建议用后一种方式更新。

著名的ecommerce软件magento就采用了EAV表作为核心架构，下面看一下通常的表设计：

这里是EAV表的设计:

magento的做法是遵循php|architect的一些建议，通常有如下表等：

CREATE TABLE field_names (
  fid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  field_name VARCHAR (50) NOT NULL DEFAULT '',
  field_type ENUM (
    'VARCHAR',
    'INTEGER',
    'DOUBLE',
    'DATE',
    'TEXT'
  ) NOT NULL DEFAULT 'VARCHAR',
  UNIQUE KEY (field_name)
) ;

CREATE TABLE varchar_values (
  vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  VALUE VARCHAR (255) NOT NULL DEFAULT '',
  UNIQUE KEY (VALUE)
) ;

CREATE TABLE integer_values (
  vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  VALUE INT (11) NOT NULL DEFAULT 0,
  UNIQUE KEY (VALUE)
) ;

CREATE TABLE double_values (
  vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  VALUE DOUBLE NOT NULL DEFAULT 0,
  UNIQUE KEY (VALUE)
) ;

CREATE TABLE date_values (
  vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  VALUE DATE NOT NULL DEFAULT '0000-00-00',
  UNIQUE KEY (VALUE)
) ;

CREATE TABLE text_values (
  vid INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  VALUE TEXT NOT NULL DEFAULT '',
  UNIQUE KEY (VALUE (100))
) ;

可以定义一些mysql函数，方便数据类型到具体表的转换.

CREATE FUNCTION `value_display` (
  `type` ENUM (
    'NUMBER',
    'ENUM',
    'DATE',
    'TIME',
    'TEXT'
  ),
  `value` INT,
  `option` VARCHAR (255),
  `text` TEXT,
  `precision` INT,
  `date_format` VARCHAR (50)
) RETURNS VARCHAR (255) CHARACTER SET latin1 NO SQL
BEGIN
  CASE
    TYPE
    WHEN 'NUMBER'
    THEN RETURN `value` / POW(10, `precision`) ;
    WHEN 'ENUM' THEN RETURN `option`;
    WHEN 'DATE' THEN RETURN DATE_FORMAT(FROM_DAYS(`value`), `date_format`);
    WHEN 'TIME' THEN RETURN FROM_UNIXTIME(`value`, `date_format`);
    WHEN 'TEXT' THEN RETURN `text`;
    ELSE RETURN NULL;
  END CASE;
  RETURN NULL;
END;

当使用EAV表模型时，InnoDB比MYISAM的性能要好不少。

» 版权所有：YaoLei's Blog » EAV表设计建模
» 本文链接：https://www.yaolei.info/archives/238