How to store arrays in MySQL?

Question

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

id | name | fruits 

The fruits column may contain zero or an array of rows, such as ("apple", "orange", "banana") or ("strawberry"), etc. The second table is “Table Fruits” and has the following three columns:

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

So, how do I create the fruits column in the first table so that it can contain an array of rows that take values ​​from the fruit_name column in the second table? Since there is no array data type in MySQL, how do I do this?

Answer 1

The right 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 that the person is associated with and effectively links the person and fruits tables together, IE

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

When you want to get a person and all your fruits, 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 

Answer 2

The reason SQL doesn't have arrays is because most people really don't need it. Relational databases (SQL is exactly what) work using relationships, and most of the time it’s best to assign one row of the table to each “bit of information”. For example, where you might think, “I need a list of things here,” create a new table instead, linking a row in one table to a row in another table. [1] Thus, you can represent the relationship M: N. Another advantage is that these links will not interfere with the line containing the associated element. And the database can index these rows. Arrays are usually not indexed.

If you do not need relational databases, you can use, for example, a repository of key values.

Read about database normalization , please. The golden rule: "[Each] non-key [attribute] must ensure the fact of the key, the entire key and nothing but the key." An array does too much. It has several facts and keeps order (which is not related to the relation itself). And performance is bad (see above).

Imagine that you have a table for people, and you have a table with the phone calls of people. Now you can make each line have a list of its phone calls. But many have many other relationships with many other things. Does this mean that my people table should contain an array for every single thing that it is connected to? No, this is not an attribute of the person himself.

[1]: This is normal if there are only two columns in the link table (primary keys from each table)! If the relationship itself has additional attributes, they should be presented in this table in columns.

Answer 3

Edit:

(3/10/2016)

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

Arrays do not display the best databases, so object-relational maps can be quite complex. Historically, people store lists / arrays in MySQL by creating a table that describes them and adding each value as their own record. A table may contain only 2 or 3 columns, or may contain many more. How you store this type of data really depends on the characteristics of the data.

For example, does the list contain a static or dynamic number of entries? Will the list remain small, or is it expected to grow to millions of entries? Will there be many posts in this table? Does he write a lot? Lots of updates? These are all factors to consider when choosing data storage.

In addition, the key ones: value data warehouses / document stores such as Cassandra, MongoDB, Redis, etc., are also a good solution. Just remember where the data is stored (if it is stored on disk or in memory). Not all of your data must be in the same database. Some data does not compare well with a relational database, and you may have reasons to store it elsewhere, or you might want to use the database in memory: the database value as a hot cache for data stored on disk somewhere or in as an ephemeral repository for things like sessions.

Answer 4

As a side element to consider, you can store arrays in Postgres.

Answer 5

Use the field type of the BLOB database to store arrays.

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

Return values

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

Note that this is a binary string that can contain null bytes, and needs to be stored and processed as such. For example, serialize () output should usually be stored in the BLOB field in the database, rather than the CHAR or TEXT field.

Answer 6

you can save your array using group_Concat like this

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

HERE script example