如何使用sqlite将多个值插入单个列？

I would like to have a column in my table that can store a variable amount of int values. What is the best way to do this? I would like to be able to select based on these ints so a csv list would not work.

我希望在我的表中有一个列可以存储可变数量的int值。做这个的最好方式是什么?我希望能够根据这些整数进行选择,因此csv列表不起作用。

Basically I have a bunch of rows in my table that can belong to multiple different categories. I would like to store the category ids in my table and be able to select rows based on the categories they belong to. I believe this is called a many to many relationship. I am using sqlite.

基本上我的表中有一堆行可以属于多个不同的类别。我想在我的表中存储类别ID,并能够根据它们所属的类别选择行。我相信这被称为多对多的关系。我正在使用sqlite。

2 个解决方案

#1

You will need an intermediary table, where each row is a item and a category.

您将需要一个中间表,其中每一行都是一个项目和一个类别。

ItemID          Category
111             1
111             2
222             1
222             2
222             3
333             3

To select all items based on a category (say category 2), you could do the following query

要根据类别(例如类别2)选择所有项目,您可以执行以下查询

SELECT * FROM Items AS I INNER JOIN ItemsInCategories AS N ON N.ItemID = I.ItemID WHERE N.Category = 2

And this would return

这会回来

ItemID
111
222

#2

A many to many relationship should be accomplished using a mapping table. Not by hacking a multi-valued column type

应使用映射表完成多对多关系。不是通过黑客攻击多值列类型

For example this BlogPost <-> Category sample:

例如,这个BlogPost < - >类别示例:

Blog
  Id
  Title
  Content

Category
  Id
  Title

Blog_Category
  BlogId
  CategoryId

This means that when BlogPost with Id 12, is part of Category 3,5 and 10, Blog_Category contains these rows:

这意味着当具有Id 12的BlogPost属于类别3,5和10时,Blog_Category包含以下行:

12, 3
12, 5
12, 10

#1