如何在MySQL中存储数组? [英] How to store arrays in MySQL?
问题描述
我在MySQL中有两个表.表人具有以下列:
I have two tables in MySQL. Table Person has the following columns:
id | name | fruits
fruits
列可以包含null或字符串数组,例如('apple','orange','banana')或('strawberry')等.第二个表是Table Fruit,具有以下内容三栏:
The fruits
column may hold null or an array of strings like ('apple', 'orange', 'banana'), or ('strawberry'), etc. The second table is Table Fruit and has the following three columns:
____________________________
fruit_name | color | price
____________________________
apple | red | 2
____________________________
orange | orange | 3
____________________________
...,...
那么我应该如何设计第一个表中的fruits
列,以便它可以容纳从第二个表中的fruit_name
列中获取值的字符串数组?由于MySQL中没有数组数据类型,该怎么办?
So how should I design the fruits
column in the first table so that it can hold array of strings that take values from the fruit_name
column in the second table? Since there is no array data type in MySQL, how should I do it?
推荐答案
执行此操作的正确方法是使用多个表,并在查询中使用JOIN
它们.
The proper way to do this is to use multiple tables and JOIN
them in your queries.
例如:
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_fruit
表针对与某人相关联的每个水果包含一行,并有效地将person
和fruits
表链接在一起,即.
The person_fruit
table contains one row for each fruit a person is associated with and effectively links the person
and fruits
tables together, I.E.
1 | "banana"
1 | "apple"
1 | "orange"
2 | "straberry"
2 | "banana"
2 | "apple"
要检索一个人及其所有果实时,可以执行以下操作:
When you want to retrieve a person and all of their fruit you can do something like this:
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
这篇关于如何在MySQL中存储数组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!