如何支持MySQL实体属性值模式 [英] How to pivot a MySQL entity-attribute-value schema

问题描述

我需要设计存储文件的所有元数据（即文件名，作者，标题，创建日期）和自定义元数据（已由用户添加到文件中的表，例如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.

要存储这个，我创建了一个基表（具有文件的所有常见元数据），一个属性表（包含可以在文件上设置的附加的可选属性）和一个 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'

这样你只需要一个DB查询，输出很容易解析。如果要将属性存储为数据库中的实时日期时间等等，那么您需要使用动态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.

这篇关于如何支持MySQL实体属性值模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！