存储“额外”的最佳方式MySQL 中的用户数据？
我正在为 CMS 的用户模块添加一个新功能，但我遇到了障碍……或者我猜，这是一个岔路口，我想在做出任何承诺之前从 stackoverflow 获得一些意见。
基本上，我希望允许管理员添加新的“额外”用户字段，用户可以在注册时填写这些字段，在其个人资料中进行编辑，和/或由其他模块控制。例如，生日字段、对自己的冗长描述，或者用户在网站上获得的积分。不用说，存储的数据会多种多样，范围可以从大量文本到小整数值。更糟糕的是 - 我希望有一个选项来搜索这些数据。
既然这样——最好的方法是什么？现在我倾向于拥有一个包含以下列的表格。
userid, refFieldID, varchar, tinyint, smallint, int, text, date, datetime, etc.
我更喜欢这个，因为它会使搜索速度明显加快，并且参考表（其中包含所有字段的数据，例如字段的名称，是否可搜索等）可以参考在以下情况下应使用哪一列：存储该字段的数据。
另一个想法是向我建议的，我已经在其他解决方案中看到过使用（vBulletin 就是其中之一，尽管我看到其他人的名字现在我记不清了），其中您只有用户 ID、参考 ID 和 medtext场地。我对 MySQL 的了解不够，无法肯定地说这一点，但这种方法似乎搜索速度会更慢，而且可能会有更大的开销。
那么哪种方法是“最好的”？我还缺少另一种方法吗？无论我最终使用哪种方法，它都需要快速搜索，而不是大量搜索（一点点开销就可以了），并且最好允许对数据使用复杂的查询。
I'm adding a new feature to my user module for my CMS and I've hit a road block... Or I guess, a fork in the road, and I wanted to get some opinions from stackoverflow before I commit to anything.
Basically I want to allow admins to add new, 'extra' user fields that users can fill out on registration, edit in their profile, and/or be controlled by other modules. An example of this would be a birthday field, a lengthy description of themselves, or maybe points the user has earned on the site. Needless to say, the data stored will be varied and can range from large amounts of text, to a small integer value. To make matters worse - I want there to be the option to search this data.
With that out of the way - what would be the best way to do this? Right now I'm leaning towards having a table with the following columns.
userid, refFieldID, varchar, tinyint, smallint, int, text, date, datetime, etc.
I would prefer this as it would make searching significantly faster, and the reference table (Which holds all of the field's data, such as the name of the field, whether it's searchable or not, etc.) can reference which column should be used when storing data for that field.
The other idea, which was suggested to me and I've seen used in other solutions (vBulletin being one, although I have seen others whose names escape me at the moment), where you just have the userid, reference id, and a medtext field. I don't know enough about MySQL to say this with any certainty, but this method seems like it would be slower to search, and possibly have a larger overhead.
So which method would be 'best'? Is there another method I'm missing? Whichever method I end up using, it needs to be fast to search, not massive (A tiny bit of overhead is fine), and preferably allow complex queries used against the data.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我同意键值表可能是最好的解决方案。我的第一个倾向是只存储一个文本列，就像 vBulletin 所做的那样。但是，如果您想添加数据存储的功能，使其更加可扩展和可搜索，就像您所布置的那样，我可能建议：
这些字段将允许您在表中存储几乎任何类型的数据，但无需扩大表的宽度**（就像 varchar 一样）并避免任何冗余存储（例如tinyint和mediumint等）。仍然可以使用全文索引或常规有限长度索引（例如
index longtext_storage(8)）合理搜索长文本字段中存储的文本。** 所有 blob 值（例如长文本）都独立于主表存储。
I agree that a key-value table is probably the best solution. My first inclination would be to just store a text column, like vBulletin did. But, if you wanted to add the ability for the data store to be a bit more extensible and searchable like you've laid out, I might suggest:
These fields will allow you to store nearly any type of data in the table but without inflating the width of table** (like a varchar would) and avoid any redundant storage (like having tinyint and mediumint etc). The text stored in the longtext field can still be reasonably searched using a fulltext index or a regular limited length index (e.g.
index longtext_storage(8)).** all blob values, such as longtext, are stored independently from the main table.
一种可能适合您的技术是将这些任意数据存储为文本，采用 JSON、XML 或 YAML 等表示法。此决定取决于您需要如何访问数据：如果您只查找每个用户的完整用户数据块，那么它可能是理想的。如果需要对用户数据中的特定字段运行 SQL 查询，则需要使用纯 SQL 或混合方法。
许多更新的、高度可扩展的“NoSQL”系统似乎更喜欢 JSON 数据（例如 MongoDB、CouchDB 和 Project Voldemort）。它简洁明了，您可以创建任意复杂的结构，包括地图（JSON 对象）和列表（JSON 数组）。
One technique that might work for you is to store this arbitrary data as text, in some notation like JSON, XML, or YAML. This decision depends on how you'll need to access the data: if you only look up each user's full chunk of user data, it could be ideal. If you need to run SQL queries on specific fields in the user data, you'll need to use a pure SQL or a hybrid approach.
Many of the newer, highly scalable "NoSQL" systems seem to favor JSON data (eg, MongoDB, CouchDB, and Project Voldemort). It's nice and terse, and you can create arbitrarily complex structures including maps (JSON objects) and lists (JSON arrays).