在 MySQL 数据库中存储数组的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9299658/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to store an array in MySQL database?
提问by gordyr
Part of my app includes volume automation for songs.
我的应用程序的一部分包括歌曲的音量自动化。
The volume automation is described in the following format:
卷自动化按以下格式描述：
[[0,50],[20,62],[48,92]]
I consider each item in this array a 'data point' with the first value containing the position in the song and the second value containing the volume on a scale of 0-100.
我认为这个数组中的每个项目都是一个“数据点”，第一个值包含歌曲中的位置，第二个值包含 0-100 的音量。
I then take these values and perform a function client-side to interpolate this data with 'virtual' data points in order to create a bezier curve allowing smooth volume transition as an audio file is playing.
然后，我采用这些值并在客户端执行一个函数，将这些数据与“虚拟”数据点进行插值，以创建贝塞尔曲线，从而在播放音频文件时实现平滑的音量过渡。
However, the need has arisen to allow a user to save this automation into the database for recall at a later date.
然而，需要允许用户将这种自动化保存到数据库中以供日后调用。
The datapoints can be unlimited (though in reality should never really exceed around 40-50 with most being less than 10)
数据点可以是无限的（尽管实际上永远不应该真正超过 40-50，大多数小于 10）
Also how should I handle the data? Should it be stored as is, in a text field? Or should I process it in some way beforehand for optimum results?
另外我应该如何处理数据？它应该按原样存储在文本字段中吗？或者我应该事先以某种方式处理它以获得最佳结果？
What data type would be best to use in MySQL to store an array?
在 MySQL 中最好使用哪种数据类型来存储数组？
回答by Mikhail
Definitely not a textfield, but a varchar-- perhaps. I wouldn't recommend parsing the results and storing them in individual columns unless you want to take advantage of that data in database sense -- statistics etc.
绝对不是一个text领域，而是一个varchar——也许。我不建议解析结果并将它们存储在单独的列中，除非您想利用数据库意义上的数据——统计等。
If you never see yourself asking "What is the average volume that users use?" then don't bother parsing it.
如果您从未见过自己问“用户使用的平均音量是多少？” 然后不要打扰解析它。
To figure out how to store this data ask yourself "How will i use it later?" If you will obtain the array and need to utilize it with PHP you can use serializefunction. If you will use the values in JavaScript then JSON encoding will probably be best for you (plus many languages know how to decode it)
要弄清楚如何存储这些数据，请问自己“以后我将如何使用它？” 如果您将获得数组并需要在 PHP 中使用它，您可以使用序列化函数。如果您将使用 JavaScript 中的值，那么 JSON 编码可能最适合您（而且许多语言都知道如何对其进行解码）
Good luck!
祝你好运！
回答by Herobrine
I suggest you to take a look at the JSON data type. This way you can store your array in a more efficient way than text or varchar, and you can access your data directly form MySQL without having to parse the whole thing.
我建议你看看 JSON 数据类型。通过这种方式，您可以以比 text 或 varchar 更有效的方式存储数组，并且可以直接从 MySQL 访问数据，而无需解析整个内容。
Take a look at this link : https://dev.mysql.com/doc/refman/5.7/en/json.html
回答by Eric Leschinski
If speed is the most important when retrieving the rows then make a new table and make it dedicated to holding the indices of your array. Use the data type of integer and have each row represent an index of the array. You'll have to create another numeric column which binds these together so you can re-assemble the array with an SQL query.
如果检索行时速度是最重要的，则创建一个新表并使其专用于保存数组的索引。使用整数数据类型并让每一行代表数组的一个索引。您必须创建另一个数字列将它们绑定在一起，以便您可以使用 SQL 查询重新组装数组。
This way you help MySQL help you speed up access. If you only want certain parts of the array, you just change the range in the SQL query and you can reassemble the array however you want.
这样您就可以帮助 MySQL 帮助您加快访问速度。如果您只需要数组的某些部分，您只需更改 SQL 查询中的范围，然后您就可以根据需要重新组合数组。
