Tricky MySQL q'n

Trying to work with arrays

[dcrombie]

I've got a situation where I can do something very easily in Postgres, but it seems near impossible in MySQL
(not the first time that's happened ;)).

In postgres, I would create a table like this:

CREATE TABLE test ( 
 name varchar, 
 description varchar[] 
);

insert:

INSERT INTO test (name, description) VALUES ('test1', '{desc1,desc2}');

and select (it's for two different websites sharing a database):

SELECT name, description[1] FROM test; 
SELECT name, description[2] FROM test;

Alternatively, I could use two tables and create a VIEW, but that makes the INSERT/UPDATE process a bit trickier.

Now, how to get the same functionality in MySQL...

CREATE TABLE test ( 
 id int(11) NOT NULL auto_increment, 
 name varchar(255), 
 description text, 
 PRIMARY KEY (id) 
) TYPE=MyISAM;

At the moment I have a '¦'-delimited string such as "desc1¦desc2".

There are currently only two values that need to be stored, and after some trawling through the MySQL docs I came up with:

SELECT name, SUBSTRING_INDEX(description,'¦',1) FROM test; 
SELECT name, SUBSTRING_INDEX(description,'¦',-1) FROM test;

That's fine for now, but as soon as a third value is added it will fail miserably.

Anyone?!?

[coopster]

Have you considered SET [dev.mysql.com] as an alternative to the ARRAY collection type?

BTW, if you want to restrict your code to Core SQL you won't be defining any ARRAY data types (as of SQL-99 at least).

[mcibor]

If you are using php then you can always retrieve the data and the explode it

$data_array = explode("¦", $data);// will give you array of values separated by "¦".

I don't know of any other way you could store array in db

Best regards
Michal Cibor

[madpenguin2]

Or, instead of explode use serialize and unserialize. It would be easy to store an array using these functions. As a reply said earlier, it's not a standard in SQL 99. I'm sure you've noticed nearly every db server has their own 'extended' set of SQL. It's just a way for companies to differentiate their DB's. And a way for them to say their better then their competition.

[dcrombie]

coopster, I spent a while working out SETs in MySQL but they seem to be about restricting input values rather than allowing free text. If I'm wrong I'd be happy to be enlightened ;)

mcibor+madpenguin2, the option of exploding in PHP would be a last resort as it's very inefficient in this case:

SELECT name FROM test WHERE SUBSTRING_INDEX(description,'¦',1)='desc1';

Using PHP you'd have to select ALL rows from the table then loop through and work out which was the right one.
Another option is to use two tables with a one-to-'a few' relationship, but updating then becomes a hassle unless VIEWs are available.
Or I could assign a fixed number of characters to each value and use SUBSTRING...

The situation is that I have a number of database tables that are currently shared between two sites, and may extend to four or five. Most of the fields are common (same value on all sites) but some of them need to be editable on a per-site basis.

Is there no ARRAY type, or VIEW in MySQL? Or some way of exploding a value using SQL?!?

[coopster]

The SET data type is a string object that can have zero or more values, each of which must be chosen from the list of values 'value1', 'value2', ... A SET column can have a maximum of 64 members.

The FIELD and FIND_IN_SET string functions will return the index of the search string you specify in the list you specify:

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); 
-> 2 
--  
-- OR  
-- 
mysql> SELECT FIND_IN_SET('b','a,b,c,d'); 
-> 2

However, if it is not a value list that you want to work with, you are correct, it may not be the column type you want to use to define your column. An alternative is to define your column as a string column of some form or other though (CHAR, VARCHAR, TEXT, etc) with comma-separated values. Then, if I am truly understanding you correctly, there is a MySQL-specific solution to your problem, the ELT string function.

mysql> SELECT ELT(3, 'website1', 'website2', 'website3', 'website4'); 
-> 'website3'

All of the string functions mentioned within this message can be found on the MySQL String Functions manual pages [dev.mysql.com].