How to store arrays in MySQL?

In MySQL, there is no direct data type for storing arrays. However, we can use several methods to indirectly store array data. Here are several common approaches:

1. Storing via Serialization

Serialize the array into a string and store it in a text-type field (e.g., TEXT). In PHP, use the serialize() function for serialization, while in JavaScript, use JSON.stringify().

Example: Assume an array ['apple', 'banana', 'cherry']; in PHP, you can do:

php
$fruits = ['apple', 'banana', 'cherry'];
$serialized_fruits = serialize($fruits);
// Store to database

When retrieving, use unserialize() to convert the string back to an array.

2. Using JOIN Operations

If the array represents relational data (e.g., multiple related items), a more appropriate method is to leverage relational database features, such as storing data in separate tables. For instance, for a user with multiple hobbies, create a users table and a hobbies table, then use a user_hobbies junction table to link them.

Example:

• users table: id, name
• hobbies table: id, hobby
• user_hobbies table: user_id, hobby_id

Querying a user and their hobbies:

sql
SELECT u.name, h.hobby
FROM users u
JOIN user_hobbies uh ON u.id = uh.user_id
JOIN hobbies h ON uh.hobby_id = h.id
WHERE u.id = 1;

3. Using JSON Data Type

Starting from MySQL 5.7, MySQL supports the JSON data type, enabling direct storage of JSON-formatted arrays or objects in the database and retrieval/modification via SQL functions.

Example: Assume storing a user's hobby list:

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    hobbies JSON
);

INSERT INTO users (name, hobbies) VALUES ('John Doe', JSON_ARRAY('reading', 'swimming', 'coding'));

SELECT JSON_UNQUOTE(JSON_EXTRACT(hobbies, '$[1]')) AS hobby
FROM users
WHERE name = 'John Doe';

Among these methods, the choice depends on specific factors like data usage frequency, structural complexity, and performance needs. For frequent searches or individual element retrieval, the junction table method is typically more efficient. For simple storage and retrieval of entire arrays, serialization or JSON data type may be more convenient.