Mysql explode function

Question

I have a table witch has a field with the following record:

1,2,3,4,5,6

I would like to ask the following two things:

1) How can i make a foreign key in another table? The rule would be: For any value seperated by comma in field `field_name` must be record of other_table.field_id

2) How can i do something like: SELECT explode(field) AS ex FROM table_name ? the name's of row maybe can retrieve as ex[0]-->1, ex[1]-->2

See [Is storing a delimited list in a database column really that bad?](http://stackoverflow.com/a/3653574) — eggyal, Jun 17 '13 at 13:14
@eggyal i know that it's not efficient but, i am working into a research project, and we are trying to see how bad that is — user2493470, Jun 17 '13 at 13:15
then the question 1 is irrelevant. You're trying to blend normalised and denormalised standards. — Sebas, Jun 17 '13 at 13:17
Also, I really think your question 2 shows very little understanding of SQL in general. I don't mean to offend nor to be rude, I just think you should go back to the basics (or maybe give more details? I might be wrong!) — Sebas, Jun 17 '13 at 13:18
I am trying to store a polyline without using the standar types i have make `points: (id,x,y) and segments(id,start_point,end_point)` and i thougth to make polyline table like `polyline(id,segment_sequnce)` — user2493470, Jun 17 '13 at 13:20

score 0 · Accepted Answer · answered Jun 17 '13 at 13:20

While it is possible to do a join on a comma separated field (using FIND_IN_SET for example), I don't think there is a way to do this for a foreign key.

MySQL doesn't have an explode function, and your idea would seem to suggest a varying number of columns on each row.

You can split them onto different rows if necessary but it is ugly. And more a good reason to NOT use comma separated fields

SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(field, ',', 1 + units.i + tens.i * 10 + hundreds.i * 100), ',', -1)
FROM table_name
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) units
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) tens
CROSS JOIN (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) hundreds

Show you tell, that what i am trying to do is not possible.. Can you suggest me a structure for polyline table (one table plz if it's possible) — user2493470, Jun 17 '13 at 13:33
I presume the number of points varies. In which case you can't really do it with one table. Relational databases are designed to deal with sets of data. With sets on one table related to a record on another table. Hence you should really have one main table (with an owning record for each set of points) and then another table with multiple rows for each record on the first table. — Kickstart, Jun 17 '13 at 13:45

Mysql explode function

1 Answers1