元数据上的 MySQL 字段大小
我想创建一个包含动态数据的表，它可以是日期、布尔值或文本文章的形式，
例如：
meta_key = "isActive" meta_value =“1”
或
meta_key =“theDate” meta_value = "2005 年 7 月 23 日星期六 02:16:57"
或
meta_key = "描述" meta_value =“这是一个描述，这个文本可以继续，所以我需要一个很长的字段”
问题是meta_value应该是什么类型的字段，以免插入的每个“1”使数据库膨胀太多，哪些字段是动态的，只会消耗自己长度的空间
希望我很清楚......
I want to create a table that will contain dynamic data, it can be in a form of a date, boolean or a text article
for example:
meta_key = "isActive"
meta_valu = "1"
or
meta_key = "theDate"
meta_value = "Sat Jul 23 02:16:57 2005"
or
meta_key = "description"
meta_value = "this is a description and this text can go on and on so i need a long field"
The question is what type of field should meta_value be in order to not inflate the DB too much for every "1" inserted, which fields are dynamic and will only consume the space of their own length
hope I was clear...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
如果您存储非结构化数据或文档（例如friendfeed），我只会使用非结构化数据模型，就像您建议的那样。
替代存储思路
有很多比 SQL Server 更适合非结构化数据的数据存储系统。我建议将其中之一与您现有的结构化数据库结合起来。
SQL 选项
如果您无法做到这一点并且必须在 SQL DB 中存储非结构化数据，您有几个选择，数据类型并不是真正唯一关心的问题，数据的存储方式才是。
某些结构允许读取数据的应用程序能够轻松解析数据，而无需复杂的字符串操作函数。
能够为应用程序中的数据定义模型，因此当您读取数据时，您知道自己得到了什么。
能够为
以下两个选项为这两个挑战提供了解决方案...
XML - xml 数据类型
您需要考虑要存储的数据。如果您需要返回它并对内容执行复杂的搜索，那么 XML 是您的最佳选择。它还允许您验证存储的数据是否与定义的结构匹配（使用 dtd）。请参阅这篇文章。
http://msdn.microsoft.com/en-us/library/ms189887。 aspx
或 JSON - nvarchar(max) 数据类型
如果您需要返回此数据以在网页上显示或在 Javascript 中使用，那么存储为 JSON 将是最容易使用的。您可以轻松地将其加载到可以直接使用和操作的对象模型中。缺点是与 XPATH 相比，对数据的复杂搜索会非常慢（迭代所有对象，查找匹配的对象）。
如果您要存储来自其他语言或奇怪字符的数据，请使用 nvarchar（unicode 版本）。否则 varchar 将是最有效的。
I would only use an unstructured data model, like how you suggest, if you are storing unstructured data or documents (e.g. friendfeed).
Alternative storage thoughts
There are many more suitable data storage systems for unstructured data than SQL server. I would recommend combining one of these with your existing structured database.
SQL Options
If you can't do this and must store unstructured data in your SQL DB, you have a couple of options, the datatype isn't really the only concern, how your data is stored is.
Some structure to allow an application reading the data to be able to easily parse the data without complex string manipulation functions.
Be able to define a model for the data in your application, so when you read the data, you know what you've got.
The following 2 options provide a solution to both these challenges...
XML - xml data type
You need to concider the data you are storing. If you need to return it and perform complex searches on the contents, then XML is your best bet. It also allows you to validate that the data stored matches a defined structure (using a dtd). See this article.
http://msdn.microsoft.com/en-us/library/ms189887.aspx
or JSON - nvarchar(max) datatype
If you need to return this data for display on a webpage or use in a Javascript, then storing as JSON would be easiest to work with. You can easily load it into an object model which can be worked with directly and manipulated. The downside is that complex searches on the data will be very slow compared to XPATH (iterate through all the objects, find ones that match).
If you are storing data from other languages or strange characters go with nvarchar (unicode version). Otherwise varchar would be most efficient.
您可能需要 VARCHAR 字段类型。
You probably want the VARCHAR field type.
希望这有帮助：
Hope this helps:
这些是用作临时表还是实时表？
这是一个我还没有看到的想法，但如果您主要担心大小爆炸，但不关心让程序做一些额外的工作，那么可能对您有用。但是，我认为最佳实践是使用自己的表中的字段（例如，OrderDate）创建这些元键，然后您可以拥有描述、日期等。包罗万象的数据库表可能会带来很多麻烦。
创建元表，使用这个想法：
MetaID
元密钥
元变量(255)
元文本
MetaDate
varchar、文本和日期可以为 null。
让插入程序决定将其放入哪个单元格，数据库调用将仅显示不为空的字段。短项目将采用 varchar 形式，长项目将采用文本形式，您可以使用日期来更改日期的显示方式。
Are these being used as temp tables or live tables?
Here's an idea I haven't seen yet, but MAY work for you, if you are primarily worried about size explosion, but don't care about having the program do a little extra work. However, I believe the best practice is to create these meta keys with fields in their own table (for example, OrderDate), and then you can have descriptions, dates, etc. A catchall DB table can make for a lot of headaches.
Create the meta table, using this idea:
MetaID
MetaKey
MetaVarchar(255)
MetaText
MetaDate
varchar, text, and date can be null.
Let the inserting program decide what cell to put it in, and the database call will just show whatever field wasn't null. Short items will be in varchar, long ones in text, and date you can use so that you can change the way dates are shown.
在 MySQL 中，我通常使用 blob 数据类型，我存储用于网站的动态类的序列化版本。
Blob 基本上是二进制数据，因此一旦您弄清楚如何序列化和反序列化数据，您在很大程度上应该对此感到满意。
请注意，对于大量数据，它的效率确实会降低很多，但话又说回来，它不需要您更改整个结构。
以下是对 blob 数据类型的更好解释：
http://dev.mysql.com/doc/refman/5.0/ en/blob.html
In MySQL I generally use the blob datatype which I store a serialized version of a dynamic class that I use for a website.
A blob is basically binary data, so once you figure out how to serialize and de-serialize the data you should for the most part be golden with that.
Please note that for large amounts of data it does become much less efficient but then again it doesn't require you to change your whole structure.
Here is a better explanation of the blob data type:
http://dev.mysql.com/doc/refman/5.0/en/blob.html