List:General Discussion« Previous MessageNext Message »
From:Mark Goodge Date:May 16 2010 4:04pm
Subject:Re: Array data type
View as plain text  
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
Thread
Array data typeSamrat Kar14 May
  • Re: Array data typeCarsten Pedersen14 May
  • Re: Array data typeJoerg Bruehe14 May
    • Re: Array data typeMark Goodge16 May
      • Re: Array data typePrabhat Kumar16 May