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

[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)
```