mysql：存储任意数据

Question

背景：
我问了一个关于堆栈溢出的问题，关于在进行此对话时动态创建表：

这听起来是个糟糕的主意！事实上，它闻起来就像这个。
你到底想用这个做什么？ – 德塞兹

<小时> @deceze：非常正确，但是，您还如何存储这些 CSV 文件的内容。
它们必须存储在mysql中以便建立索引。 关于它们唯一可靠的事实是它们都有一个标准格式的移动栏。
CSV 可以具有任意数量的列和任意数量的行。
它们的范围可以（毫不夸张）从单行、35 列 CSV 到 80k 行单列 CSV。我对其他想法持开放态度。 ——海尔伍德 <小时> 有很多解决方案，从属性值模式到 JSON 存储和 NoSQL 存储。打开一个 关于它的新问题。无论你做什么 不过，不要动态创建 桌子！ – 德塞兹

问题：
所以我的问题是， 您认为存储这些数据的最佳方式是什么？
您是否同意deceze关于不创建动态表的观点？

Answer 1

存储任意长记录的一个非常简单的模式是这样的：

Table `records`
===============
id
created
... other meta data ...

Table `record_attributes`
=====================
id
record_id
attribute
value

因此 CSV 记录可以这样存储：

"Foo","Bar","Baz"     // column names
42,"Lorem","Jerry"    // first record
7,"Ipsum","Tom"       // second record
...

records(1, '2010-1-17', ...)
record_attributes(1, 1, 'Foo', 42)
record_attributes(2, 1, 'Bar', 'Lorem')
record_attributes(3, 1, 'Baz', 'Jerry')

records(2, '2010-1-17', ...)
record_attributes(4, 2, 'Foo', 7)
record_attributes(5, 2, 'Bar', 'Ipsum')
record_attributes(6, 2, 'Baz', 'Tom')

另一种方法是将记录数据作为 JSON 打包的 blob 存储在单个列中。如果您不需要搜索数据，这是最紧凑的方式，尽管不是非常 RDBMS。

最合适的可能是 NoSQL 数据库（如果您有该选项）。

Answer 2

XML 文件可以处理这种事情吗？使用XQuery使其在语言结构上与SQL相似，并且XML在动态添加数据方面没有问题。

Answer 3

您还可以查看 Amazon 的 SimpleDB。它是专门为此目的而设计的。它允许您向所有记录和索引添加任意属性。我确信 NoSQL 领域可能还有其他解决方案。

Answer 4

我想详细说明 deceze 的记录/记录属性答案，但评论还不够……

这让人想起 SimpleDB 的项目和属性模型。如果您来自普通的 RDB 世界，请查看 SimpleDB 文档，了解一些您需要考虑的奇怪之处，例如：

• 所有值都存储为文本，以便排序（或选择值范围）对于非字符串数据类型（通常是数字和日期），您需要采取一些不寻常的数据步骤，包括零填充和偏移量，以便数据“按字典顺序”正确排序。

• 考虑您的查询是什么样子。要获取具有 Color='Red' 和 Size>3 等属性的项目，您可以从以下内容开始：

SELECT Items.*, Sizes.Value AS Size
来自项目
INNER JOIN 属性作为颜色 ON Items.ItemID=Attributes.ItemID AND Attributes.Name='Color'
INNER JOIN 属性 AS Sizes ON Items.ItemID=Attributes.ItemID AND Attributes.Name='Size'
WHERE Colors.Value='Red' AND Sizes.Value>'003'

您可以通过几种替代方式构造此查询，但需要注意的主要事项是：

• 您要过滤的属性越多，就越多加入您需要的。请注意，您不能简单地执行以下操作： SELECT ... FROM Items INNER JOIN Attributes USING ItemID WHERE (Attributes.Name='Color' AND Attributes.Value='Red') AND (Attributes.Name='Size' AND Attributes. Value>'003') -- 一旦你看到它写出来，这是不言而喻的

• 如果你想在响应中添加额外的属性，你需要添加更多的连接（我包括了 Size 来表明它对于以下之一来说很简单已经加入的属性）。但是，如果您想要检索包含所选项目的大量属性的列的响应，该怎么办？查询将开始变得更加复杂。至少 SimpleDB 会透明地为您处理这些内容，以便对查询的响应看起来像您所期望的那样，其中包含指定属性的列。

关键是，以这种方式存储数据相当容易，但查询它却变得困难。如果您的数据集变大，您可能需要考虑索引属性的正确方法。