如何将MySQL实体-属性-值模式进行透视操作

Question

如何将MySQL实体-属性-值模式进行透视操作

mysqldatabase-designpivotentity-attribute-value

28

我需要设计一些表来存储文件的所有元数据（例如文件名、作者、标题、创建日期）以及用户添加的自定义元数据（例如CustUseBy、CustSendBy等）。自定义元数据字段的数量不能事先确定。实际上，确定哪些自定义标签已经添加到文件中，以及有多少自定义标签已经添加到文件中的唯一方法是检查表中存在什么。

为了存储这些信息，我创建了一个基本表（包含文件的所有公共元数据），一个“属性”表（保存可以在文件上设置的其他可选属性）和一个“文件属性”表（为文件分配属性值）。

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

哪个查询会生成这个结果？

- Ashok

你打算使用哪种编程语言？ - S.Lott

MYSQL，我打算通过MYSQL查询获得所需的结果，然后将这个结果与其他表连接，并将所需的结果传递到前端。 - Ashok

MySQL不是一种编程语言... - Lightness Races in Orbit

7个回答

10

如果你正在寻找比group-concat结果更易用（且可加入）的东西，请尝试下面的解决方案。我创建了一些非常类似于你示例的表格，以便让它更容易理解。

当以下情况适用时，此方法有效：

您需要一个纯SQL解决方案（无代码、无循环）
您有一组可预测的属性（例如不是动态的）
添加新的属性类型时可以更新查询
您希望获得可以连接、合并或作为子查询嵌套的结果

表A（文件）

FileID, Title, Author, CreatedOn

表B（属性）

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

表格 C（文件属性）

FileID, AttrID, AttrValue

传统查询会提取许多冗余行：

SELECT * FROM 
Files F 
LEFT JOIN Files_Attributes FA USING (FileID)
LEFT JOIN Attributes A USING (AttributeID);

这个合并行的查询（使用 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;

文件编号  文件标题         作者   创建时间    只读权限    文件格式     私有性  最后修改时间
1       TestFile        Joe     2011-01-01  是         xls         否      2011-10-03
2       LongNovel       Mary    2011-02-01  是         json        是      2011-10-04
3       ShortStory      Susan   2011-03-01  否         ascii       否      2011-10-01
4       ProfitLoss      Bill    2011-04-01  否         text        是      2011-10-02
5       MonthlyBudget   George  2011-05-01  否         binary      否      2011-10-20

- methai

9

这种查询的一般形式如下：

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 伪代码，例如：

$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";

- Paul Dixon

我可以创建一个存储过程并在其中编写游标以遍历记录来实现结果吗？如果可能的话，请给我举个例子。感谢您的帮助。 - Ashok

是的，如果您执行并迭代$pivotsql查询，每一行将是一个文件，并且每个属性都有一列，如果该文件不存在该属性，则该列将为NULL。 - Paul Dixon

2

这似乎正是我过去几周一直在寻找的。非常感谢。 - Neil Aitken

我在想...在上述解决方案中，使用多个LEFT JOIN连接同一张表以将连接的行收集为列是否会存在性能问题？ - ricosrealm

这将取决于表定义及其大小。在生成的SQL上运行EXPLAIN以评估性能影响。 - Paul Dixon

6

这是SQL中的标准“行转列”问题。

最容易在SQL外部完成。

在应用程序中，执行以下步骤：

1. 定义一个简单的类来包含文件、系统属性和客户属性的Collection。列表是这个客户属性集合的不错选择。我们将这个类称为FileDescription。

2. 在文件和文件所有客户属性之间执行一个简单的连接。

3. 编写一个循环来从查询结果组装FileDescriptions。

- 获取第一行，创建一个FileDescription并设置第一个客户属性。 - 当有更多行需要获取时： - 获取一行 - 如果这行的文件名与我们正在构建的FileDescription不匹配：完成对FileDescription的构建；将此附加到结果File Descriptions的Collection；使用给定名称创建一个新的、空的FileDescription，并设置第一个客户属性。 - 如果这行的文件名与我们正在构建的FileDescription匹配：将另一个客户属性附加到当前的FileDescription。

- S.Lott

嗨，谢谢。但是我不太擅长编程语言，我有T-SQL的经验，你能简要地解释一下如何实现这个功能并举个例子吗？非常感谢。 - Ashok

4

我一直在尝试不同的方法，Methai的答案对我来说最方便。虽然我的当前项目使用了Doctrine和MySQL，但有很多松散的表。

以下是我使用Methai解决方案的结果：

创建实体表

DROP TABLE IF EXISTS entity;
CREATE TABLE entity (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    author VARCHAR(255),
    createdOn DATETIME NOT NULL
) Engine = InnoDB;

创建属性表

DROP TABLE IF EXISTS attribute;
CREATE TABLE attribute (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL
) Engine = InnoDB;

创建属性值表

DROP TABLE IF EXISTS attributevalue;
CREATE TABLE attributevalue (
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    value VARCHAR(255) NOT NULL,
    attribute_id INT UNSIGNED NOT NULL,
    FOREIGN KEY(attribute_id) REFERENCES attribute(id)
 ) Engine = InnoDB;

创建实体属性值连接表。

DROP TABLE IF EXISTS entity_attributevalue;
CREATE TABLE entity_attributevalue (
    entity_id INT UNSIGNED NOT NULL,
    attributevalue_id INT UNSIGNED NOT NULL,
    FOREIGN KEY(entity_id) REFERENCES entity(id),
    FOREIGN KEY(attributevalue_id) REFERENCES attributevalue(id)
) Engine = InnoDB;

填充实体表

INSERT INTO entity
    (title, author, createdOn)
VALUES
    ('TestFile', 'Joe', '2011-01-01'),
    ('LongNovel', 'Mary', '2011-02-01'),
    ('ShortStory', 'Susan', '2011-03-01'),
    ('ProfitLoss', 'Bill', '2011-04-01'),
    ('MonthlyBudget', 'George', '2011-05-01'),
    ('Paper', 'Jane', '2012-04-01'),
    ('Essay', 'John', '2012-03-01'),
    ('Article', 'Dan', '2012-12-01');

填充属性表

INSERT INTO attribute
    (name, type)
VALUES
    ('ReadOnly', 'bool'),
    ('FileFormat', 'text'),
    ('Private', 'bool'),
    ('LastModified', 'date');

填充attributevalue表

INSERT INTO attributevalue 
    (value, attribute_id)
VALUES
    ('true', '1'),
    ('xls', '2'),
    ('false', '3'),
    ('2011-10-03', '4'),
    ('true', '1'),
    ('json', '2'),
    ('true', '3'),
    ('2011-10-04', '4'),
    ('false', '1'),
    ('ascii', '2'),
    ('false', '3'),
    ('2011-10-01', '4'),
    ('false', '1'),
    ('text', '2'),
    ('true', '3'),
    ('2011-10-02', '4'),
    ('false', '1'),
    ('binary', '2'),
    ('false', '3'),
    ('2011-10-20', '4'),
    ('doc', '2'),
    ('false', '3'),
    ('2011-10-20', '4'),
    ('rtf', '2'),
    ('2011-10-20', '4');

填充entity_attributevalue表

INSERT INTO entity_attributevalue 
    (entity_id, attributevalue_id)
VALUES
    ('1', '1'),
    ('1', '2'),
    ('1', '3'),
    ('1', '4'),
    ('2', '5'),
    ('2', '6'),
    ('2', '7'),
    ('2', '8'),
    ('3', '9'),
    ('3', '10'),
    ('3', '11'),
    ('3', '12'),
    ('4', '13'),
    ('4', '14'),
    ('4', '15'),
    ('4', '16'),
    ('5', '17'),
    ('5', '18'),
    ('5', '19'),
    ('5', '20'),
    ('6', '21'),
    ('6', '22'),
    ('6', '23'),
    ('7', '24'),
    ('7', '25');

显示所有记录

SELECT * 
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id;

id 标题作者创建时间实体ID 属性值ID ID 值属性ID ID 名称类型 1 测试文件乔 2011-01-01 00:00:00 1 1 1 true 1 1 只读布尔 1 测试文件乔 2011-01-01 00:00:00 1 2 2 xls 2 2 文件格式文本 1 测试文件乔 2011-01-01 00:00:00 1 3 3 false 3 3 私有布尔 1 测试文件乔 2011-01-01 00:00:00 1 4 4 2011-10-03 4 4 最后修改日期日期 2 长篇小说玛丽 2011-02-01 00:00:00 2 5 5 true 1 1 只读布尔 2 长篇小说玛丽 2011-02-01 00:00:00 2 6 6 json 2 2 文件格式文本 2 长篇小说玛丽 2011-02-01 00:00:00 2 7 7 true 3 3 私有布尔 2 长篇小说玛丽 2011-02-01 00:00:00 2 8 8 2011-10-04 4 4 最后修改日期日期 3 短篇小说苏珊 2011-03-01 00:00:00 3 9 9 false 1 1 只读布尔 3 短篇小说苏珊 2011-03-01 00:00:00 3 10 10 ascii 2 2 文件格式文本 3 短篇小说苏珊 2011-03-01 00:00:00 3 11 11 false 3 3 私有布尔 3 短篇小说苏珊 2011-03-01 00:00:00 3 12 12 2011-10-01 4 4 最后修改日期日期 4 利润损失表比尔 2011-04-01 00:00:00 4 13 13 false 1 1 只读布尔 4 利润损失表比尔 2011-04-01 00:00:00 4 14 14 text 2 2 文件格式文本 4 利润损失表比尔 2011-04-01 00:00:00 4 15 15 true 3 3 私有布尔 4 利润损失表比尔 2011-04-01 00:00:00 4 16 16 2011-10-02 4 4 最后修改日期日期 5 月度预算乔治 2011-05-01 00:00:00 5 17 17 false 1 1 只读布尔 5 月度预算乔治 2011-05-01 00:00:00 5 18 18 binary 2 2 文件格式文本 5

数据透视表

SELECT e.*,
    MAX( IF(a.name = 'ReadOnly', av.value, NULL) ) as 'ReadOnly',
    MAX( IF(a.name = 'FileFormat', av.value, NULL) ) as 'FileFormat',
    MAX( IF(a.name = 'Private', av.value, NULL) ) as 'Private',
    MAX( IF(a.name = 'LastModified', av.value, NULL) ) as 'LastModified'
FROM `entity` e
LEFT JOIN `entity_attributevalue` ea ON ea.entity_id = e.id
LEFT JOIN `attributevalue` av ON ea.attributevalue_id = av.id
LEFT JOIN `attribute` a ON av.attribute_id = a.id
GROUP BY e.id;

编号  标题            作者    创建时间            只读      文件格式     私有    最后修改时间
1     测试文件        乔       2011-01-01 00:00:00 是        xls         否      2011-10-03
2     长篇小说        玛丽     2011-02-01 00:00:00 是        json        是      2011-10-04
3     短篇小说        苏珊     2011-03-01 00:00:00 否        ascii       否      2011-10-01
4     盈亏表          比尔     2011-04-01 00:00:00 否        text        是      2011-10-02
5     月度预算        乔治     2011-05-01 00:00:00 否        binary      否      2011-10-20
6     论文            简      2012-04-01 00:00:00 NULL      binary      否      2011-10-20
7     散文            约翰     2012-03-01 00:00:00 NULL      binary      NULL   2011-10-20
8     文章            丹       2012-12-01 00:00:00 NULL      NULL        NULL   NULL

- thoroc

0

然而，有解决方案可以将行用作列，即转置数据。这需要使用查询技巧在纯SQL中执行，或者您必须依赖于某些仅在特定数据库中可用的功能，使用透视表（或交叉表）。

例如，您可以在Oracle（11g）中查看如何执行此操作。

编程版本将更容易维护和制作，并且还将与任何数据库一起使用。

- MarmouCorp

请解释一下，如果将属性值存储在表的一列中，并使用分隔符，那么我们应该将AttributeName存储在哪里。如果我们将AttributeName和Value都存储在两个列中，并使用分隔符进行分隔（在一个文件有多个属性的情况下），那么我该如何转置这些值。 - Ashok

由于您在运行时不知道自定义属性的数量，因此我建议采用编程方式。可以使用程序动态构建查询，或者使用联接提取所有数据并进行循环遍历。构建复杂查询会增加可维护性和调整的复杂度。 - MarmouCorp

-2

我只能提供部分答案，因为我不熟悉MySQL。在MSSQL中，我会查看透视表或者创建一个临时表来存储过程。这可能需要一些时间和精力...

- Sascha

嗨，谢谢。但是我不太擅长编程语言，我有T-SQL的经验。你能简要地解释一下如何通过示例实现这个吗？非常感谢。 - Ashok

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- nawroth · Accepted Answer

问题提到了MySQL，实际上这个数据库管理系统有一个特殊的函数来解决这种问题：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'

这样你只需要一个数据库查询，输出易于解析。如果您想将属性存储为实际的Datetime等类型，则需要使用动态SQL，但我建议清除那些并将值存储在varchars中。