Hoe arrays op te slaan in MySQL?

Antwoord 1, Autoriteit 100%

De juiste manier om dit te doen, is om meerdere tabellen en JOINze in uw zoekopdrachten.

Bijvoorbeeld:

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

De person_fruitTabel bevat één rij voor elke vrucht die een persoon is gekoppeld aan en koppelt de personen fruitstafels samen, dat wil zeggen

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

Wanneer u een persoon en al hun vrucht wilt ophalen, kunt u zoiets doen:

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

Antwoord 2, Autoriteit 37%

De reden dat er geen arrays in SQL zijn, is omdat de meeste mensen het niet echt nodig hebben. Relationele databases (SQL is precies dat) werken met relaties, en meestal is het het beste als u een rij van een tabel toewijst aan elk “beetje informatie”. Als je bijvoorbeeld denkt “Ik wil hier een lijst met dingen”, maak in plaats daarvan een nieuwe tabel, waarbij je de rij in de ene tabel koppelt aan de rij in een andere tabel.[1] Op die manier kunt u M:N-relaties weergeven. Een ander voordeel is dat die links de rij met het gekoppelde item niet rommelig maken. En de database kan die rijen indexeren. Arrays worden doorgaans niet geïndexeerd.

Als u geen relationele databases nodig heeft, kunt u b.v. een winkel met sleutelwaarde.

Lees alstublieft over databasenormalisatie. De gouden regel is “[Elke] niet-sleutel [attribuut] moet een feit bevatten over de sleutel, de hele sleutel en niets anders dan de sleutel.”. Een array doet te veel. Het heeft meerdere feiten en het slaat de volgorde op (die niet gerelateerd is aan de relatie zelf). En de prestaties zijn slecht (zie hierboven).

Stel je voor dat je een personentafel hebt en je hebt een tafel met telefoontjes van mensen. Nu kunt u ervoor zorgen dat elke persoon in de rij een lijst met zijn telefoongesprekken heeft. Maar ieder mens heeft vele andere relaties met vele andere dingen. Betekent dit dat mijn persoonstabel een array moet bevatten voor elk ding waarmee hij is verbonden? Nee, dat is geen eigenschap van de persoon zelf.

[1]: Het is niet erg als de koppelingstabel slechts twee kolommen heeft (de primaire sleutels van elke tabel)! Als de relatie zelf echter aanvullende kenmerken heeft, moeten deze in deze tabel worden weergegeven als kolommen.

Antwoord 3, autoriteit 30%

MySQL 5.7 biedt nu een JSON-gegevenstype. Dit nieuwe datatype biedt een handige nieuwe manier om complexe data op te slaan: lijsten, woordenboeken, enz.

Dat gezegd hebbende, arrays brengen databases niet goed in kaart en daarom kunnen object-relationele kaarten behoorlijk complex zijn. Historisch gezien hebben mensen lijsten/arrays opgeslagen in MySQL door een tabel te maken die ze beschrijft en elke waarde als zijn eigen record toe te voegen. De tabel kan slechts 2 of 3 kolommen hebben, of er kunnen er veel meer zijn. Hoe u dit type gegevens opslaat, hangt echt af van de kenmerken van de gegevens.

Bevat de lijst bijvoorbeeld een statisch of dynamisch aantal vermeldingen? Blijft de lijst klein, of zal deze naar verwachting groeien tot miljoenen records? Zal er veel gelezen worden op deze tafel? Veel schrijft? Veel updates? Dit zijn allemaal factoren waarmee rekening moet worden gehouden bij de beslissing hoe gegevensverzamelingen moeten worden opgeslagen.

Ook Key/Value data stores, Document stores zoals Cassandra, MongoDB, Redis etc bieden ook een goede oplossing. Houd er rekening mee waar de gegevens daadwerkelijk worden opgeslagen (als deze op schijf of in het geheugen worden opgeslagen). Niet al uw gegevens hoeven in dezelfde database te staan. Sommige gegevens komen niet goed overeen met een relationele database en u kunt redenen hebben om deze ergens anders op te slaan, of u wilt misschien een in-memory key:value-database gebruiken als een hot-cache voor gegevens die ergens op schijf zijn opgeslagen of als tijdelijke opslag voor zaken als sessies.

Antwoord 4, autoriteit 28%

Een kanttekening om te overwegen, u kunt arrays opslaan in Postgres.

Antwoord 5, autoriteit 20%

Gebruik in MySQL het JSON-type.

In tegenstelling tot de antwoorden hierboven, omvat de SQL-standaard al bijna twintig jaar array-types; ze zijn nuttig, zelfs als MySQL ze niet heeft geïmplementeerd.

In uw voorbeeld wilt u waarschijnlijk drie tabellen maken: person en fruit, en vervolgens person_fruit om ze samen te voegen.

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

Als u de persoon wilt associëren met een reeks fruit, kunt u dit doen met een weergave:

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;

De weergave toont de volgende gegevens:

+---------------+-----------------+----------------------------------+
| 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 wil je JSON_ARRAYAGG, in plaats van de array samen te hacken vanuit een string.

Antwoord 6

Gebruik databaseveldtype BLOB om arrays op te slaan.

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

Retourwaarden

Retourneert een tekenreeks met een bytestream-representatie van een waarde die
kan overal worden opgeslagen.

Merk op dat dit een binaire tekenreeks is die null-bytes kan bevatten, en
als zodanig moet worden opgeslagen en behandeld. Bijvoorbeeld serialize()
output moet over het algemeen worden opgeslagen in een BLOB-veld in een database,
in plaats van een CHAR- of TEXT-veld.

Antwoord 7

U kunt uw array opslaan met behulp van Group_Concat zoals dat

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

Hier een voorbeeld in Fiddle