在mysql的列中保存多个项目 [英] Holding multiple items in a column on mysql
问题描述
我当前正在创建一个数据库,它允许用户上传出版物,该出版物存储在一个名为paper的表中,它存储paper_id,title,抽象文件名和topic_id.我有一个名为topic的表,该表具有topic_id和topic_name,我用于用户选择要发布的主题.但是我希望用户能够选择至少3个主题,是否可以使用此系统?我用尽了所有的想法,将不胜感激
I am currently creating a database, it allows a user to upload a publication, which is stored in a table called paper, it stores the paper_id, title, abstract filename and topic_id .I have a table called topic which has topic_id and topic_name which i use for the user to select a topic for their publication. However i want the user to be able to select at least 3 topics, is this possible using this system? I have run out of ideas of how to do it and help would be greatly appreciated
推荐答案
不要将topic_id
存储在paper
表中.而是创建另一个将topic_id
链接到paper_id
的规范化(多对多)表.
Don't store topic_id
in the paper
table. Instead, create another normalized (many-to-many) table which links topic_id
to paper_id
.
/* Each paper can exist in this table as many times as necessary for all its topics */
CREATE TABLE paper_topics (
paper_id INT NOT NULL,
topic_id INT NOT NULL,
FOREIGN KEY (paper_id) REFERENCES paper (paper_id),
FOREIGN KEY (topic_id) REFERENCES topic (topic_id),
PRIMARY KEY (paper_id, topic_id)
);
这将使您可以根据需要在每篇论文中存储尽可能多的主题.
This will allow you to store as many topics per paper as necessary.
要检索论文的主题,请使用:
To retrieve the topics for a paper, use:
SELECT
paper.*,
topic_name
FROM
paper
LEFT JOIN paper_topics ON paper.paper_id = topic.paper_id
LEFT JOIN topic ON topic.topic_id = paper_topic.topic_id
WHERE paper.paper_id = <some paper id value>
尝试将多个值存储在一个列中(例如,在paper
表中以逗号分隔的topic_id
列表)几乎不是一个好主意.原因是为了对其进行查询,必须使用 FIND_IN_SET()
,这提高了执行连接的复杂性,并使得在查询时无法利用列索引.
It is just about never a good idea to attempt to store multiple values in one column (such as a comma-separated list of topic_id
in the paper
table). The reason is that in order to query against it, you must use FIND_IN_SET()
which drives up the complexity of performing joins and makes it impossible to utilize a column index when querying.
这篇关于在mysql的列中保存多个项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!