How to store array in MySQL?

MySQL doesn't have an array type, but it does have a JSON type. However, in this tutorial, I will not actually talk about arrays or JSON types. Instead, I will teach you the proper way to solve this sort of problem in relational databases such as MySQL.

In this tutorial, I will show you how to use multiple tables and JOIN them in our queries.

Consider the following scenario:

You have a table movie in your database that has a title, year_released, and genre columns. Then you realize that a movie can have many genres. The first thing that comes to mind is using an array type for the genre column, but then you find out that MySQL does not support an array type. So what will you do?

Just like I mentioned earlier, MySQL has a JSON type that you can use to store an array. However, there are some disadvantages to using it, which we will not talk about in this article. Instead, we will create tables for movie, genre, and movie_genre. Then we will JOIN them in our query when we select them.

1. Create the 3 tables

   CREATE TABLE movie (
     movie_id int NOT NULL,
     title varchar(100) NOT NULL,
     year_released smallint(4) NOT NULL,
     PRIMARY KEY (movie_id)
   );
   
   CREATE TABLE genre (
     genre_id int NOT NULL,
     name varchar(100) NOT NULL,
     PRIMARY KEY (genre_id)
   );
   
   CREATE TABLE movie_genre (
     movie_id int NOT NULL,
     genre_id int NOT NULL,
     PRIMARY KEY (movie_id, genre_id),
     FOREIGN KEY (movie_id) REFERENCES movie(movie_id),
     FOREIGN KEY (genre_id) REFERENCES genre(genre_id)
   );
   

   As you can see from the code above, we created a movie table that contains the columns that we needed, excluding the genre. Instead, we created a new table for genres that contains only the genre name and its ID. Finally, we created a third table movie_genre that will link the table movie and genre. It contains movie_id and genre_id columns to link the two tables; these two columns are also their own primary keys.

   In table movie_genre, each genre that is associated with a movie will have a record or row. It will contain data like the following:

   movie_id | genre_id
   1        | 1
   1        | 2
   1        | 3
   2        | 1
   2        | 2
   

2. JOIN them in query

   Now let's create a JOIN query if we want to retrieve the movie and its genre. The code will look like the following:

   SELECT m.*, g.* FROM movie AS m
   INNER JOIN movie_genre AS mg
   ON mg.movie = m.movie_id
   INNER JOIN genre AS g
   ON mg.genre_id = g.genre_id;
   

Summary

In MySQL, there is no array type. Although there is a JSON type, the more proper way to solve this sort of problem is to use multiple tables and then JOIN them in our queries.

I have shown you an example of that in the movie and genre relation, in which I created 3 tables: movie, genre, and genre_movie, and then I joined them in our query.