求一个数据表设计的思路！

[sunmoon1983]

数据库为 MySQL5.7 一张表table1吧里面有 30W+数据，字段为id bigint(20),full_name varchar(66) province bigint(20),city JSON, county JSON 要展示符合下面这些条件的记录： 注意：city 和 county 储存的数据为地区的编码，如 city=["123456","234567","345678"],county=["5123456","6234567","7345678"]这种,我可以修改数据表结构

现在有一个搜索的需求，想要按照 city 和 county 搜索对应的数据，比如，搜索的条件为前端发送过来的，province=12345,city=["234567","345678"],county=["7345678","6234567"] 就是说，搜索的 city 和 county 可以是多选的！ 我要怎么设计数据表才能够方便搜索呢？求大神解惑

[rrfeng]

拆成两张表，或者直接拍扁。

[sunmoon1983]

@rrfeng 没太懂，是要搞一张 city county 和数据的关系表吗？

[Maboroshii]

for 循环分别搜吧

[wxf666]

@sunmoon1983 你要求速度吗？还是直接全表扫描？

可以接受全表扫描的话，直接 JSON_CONTAINS 呗


WITH
　 DATA(province, city, county) AS (
　　 VALUES
　　　 ROW(12345, '["123456","234567","345678"]', '["5123456","6234567","7345678"]'),
　　　 ROW(12345, '["123456","2345678","345678"]', '["5123456","6234567","7345678"]')
　)

SELECT *
FROM DATA
WHERE province = 12345
　 AND JSON_CONTAINS(city, CAST('["234567","345678"]' AS JSON))
　 AND JSON_CONTAINS(county, CAST('["7345678","6234567"]' AS JSON))

[leonme]

方法 1 、先根据 province 过滤数据，然后在内存中过滤 city 和 country 。 方法 2 、city 和 country 重新设计，拍平存，不要聚合后再存，不然查询效率低。 方法 3 、采用 ES 查询

[sunmoon1983]

@wxf666 运营会经常搜索，肯定会有速度要求的，大佬

[sunmoon1983]

@leonme 拍平存是啥意思？没太懂

[leonme]

@sunmoon1983 单独的 city 字段，然后记录 province 和 city 的关联关系

[wxf666]

@sunmoon1983 我很好奇，不是 省 一对多 市 一对多 县 吗？ 为啥存了 县，还要存 市 和 省 呢？

[kran]

json_search/json_contains

[copper20]

如果单独考虑这个需求的话，或许可以把 city 和 county 挪到另外的表里存。设计 foo_city(id, city) 表和 foo_county(id, county) 表，id 和 city / id 和 county 都设为主键（就是说直接设计两个关系表），然后在这两个表上分别建 city 字段和 county 字段上的索引，另外在现在的 table1 的 province 上也建立索引

捞数据的时候可以直接：

(SELECT
table1.idtable1
FROM
table1
INNER JOIN
(SELECT
idtable1
FROM
foo_county
WHERE
county = <county code>) co
INNER JOIN
(SELECT
idtable1
FROM
foo_city
WHERE
city = <city code>) ci
WHERE
province = <procince code>)

不过话说回来，地区代码这种事情用 varchar 存国标的地区代码，检索的时候直接用 LIKE "110101%" 之类的，用得上索引，而且代码上会方便许多吧

[wxf666]

@copper20 这个 SQL ，如何检索多个值呢？

city=["234567","345678"]

county=["7345678","6234567"]

[LeeReamond]

有多字段需求要拆表，你现在这种每行里存列表的形式没法多段索引的。比如需求上 city 需要 in 搜索，那 city 就必须是单独一列，每行只储存一个 city 和它对应的对象 id ，这样才可以 in a,b,c city 这么搜索，其他列同理

[hoopan]

这个表好奇怪，是存省市区的表？ city 跟 county 怎么对应？

[xaplux]

如果是精确匹配，将 JSON 拆分成关系表
如果是模糊匹配，那上 ES 吧

[copper20]

@wxf666 把等于运算符换成 IN 运算符吧 比如就 county IN (1, 3)

[panda1079]

对于这种可扩展又参差不齐的数据我推荐你用 mongo

[wxf666]

@copper20 『 county IN (1, 3)』没有表现出『 1 、3 必须同时存在』的意思吧，而是『 1 、3 有其一出现即可』？

[Saxton]

难为 mysql 了

[pannanxu]

```sql
create table table1
(
id bigint primary key,
full_name varchar(66)
);

create table tab1_mapping
(
id bigint primary key,
table1_id bigint,
province bigint,
city int,
county int,
index (table1_id),
index (province, city, county)

);

select *
from table1 t1
inner join tab1_mapping t1m on t1.id = t1m.table1_id
where province = 1
and city in (1, 2, 3)
and county in (1, 2, 3)
```

[sanestays]

可以尝试一下虚拟列

[yjhatfdu2]

使用 postgresql ，直接 where city@>'["123456"]'::jsonb and county @>'["23456","34567"]'::jsonb 还可以索引,基本上是最好的方案了

[yjhatfdu2]

见文档 http://www.postgres.cn/docs/12/datatype-json.html#JSON-INDEXING ，是时候换掉落后的 mysql 了

[encro]

从 MySQL 8.0.17 开始，InnoDB 支持多值索引

[b2byco]

看起来是 table1 每一行对应多个地区(区域)，每个地区有省市县三个维度。
table1( id,full_name ) 原来的 table1
area( id , province , city ,county ) 地区表 index1 province , city ,county ;index 2 city,county ;index 3 county
relation( id , table1id , areaid ) 关系表 index 1 table1id , areaid ;index 2 areaid, table1id

select t1.* from area a
inner join relation r
on a.id = r.areaid
and a.province = xxx
and a.city = xxx
and a.county = xxx -- 或者别的过滤语句
inner join table1 t1
on r.table1id = t1.id

不知道地区名字你们怎么处理的，如果出现同样的编码改名，且要保留旧数据用旧名字的话，可以直接在 area 表里加名称字段以及启用日期和结束日期，查询的时候再根据时间过滤下就好了

[JinyAa]

直接一个新字段行政区划内部编码，这种东西不是国家固定的吗？为什么要设计这么复杂，比如苏州市高新区双凤镇的行政区划内部编码就是 1.320000.320500.320585.320585105 ，往前推 1.320000.320500.320585 就是苏州市高新区。你要查哪些地域直接前缀匹配啊，多个地域没有从属关系的直接 in

[wxf666]

@LeeReamond MySQL 8.0.17 以上都支持多值索引了（索引一个数组，也就是你说的多段索引？）

用上多值索引，4 楼的 SQL 就不是全表扫描了。但楼主 @sunmoon1983 用的还是旧版 MySQL……

没办法，只能自己模拟一下了。如楼上几位所说，拍平存。



之后如何取数据呢？像 @copper20 #11 和 @pannanxu #20 那样用 in ，表现不出『同时满足』的意思

翻了翻课本，这不就是『关系除法』干的活儿吗。。

站内另一个帖子（ https://www.v2ex.com/t/772870 ）也有类似描述：

《给定一「技能表」，根据「员工技能表」，求会「技能表」中『所有技能』的员工》



但我不会同时『除以两张表』，只能分开除，再求交集了（然而还要自己模拟 INTERSECT ……）

在此抛砖引玉，求大佬合并这两个除法


『查询条件』

prov 　　 city 　　 county
——　—————　————
123 　 [30, 20] 　 [80, 70]


『结果』

id 　 prov 　　　 city 　　　 county
—　———　——————　——————
1 　 123 　 [10, 20, 30] 　 [70, 80, 90]


『 MySQL 语法（排版原因，记得去掉每行开头的 全角空格）』

（简化了建表建索引）


WITH

　-- 要查询的数据
　 query(prov, city, county) AS (
　　 SELECT 123, '[30, 20]', '[80, 70]'
　),

　-- 原始数据
　 data(id, prov, city, county) AS (
　　 VALUES
　　　 ROW(1, 123, '[10, 20, 30]', '[70, 80, 90]'),
　　　 ROW(2, 123, '[10, 21, 30]', '[70, 80, 90]')
　),

　-- 对原始数据的 (id, prov, city) 建多值索引，即：
　-- (1, 123, 10), (1, 123, 20), (1, 123, 30)
　-- (2, 123, 10), (2, 123, 21), (2, 123, 30)
　 idx_prov_city(id, prov, city) AS (
　　 SELECT data.id, prov, arr.id
　　 FROM data, json_table(data.city, '$[*]' COLUMNS(id INT PATH '$')) arr
　),

　-- 对原始数据的 (id, prov, county) 建多值索引，即
　-- (1, 123, 70), (1, 123, 80), (1, 123, 90)
　-- (2, 123, 70), (2, 123, 80), (2, 123, 90)
　 idx_prov_county(id, prov, county) AS (
　　 SELECT data.id, prov, arr.id
　　 FROM data, json_table(data.county, '$[*]' COLUMNS(id INT PATH '$')) arr
　),

　-- 除以 (prov, city)
　 divided_by_prov_city(id) AS (
　　 SELECT DISTINCT id
　　　 FROM idx_prov_city main
　　 WHERE NOT EXISTS (
　　　　　　　 SELECT *
　　　　　　　　 FROM query
　　　　　　　　 JOIN json_table(query.city, '$[*]' COLUMNS(city INT PATH '$')) arr
　　　　　　　　 WHERE NOT EXISTS (
　　　　　　　　　　　　　 SELECT *
　　　　　　　　　　　　　　 FROM idx_prov_city self
　　　　　　　　　　　　　 WHERE self.id = main.id
　　　　　　　　　　　　　　 AND self.prov = query.prov
　　　　　　　　　　　　　　 AND self.city = arr.city))
　),
　
　-- 除以 (prov, county)
　 divided_by_prov_county(id) AS (
　　 SELECT DISTINCT id
　　　 FROM idx_prov_city main
　　 WHERE NOT EXISTS (
　　　　　　　 SELECT *
　　　　　　　　 FROM query
　　　　　　　　 JOIN json_table(query.county, '$[*]' COLUMNS(county INT PATH '$')) arr
　　　　　　　　 WHERE NOT EXISTS (
　　　　　　　　　　　　　 SELECT *
　　　　　　　　　　　　　　 FROM idx_prov_county self
　　　　　　　　　　　　　 WHERE self.id = main.id
　　　　　　　　　　　　　　 AND self.prov = query.prov
　　　　　　　　　　　　　　 AND self.county = arr.county))
　)

-- 两个除法的商求交集，再 JOIN 原数据表，获取行记录
SELECT data.*
　 FROM divided_by_prov_city
　 JOIN divided_by_prov_county USING(id)
　 JOIN data USING(id)
GROUP BY id;

[wxf666]

@sunmoon1983 上面有处地方忘改了：

　 divided_by_prov_county(id) AS (
　　 SELECT DISTINCT id
　　　 FROM idx_prov_『改成这样：county 』 main

[wxf666]

@yjhatfdu2 三年前的 MySQL 8.0.17 ，也能很好地完成楼主的任务呀。。只是楼主不换新版本而已

SELECT *
FROM DATA
WHERE province = 12345
　 AND JSON_CONTAINS(city, CAST('["234567","345678"]' AS JSON))
　 AND JSON_CONTAINS(county, CAST('["7345678","6234567"]' AS JSON))

[sunmoon1983]

@wxf666 我可以更换版本的，现在只是开发阶段，在我的开发环境中是 5.7 ^_^

[wxf666]

@sunmoon1983 可以问下，MySQL 8 出来也有六年了，为啥还优先选用旧版本吗？

[fzzff]

这个类似的需求我也遇到过, 因为涉及的逻辑比较多所以没有动原表存的 json 字段, 增加了个一对多的表专门用来查询

[RangerWolf]

虚拟列试试看

[wxf666]

@fzzff 你是如何写『获取所有「同时满足多个条件」的行记录』的 SQL 语句的？

[sunmoon1983]

@wxf666 懒，哈哈哈哈，以前的系统还有 5.7 的，换了怕有问题呀

[wxf666]

@sunmoon1983 我还好奇，你的原表这些字段是啥含义？

不是 province 一对多 city 一对多 county 吗？为啥存了 county 还要存前面两个？

[joslin1215]

这不是常见的一对多设计思路么？
需要用作查询，就关系表，仅用于展示，直接赛主表

[sy20030260]

如果是 RT 敏感且高 QPS 的业务场景，还是多建一张表才是王道，直白简单易排查易维护，Keep It Simple Stupid