How to store arrays in MySQL

Question

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

User · Answer

A sidenote to consider  you can store arrays in Postgres

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] How to store arrays in MySQL?

Examples related to mysql

Examples related to database-schema