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