How to store arrays in MySQL?

Question

The proper way to do this is to use multiple tables and JOIN them in your queries.

For example:

CREATE TABLE person (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);

CREATE TABLE fruits (
`fruit_name` VARCHAR(20) NOT NULL PRIMARY KEY,
`color` VARCHAR(20),
`price` INT
);

CREATE TABLE person_fruit (
`person_id` INT NOT NULL,
`fruit_name` VARCHAR(20) NOT NULL,
PRIMARY KEY(`person_id`, `fruit_name`)
);

The person_fruit table contains one row for each fruit a person is associated with and effectively links the person and fruits tables together, I.E.

1 | "banana"
1 | "apple"
1 | "orange"
2 | "straberry"
2 | "banana"
2 | "apple"

When you want to retrieve a person and all of their fruit you can do something like this:

SELECT p.*, f.*
FROM person p
INNER JOIN person_fruit pf
ON pf.person_id = p.id
INNER JOIN fruits f
ON f.fruit_name = pf.fruit_name

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.

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.

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

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.

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.