MySQL에 배열을 저장하는 방법은 무엇입니까?

MySQL에 배열을 저장하는 방법은 무엇입니까?

---

MySQL에는 두 개의 테이블이 있습니다. Table Person에는 다음 열이 있습니다.

id | name | fruits

fruits열에 null 또는 ( '사과', '오렌지', '바나나') 또는 ( '딸기') 등 두 번째 테이블이 표 열매와 다음의 세 개의 열이 같은 문자열의 배열을 보유 할 수있다 :

____________________________
fruit_name | color  | price
____________________________
apple      | red    | 2
____________________________
orange     | orange | 3
____________________________
...,...

그렇다면 두 번째 테이블 fruits의 fruit_name열에서 값을 가져 오는 문자열 배열을 보유 할 수 있도록 첫 번째 테이블 의 열을 어떻게 디자인해야 합니까? MySQL에는 배열 데이터 유형이 없으므로 어떻게해야합니까?

---

이를 수행하는 적절한 방법은 JOIN쿼리에서 여러 테이블과 테이블을 사용하는 것입니다.

예를 들면 :

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

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

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

이 person_fruit테이블에는 사람이 관련된 각 과일에 대해 하나의 행이 포함되어 있으며 person및 fruits테이블을 효과적으로 연결합니다 . IE

1 | "banana"
1 | "apple"
1 | "orange"
2 | "straberry"
2 | "banana"
2 | "apple"

사람과 모든 과일을 회수하려면 다음과 같이 할 수 있습니다.

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

---

SQL에 배열이없는 이유는 대부분의 사람들이 실제로 필요하지 않기 때문입니다. 관계형 데이터베이스 (정확히 SQL)는 관계를 사용하여 작동하며 대부분의 경우 각 "정보 비트"에 테이블의 한 행을 할당하는 것이 가장 좋습니다. 예를 들어, "여기에있는 항목의 목록을 원합니다"라고 생각할 수있는 경우 대신 한 테이블의 행을 다른 테이블의 행과 연결하여 새 테이블을 만듭니다. [1] 이렇게하면 M : N 관계를 나타낼 수 있습니다. 또 다른 장점은 이러한 링크가 연결된 항목을 포함하는 행을 복잡하게 만들지 않는다는 것입니다. 그리고 데이터베이스는 이러한 행을 인덱싱 할 수 있습니다. 배열은 일반적으로 인덱싱되지 않습니다.

관계형 데이터베이스가 필요하지 않은 경우 키-값 저장소 등을 사용할 수 있습니다.

Read about database normalization, please. The golden rule is "[Every] non-key [attribute] must provide a fact about the key, the whole key, and nothing but the key.". An array does too much. It has multiple facts and it stores the order (which is not related to the relation itself). And the performance is poor (see above).

Imagine that you have a person table and you have a table with phone calls by people. Now you could make each person row have a list of his phone calls. But every person has many other relationships to many other things. Does that mean my person table should contain an array for every single thing he is connected to? No, that is not an attribute of the person itself.

[1]: It is okay if the linking table only has two columns (the primary keys from each table)! If the relationship itself has additional attributes though, they should be represented in this table as columns.

---

MySQL 5.7 now provides a JSON data type. This new datatype provides a convenient new way to store complex data: lists, dictionaries, etc.

That said, rrays don't map well databases which is why object-relational maps can be quite complex. Historically people have stored lists/arrays in MySQL by creating a table that describes them and adding each value as its own record. The table may have only 2 or 3 columns, or it may contain many more. How you store this type of data really depends on characteristics of the data.

For example, does the list contain a static or dynamic number of entries? Will the list stay small, or is it expected to grow to millions of records? Will there be lots of reads on this table? Lots of writes? Lots of updates? These are all factors that need to be considered when deciding how to store collections of data.

Also, Key:Value data stores / Document stores such as Cassandra, MongoDB, Redis etc provide a good solution as well. Just be aware of where the data is actually being stored (if its being stored on disk or in memory). Not all of your data needs to be in the same database. Some data does not map well to a relational database and you may have reasons for storing it elsewhere, or you may want to use an in-memory key:value database as a hot-cache for data stored on disk somewhere or as an ephemeral storage for things like sessions.

---

A sidenote to consider, you can store arrays in Postgres.

---

In MySQL, use the JSON type.

Contra the answers above, the SQL standard has included array types for almost twenty years; they are useful, even if MySQL has not implemented them.

In your example, however, you'll likely want to create three tables: person and fruit, then person_fruit to join them.

DROP TABLE IF EXISTS person_fruit;
DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS fruit;

CREATE TABLE person (
  person_id   INT           NOT NULL AUTO_INCREMENT,
  person_name VARCHAR(1000) NOT NULL,
  PRIMARY KEY (person_id)
);

CREATE TABLE fruit (
  fruit_id    INT           NOT NULL AUTO_INCREMENT,
  fruit_name  VARCHAR(1000) NOT NULL,
  fruit_color VARCHAR(1000) NOT NULL,
  fruit_price INT           NOT NULL,
  PRIMARY KEY (fruit_id)
);

CREATE TABLE person_fruit (
  pf_id     INT NOT NULL AUTO_INCREMENT,
  pf_person INT NOT NULL,
  pf_fruit  INT NOT NULL,
  PRIMARY KEY (pf_id),
  FOREIGN KEY (pf_person) REFERENCES person (person_id),
  FOREIGN KEY (pf_fruit) REFERENCES fruit (fruit_id)
);

INSERT INTO person (person_name)
VALUES
  ('John'),
  ('Mary'),
  ('John'); -- again

INSERT INTO fruit (fruit_name, fruit_color, fruit_price)
VALUES
  ('apple', 'red', 1),
  ('orange', 'orange', 2),
  ('pineapple', 'yellow', 3);

INSERT INTO person_fruit (pf_person, pf_fruit)
VALUES
  (1, 1),
  (1, 2),
  (2, 2),
  (2, 3),
  (3, 1),
  (3, 2),
  (3, 3);

If you wish to associate the person with an array of fruits, you can do so with a view:

DROP VIEW IF EXISTS person_fruit_summary;
CREATE VIEW person_fruit_summary AS
  SELECT
    person_id                                                                                              AS pfs_person_id,
    max(person_name)                                                                                       AS pfs_person_name,
    cast(concat('[', group_concat(json_quote(fruit_name) ORDER BY fruit_name SEPARATOR ','), ']') as json) AS pfs_fruit_name_array
  FROM
    person
    INNER JOIN person_fruit
      ON person.person_id = person_fruit.pf_person
    INNER JOIN fruit
      ON person_fruit.pf_fruit = fruit.fruit_id
  GROUP BY
    person_id;

The view shows the following data:

+---------------+-----------------+----------------------------------+
| pfs_person_id | pfs_person_name | pfs_fruit_name_array             |
+---------------+-----------------+----------------------------------+
|             1 | John            | ["apple", "orange"]              |
|             2 | Mary            | ["orange", "pineapple"]          |
|             3 | John            | ["apple", "orange", "pineapple"] |
+---------------+-----------------+----------------------------------+

In 5.7.22, you'll want to use JSON_ARRAYAGG, rather than hack the array together from a string.

---

Use database field type BLOB to store arrays.

Ref: http://us.php.net/manual/en/function.serialize.php

Return Values

Returns a string containing a byte-stream representation of value that can be stored anywhere.

Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field.

---

you can store your array using group_Concat like that

 INSERT into Table1 (fruits)  (SELECT GROUP_CONCAT(fruit_name) from table2)
 WHERE ..... //your clause here

HERE an example in fiddle

참고URL : https://stackoverflow.com/questions/17371639/how-to-store-arrays-in-mysql