数据库存数组MySQL,存储在MySQL数据库数组的最佳方法？

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.

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)

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?

解决方案

Definitely not a text field, 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.

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 serialize function. 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)

Good luck!