Store 2D or higher order array in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Store 2D or higher order array in MySQL

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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?
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Again, I don't see the benefit.

By the way, that's JSON notation.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

I think that it is very natural not to have only scalar types in a DB ...
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Everything breaks down to scalar types. :?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post 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
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
nathanr
Forum Contributor
Posts: 200
Joined: Wed Jun 07, 2006 5:46 pm

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post 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...
Post Reply