输入未知的字段数到mysql - 最好的结构为此？ [英] input unknown number of fields into mysql - best structure for this?

问题描述

我需要存储从多个表单发送的数据 - 有趣的是，这些表单将由表单构建器创建，我正在计划让用户访问，我不知道有多少个字段将在表单或用户将要提交的字段数。我已经想到了一个类似于以下的表结构（表单本身被保存为包含的PHP）：

I need to store data that has been sent from multiple forms - the fun bit is, as these forms will be created by a form builder I am planning on giving users access to, I have no idea how many fields will be in the form, or how many fields the user will be submitting. I have thought about a table structure similar to the following (the form itself is saved as php for include):

| FormId | FormType | InputID | InputType | LongText | Text | Date |

FormId与存储表单文件等的位置的另一个表相关（例如下面），输入ID与页面上的输入相关。

FormId is relevant to another table storing location of the form file etc for include (e.g below), Input ID is relevant to the input on the page.

| FormId | FormType | PathToForm | DateCompleted |...etc

从formId 1输入a可能如下所示：

input "a" from formId 1 might look like:

<input id="a" name="a" type="text" value="example"/>

而从formId 2输入a可能如下所示：

whereas input "a" from formId 2 might look like:

<textarea name="a" id="a"></textarea>

我可以写入这样一张表：

Which I can write into a table like this:

| FormId | FormType | InputID | InputType | LongText |   Text  | Date |
--------------------------------------------------------------------------
|   1    |     1    |    a    |    text   |   NULL   | example | NULL |
|   2    |     2    |    a    |  textarea |  example |   NULL  | NULL |

每个表单上的输入数量可能是无限的 - 不同表单模板上的输入可能是每次不同的类型。

The number of "inputs" on each form is potentially unlimited - the inputs on different form templates may be of different "type" each time.

我的问题（终于！） - 这是我可以做的最好的方法吗？我知道存储每行一个表单的输出不是完全理想的 - 但是没有数百个表，并且没有丢失如果我以后需要（FormType）有用的数据的可能性，我'我不知道有什么其他的方式来组织。没有真正遇到过这样的事情，对不起，如果它听起来很傻！

My question (finally!) - is this the best way I can be doing this? I know storing output of a form, per line, isn't exactly ideal - however without having hundreds of tables and, without loosing the possibility of pulling the data usefully should I ever need to in the future (by "FormType") I'm not sure of any other way to structure. Not really come across anything like this before, sorry if it sounds silly!

推荐答案

有一些在mysql 5.7定制为这个情况。 JSON类型。

There is something in mysql 5.7 tailor made for this situation. The JSON type.

您可以做的是将表单转换为关联数组，然后将其保存在JSON字段中。

What you can do is convert your form to an associative array and then save that in the JSON field.

有一些注意事项，虽然可搜索的这个领域在正常的varchar列中的搜索效率并不高。另外，如果要在。

There are a few caveats though, this field though searchable isn't as efficient at searches as would a normal varchar column. Additionally if you want to use it in a join you are in deep trouble.

这篇关于输入未知的字段数到mysql - 最好的结构为此？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！