表中要保留多少列？ - MySQL
我在用于存储某些项目的行表设计与列表设计之间陷入困境，但决定是哪个表更容易管理，如果是列，那么最好有多少列？例如，我有对象元数据，理想情况下，每个对象需要存储的同一级别上有 45 条信息（标准化后）。那么，读/写繁重的表中 45 列好吗？它能在现实世界中大量并发读/写的情况下完美工作吗？
I am stuck between row vs columns table design for storing some items but the decision is which table is easier to manage and if columns then how many columns are best to have? For example I have object meta data, ideally there are 45 pieces of information (after being normalized) on the same level that i need to store per object. So is 45 columns in a heavry read/write table good? Can it work flawless in a real world situation of heavy concurrent read/writes?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
如果您的所有或大部分列都填充了数据并且此数字是固定的，则只需使用
45字段。45列本身并没有什么坏处。如果满足所有条件：
您有可能获得在设计时既不知道也无法预测的属性
属性仅偶尔填充（例如，每个实体 10 个或更少）
有许多可能的属性（数百个或更多）
大多数实体都没有填写任何属性
，那么您就有一个这样的称为 <代码>空间矩阵。这个（也只有这个）模型可以用
EAV表更好地表示。If all or most of your columns are filled with data and this number is fixed, then just use
45fields. It's nothing inherently bad with45columns.If all conditions are met:
You have a possibility of the the attributes which are neither known nor can be predicted at design time
The attributes are only occasionally filled (say, 10 or less per entity)
There are many possible attributes (hundreds or more)
No attribute is filled for most entities
then you have a such called
sparce matrix. This (and only this) model can be better represented with anEAVtable.“每个表有 4096 列的硬限制”，应该没问题。
"There is a hard limit of 4096 columns per table", it should be just fine.
考虑问题的“更容易管理”部分：
如果您正在收集的属性名称没有更改，那么列就可以了。即使人口稀少，磁盘空间也很便宜。
但是，如果每个项目（行）最多有 45 个属性，但这些属性可能在一个元素与另一个元素之间完全不同，那么使用行会更好。
例如，获取产品目录。一件产品可能有颜色、重量和高度。另一个可能有许多按钮或手柄。这些显然是完全不同的属性。此外，此类数据表明将添加可能仅与一组特定产品相关的新属性。在这种情况下，行要好得多。
另一种选择是使用 NoSql 并利用基于文档的数据库服务器。这将允许您在每个项目的基础上设置命名的“列”。
综上所述，行的管理将由应用程序完成。这将需要一些高级的数据库技能。列的管理将由开发商在设计时完成；对于大多数人来说，这通常更容易理清思路。
Taking the "easier to manage" part of the question:
If the property names you are collecting do not change, then columns is just fine. Even if it's sparsely populated, disk space is cheap.
However, if you have up to 45 properties per item (row) but those properties might be radically different from one element to another then using rows is better.
For example taking a product catalog. One product might have color, weight, and height. Another might have a number of buttons or handles. These are obviously radically different properties. Further this type of data suggests that new properties will be added that might only be related to a particular set of products. In this case, rows is much better.
Another option is to go NoSql and utilize a document based database server. This would allow you to set the named "columns" on a per item basis.
All of that said, management of rows will be done by the application. This will require some advanced DB skills. Management of columns will be done by the developer at design time; which is usually easier for most people to get their minds around.
我不知道我是否正确，但我曾经在MySQL中读过，如果可能的话，让你的表保持最少的列，（阅读：http://dev.mysql.com/doc/refman/5.0/en/data-size.html ），请注意：这是如果您正在使用 MySQL，我不知道他们的概念是否适用于其他 DBMS，如 oracle、firebird、posgresql 等。
您可以查看包含 45 列的表并分析您真正需要的内容，并将可选字段保留到其他表中。
希望有帮助，祝你好运
I don't know if I'm correct but I once read in MySQL to keep your table with minimum columns IF POSSIBLE, (read: http://dev.mysql.com/doc/refman/5.0/en/data-size.html ), do NOTE: this is if you are using MySQL, I don't know if their concept applies to other DBMS like oracle, firebird, posgresql, etc.
You could take a look at your table with 45 column and analyze what you truly need and leave the optional fields into other table.
Hope it helps, good luck