How to store arrays in MySQL?

Question

I am having two tables in the MySQL. Table Person has the below columns:

id | name | fruits

The fruits column may contain null or an array of strings like ('apple', 'orange', 'banana'), or ('strawberry'), etc. The other table is named Table Fruit and containing the below 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 contain an array of strings that take the values from the fruit_name column in the second table? Considering there is no array data type in the MySQL, how should I do it?

Answer 1

The precise way to do this is to use the 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 holds 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 need to retrieve a person and all of their fruit you can do 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

Want to be a SQL expert? Join this SQL Certification by Intellipaat.

If you want to know more about MySQL, refer to the below MySQL tutorial video that will help you out in a better way: