Come archiviare gli array in MySQL?

Ho due tabelle in MySQL. Tabella Persona ha le seguenti colonne:

id | name | fruits 

La colonna di fruits può contenere nullo o una serie di stringhe come (‘mela’, ‘arancione’, ‘banana’) o (‘fragola’), ecc. La seconda tabella è Table Fruit e presenta le seguenti tre colonne:

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

Quindi, come dovrei progettare la colonna di fruits nella prima tabella in modo che possa contenere una serie di stringhe che prendono i valori dalla colonna fruit_name nella seconda tabella? Dal momento che non esiste un tipo di dati array in MySQL, come dovrei farlo?

Il modo corretto per farlo è utilizzare più tabelle e JOIN alle query.

Per esempio:

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

La tabella person_fruit contiene una riga per ogni frutto a cui una persona è associata e collega efficacemente le tabelle person e fruits insieme, IE

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

Quando vuoi recuperare una persona e tutti i suoi frutti puoi fare qualcosa del genere:

 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 

La ragione per cui non ci sono matrici in SQL, è perché la maggior parte delle persone non ne ha davvero bisogno. I database relazionali (SQL è esattamente questo) funzionano usando le relazioni e, la maggior parte delle volte, è meglio se assegni una riga di una tabella a ciascun “bit di informazione”. Ad esempio, dove potresti pensare “Mi piacerebbe un elenco di cose qui”, invece crea una nuova tabella, collegando la riga in una tabella con la riga in un’altra tabella. [1] In questo modo, puoi rappresentare le relazioni M: N. Un altro vantaggio è che quei collegamenti non ingombrano la riga contenente l’object collegato. E il database può indicizzare quelle righe. Gli array in genere non sono indicizzati.

Se non hai bisogno di database relazionali, puoi usare, ad esempio, un archivio di valori-chiave.

Leggi la normalizzazione del database , per favore. La regola d’oro è “[Ogni] non-chiave [attributo] deve fornire un dato sulla chiave, l’intera chiave e nient’altro che la chiave.”. Un array fa troppo. Ha più fatti e memorizza l’ordine (che non è correlato alla relazione stessa). E la performance è scarsa (vedi sopra).

Immagina di avere un tavolo per persone e di avere un tavolo con le telefonate delle persone. Ora puoi fare in modo che ogni persona abbia una lista delle sue telefonate. Ma ogni persona ha molte altre relazioni con molte altre cose. Significa che la mia tabella personale dovrebbe contenere un array per ogni singola cosa a cui è connesso? No, non è un attributo della persona stessa.

[1]: Va bene se la tabella di collegamento ha solo due colonne (le chiavi primarie di ogni tabella)! Se la relazione stessa ha attributi aggiuntivi, dovrebbero essere rappresentati in questa tabella come colonne.

MySQL 5.7 ora fornisce un tipo di dati JSON . Questo nuovo tipo di dati offre un nuovo modo conveniente per archiviare dati complessi: elenchi, dizionari, ecc.

Detto questo, i rails non mappano bene i database, motivo per cui le mappe relazionali oggettuali possono essere piuttosto complesse. Storicamente le persone hanno memorizzato liste / array in MySQL creando una tabella che li descriva e aggiungendo ogni valore come proprio record. La tabella può avere solo 2 o 3 colonne o potrebbe contenere molte di più. Il modo in cui memorizzi questo tipo di dati dipende in realtà dalle caratteristiche dei dati.

Ad esempio, l’elenco contiene un numero statico o dinamico di voci? La lista rimarrà piccola o si prevede che cresca fino a milioni di record? Ci saranno molte letture su questo tavolo? Un sacco di scritture? Un sacco di aggiornamenti? Questi sono tutti fattori che devono essere considerati al momento di decidere come archiviare le raccolte di dati.

Inoltre, Key: i negozi di dati di valore / i negozi di documenti come Cassandra, MongoDB, Redis ecc. Offrono anche una buona soluzione. Basta essere consapevoli di dove vengono effettivamente memorizzati i dati (se sono memorizzati su disco o in memoria). Non tutti i tuoi dati devono essere nello stesso database. Alcuni dati non si adattano bene a un database relazionale e potresti avere motivi per archiviarli altrove, oppure potresti voler usare una chiave in memoria: database di valori come hot-cache per i dati memorizzati su disco da qualche parte o come spazio di archiviazione effimero per cose come sessioni.

Un sidenote da considerare, è ansible archiviare gli array in Postgres.

In MySQL, usa il tipo JSON.

Per quanto riguarda le risposte precedenti, lo standard SQL ha incluso tipi di array per quasi venti anni; sono utili, anche se MySQL non li ha implementati.

Nel tuo esempio, tuttavia, probabilmente vorrai creare tre tabelle: persona e frutta, quindi person_fruit per unirle.

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

Se desideri associare la persona a una serie di frutti, puoi farlo in una vista:

 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; 

La vista mostra i seguenti dati:

 +---------------+-----------------+----------------------------------+ | 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, ti consigliamo di usare JSON_ARRAYAGG , piuttosto che hackerare l’array insieme da una stringa.

Utilizzare il campo BLOB del tipo di database per archiviare gli array.

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

Valori di ritorno

Restituisce una stringa contenente una rappresentazione in byte di valore che può essere memorizzata ovunque.

Si noti che questa è una stringa binaria che può includere byte nulli e deve essere archiviata e gestita come tale. Ad esempio, l’output serialize () dovrebbe generalmente essere memorizzato in un campo BLOB in un database, piuttosto che in un campo CHAR o TEXT.

puoi memorizzare il tuo array usando group_Concat in questo modo

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

QUI un esempio di violino