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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
