Store array in mySql

Question

I have a table in mysql with restaurants and I want to store an array for the categories the restaurants fall under. How should I do this, as mysql doesn't have an array section. So what I want is something like this:

id|name    |categories         |
 1|chipotle|[mexican,fast_food]|

How can I do this?

possible duplicate of [How to store arrays in MySQL?](http://stackoverflow.com/questions/17371639/how-to-store-arrays-in-mysql) — Daniel Timberlake, Aug 04 '15 at 20:06
^^^ possible duplicate of about 2k questions (a grossly conservative estimate) — Drew, Aug 04 '15 at 20:07

Drew · Accepted Answer · 2015-08-04T20:33:57.503

Ok, give me a minute or two to add sample data and FK's

create table food
(
    id int auto_increment primary key,
    name varchar(100) not null
);

create table category
(
    id int auto_increment primary key,
    name varchar(100) not null
);

create table fc_junction
(   -- Food/Category junction table
    -- if a row exists here, then the food and category intersect
    id int auto_increment primary key,
    foodId int not null,
    catId int not null,

    -- the below unique key makes certain no duplicates for the combo
    -- duplicates = trash
    unique key uk_blahblah (foodId,catId),

    -- Below are the foreign key (FK) constraints. A part of Referential Integrity (RI).
    -- So a row cannot exist with faulty foodId or catId. That would mean insert/update here.
    -- It also means the parents (food and category) row(s) cannot be deleted and thus 
    -- orphaning the children (the children are these rows in fc_junction)
    CONSTRAINT fc_food FOREIGN KEY (foodId) REFERENCES food(id),
    CONSTRAINT fc_cat FOREIGN KEY (catId) REFERENCES category(id)
);

So you are free to add food and categories and hook them up later via the junction table. You can create chipotle, burritos, hotdogs, lemonade, etc. And in this model (the generally accepted way = "don't do it any other way), you do not need to know what categories the foods are in until whenever you feel like it.

In the original comma-separated way (a.k.a. the wrong way), you have zero RI and you can bet there will be no use of fast indexes. Plus getting to your data, modifying, deleting a category, adding one, all of that is a kludge and there is much snarling and gnashing of teeth.

score 0 · Answer 2 · edited May 23 '17 at 12:29

0

See How to store arrays in MySQL?, you need to create a separate table and use a join.

Or you can use Postgres, http://www.postgresql.org/docs/9.4/static/arrays.html.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 04 '15 at 20:06

Daniel Timberlake

1,179
6
15

okay, but making a whole new table just for 2 string seems unnecessary, is there a better way I could store it – spen123 Aug 04 '15 at 20:08
It's really a bad idea, but if you must store them in that field, you could concatenate the strings and store them in a varchar. – Daniel Timberlake Aug 04 '15 at 20:10
@DrewPierce I believe you, I guess Ill make a new table, what would the columns be though, I would have `id|name|and...` because there are a bunch of categories and Im not sure what they all are before I am storing the data. Do you know what I mean? – spen123 Aug 04 '15 at 20:13

Store array in mySql

2 Answers2