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

2021-11-20 00:00:00 pivot mysql database-design entity-attribute-value

我需要设计表格来存储文件的所有元数据(即文件名、作者、标题、创建日期)和自定义元数据(已由用户添加到文件中，例如 CustUseBy、CustSendBy).无法预先设置自定义元数据字段的数量.实际上，确定在文件上添加了哪些自定义标签以及添加了多少自定义标签的唯一方法是检查表中存在的内容.

I need to design tables which stores all the metadata of files (i.e., file name, author, title, date created), and custom metadata (which has been added to files by users, e.g. CustUseBy, CustSendBy). The number of custom metadata fields cannot be set beforehand. Indeed, the only way of determining what and how many custom tags have been added on files is to examine what exists in the tables.

为了存储它，我创建了一个基表(包含文件的所有通用元数据)、一个 Attributes 表(保存可以在文件上设置的附加可选属性)和一个 FileAttributes 表(为文件的属性赋值).

To store this, I have created a base table (having all common metadata of files), an Attributes table (holding additional, optional attributes that may be set on files) and a FileAttributes table (which assigns a value to an attribute for a file).

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');

现在的问题是我想以这样的方式显示数据:

Now the problem is I want to show the data in a manner like this:

FileId, Title, Author, CustomAttri1, CustomAttr2, ...
F001    Dox    vinay   Akash         2009/03/02   ...
F002    Excel  Ajay

什么查询会产生这个结果?

What query will generate this result?

推荐答案

问题提到了 MySQL，实际上这个 DBMS 对这类问题有一个特殊的功能:GROUP_CONCAT(expr).查看 MySQL 参考手册按功能分组.该功能是在 MySQL 4.1 版中添加的.您将在查询中使用 GROUP BY FileID.

The question mentions MySQL, and in fact this DBMS has a special function for this kind of problem: GROUP_CONCAT(expr). Take a look in the MySQL reference manual on group-by-functions. The function was added in MySQL version 4.1. You'll be using GROUP BY FileID in the query.

我不太确定您希望结果看起来如何.如果您希望为每个项目列出每个属性(即使未设置)，这将更加困难.但是，这是我的建议:

I'm not really sure about how you want the result to look. If you want every attribute listed for every item (even if not set), it will be harder. However, this is my suggestion for how to do it:

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;

这会以相同的顺序为您提供所有属性，这可能很有用.输出将如下所示:

This gives you all attributes in the same order, which could be useful. The output will be like the following:

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

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

This way you only need one single DB query, and the output is easy to parse. If you want to store the attributes as real Datetime etc. in the DB, you'd need to use dynamic SQL, but I'd stay clear from that and store the values in varchars.

相关文章