Jak przechowywać tablice w MySQL?

Mam dwie tabele w MySQL. Tabela Osoba ma następujące kolumny:

id | name | fruits

fruitsKolumna może posiadać wartość zerową lub szereg łańcuchów jak ( "KOMPUTER„ pomarańczy”,„banana”), lub („”), truskawka itp Druga tabela Tabela owoców i ma trzy kolumny:

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

Jak więc zaprojektować fruitskolumnę w pierwszej tabeli, aby mogła pomieścić tablicę ciągów pobierających wartości z fruit_namekolumny w drugiej tabeli? Ponieważ w MySQL nie ma typu danych tablicowych, jak mam to zrobić?

Właściwym sposobem na to jest użycie wielu tabel i JOINich w zapytaniach.

Na przykład:

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_fruitTabela zawiera jeden wiersz dla każdego owocu osoba jest związana z i skutecznie łączy personi fruitstabel razem, IE

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

Jeśli chcesz odzyskać osobę i wszystkie jej owoce, możesz zrobić coś takiego:

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

Powodem, dla którego nie ma tablic w SQL, jest to, że większość ludzi tak naprawdę tego nie potrzebuje. Relacyjne bazy danych (dokładnie tym jest SQL) działają z wykorzystaniem relacji i przez większość czasu najlepiej jest przypisać jeden wiersz tabeli do każdego „bitu informacji”. Na przykład, jeśli możesz pomyśleć „Chciałbym tutaj uzyskać listę rzeczy”, zamiast tego utwórz nową tabelę, łącząc wiersz w jednej tabeli z wierszem w innej tabeli. [1] W ten sposób możesz przedstawić relacje M: N. Kolejną zaletą jest to, że te linki nie zaśmiecają wiersza zawierającego linkowany element. Baza danych może indeksować te wiersze. Tablice zazwyczaj nie są indeksowane.

Jeśli nie potrzebujesz relacyjnych baz danych, możesz skorzystać np. Z magazynu klucz-wartość.

Przeczytaj o normalizacji baz danych . Złota zasada brzmi: „[Każdy] element niebędący kluczem [atrybut] musi zawierać informacje o kluczu, całym kluczu i tylko o kluczu”. Tablica robi za dużo. Zawiera wiele faktów i przechowuje kolejność (która nie jest związana z samą relacją). Wydajność jest słaba (patrz wyżej).

Wyobraź sobie, że masz stolik dla osób i masz stolik z telefonami od ludzi. Teraz możesz sprawić, by każdy wiersz miał listę swoich rozmów telefonicznych. Ale każda osoba ma wiele innych związków z wieloma innymi rzeczami. Czy to oznacza, że ​​moja tabela person powinna zawierać tablicę dla każdej rzeczy, z którą jest połączony? Nie, to nie jest atrybut samej osoby.

[1]: W porządku, jeśli tabela łącząca ma tylko dwie kolumny (klucze podstawowe z każdej tabeli)! Jeśli jednak sama relacja ma dodatkowe atrybuty, powinny one być przedstawione w tej tabeli jako kolumny.

MySQL 5.7 udostępnia teraz typ danych JSON . Ten nowy typ danych zapewnia wygodny nowy sposób przechowywania złożonych danych: list, słowników itp.

To powiedziawszy, rraye nie mapują dobrze baz danych, dlatego mapy obiektowo-relacyjne mogą być dość złożone. W przeszłości ludzie zapisywali listy / tablice w MySQL, tworząc tabelę, która je opisuje i dodając każdą wartość jako własny rekord. Tabela może mieć tylko 2 lub 3 kolumny lub może zawierać znacznie więcej. Sposób przechowywania tego typu danych w rzeczywistości zależy od właściwości danych.

Na przykład, czy lista zawiera statyczną czy dynamiczną liczbę wpisów? Czy lista pozostanie mała, czy też oczekuje się, że wzrośnie do milionów rekordów? Czy na tym stole będzie dużo czytań? Dużo pisze? Wiele aktualizacji? Są to wszystkie czynniki, które należy wziąć pod uwagę przy podejmowaniu decyzji o sposobie przechowywania zbiorów danych.

Ponadto klucz: magazyny danych wartości / magazyny dokumentów, takie jak Cassandra, MongoDB, Redis itp. Również stanowią dobre rozwiązanie. Po prostu bądź świadomy tego, gdzie dane są faktycznie przechowywane (jeśli są przechowywane na dysku lub w pamięci). Nie wszystkie dane muszą znajdować się w tej samej bazie danych. Niektóre dane nie są dobrze mapowane do relacyjnej bazy danych i możesz mieć powody do przechowywania ich w innym miejscu lub możesz chcieć użyć bazy danych klucz: wartość w pamięci jako gorącej pamięci podręcznej dla danych przechowywanych gdzieś na dysku lub jako tymczasowego magazynu na takie rzeczy jak sesje.

Warto wziąć pod uwagę, że tablice można przechowywać w Postgres.

W MySQL użyj typu JSON.

W przeciwieństwie do powyższych odpowiedzi, standard SQL zawiera typy tablic od prawie dwudziestu lat; są przydatne, nawet jeśli MySQL ich nie zaimplementował.

Jednak w twoim przykładzie prawdopodobnie będziesz chciał utworzyć trzy tabele: osoba i owoc, a następnie osoba_owoc, aby do nich dołączyć.

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);

Jeśli chcesz skojarzyć osobę z szeregiem owoców, możesz to zrobić z widokiem:

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;

Widok przedstawia następujące dane:

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

W 5.7.22 będziesz chciał użyć JSON_ARRAYAGG , zamiast hakować tablicę razem z ciągiem.

Użyj pola bazy danych typu BLOB do przechowywania tablic.

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

Zwracane wartości

Zwraca ciąg znaków zawierający reprezentację wartości w strumieniu bajtów, która może być przechowywana w dowolnym miejscu.

Zauważ, że jest to ciąg binarny, który może zawierać bajty zerowe i musi być przechowywany i traktowany jako taki. Na przykład dane wyjściowe serialize () powinny zasadniczo być przechowywane w polu BLOB w bazie danych, a nie w polu CHAR lub TEXT.

możesz przechowywać swoją tablicę za pomocą group_Concat w ten sposób

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

TUTAJ przykład w skrzypcach