How Can I Store Data Similar to Arrays in MySQL?

Storing Arrays in MySQL

In MySQL, arrays as data types are not supported. However, there are alternative approaches to handle data that resembles arrays.

Tables and JOINs Approach

One method is to create multiple tables and establish relationships between them using JOINs. Consider the following schema:

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

CREATE TABLE fruits (
  fruit_name VARCHAR(20) PRIMARY KEY,
  color VARCHAR(20),
  price INT
);

CREATE TABLE person_fruit (
  person_id INT,
  fruit_name VARCHAR(20),
  PRIMARY KEY (person_id, fruit_name)
);

Here, the person_fruit table contains one row for each fruit associated with a person. This approach lets you store an essentially unlimited number of fruits.

Example:

person_id	fruit_name
1	banana
1	apple
1	orange

Query:

SELECT p.*, f.*
FROM person p
INNER JOIN person_fruit pf
  ON pf.person_id = p.id
INNER JOIN fruits f
  ON f.fruit_name = pf.fruit_name;

This query returns all the fruits associated with a person.

The above is the detailed content of How Can I Store Data Similar to Arrays in MySQL?. For more information, please follow other related articles on the PHP Chinese website!