在MySQL表中存储数组

I have a 5 level multidimensional array. The number of keys in the array fluctuates but I need to store it in a database so I can access it with PHP later on. Are there any easy ways to do this?

我有一个5级多维数组。数组中的键数量有所波动，但我需要将其存储在数据库中，以便稍后可以使用PHP访问它。有没有简单的方法来做到这一点？

My idea was to convert the array into a single string using several different delimiters like #* and %* and then using a series of explode() to convert the data back into an array when I need it.

我的想法是使用几个不同的分隔符（如＃*和％*）将数组转换为单个字符串，然后使用一系列explode（）在需要时将数据转换回数组。

I haven't written any code at this point because I'm hoping there will be a better way to do this. But I do have a potential solution which I tried to outline below:

我此时还没有编写任何代码，因为我希望有更好的方法来做到这一点。但我确实有一个潜在的解决方案，我试图在下面概述：

here's an overview of my array:

这是我的数组的概述：

n=button number
i=item number
btn[n][0]               = button name
btn[n][1]               = button desc
btn[n][2]               = success or not (Y or N)
btn[n][3]               = array containing item info
    btn[n][3][i][0]     = item intput type (Default/Preset/UserTxt/UserDD)
    btn[n][3][i][1]     = array containing item value - if more than one index then display as drop down

Here's a run-down of the delimiters I was going to use:

这是我要使用的分隔符的破坏：

#*Button Title                      //button title
    &*val1=*usr1234                 //items and values
    &*val2=*FROM_USER(_TEXT_$*name:)        //if an items value contains "FROM_USER" then extract the data between the perenthesis
    &*val3=*FROM_USER(_TEXT_$*Time:)        //if the datatype contains _TEXT_ then explode AGAIN by $* and just display a textfield with the title
    &*val4=*FROM_USER($*name1@*value1$*name2@*value2)       //else explode AGAIN by $* for a list of name value pairs which represent a drop box - name2@*value2

//sample string - a single button
#*Button Title%*val1=*usr1234&*val2=*FROM_USER(_TEXT_$*name:)&*val3=*FROM_USER(_TEXT_$*date:)&*val4=*FROM_USER($*name1@*value1$*name2@*value2)

I hope that made sense. Either way. If anyone can give me some ideas of how to store a multidimensional array in a single database table I would REALLY appreciate it.

我希望这是有道理的。无论哪种方式。如果有人能给我一些如何在一个数据库表中存储多维数组的想法，我真的很感激。

4 个解决方案

#1

What you want is a data serialization method. Don't invent your own, there are plenty already out there. The most obvious candidates are JSON (json_encode) or the PHP specific serialize. XML is also an option, especially if your database may support it natively to some degree.

你想要的是一种数据序列化方法。不要发明自己的，已经有很多。最明显的候选者是JSON（json_encode）或PHP特定的序列化。 XML也是一种选择，特别是如果您的数据库可能在某种程度上支持它本身。

#2

Have a look at serialize or json_encode

看看serialize或json_encode

#3

The best decision for you is json_encode.

最好的决定是json_encode。

It has some advantages for json_encode beside serialize for storing in db.

除了用于存储在db中的序列化之外的json_encode，它有一些优点。

taking smaller size
缩小尺寸
if you must modify data manually in db there will be some problems with serialize, because this format stores size of values that has been serialized and modifying this values you must count and modify this params.
如果必须在db中手动修改数据，则序列化会出现一些问题，因为此格式存储已序列化的值的大小并修改此值，您必须计算并修改此参数。

#4

SQL (whether mySQL or any other variant) does not support array data types.

SQL（无论是mySQL还是其他任何变体）都不支持数组数据类型。

The way you are supposed to deal with this kind of data in SQL is to store it across multiple tables.

您应该在SQL中处理此类数据的方式是将其存储在多个表中。

So in this example, you'd have one table that contains buttonID, buttonName, buttonSuccess, etc fields, and another table that contains buttonInputType and buttonInputValue fields, as well as buttonID to link back to the parent table.

因此，在此示例中，您将拥有一个包含buttonID，buttonName，buttonSuccess等字段的表，以及另一个包含buttonInputType和buttonInputValue字段的表，以及用于链接回父表的buttonID。

That would be the recommended "relational" way of doing things. The point of doing it this way is that it makes it easier to query the data back out of the DB when the time comes.

这将是推荐的“关系”做事方式。这样做的关键在于，当时机成熟时，它可以更容易地从数据库中查询数据。

There are other options though.

还有其他选择。

One option would be to use mySQL's enum feature. Since you've got a fixed set of values available for the input type, you could use an enum field for it, which could save you from needing to have an extra table for that.

一种选择是使用mySQL的枚举功能。由于您有一组固定的值可用于输入类型，因此可以使用枚举字段，这可以使您无需为此创建额外的表。

Another option, of course, is what everyone else has suggested, and simply serialise the data using json_encode() or similar, and store it all in a big text field.

当然，另一个选择是其他人都建议的，只需使用json_encode（）或类似方法序列化数据，并将其全部存储在一个大文本字段中。

If the data is going to be used as a simple block of data, without any need to ever run a query to examine parts of it, then this can sometimes be the simplest solution. It's not something a database expert would want to see, but from a pragmatic angle, if it does the job then feel free to use it.

如果数据将被用作简单的数据块，而不需要运行查询来检查其中的部分数据，那么这有时可能是最简单的解决方案。这不是数据库专家想要看到的东西，但从实用的角度来看，如果它完成了工作，那么随意使用它。

However, it's important to be aware of the limitations. By using a serialised solution, you're basically saying "this data doesn't need to be managed in any way at all, so I can't be bothered to do proper database design for it.". And that's fine, as long as you don't need to manage it or search for values within it. If you do, you need to think harder about your DB design, and be wary of taking the 'easy' option.

但是，了解这些限制很重要。通过使用序列化解决方案，您基本上会说“这些数据根本不需要以任何方式进行管理，因此我不能为它做适当的数据库设计而烦恼。”只要您不需要管理它或在其中搜索值，那就没问题。如果你这样做，你需要更加努力地思考你的数据库设计，并警惕采用“简单”选项。

#1

#2