如何透视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 AttrType50 1 TestFile Joe 2011-01-01 true ReadOnly bool60 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 bool50 3 ShortStory Susan 2011-03-01 false ReadOnly bool60 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 bool70 4 ProfitLoss Bill 2011-04-01 true Private bool80 4 ProfitLoss Bill 2011-04-01 2011-10-02 LastModified date60 4 ProfitLoss Bill 2011-04-01 text FileFormat text50 5 MonthlyBudget George 2011-05-01 false ReadOnly bool60 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的方法）可以合并行：SELECTF.*,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 BYF.FileID;FileID标题作者CreatedOn ReadOnly FileFormat Private LastModified1 TestFile Joe 2011-01-01 true xls false 2011-10-032 LongNovel Mary 2011-02-01 true json true 2011-10-043 ShortStory Susan 2011-03-01 false ascii false 2011-10-014 ProfitLoss Bill 2011-04-01 false text true 2011-10-025 MonthlyBudget George 2011-05-01 false binary false 2011-10-20