Jak przechowywać tablice w MySQL?

Powodem, dla którego w SQL nie ma tablic, jest to, że większość ludzi tak naprawdę jej nie potrzebuje. Relacyjne bazy danych (właśnie taki jest SQL) działają przy użyciu relacji, a przez większość czasu najlepiej jest przypisać jeden wiersz tabeli do każdego "bitu informacji". Na przykład, gdy możesz pomyśleć "chciałbym listę rzeczy tutaj", zamiast zrobić nową tabelę, łącząc wiersz w jednej tabeli z wierszem w innej tabeli.[1] w ten sposób możesz reprezentować relacje M: n. Kolejną zaletą jest to, że te linki nie zaśmieci wiersza zawierającego połączony element. Baza danych może indeksować te wiersze. Tablice zazwyczaj nie są indeksowane.

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

Przeczytaj o normalizacji bazy danych , proszę. Złota reguła brzmi: "[każdy] nie-klucz [atrybut] musi dostarczać informacji o kluczu, o całym kluczu i o niczym innym jak o kluczu.". Tablica robi za dużo. Ma wiele faktów i przechowuje zamówienie (co nie jest związane z samej relacji). A wydajność jest słaba (patrz wyżej).

Wyobraź sobie, że masz stół z osobami i masz stół z rozmowami telefonicznymi przez ludzi. Teraz możesz sprawić, że każda osoba będzie miała listę swoich telefonów. Ale każda osoba ma wiele innych związków z wieloma innymi rzeczami. Czy to znaczy, że moja tabela osób powinna zawierać tablicę dla każdej rzeczy, z którą jest połączony? Nie, to nie jest atrybut samej osoby.

[1]: jest w porządku, jeśli tabela linkowania tylko posiada dwie kolumny (podstawowe klucze z każdej tabeli)! Jeśli jednak sama relacja ma dodatkowe atrybuty, powinny one być reprezentowane 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: listy, słowniki itp.

To powiedziawszy, rrays nie mapuje dobrze baz danych, dlatego mapy obiektowo-relacyjne mogą być dość złożone. Historycznie ludzie przechowywali 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ć o wiele więcej. Jak przechowywać tego typu dane tak naprawdę zależą od charakterystyki danych.

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

Również Key: Value przechowuje dane / przechowuje dokumenty, takie jak Cassandra, MongoDB, Redis itp. rozwiązanie również. Po prostu należy pamiętać, gdzie dane są faktycznie przechowywane (jeśli są przechowywane na dysku lub w pamięci). Nie wszystkie Twoje dane muszą znajdować się w tej samej bazie danych. Niektóre dane nie mapują się dobrze do relacyjnej bazy danych i możesz mieć powody do przechowywania ich w innym miejscu lub możesz użyć bazy danych key:value jako hot-cache dla danych przechowywanych gdzieś na dysku lub jako efemerycznego magazynu dla rzeczy takich jak sesje.

Uwaga boczna do rozważenia, można przechowywać tablice 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ą one użyteczne, nawet jeśli MySQL ich nie zaimplementował.

W twoim przykładzie prawdopodobnie będziesz chciał utworzyć trzy tabele: person I fruit, a następnie person_fruit, 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 tablicą 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 pokazuje następujące data:

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

W wersji 5.7.22, będziesz chciał użyć JSON_ARRAYAGG , zamiast zhakować tablicę z łańcucha znaków.

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

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

Zwracane Wartości

Zwraca łańcuch zawierający reprezentację strumienia bajtów wartości, która może być przechowywany w dowolnym miejscu.

Zauważ, że jest to ciąg binarny, który może zawierać null bajtów i musi być przechowywany i traktowany jako taki. Na przykład serialize() dane wyjściowe powinny być zazwyczaj przechowywane w polu BLOB w bazie danych, zamiast znaku lub pola tekstowego.

Możesz zapisać swoją tablicę używając 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 fiddle