用Sqlite设计基于标签的数据表的最佳方法是什么？

从服务器收到的Json拥有此表格。

[
 {
  "id": 1103333,
  "name": "James",
  "tagA": [
    "apple",
    "orange",
    "grape"
  ],
  "tagB": [
    "red",
    "green",
    "blue"
  ],
  "tagC": null
  },

  {
  "id": 1103336,
  "name": "John",
  "tagA": [
    "apple",
    "pinapple",
    "melon"
  ],
  "tagB": [
    "black",
    "white",
    "blue"
  ],
  "tagC": [
    "London",
    "New York"
    ]
  }
]

一个对象可以具有多个标签，并且一个标签可以与多个对象相关联。

在此列表中，我想找到一个对象，其tagA是苹果或葡萄，而tagB是黑色。

这是我以前写的第一张表。

create table response(id integer primary key, name text not null, tagA text, 
tagB text, tagC text)

select * from response where (tagA like '%apple%' or tagA like '%grape%') and (tagB like '%black%')

这种类型的表设计存在搜索速度非常慢的问题，因为在使用ORM库（例如Room）时，它不支持fts函数的表面函数。

我想到的下一件事是为每个标签创建一个表。

create table response(id integer primary key, name text not null)

create table tagA(objectID integer, value text, primary key(objectID, value))

create table tagB(objectID integer, value text, primary key(objectID, value))

create table tagC(objectID integer, value text, primary key(objectID, value))

select * from response where id in ((select objectId from tagA where value in ('apple','grape')) 
intersect
(select objectId from tagB where value in 'black'))

这极大地增加了APK的插入时间和容量（每个附加表的容量大约是后者的两倍），但是搜索速度远远落后于FTS虚拟表。

我想尽可能避免使用FTS表，因为我需要管理更多的事情。

我错过了很多东西（索引等），但是我不知道它是什么。

如何不使用FTS方法来优化数据库？

您可以使用参考表（又名映射表以及许多其他名称）来允许标签（所有表为单个）和对象（再次为单个表）之间的多对多关系。

因此，您有一个object表，每个对象都有一个id，并且又有一个tag表，每个对象都有一个id。所以像：-

DROP TABLE IF EXISTS object_table;
CREATE TABLE IF NOT EXISTS object_table (id INTEGER PRIMARY KEY, object_name);
DROP TABLE IF EXISTS tag_table;
CREATE TABLE IF NOT EXISTS tag_table (id INTEGER PRIMARY KEY, tag_name);

您将同时填充两个

INSERT INTO object_table (object_name) VALUES
    ('Object1'),('Object2'),('Object3'),('Object4');
INSERT INTO tag_table (tag_name) VALUES
    ('Apple'),('Orange'),('Grape'),('Pineapple'),('Melon'),
    ('London'),('New York'),('Paris'),
    ('Red'),('Green'),('Blue'); -- and so on

您将拥有映射表，例如：

DROP TABLE IF EXISTS object_tag_mapping;
CREATE TABLE IF NOT EXISTS object_tag_mapping (object_reference INTEGER, tag_reference INTEGER);

将标签分配给对象会加班，反之亦然，您可以添加映射，例如：

INSERT INTO object_tag_mapping VALUES
    (1,4), -- obj1 has tag Pineapple
    (1,1),  -- obj1 has Apple
    (1,8), -- obj1 has Paris
    (1,10), -- obj1 has green
    (4,1),(4,3),(4,11), -- some tags for object 4
    (2,8),(2,7),(2,4), -- some tags for object 2
    (3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7),(3,8),(3,9),(3,10),(3,11); -- all tags for object 3

然后，您可以查询：

SELECT object_name, 
    group_concat(tag_name,' ~ ') AS tags_for_this_object 
FROM object_tag_mapping 
JOIN object_table ON object_reference = object_table.id
JOIN tag_table ON tag_reference = tag_table.id
GROUP BY object_name
;

group_concat是一个聚合函数（适用于GROUP），该函数将使用（可选）分隔符为指定列找到的所有值连接在一起。

查询的结果是：

以下可能是基于标签的搜索（不是您可能同时使用tag_name和tag_reference）：-

SELECT object_name, tag_name 
FROM object_tag_mapping 
JOIN object_table ON object_reference = object_table.id
JOIN tag_table ON tag_reference = tag_table.id
WHERE tag_name = 'Pineapple' OR tag_reference = 9
;

这将导致：-

请注意，这是一个简单的概述，例如，您可能要考虑将映射表作为WITHOUT ROWID表使用，或者可能具有复合UNIQUE约束。

附加评论：-

如何实现同时包含两个或多个标签的查询？

如果您想要特定的标签但仍然可行，则要复杂一些。这是一个使用CTE（公用表表达式）和HAVING子句（在生成输出后应用的where子句，因此可以应用于聚合）的示例：

WITH cte1(otm_oref,otm_tref,tt_id,tt_name, ot_id, ot_name) AS 
    (
        SELECT * FROM object_tag_mapping 
        JOIN tag_table ON tag_reference = tag_table.id 
        JOIN object_table ON object_reference = object_table.id
        WHERE tag_name = 'Pineapple' OR tag_name = 'Apple'
    )
SELECT ot_name, group_concat(tt_name), count() AS cnt FROM CTE1 
GROUP BY otm_oref
HAVING cnt = 2
;

结果是：