How to Store Arrays in MySQL

1. Mock Arrays as Relations to Store Arrays in MySQL
2. Store Arrays Within a Different SQL Data Type

This tutorial shows you how to store or simulate arrays as a field in MySQL.

SQL doesn’t explicitly support arrays as a data type within its own language, but there are many workarounds to make it possible because it’s a relational database.

Relational databases like SQL work using relations and keys. Most times, we utilize those relationships to connect multiple values to a single row it’s related to.

Relations are a de-coupled version of arrays, and it works well with the nature and design of SQL (see normalization). That’s why there isn’t an array data type in SQL because more often than not, arrays aren’t needed because relations exist.

Arrays in a database can be dangerous if used or manipulated poorly. You can implement your database without them and have a very optimized database.

If you really want to or need to store arrays, here are some ways that you can do it.

Mock Arrays as Relations to Store Arrays in MySQL

If we’re to follow the nature and convention of SQL, then arrays should be treated as relations.

Let’s say we have a customer who has multiple orders in a restaurant.

Firstly we create customer and order tables.

CREATE TABLE customer (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50)
);

CREATE TABLE order (
`order_id` INT NOT NULL PRIMARY KEY,
`order` VARCHAR(40),
`price` DOUBLE
);

The customer and their orders exhibit a one-to-many relationship; for this to be implemented, we would need an associative entity to connect the two tables to relate them to each other.

CREATE TABLE customer_order (
`customer_id` INT NOT NULL,
`order_id` INT NOT NULL,
PRIMARY KEY(`customer_id`, `order_id`)
);

Let’s imagine the tables are populated as such:

• customer

• order

• customer_order

From these tables, we can relate John Doe ordering Scallops, Lobster, and Cheesecake. Meanwhile, Jane Doe ordered Steak and Cheesecake.

If we want to query for everything that John Doe has ordered, we would simply use a SELECT JOIN query as such:

SELECT c.name, o.order
FROM customer c
INNER JOIN customer_order co
ON co.customer_id = c.customer_id
INNER JOIN order o
ON o.order_id = co.order_id
WHERE name = 'John Doe'

The query will generate this result:

Essentially, relations are SQL versions of arrays. So if you ever want to maintain good design, understand the concept of relations, and use them as much as possible.

Store Arrays Within a Different SQL Data Type

During the MySQL 5.7 update and upwards, the language now supports JSON as a data type. JSON provides SQL a convenient method to store complex data types like arrays, sets, maps, dictionaries, and many more.

The fastest way you can mock an array within SQL is to store it as a string.

Create tables customer and order.

CREATE TABLE customer (
`id` INT NOT NULL PRIMARY KEY,
`name` VARCHAR(50),
`order` VARCHAR(999)
);

CREATE TABLE order (
`id` INT NOT NULL PRIMARY KEY,
`order` VARCHAR(50),
`price` DOUBLE
);

Each time a customer makes an order, use GROUP_CONCAT() to store a new entry in the order field of the customer.

UPDATE customer
SET order = CONCAT(order, ', Cheesecake');

UPDATE customer
SET order = CONCAT(order, ', Ravioli');

If you query the customer with the orders, the result will display:

This method is essentially an array type being stored in a single column. However, this would mean that you aren’t following the convention of a relational database, which SQL is.

This is an effective way to store an array within SQL. Still, it’s considered a bad design to force an array into a relational database when you can easily store it in a new table and configure the relations and constraints.