Page 1 of 1
Store 2D or higher order array in MySQL
Posted: Fri Dec 07, 2007 6:02 am
by VladSun
Can I define a field as variable-length multidimensional array in MySQL 5.0+? It can be done in PostgreSQL, but I couldn't find how to do it in MySQL ...
If it can't be done, what would be the most appropriate way to do this? At this moment, I serialize the array and store it in a BLOB field, but it makes its data not accessible by the DB engine.
Posted: Fri Dec 07, 2007 9:05 am
by nathanr
not possible sadly (and a my only negative on mysql) , only suggestion I can make is to turn it to xml and save?
Posted: Mon Dec 10, 2007 5:05 am
by VladSun
Thanks for replying me

Storing the array as XML data would solve my problem partially, but still a lot of queries can not be done.
Does anybody have an idea if MySQL development team is going to implement array type soon?
Posted: Mon Dec 10, 2007 6:39 pm
by feyd
I seriously doubt it. It's not in six right now.
What's the actual benefit? I don't see one. Feels more like a smell to me.
Posted: Mon Dec 10, 2007 6:42 pm
by VladSun
feyd wrote:I seriously doubt it. It's not in six right now.
What's the actual benefit? I don't see one. Feels more like a smell to me.
Imagine a 6D array stored in DB as records (along their relations) ... Now, construct a simple query to get its value back to PHP array.
And this looks beautiful:
http://www.postgresql.org/docs/8.0/inte ... rrays.html
Posted: Mon Dec 10, 2007 6:46 pm
by feyd
Again, I don't see the benefit.
By the way, that's JSON notation.
Posted: Mon Dec 10, 2007 6:56 pm
by VladSun
I think that it is very natural not to have only scalar types in a DB ...
Posted: Mon Dec 10, 2007 7:09 pm
by feyd
Everything breaks down to scalar types.

Posted: Tue Dec 11, 2007 6:28 am
by VladSun
Depends on the point of view. One could say that any scalar value is just a 1x1 matrix

What is a point in zero dimension space ?

I've heard definitions like : "a point is a sphere with zero radius" ...
Offtopic:
http://www.tenthdimension.com/medialinks.php
Posted: Tue Dec 11, 2007 11:05 am
by nathanr
personally I storing arrays in mysql would be great, but then thats purely because it would make it easier to store xml, which is infact what I want to do..
infact no I can already store sql, what I want is an sql language for xml repositories.
Posted: Tue Dec 11, 2007 4:00 pm
by Kieran Huggins
http://en.wikipedia.org/wiki/XQuery
XML database availability is the issue there. There's no MyXML or XMLite projects
If I need to store a data structure in a DB I either use a serialized object/array, or JSON. Serialization is nice and simple, but JSON is more flexible language-wise. Six of one...