Array Type in RDB

Overview

Recently, I used MYSQL when I moved to a different company, and I had a chance to receive a price by listing, but when I checked, I found out that MYSQL itself does not have an ARRAY type, so I would like to organize it simply.

Array Type

The RDBs I used so far are Oracle and PostgreSql, so I naturally thought that MYSQL also had an Array type. It wasn’t really like that, but similarly, I summarized the DBs that support the Array type in RDB and the DBs that are not.

Why doesn’t MySQL have an Array Type?

When looking at MYSQL only, you need to understand the initial design philosophy and objectives. As the type of array came in, there were some areas that required more complex processing in terms of simplicity and performance, and from the perspective of relational data modeling, the Array type went against the principle of normalization and complicated integrity management. In particular, we designed it to easily transfer from database A to database B by following standard SQL, and this seems to have had a lot of influence.

Why does PostgreSQL have an Array Type?

You also need to understand the philosophy that early PostgreSQL had at the time of its design. PostgreSQL has this phrase if you go to the site.

“The World’s Most Advanced Open Source Relational Database”

From the design stage, we have tried to achieve this goal through a variety of advanced features and flexible data type support, and the array type is one of the key goals that PostgreSQL is pursuing.

There may be some misunderstanding, so PostgreSQL is a better database than MYSQL because it supports advanced queries and various types. However, as everything always has its pros, MYSQL has advantages over PostgreSQL and, on the other hand, PostgreSQL has advantages over MYSQL, so you can choose the right database for your business needs.

Alternative

Then, since there is no Array type in MYSQL, you may think that it should be normalized and saved, but that is not true. Traditionally, there is a method of saving and importing text through a specific separator (","), and you can use the JSON type described below similarly.

MySQL JSON Type

Advances in web technology and applications have made data storage and processing requirements more diverse and complex. In particular, with the advent of NoSQL database systems, the need to efficiently store and manage unstructured data has increased. To meet these market changes and the needs of the developer community, we officially began supporting JSON data types in MySQL version 5.7.

1CREATE TABLE users (
2id INT AUTO_INCREMENT PRIMARY KEY,
3name VARCHAR(255),
4hobbies JSON
5);
6
7INSERT INTO users (name, hobbies) VALUES ('Jane Doe', JSON_ARRAY('독서', '영화 감상', '요리'));
8
9SELECT name, JSON_EXTRACT(hobbies, '$[0]') AS first_hobby FROM users;

This example shows a query that stores a user’s hobby in an JSON array and queries the first hobby using the JSON_EXTRACT function.

Processing in PostgreSQL

PostgreSQL allows you to declare the Array type, as mentioned above, and you can use query statements as you would normally approach an array.

1name VARCHAR(255),
2hobbies TEXT[]
3);
4
5INSERT INTOUSERS (name, hobbies) VALUES ("Jane Doe," "Reading," "Watching Movies," "Cooking");
6
7SELECT name, hobbies[1] AS first_hobby FROM users;

Clean up

We have summarized the RDBs that support the Array type and the RDBs that do not support the Array type. As mentioned earlier, DBs are absolutely not good, and depending on the design philosophy, we need to think a lot about which DB will be suitable for the service we are currently operating.