Variable-Length Array in MySQL

Question

I'm writing an application in PHP that uses a MySQL database to store information. One of the pieces of information that it has to store is an array of names, but the array has no set length. It could range from one to many items. Is there a way to store this array in MySQL and be able to retrieve it by specifying just one item from the array?

For example, I want to be able to store something like this in a table:

Foo        Bar
-------------------
[baz,car]  fiz
[abc,def]  ghi

Then, I want to be able to tell MySQL just to search for car and SELECT the first row in the table above.

Is there a feasible way to implement this?

Normalizing your data would make this unnecessary and your life a lot easier — John Conde
– John Conde, Commented Jul 27, 2012 at 2:02

Chris Trahey · Accepted Answer · 2012-07-27 02:16:47Z

2

The way to implement it is to "normalized" the schema. There are several related ideas which comprise schema normalization, but the critical one here is that your data should be represented with two tables and a relationship between. The relationship is called, "one to many", since for one "Bar" there are one-or-more "Foo".

A necessary step, then, is to add unique identifier columns to your schema (easiest just to use auto-incrementing integers), and then query using the JOIN mechanism to relate your data.

this is why we call MySQL (and many others) a "relational" database.

Bar
+----+----------+
| id |   name   | 
+----+----------+
| 01 |   fiz    |
+----+----------+
| 02 |   ghi    |
+----+----------+


Foo
+----+--------+----------+
| id | bar_id |   name   | 
+----+--------+----------+
| 01 |   01   |  baz     |
+----+--------+----------+
| 02 |   01   |  car     |
+----+--------+----------+
| 03 |   02   |  abc     |
+----+--------+----------+
| 04 |   03   |  def     |
+----+--------+----------+

And here is what the join looks like to select the "fiz" record based on "car" in the Foo relation

SELECT 
  Bar.* 
FROM Bar 
  JOIN Foo ON Bar.id = Foo.bar_id 
WHERE Foo.name = "car"

And if you want the entire list of Foo relations for each bar with a matching Foo:

SELECT 
  Bar.*, 
  GROUP_CONCAT(Foo.name) 
FROM Bar 
  JOIN Foo ON Bar.id = Foo.bar_id 
WHERE Foo.name = "car" 
GROUP BY Bar.id

edited Jul 27, 2012 at 2:16

answered Jul 27, 2012 at 2:10

Chris Trahey

18.3k1 gold badge44 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wecsam Over a year ago

Ah, so this would be a good place to use a unique index, right?

Chris Trahey Over a year ago

Yes, both of the id columns should be declared as PRIMARY KEY, which enforces a unique constraint. The declaration might look like this: id int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY

sel · Accepted Answer · 2012-07-27 02:12:55Z

0

Either

SELECT Bar FROM tbl
WHERE FIND_IN_SET('car', Foo)

edited Jul 27, 2012 at 2:12

answered Jul 27, 2012 at 2:05

sel

4,9421 gold badge18 silver badges23 bronze badges

Collectives™ on Stack Overflow

Variable-Length Array in MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related