Whats the most efficient way to store an array of integers in a MySQL column?

I've got two tables

A:

plant_ID | name.
1        | tree
2        | shrubbery
20       | notashrubbery

B:

area_ID | name    | plants
1       | forrest | *needhelphere*

now I want the area to store any number of plants, in a specific order and some plants might show up a number of times: e.g 2,20,1,2,2,20,1

Whats the most efficient way to store this array of plants?

Keeping in mind I need to make it so that if I perform a search to find areas with plant 2, i don't get areas which are e.g 1,20,232,12,20 (pad with leading 0s?) What would be the query for that?

if it helps, let's assume I have a database of no more than 99999999 different plants. And yes, this question doesn开发者_高级运维't have anything to do with plants....

Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?

---

If you're going to be searching both by forest and by plant, sounds like you would benefit from a full-on many-to-many relationship. Ditch your plants column, and create a whole new areas_plants table (or whatever you want to call it) to relate the two tables.

If area 1 has plants 1 and 2, and area 2 has plants 2 and 3, your areas_plants table would look like this:

area_id | plant_id | sort_idx
-----------------------------
      1 |        1 |     0
      1 |        2 |     1
      2 |        2 |     0
      2 |        3 |     1

You can then look up relationships from either side, and use simple JOINs to get the relevant data from either table. No need to muck about in LIKE conditions to figure out if it's in the list, blah, bleh, yuck. I've been there for a legacy database. No fun. Use SQL to its greatest potential.

---

How about this:

table: plants

plant_ID | name
1        | tree
2        | shrubbery
20       | notashrubbery

table: areas

area_ID | name
1       | forest

table: area_plant_map

area_ID | plant_ID | sequence
1       | 1        | 0
1       | 2        | 1
1       | 20       | 2

That's the standard normalized way to do it (with a mapping table).

To find all areas with a shrubbery (plant 2), do this:

SELECT *
FROM areas
INNER JOIN area_plant_map ON areas.area_ID = area_plant_map.area_ID
WHERE plant_ID = 2

---

You know this violates normal form?

Typically, one would have an areaplants table: area_ID, plant_ID with a unique constraint on the two and foreign keys to the other two tables. This "link" table is what gives you many-many or many-to-one relationships.

Queries on this are generally very efficient, they utilize indexes and do not require parsing strings.

---

8 years after this question was asked, here's 2 ideas:

1. Use json type (link)

As of MySQL 5.7.8, MySQL supports a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON (JavaScript Object Notation) documents.

2. Use your own codification

Turn area_id into a string field (varchar or text, your choice, think about performance), then you can represent values as for example -21-30-2-4-20- then you can filter using %-2-%.

If you somehow try one of these, I'd love it if you shared your performance results, with 100M rows as you suggested.

--

Remember than using any of these breaks first rule of normalization, which says every column should hold a single value

---

Your relation attributes should be atomic, not made up of multiple values like lists. It is too hard to search them. You need a new relation that maps the plants to the area_ID and the area_ID/plant combination is the primary key.

---

Use many-to-many relationship:

CREATE TABLE plant (
    plant_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=INNODB;

CREATE TABLE area (
    area_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255)
) ENGINE=INNODB;

CREATE TABLE plant_area_xref (
    plant_id INT NOT NULL,
    area_id INT NOT NULL,
    sort_idx INT NOT NULL,
    FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON DELETE CASCADE,
    FOREIGN KEY (area_id) REFERENCES area(area_id) ON DELETE CASCADE,
    PRIMARY KEY  (plant_id, area_id, sort_idx)    
) ENGINE=INNODB;

EDIT:

Just to answer your bonus question:

Bonus Question Is it time to step away from MySQL? Is there a better DB to manage this?

This has nothing to do with MySQL. This was just an issue with bad database design. You should use intersection tables and many-to-many relationship for cases like this in every RDBMS (MySQL, Oracle, MSSQL, PostgreSQL etc).