How many fields are OK to have in a MySQL table?
在MySQL表中可以有多少个字段?
I have a form to get information from users, it is divided into 6 or 7 unites, but all related to each other. They are about 100 fields.
我有一个表单来获取用户的信息,它分为6或7个单元,但都相互关联。他们大约有100个领域。
Do you recommend to keep all of them in one table?
你建议把所有这些都放在一张桌子里吗?
Thank you all for your responses,well my table's data are about some personal information of users,all the work that is needed to be done is to get them from the user,save and show them and have the edit option. should i use the normalization? doesn't it increase the process time in my situation?
谢谢大家的回复,我的表的数据是关于用户的一些个人信息,所有需要完成的工作是从用户那里获取,保存并显示它们并拥有编辑选项。我应该使用规范化吗?它不会增加我的处理时间吗?
8 个解决方案
#1
9
Providing you are following database normalization, you generally should be ok - although you may find some performance issues down the road.
如果你正在遵循数据库规范化,你通常应该没问题 - 虽然你可能会发现一些性能问题。
To me, it seems like perhaps there could be some normalization?
对我来说,似乎可能有一些正常化?
Also, you should consider how many of these columns will just have null values, and what naming conventions you are using (not just name, name2 etc)
此外,您应该考虑这些列中有多少只有空值,以及您使用的命名约定(不仅仅是名称,名称2等)
In case you want to read into it more.:
如果您想要阅读更多:
数据规范化基础知识
MySql :: An introduction to database normalization
MySql ::数据库规范化简介
#2
4
MySQL supports up to 4096 columns per table. Dont use a field in main table if it often takes empty values.
MySQL每个表最多支持4096列。如果它通常采用空值,请不要在主表中使用字段。
-
if a column takes NULL no problem but if one stored as " " its memory wastage.
如果一列采用NULL没有问题,但如果一个存储为“”其内存浪费。
-
Too often null values means optional field data then why should one store it in main table.
通常空值表示可选字段数据,那么为什么要将它存储在主表中。
Thats what i meant in the statement.
这就是我在声明中的意思。
#3
1
There's no general rule to that. However, 100 columns definitely hints that your DB design is plain wrong. You should read about normalization and database design before continuing. I have a DB design with ~150 columns split up into nearly 40 tables, just to give you an idea.
对此没有一般规则。但是,100列绝对暗示您的数据库设计是完全错误的。在继续之前,您应该阅读规范化和数据库设计。我有一个数据库设计,有大约150个列,分成近40个表,只是为了给你一个想法。
#4
1
For data that does not need to be indexed or searched I create a single column in my database like col_meta which holds a serialized PHP array of values, it saves space and can expand or contract as needed. Just a thought.
对于不需要索引或搜索的数据,我在数据库中创建了一个列,如col_meta,它包含一个序列化的PHP数组值,它可以节省空间并可以根据需要进行扩展或收缩。只是一个想法。
#5
0
If they all relate and can not be normalised (not to mention nothing > 1 is serialised) then I guess it will be OK.
如果它们都相关并且不能被标准化(更不用说没有> 1被序列化)那么我想它会没问题。
Are you sure some things can't be split into multiple tables though? Can you provide a sample of the information you are gathering?
你确定有些东西不能分成多个表吗?您能提供您正在收集的信息样本吗?
Could you split things into such as
你能把事情分成两部分吗?
- user_details
- user_car_details
- ...
#6
0
I would typically start splitting them into separate tables after around 20-30 columns. However, it is really driven by how you are going to use the data.
我通常会在大约20-30列后开始将它们分成单独的表。但是,它实际上是由你如何使用数据驱动的。
If you always need all the columns, then splitting them out into separate tables will add unnecessary complication and overhead. However, if there are groups of columns that tend to be accessed together, then splitting along those lines with a 1-1 relationship between the tables might be useful.
如果您总是需要所有列,那么将它们拆分为单独的表将增加不必要的复杂性和开销。但是,如果存在易于一起访问的列组,则沿着这些表之间的1-1关系拆分可能是有用的。
This technique can be useful if performance is a concern, especially if you have large text columns in your table.
如果要考虑性能,此技术非常有用,尤其是如果表中包含大型文本列。
Lastly, I would echo the other posters comments about normalisation. It is relatively rare to have 100s of columns in one table and it may be that you need to normalise the table.
最后,我会回应其他海报关于规范化的评论。在一个表中有100个列是相对罕见的,可能需要对表进行规范化。
For example columns, SETTING_1, SETTING_2, SETTING_3, SETTING_4 might be better in a separate SETTINGS table, which has a 1-many relationship to the original table.
例如,SETTING_1,SETTING_2,SETTING_3,SETTING_4列可能在单独的SETTINGS表中更好,该表与原始表具有1-many关系。
#7
0
Thank you all,so i think it's better for me to merge some of my data into one column,which save me more space and decrease overhead
谢谢大家,所以我认为将我的一些数据合并到一个列中更好,这样可以节省更多空间并减少开销
#8
0
I've noticed a performance loss around 65 Columns in a DB with around 10 000 Rows. But could be lack of serverstructure or something else.
我注意到数据库中有大约65列的性能损失,大约有10 000行。但可能缺乏服务器结构或其他东西。
#1
9
Providing you are following database normalization, you generally should be ok - although you may find some performance issues down the road.
如果你正在遵循数据库规范化,你通常应该没问题 - 虽然你可能会发现一些性能问题。
To me, it seems like perhaps there could be some normalization?
对我来说,似乎可能有一些正常化?
Also, you should consider how many of these columns will just have null values, and what naming conventions you are using (not just name, name2 etc)
此外,您应该考虑这些列中有多少只有空值,以及您使用的命名约定(不仅仅是名称,名称2等)
In case you want to read into it more.:
如果您想要阅读更多:
数据规范化基础知识
MySql :: An introduction to database normalization
MySql ::数据库规范化简介
#2
4
MySQL supports up to 4096 columns per table. Dont use a field in main table if it often takes empty values.
MySQL每个表最多支持4096列。如果它通常采用空值,请不要在主表中使用字段。
-
if a column takes NULL no problem but if one stored as " " its memory wastage.
如果一列采用NULL没有问题,但如果一个存储为“”其内存浪费。
-
Too often null values means optional field data then why should one store it in main table.
通常空值表示可选字段数据,那么为什么要将它存储在主表中。
Thats what i meant in the statement.
这就是我在声明中的意思。
#3
1
There's no general rule to that. However, 100 columns definitely hints that your DB design is plain wrong. You should read about normalization and database design before continuing. I have a DB design with ~150 columns split up into nearly 40 tables, just to give you an idea.
对此没有一般规则。但是,100列绝对暗示您的数据库设计是完全错误的。在继续之前,您应该阅读规范化和数据库设计。我有一个数据库设计,有大约150个列,分成近40个表,只是为了给你一个想法。
#4
1
For data that does not need to be indexed or searched I create a single column in my database like col_meta which holds a serialized PHP array of values, it saves space and can expand or contract as needed. Just a thought.
对于不需要索引或搜索的数据,我在数据库中创建了一个列,如col_meta,它包含一个序列化的PHP数组值,它可以节省空间并可以根据需要进行扩展或收缩。只是一个想法。
#5
0
If they all relate and can not be normalised (not to mention nothing > 1 is serialised) then I guess it will be OK.
如果它们都相关并且不能被标准化(更不用说没有> 1被序列化)那么我想它会没问题。
Are you sure some things can't be split into multiple tables though? Can you provide a sample of the information you are gathering?
你确定有些东西不能分成多个表吗?您能提供您正在收集的信息样本吗?
Could you split things into such as
你能把事情分成两部分吗?
- user_details
- user_car_details
- ...
#6
0
I would typically start splitting them into separate tables after around 20-30 columns. However, it is really driven by how you are going to use the data.
我通常会在大约20-30列后开始将它们分成单独的表。但是,它实际上是由你如何使用数据驱动的。
If you always need all the columns, then splitting them out into separate tables will add unnecessary complication and overhead. However, if there are groups of columns that tend to be accessed together, then splitting along those lines with a 1-1 relationship between the tables might be useful.
如果您总是需要所有列,那么将它们拆分为单独的表将增加不必要的复杂性和开销。但是,如果存在易于一起访问的列组,则沿着这些表之间的1-1关系拆分可能是有用的。
This technique can be useful if performance is a concern, especially if you have large text columns in your table.
如果要考虑性能,此技术非常有用,尤其是如果表中包含大型文本列。
Lastly, I would echo the other posters comments about normalisation. It is relatively rare to have 100s of columns in one table and it may be that you need to normalise the table.
最后,我会回应其他海报关于规范化的评论。在一个表中有100个列是相对罕见的,可能需要对表进行规范化。
For example columns, SETTING_1, SETTING_2, SETTING_3, SETTING_4 might be better in a separate SETTINGS table, which has a 1-many relationship to the original table.
例如,SETTING_1,SETTING_2,SETTING_3,SETTING_4列可能在单独的SETTINGS表中更好,该表与原始表具有1-many关系。
#7
0
Thank you all,so i think it's better for me to merge some of my data into one column,which save me more space and decrease overhead
谢谢大家,所以我认为将我的一些数据合并到一个列中更好,这样可以节省更多空间并减少开销
#8
0
I've noticed a performance loss around 65 Columns in a DB with around 10 000 Rows. But could be lack of serverstructure or something else.
我注意到数据库中有大约65列的性能损失,大约有10 000行。但可能缺乏服务器结构或其他东西。