On 14/05/2010 09:54, Joerg Bruehe wrote:
> Hi Samrat, all!
>
>
> Samrat Kar wrote:
>> Hello,
>>
>>
>>
>> How to store multiple values in a single field? Is there any array data type
>> concept in mysql?
>
> "Multiple values in a single field" would be an explicit violation of
> the relational model (on which the SQL language is based) and cause all
> kinds of trouble in your queries.
>
> Ever and again, developers use some kind of encoding to store a
> combination of values (like flags in a bit field) in one database field,
> but in many cases this makes queries very hard to write, and may prevent
> optimization of the SQL statement.
>
> It depends on your application, especially on whether this field will be
> used in search conditions ("... WHERE combined_field has flag_X ..."),
> to decide about a sensible approach.
> In general, I would prefer separate fields for different flags, and a
> separate table for a truly multi-valued field (like multiple postal or
> mail addresses for a person).
If you're merely *storing* the data in the table, and will only ever
retrieve it based on other factors - that is, you'll never use that
field for any operands including joins and 'where' clauses - then it's
often useful to store a flattened array (eg, one created by PHP's
serialize() function, javascript JSON or even XML) as a string and then
expand it to an array again after retrieving it. That can often be a
useful way of storing meta-data about a data object (eg, EXIF data from
a photograph), especially where you can't know in advance what the array
structure will be when you create the database.
However, that's not really an array datatype in MySQL, it's simply a
method of storing an array as a string. So it's of fairly limited
application, there are cases where it's very useful but it's not a
substitute for storing the array values separately using the appropriate
table design where you do need to run queries against it.
Mark
