Storage of arrays in MySQL?

Question

On the FQL pages on Facebook, it displays the structure of the FQL table, here is a screenshot below to show some of them (the screenshot is gone).

You will notice that some elements are an array, for example meeting_sex, meeting_for current_location. I'm just wondering how you think they store it as an array in mysql or just return it as one, from this data it really makes me think that it is stored as an array. If you think this is the case, or if you did this, then what is the way to save these elements as an array in 1 field of the table, and then get it as an array on a PHP page?

alt text http://img2.pict.com/3a/70/2a/2439254/0/screenshot2b187.png

Answer 1

There are two storage options as an array:

The first thing you mentioned is to make one or more tables and list each possible key that you are going to store. This is best suited for finding and retrieving data that makes sense.

However, for what you want to do, use serialize() . Note: NEVER EVER try to search this data in your native string form. It is much faster (and more sensible) to simply reload it, call unserialize() , and then search by your criteria than to create a crazy template search to make your bets.

EDIT: If it were me, and it was something that I seriously develop for others to use (or even for myself, to use to be completely honest), I probably create a second lookup table to store all the keys like columns; If you have done this, mysql_fetch_assoc() can provide you with the array you need by simply executing a quick second query (or you can retrieve them through a JOIN ed request). However, if it's just quick and dirty to do any job, then you might need a serialized array. If you really, really don't care about ever looking for this data, the correct relationship between columns and keys, I think most would agree is superior.

Answer 2

The correct way to store an array in a database is to store it in the form of a table, where each element of the array is a row in the table.

All the rest is hacking and ultimately will make you regret your decision to try to avoid the extra table.

Answer 3

I guarantee that Facebook does not store this data in arrays inside its database.

What you need to understand in FQL is that you are not directly querying the main Facebook data servers. FQL is a shell designed to provide access to basic social data, not allowing you to run crazy queries on real servers with performance requirements. Arbitrary user queries in the main database will be functional suicide.

FQL provides a well-designed data return structure that is convenient for the type of data you request, so any piece of data that can have multiple associations (for example, "meeting_for") is packed into an array before it is returned as an API result.

As other posters noted, the only way to preserve the structure of a programming language (for example, an array or an object) inside a database (which has no idea about these things) is to serialize it. Serialization is expensive, and once you serialize something, you actually make it unsuitable for indexing and searching. Being a social network, Facebook has to index and search for almost everything, so this data will never exist in the form of an array inside their main schemes.

Usually the only time you ever want to store serialized data in a database is temporary, such as session data, or when you have valid performance requirements. Otherwise, your data quickly becomes useless.

Answer 4

Divide it into other tables. You can serialize it, but this ensures that you want to request data later. Save the disappointment later and just share it now.

Answer 5

you can serialize the array, insert it and then non-esterize it when it is received.

Answer 6

They can use multiple tables with many-to-many relationships, but use the join functions and MySql GROUP_CONCAT to return array values ​​for these columns in a single query.