Have you considered creating another table to store your array elements?
It would look something like
CREATE TABLE arrayFields (
origtable_PK int,
set_index int,
array_index int,
value int,
PRIMARY KEY (origtable_PK, set_index, array_index)
)
This is the inverse of creating a pivot table. In this case I am turning
what were multiple columns of one row of data into multiple rows of data
(a list). You would still have random access to your array values if you
needed to update them. It is also a trivial matter in code to convert a
list of data elements into an array. It may take up more storage room than
the same array would in a memory structure but this method gives you the
flexibility to store arrays of any length.
For instance, if you had the values {4,6,8,10}, {45,46,47,48}, and
{50,50,50,54} in sets 1, 2, and 3 respectively. This statement would store
those sets of data and associate them with record 123 of your main table.
INSERT arrayFields (origtable_PK, set_index, array_index, value)
VALUES (123,1,1,4),(123,1,2,6),(123,1,3,8),(123,1,4,10)
,(123,2,1,45),(123,2,2,46),(123,2,3,47),(123,2,4,48)
,(123,3,1,50),(123,3,2,50),(123,3,3,50),(123,3,4,54);
You would be able to query for any item of any set for any record you
wanted, just the nth item of all sets, an entire set at once, or all sets
with all values. The flexibility really the best feature of this type of
storage scheme.
Another option is to somehow concatenate/de-concatenate your array of
values into a string list. Most languages have commands to do this built
into them (for example: VB, VB Script, and JScript all have the JOIN() and
SPLIT() commands. I know Java has a pair of methods that perform this
function, too). All you would need to do is to store that list into a
large enough varchar column then either convert from array to list or list
to array depending on the direction the information is flowing. However,
that means that if you need only the 7th element of the 3rd set of data
you will have to retrieve the entire 3rd set then de-concatenate it before
you can find the 7th element.
Keep us informed, please.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Raghudev Ramaiah <[EMAIL PROTECTED]> wrote on 09/02/2004 05:43:33
PM:
> Hi,
> I need this since I have around 3 sets of 20 fields ....each are of
> the same data type .
> if i am able to use arrays , i can say
>
> integer[20] m1;
> integer[20] m2;
> integer[20] m3;
>
> if not , i will have to declare 60 fields
> integer m1 to integr m60.
>
> any solutions please?its quite urgent!
>
> Thanks and Regards
> Raghu
> "V. M. Brasseur" <[EMAIL PROTECTED]> wrote:
> Numeric types are simply that: numbers. They cannot be arrays.
>
> http://dev.mysql.com/doc/mysql/en/Numeric_types.html
>
> Incidentally, why would you need this? Maybe the group can come up with
> a viable alternative for you.
>
> Cheers,
>
> --V
>
> Raghudev Ramaiah wrote:
> > hi
> > i have used column types such as intger and float and double for
> fields in the MySQL tables. can i use arryas of integers, floats and
> doubles as column type? i.e., can a field ina table be an integer
> array or float array ?
> > ex: count[20] integer is it allowed and is it an array?
> >
> > thanks and regards
> > Raghu
> >
> >
> > ---------------------------------
> > Do you Yahoo!?
> > Win 1 of 4,000 free domain names from Yahoo! Enter now.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
> ---------------------------------
> Do you Yahoo!?
> Yahoo! Mail - 50x more storage than other providers!
