[mysql] How to store arrays in MySQL?

I have two tables in MySQL. Table Person has the following columns:

id | name | fruits

The fruits column may hold null or an array of strings like ('apple', 'orange', 'banana'), or ('strawberry'), etc. The second table is Table Fruit and has the following three columns:

____________________________
fruit_name | color  | price
____________________________
apple      | red    | 2
____________________________
orange     | orange | 3
____________________________
...,...

So how should I design the fruits column in the first table so that it can hold array of strings that take values from the fruit_name column in the second table? Since there is no array data type in MySQL, how should I do it?

In MySQL, use the JSON type.

Contra the answers above, the SQL standard has included array types for almost twenty years; they are useful, even if MySQL has not implemented them.

In your example, however, you'll likely want to create three tables: person and fruit, then person_fruit to join them.

DROP TABLE IF EXISTS person_fruit;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS fruit;

CREATE TABLE person (
  person_id   INT           NOT NULL AUTO_INCREMENT,
  person_name VARCHAR(1000) NOT NULL,
  PRIMARY KEY (person_id)
);

CREATE TABLE fruit (
  fruit_id    INT           NOT NULL AUTO_INCREMENT,
  fruit_name  VARCHAR(1000) NOT NULL,
  fruit_color VARCHAR(1000) NOT NULL,
  fruit_price INT           NOT NULL,
  PRIMARY KEY (fruit_id)
);

CREATE TABLE person_fruit (
  pf_id     INT NOT NULL AUTO_INCREMENT,
  pf_person INT NOT NULL,
  pf_fruit  INT NOT NULL,
  PRIMARY KEY (pf_id),
  FOREIGN KEY (pf_person) REFERENCES person (person_id),
  FOREIGN KEY (pf_fruit) REFERENCES fruit (fruit_id)
);

INSERT INTO person (person_name)
VALUES
  ('John'),
  ('Mary'),
  ('John'); -- again

INSERT INTO fruit (fruit_name, fruit_color, fruit_price)
VALUES
  ('apple', 'red', 1),
  ('orange', 'orange', 2),
  ('pineapple', 'yellow', 3);

INSERT INTO person_fruit (pf_person, pf_fruit)
VALUES
  (1, 1),
  (1, 2),
  (2, 2),
  (2, 3),
  (3, 1),
  (3, 2),
  (3, 3);

If you wish to associate the person with an array of fruits, you can do so with a view:

DROP VIEW IF EXISTS person_fruit_summary;
CREATE VIEW person_fruit_summary AS
  SELECT
    person_id                                                                                              AS pfs_person_id,
    max(person_name)                                                                                       AS pfs_person_name,
    cast(concat('[', group_concat(json_quote(fruit_name) ORDER BY fruit_name SEPARATOR ','), ']') as json) AS pfs_fruit_name_array
  FROM
    person
    INNER JOIN person_fruit
      ON person.person_id = person_fruit.pf_person
    INNER JOIN fruit
      ON person_fruit.pf_fruit = fruit.fruit_id
  GROUP BY
    person_id;

The view shows the following data:

+---------------+-----------------+----------------------------------+
| pfs_person_id | pfs_person_name | pfs_fruit_name_array             |
+---------------+-----------------+----------------------------------+
|             1 | John            | ["apple", "orange"]              |
|             2 | Mary            | ["orange", "pineapple"]          |
|             3 | John            | ["apple", "orange", "pineapple"] |
+---------------+-----------------+----------------------------------+

In 5.7.22, you'll want to use JSON_ARRAYAGG, rather than hack the array together from a string.

you can store your array using group_Concat like that

 INSERT into Table1 (fruits)  (SELECT GROUP_CONCAT(fruit_name) from table2)
 WHERE ..... //your clause here

HERE an example in fiddle

A sidenote to consider, you can store arrays in Postgres.

The reason that there are no arrays in SQL, is because most people don't really need it. Relational databases (SQL is exactly that) work using relations, and most of the time, it is best if you assign one row of a table to each "bit of information". For example, where you may think "I'd like a list of stuff here", instead make a new table, linking the row in one table with the row in another table.[1] That way, you can represent M:N relationships. Another advantage is that those links will not clutter the row containing the linked item. And the database can index those rows. Arrays typically aren't indexed.

If you don't need relational databases, you can use e.g. a key-value store.

Read about database normalization, please. The golden rule is "[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key.". An array does too much. It has multiple facts and it stores the order (which is not related to the relation itself). And the performance is poor (see above).

Imagine that you have a person table and you have a table with phone calls by people. Now you could make each person row have a list of his phone calls. But every person has many other relationships to many other things. Does that mean my person table should contain an array for every single thing he is connected to? No, that is not an attribute of the person itself.

[1]: It is okay if the linking table only has two columns (the primary keys from each table)! If the relationship itself has additional attributes though, they should be represented in this table as columns.

Use database field type BLOB to store arrays.

Ref: http://us.php.net/manual/en/function.serialize.php

Return Values

Returns a string containing a byte-stream representation of value that can be stored anywhere.

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

MySQL 5.7 now provides a JSON data type. This new datatype provides a convenient new way to store complex data: lists, dictionaries, etc.

That said, arrays don't map well databases which is why object-relational maps can be quite complex. Historically people have stored lists/arrays in MySQL by creating a table that describes them and adding each value as its own record. The table may have only 2 or 3 columns, or it may contain many more. How you store this type of data really depends on characteristics of the data.

For example, does the list contain a static or dynamic number of entries? Will the list stay small, or is it expected to grow to millions of records? Will there be lots of reads on this table? Lots of writes? Lots of updates? These are all factors that need to be considered when deciding how to store collections of data.

Also, Key/Value data stores, Document stores such as Cassandra, MongoDB, Redis etc provide a good solution as well. Just be aware of where the data is actually being stored (if its being stored on disk or in memory). Not all of your data needs to be in the same database. Some data does not map well to a relational database and you may have reasons for storing it elsewhere, or you may want to use an in-memory key:value database as a hot-cache for data stored on disk somewhere or as an ephemeral storage for things like sessions.