------=_NextPart_000_002E_01CAF36C.F9658D00
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Hello,
How to store multiple values in a single field? Is there any array data type
concept in mysql?
Regards,
Samrat Kar
FRD, BARC
Tel: 022-25597295
Alternate Email: esamrat@yahoo.com
------=_NextPart_000_002E_01CAF36C.F9658D00--
There are SETs and ENUMs, but I've always found that dealing with them is
annoying.
YMMV
/ Carsten
On Fri, 14 May 2010 13:54:29 +0530, "Samrat Kar"
wrote:
> Hello,
>
>
>
> How to store multiple values in a single field? Is there any array data
> type
> concept in mysql?
>
>
>
> Regards,
>
>
>
> Samrat Kar
>
> FRD, BARC
>
>
>
> Tel: 022-25597295
>
> Alternate Email: esamrat@yahoo.com
>
>
>
>
>
> !DSPAM:451,4bed08e0408231671817791!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org
Hi Samrat, all!
Samrat Kar wrote:
> Hello,
>=20
> =20
>=20
> 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 a=
ll
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 fie=
ld,
but in many cases this makes queries very hard to write, and may prev=
ent
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 o=
r
mail addresses for a person).
HTH,
Jörg
--=20
Joerg Bruehe, MySQL Build Team, Joerg.Bruehe@Sun.COM
Sun Microsystems GmbH, Komturstrasse 18a, D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz
Amtsgericht Muenchen: HRB161028
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg
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=gcdmg-mysql-2@m.gmane.org
--000feaed6430adec060486b9f047
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Hi,
> How to store multiple values in a single field? Is there any array data
> type
> concept in mysql?
>
As Jörg said "Multiple values in a single field" would be an explicit
violation of
the relational model..."
then also, if you want to use.
this might be this will help you.
I used like this in past:
In database, I have taken a column as TEXT. In which I have separated a
value by *- *(hifen)
eg. furniture table there is 2 col , name (person name) - used (furniture's
used by that person).
value will be :
> ====================
> | Name | Used |
> --------------------------------------
> | Prabhat | chair-table-bed |
> ===================3D
>
And in PHP you can easily separate these value.
eg,
> $names =3D "Markus;Nigel;David";
>
To use these names in a meaningful way, we should first separate them into
an array ($namearray), using
explode()
:
$namearray =3D explode(";", $names);
The end result:
$namearray =3D Array ( [0] =3D> Markus [1] =3D> Nigel [2] =3D> David )
But remember this is VERY bad database design. I had used since, that was
required for few days only.
Thanks,
In database :
On Sun, May 16, 2010 at 9:34 PM, Mark Goodge
> 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 storin=
g
> meta-data about a data object (eg, EXIF data from a photograph), especial=
ly
> 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 meth=
od
> 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 stor=
ing
> 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=3Daim.prabhat@gmail.com
>
>
--=20
Best Regards,
Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com
My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat
--000feaed6430adec060486b9f047--