如何透视MySQL实体 - 属性 - 值模式

Question

如何透视MySQL实体 - 属性 - 值模式我需要设计存储文件所有元数据的表（即文件名，作者，标题，创建日期）和自定义元数据（用户已添加到文件中，例如CustUseBy，CustSendBy）。无法预先设置自定义元数据字段的数量。实际上，确定在文件中添加了什么和多少自定义标记的唯一方法是检查表中存在的内容。为了存储它，我创建了一个基表（具有文件的所有公共元数据），一个Attributes表（包含可以在文件上设置的附加，可选属性）和一个FileAttributes表（为文件的属性赋值）。CREAT TABLE FileBase (    id VARCHAR(32) PRIMARY KEY,    name VARCHAR(255) UNIQUE NOT NULL,    title VARCHAR(255),    author VARCHAR(255),    created DATETIME NOT NULL,) Engine=InnoDB;CREATE TABLE Attributes (    id VARCHAR(32) PRIMARY KEY,    name VARCHAR(255) NOT NULL,    type VARCHAR(255) NOT NULL) Engine=InnoDB;CREATE TABLE FileAttributes (    sNo INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,    fileId VARCHAR(32) NOT NULL,    attributeId VARCHAR(32) NOT NULL,    attributeValue VARCHAR(255) NOT NULL,    FOREIGN KEY fileId REFERENCES FileBase (id),    FOREIGN KEY attributeId REFERENCES Attributes (id) ) Engine=InnoDB;样本数据：INSERT INTO FileBase(id,      title,  author,  name,        created)  VALUES('F001', 'Dox',   'vinay', 'story.dox', '2009/01/02 15:04:05'),('F002', 'Excel', 'Ajay',  'data.xls',  '2009/02/03 01:02:03');INSERT INTO Attributes(id,      name,            type)  VALUES('A001', 'CustomeAttt1',  'Varchar(40)'),('A002', 'CustomUseDate', 'Datetime');INSERT INTO FileAttributes (fileId, attributeId, attributeValue)  VALUES('F001', 'A001',      'Akash'),('F001', 'A002',      '2009/03/02');现在问题是我想以这样的方式显示数据：FileId, Title, Author, CustomAttri1, CustomAttr2, ...F001    Dox    vinay   Akash         2009/03/02   ...F002    Excel  Ajay     什么查询会生成此结果？

Answer 1

这个问题提到了MySQL，实际上这个DBMS对这类问题有一个特殊的功能：GROUP_CONCAT(expr)。请参阅有关逐个功能的MySQL参考手册。该功能已在MySQL 4.1版中添加。您将GROUP BY FileID在查询中使用。

我不确定你希望结果如何。如果你想为每个项目列出每个属性（即使没有设置），它将更难。但是，这是我对如何做的建议：

SELECT bt.FileID, Title, Author, 
 GROUP_CONCAT(
  CONCAT_WS(':', at.AttributeName, at.AttributeType, avt.AttributeValue) 
  ORDER BY at.AttributeName SEPARATOR ', ') FROM BaseTable bt JOIN AttributeValueTable avt ON avt.FileID=bt.FileID 
 JOIN AttributeTable at ON avt.AttributeId=at.AttributeId 
GROUP BY bt.FileID;

这为您提供了相同顺序的所有属性，这可能很有用。输出将如下所示：

'F001', 'Dox', 'vinay', 'CustomAttr1:varchar(40):Akash, CustomUseDate:Datetime:2009/03/02'

这样，您只需要一个单独的数据库查询，并且输出很容易解析。如果要将属性存储为数据库中的实际日期时间等，则需要使用动态SQL，但我会保持清晰并将值存储在varchars中。

Answer 2

这种查询的一般形式是

SELECT file.*,
   attr1.value AS 'Attribute 1 Name', 
   attr2.value AS 'Attribute 2 Name', 
   ...FROM
   file 
   LEFT JOIN attr AS attr1 
      ON(file.FileId=attr1.FileId and attr1.AttributeId=1)
   LEFT JOIN attr AS attr2 
      ON(file.FileId=attr2.FileId and attr2.AttributeId=2)
   ...

因此，您需要根据所需的属性动态构建查询。在php-ish伪代码中

$cols="file";$joins="";$rows=$db->GetAll("select * from Attributes");foreach($rows as $idx=>$row){
   $alias="attr{$idx}";
   $cols.=", {$alias}.value as '".mysql_escape_string($row['AttributeName'])."'";   
   $joins.="LEFT JOIN attr as {$alias} on ".
       "(file.FileId={$alias}.FileId and ".
       "{$alias}.AttributeId={$row['AttributeId']}) ";}

 $pivotsql="select $cols from file $joins";

Answer 3

如果您正在寻找比group-concat结果更可用（和可加入）的东西，请尝试以下解决方案。我已经创建了一些与你的例子非常相似的表格，以使其有意义。

这适用于：

你想要一个纯SQL解决方案（没有代码，没有循环）

您有一组可预测的属性（例如，不是动态的）

您可以在需要添加新属性类型时更新查询

您希望结果可以是JOINed，UNIONed或嵌套为子选择

表A（文件）

FileID, Title, Author, CreatedOn

表B（属性）

AttrID, AttrName, AttrType [not sure how you use type...]

表C（Files_Attributes）

FileID, AttrID, AttrValue

传统查询会拉出许多冗余行：

SELECT * FROM 

Files F 

LEFT JOIN Files_Attributes FA USING (FileID)

LEFT JOIN Attributes A USING (AttributeID);

AttrID FileID标题作者CreatedOn AttrValue AttrName AttrType

50 1 TestFile Joe 2011-01-01 true ReadOnly bool

60 1 TestFile Joe 2011-01-01 xls FileFormat文本

70 1 TestFile Joe 2011-01-01 false私人布尔

80 1 TestFile Joe 2011-01-01 2011-10-03 LastModified日期

60 2 LongNovel Mary 2011-02-01 json FileFormat文本

80 2 LongNovel Mary 2011-02-01 2011-10-04 LastModified日期

70 2 LongNovel Mary 2011-02-01 true私人布尔

50 2 LongNovel Mary 2011-02-01 true ReadOnly bool

50 3 ShortStory Susan 2011-03-01 false ReadOnly bool

60 3 ShortStory Susan 2011-03-01 ascii FileFormat文本

70 3 ShortStory Susan 2011-03-01 false私人布尔

80 3 ShortStory Susan 2011-03-01 2011-10-01 LastModified日期

50 4 ProfitLoss Bill 2011-04-01 false ReadOnly bool

70 4 ProfitLoss Bill 2011-04-01 true Private bool

80 4 ProfitLoss Bill 2011-04-01 2011-10-02 LastModified date

60 4 ProfitLoss Bill 2011-04-01 text FileFormat text

50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool

60 5 MonthlyBudget George 2011-05-01二进制文件格式文本

70 5 MonthlyBudget George 2011-05-01 false私人布尔

80 5 MonthlyBudget George 2011-05-01 2011-10-20 LastModified日期

这个合并查询（使用MAX的方法）可以合并行：

SELECT

F.*,

MAX( IF(A.AttrName = 'ReadOnly', FA.AttrValue, NULL) ) as 'ReadOnly',

MAX( IF(A.AttrName = 'FileFormat', FA.AttrValue, NULL) ) as 'FileFormat',

MAX( IF(A.AttrName = 'Private', FA.AttrValue, NULL) ) as 'Private',

MAX( IF(A.AttrName = 'LastModified', FA.AttrValue, NULL) ) as 'LastModified'

FROM 

Files F 

LEFT JOIN Files_Attributes FA USING (FileID)

LEFT JOIN Attributes A USING (AttributeID)

GROUP BY

F.FileID;

FileID标题作者CreatedOn ReadOnly FileFormat Private LastModified

1 TestFile Joe 2011-01-01 true xls false 2011-10-03

2 LongNovel Mary 2011-02-01 true json true 2011-10-04

3 ShortStory Susan 2011-03-01 false ascii false 2011-10-01

4 ProfitLoss Bill 2011-04-01 false text true 2011-10-02

5 MonthlyBudget George 2011-05-01 false binary false 2011-10-20