关于mySQL表中大量列的专家意见? [英] Expert opinions on a very large number of columns in a mySQL table?

问题描述

我正在创建一个用于在CMS中编辑网页的工具. 该工具的主要目标是为用户提供全面的灵活性.因此，可以在其中编辑大量属性-这些属性(如代码段):

I am creating a tool with which to edit web pages within a CMS. The main goal of the tool is total flexibility for the user. Therefore, a great number of properties can be edited in it - properties like these (snippet):

langbutton_menu_border_color_left 
langbutton_menu_border_width_left
langbutton_menu_border_style_left 
langbutton_menu_border_color_right

...您会发现漂移.到目前为止，我有238个这样的属性，主要是整数和短字符串. 我现在必须为数据创建一个mysql表.我有多年的Web开发经验，即使考虑将238列放入mySQL表中也是绝对的忌讳.但是，经过深思熟虑，我开始思考，为什么不呢?

... you get the drift. To date, I have 238 such properties, mostly integers and short strings. I have now to create a mysql table for the data. I have some years of web development experience, and it was an absolute taboo to even consider putting 238 columns into a mySQL table. But on second thought, I'm starting to think, why not?

这对我来说现在是最方便的事情，因为我正在将这个新工具集成到CMS中，因此它具有一组现成的输入元素，这些输入元素与单个数据库列相关联.任何其他存储属性的方式(例如，将属性分组以便将边界"属性存储在一个字段中)都需要对集合进行巨大的更改，这是我非常想避免的-我在一个大型项目中，实际上是在工作和夜晚.

It is the most convenient thing for me right now, as my CMS I am integrating this new tool in has a collection of ready-made input elements that are connected with single database columns. Any other way of storing the properties (e.g. grouping them so a "border" property is stored in one field) would require huge changes to the collection, which I would very much like to avoid - I am in a big project and literally working day and night.

我将基于XML定义创建和更改表，因此我可以管理238列表. 存储效率并不重要-预期的页数不会超过50-100. 除了使用主键一次加载单个页面外，我无需对表进行任何查询.

I would create and alter the table based on a XML definition, so I could live with administering a 238 column table. Storage efficiency is not important - the expected number of pages will not exceed 50-100. I need to make no queries on the table except for loading a single page at a time using the primary key.

因此，mySQL专家，有什么严重反对将此类数据存储在238列中的内容吗?您会期望出现问题，指数内存使用率之类的事情吗?

So, mySQL experts, is there anything seriously speaking against storing this kind of data in 238 columns? Would you expect problems, exponential memory usage, anything like that?

通常，我会将各种属性转换为完整的CSS字符串，并构建可以解析和处理此类字符串的类-这样可以大大减少数量.但是考虑时间限制吗?

Usually, I would translate the various properties into full CSS strings, and build classes that can parse and deal with such strings - that would reduce the number greatly. But considering the time constraints?

推荐答案

从理论上讲，mySQL现在限制为表中的4096列(考虑其他约束，例如默认NULL值等，少了一点).因此，您有很大的利润空间. 就个人而言，在网络开发人员中，我尝试使列数< 50.我看到具有100多个列的表，并且可以正常工作，但是很难维护这样的表. 如果您不必在该列上进行搜索，请考虑在php数组上进行序列化并将值存储在TEXT中.它更快，更灵活.

In theory, mySQL now is limited to 4096 columns in a table (a little bit less considering other constraints, i.e. default NULL values etc.). So, you have a quite big margin. Personally, in a web dev i try to keep # of columns < 50. I saw tables with 100+ columns, and it worked, but it's very hard to maintain such tables. If you don't have to search on that columns, consider serialize on php array and store values in TEXT. It's faster and more flexible.

这篇关于关于mySQL表中大量列的专家意见?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！