Jak przechowywać tablice w MySQL?


119

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ć?



1
co powiesz na dodanie go jako oddzielnych wpisów, pomarańczowy, 2, 1, róża, 2, 1 itd., a następnie możesz użyć zapytań do traktowania ich tak, jakby były tablicami.
Sai

@JanusTroelsen: Nie używam PHP do odczytu / zapisu bazy danych. Więc czy istnieje uniwersalny sposób, aby to zrobić?
tonga

1
@tonga, sprawdź moje skrzypce, czy tego chcesz?
echo_Me

Odpowiedzi:


164

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

4
Trzecia tabela to tabela połączeń między osobą a owocem. Więc jeśli dana osoba ma 100 owoców. Muszę utworzyć 100 wierszy w trzeciej tabeli, prawda? Czy to jest wydajne?
tonga

1
@tonga Dokładnie, każdy ze 100 wierszy miałby to samo, person_idale inne fruit_name. Jest to efektywna implementacja teorii z odpowiedzi Janusa.
Bad Wolf

1
Czy zawsze jest prawdą, że jakakolwiek relacja między dwiema tabelami musi być przechowywana w trzeciej tabeli? Czy mogę po prostu wykonać zapytanie, aby znaleźć relację, po prostu przechowując klucze podstawowe z dwóch tabel?
tonga

2
Tak, tak jest teraz ustawiony przykład. Wszelkie informacje o osobie powinny znajdować się w personstole, wszelkie informacje o owocach w fruitsstole, a także wszelkie informacje dotyczące relacji między konkretną osobą a konkretnym owocem na person_fruitstole. Ponieważ w tym przykładzie nie ma żadnych dodatkowych informacji, person_fruittabela ma tylko dwie kolumny, klucze podstawowe tabel personi fruits. Ilość konkretnego owocu jest przykładem czegoś innego, co jednak mogłoby znaleźć się w person_fruittabeli.
Bad Wolf,

2
Czy nie byłoby lepiej użyć INTna kluczu fruitsi mieć to tylko INTw person_fruit? Tak więc nazwę można zmienić później, a także wymagałoby mniej miejsca, jeśli nie masz o wiele więcej wierszy fruitsniż w person_fruit.
12431234123412341234123

59

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.


2
Dzięki Janus. To ma sens. Teraz rozumiem, dlaczego MySQL nie obsługuje typu tablicy w kolumnie.
tonga

2
@Sai - Czy naprawdę potrzebuję rozwiązania NoSQL do rzeczy, które robię?
tonga

1
OK, więc jeśli mam tabelę, w której pole zawiera numeryczną tablicę tysięcy elementów, np. Jakieś dane 2D zebrane z czujnika, to czy dużo lepiej jest użyć NoSQL DB?
tonga

5
@tonga: ilość danych nie określa typu bazy danych, ale charakter danych. Jeśli nie ma relacji, nie potrzebujesz w relacyjnej bazie danych. Ale ponieważ jest to standard branżowy, możesz go zachować i po prostu nie używać funkcji relacyjnych. Większość danych jest w jakiś sposób relacyjna! Częstym powodem denormalizacji relacyjnych baz danych lub korzystania z magazynów klucza i wartości są przyczyny związane z wydajnością. Ale te problemy pojawiają się tylko wtedy, gdy masz MILIONY rzędów! Nie optymalizuj przedwcześnie! Poleciłbym po prostu wybrać bazę danych SQL (polecam PostgreSQL). Jeśli masz problemy, zapytaj.
Janus Troelsen

2
PostgreSQL ma również wbudowane magazyny wartości kluczy, co oznacza, że ​​byłoby jeszcze łatwiej odejść od modelu relacyjnego, jeśli nie pasuje do ciebie.
Janus Troelsen

50

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.


43

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


6
Uwaga dodatkowa: mogą być indeksowane, więc zapytania sprawdzające istnienie określonych wartości w tablicy mogą być bardzo szybkie. To samo dotyczy złożonych typów JSON.
rozkład jazdy

5
To w żaden sposób nie odpowiada na pytanie. OP zapytał o MySQL.
jhpratt

1
Jeśli używasz ArrayField w Postgres i masz wyczerpującą listę wartości w tej kolumnie (jak ustalona lista tagów), możesz utworzyć indeks GIN - znacznie przyspieszy to zapytania w tej kolumnie.
lumos42

25

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.


2

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.


-4

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


4
Niezbyt dobrze wyjaśnione. Złe nazwy tabel.
Martin F
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.