mysql - 查询实体-属性-值模型的有效方法是什么

Question

我正在使用这样设置的数据库:

  entity field       value
   1      start_date  June 1, 2010
   1      end_date    August 30, 2010
   1      artist      some name

而且我想查询所有已经开始但未结束的艺术家名称为“some name”的实体。

我想到了这样的事情:

SELECT start.entity
FROM field_values AS `start`
  INNER JOIN field_values AS `end`
  INNER JOIN field_values AS `artist`
WHERE 
  (start.field = 'start_date' AND end.field = 'end_date' AND artist.field='artist')  AND 
  (STR_TO_DATE(start.value, '%M %d, %Y') < NOW() AND 
   STR_TO_DATE(end.value, '%M %d, %Y') > NOW())  AND 
  artist.value="some artist";

但这并没有让我觉得非常高效。对此有更好的方法吗？

Answer 1

为了清晰起见，您可以将连接子句项放在连接子句中，但就查询优化而言，这几乎是一种做法。

不过，您可以考虑将查询重写为这样的内容:

SELECT start.entity
FROM entity
  JOIN field_values AS start
    ON entity.id = start.entity AND start.field = 'start_date'
  INNER JOIN field_values AS end
    ON entity.id = end.entity AND end.field = 'end_date'
  INNER JOIN field_values AS artist
    ON entity.id = artist.entity AND artist.field = 'artist'
WHERE STR_TO_DATE(start.value, '%M %d, %Y') < NOW()
  AND STR_TO_DATE(end.value, '%M %d, %Y') > NOW()
  AND artist.value="some artist"
;

您还可以规范化该字段以节省一点空间(假设该字段不是枚举)