@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;