How to store array data type in MySQL

Although an array is one of the most common data types in the world of programming, MySQL actually doesn’t support saving an array type directly.

You can’t create a table column of array type in MySQL. The easiest way store array type data in MySQL is to use the JSON data type.

The JSON data type was first added in MySQL version 5.7.8, and you can use the type for storing JSON arrays and objects.

Let’s see an example of using JSON type in MySQL.

First, create a table called example with the following SQL statement:

CREATE TABLE example (
  `id` int NOT NULL AUTO_INCREMENT,
  `docs` JSON,
  PRIMARY KEY (`id`)
);

The example table will have two columns: the id column and the docs column.

Now you can put an array inside the docs column with the following INSERT statement:

INSERT INTO example (docs)
  VALUES ('["hot", "cold"]');

Now the column_one column will have an array stored inside it as follows:

+----+-----------------+
| id | docs            |
+----+-----------------+
|  1 | ["hot", "cold"] |
+----+-----------------+

And that’s the easiest way you can store an array type using MySQL.

You can put arrays with different lengths as the column values:

INSERT INTO example (docs)
  VALUES ('["morning", "noon", "night"]'),
    ('["raining", "cloudy"]'),
    ('["storm"]');

The table column would have arrays of different lengths as shown below:

+----+------------------------------+
| id | docs                         |
+----+------------------------------+
|  1 | ["hot", "cold"]              |
|  2 | ["morning", "noon", "night"] |
|  3 | ["raining", "cloudy"]        |
|  4 | ["storm"]                    |
+----+------------------------------+

You can query specific data by using the combination of WHERE and LIKE clause as follows:

SELECT id, docs 
  FROM example 
  WHERE docs LIKE '%cold%';

And you can also UPDATE specific rows like this:

UPDATE example SET docs = '["day", "night"]' 
  WHERE docs LIKE '%cold%';

If you’d like to learn more about how MySQL handles JSON arrays, you can look into the MySQL JSON data type documentation.

Thanks for reading! 🙏