一种多选项的高效存取（存储、查询）解决方案

紫色喵 發表於 2026-2-3 22:26:00

一种多选项的高效存取（存储、查询）解决方案

本文主要内容
<ul>
<li>对于多选项的值，如何保存？本文提供了一种非常规的方案。</li>
<li>对于记录在数据库中的多选项的值，如何快速查询那些记录是包含了某个（某些）选项？本文使用了“与位运算”解决查询问题。</li>
<li>源码地址：https://github.com/HackyleShawe/JavaBackEndDemos/tree/master/BusinessCommonSolution/multi-options-storage-query-demo</li>
</ul>
文章前置知识：SpringBoot、JDBCTemplate、位运算（与运算）、jQuery
内容导览 
<div class="mce-toc">
<ul>
<li>背景</li>
<li>设计思想
<ul>
<li>编码：将多选项转换为数字
<ul>
<li>例1</li>
<li>例2</li>
</ul>
</li>
<li>查询原理</li>
</ul>
</li>
<li>项目启动
<ul>
<li>新增数据演示</li>
<li>查询数据演示</li>
</ul>
</li>
<li>查询示例
<ul>
<li>构造数据</li>
<li>插入到数据库</li>
<li>查询选择了"1-编程"这个选项的记录</li>
<li>查询选择了"2-听音乐唱歌，5-看电影"这些选项的记录</li>
</ul>
</li>
</ul>
</div>
<h1 id="mcetoc_1h68ttla31tr7" class="md-end-block md-heading md-focus">背景</h1>
在项目开发中，如何保存多选项的值呢？例如下图中的职业发展和兴趣爱好
<img src="https://res.hackyle.com/blog/2023/07/f0d775b5795840f4999f5cbc88ee5ab0.png">
<ul class="ul-list" data-mark="-">
<li class="md-list-item md-focus-container">
最容易想到的就是，选择了哪些选项，就把该选项值存储起来。
</li>
<li class="md-list-item md-focus-container">
在数据库层面设置一个VARCHAR，例如选择了"收入无上线、培训与发展、职业价值感"，前端就传递"收入无上线、培训与发展、职业价值感"，数据库就保存为"收入无上线、培训与发展、职业价值感"。
</li>
</ul>
可是这样存在一个问题，我要查询那些人选择了其中的某个、某些选项，就很难实现。例如，查询那些人的兴趣爱好是"编程、篮球"，查询兴趣爱好是"看书、写作"的人数有多少。
为了解决这种多选项的高效查询问题，本人设计了一种方法，可以实现快速、高效地查询多选项中有某个、某些选项的所有记录。
<h1 id="mcetoc_1h68ttla31tr8" class="md-end-block md-heading">设计思想</h1>
核心思路：
<ol class="ol-list">
<li class="md-list-item">
编码：将每个选项与特定的比特位上对应起来，选中为1，未选中为0
</li>
<li class="md-list-item">
存储：将比特位转换为Long型（64bit，最大支持64个多选项），数据库表字段为BIGINT
</li>
<li class="md-list-item md-focus-container">
查询：与（&）位运算
</li>
</ol>
主要流程
<ul class="ul-list" data-mark="-">
<li class="md-list-item">
编码：将多选项进行编号：
<ul class="ul-list" data-mark="-">
<li class="md-list-item">
例如：对职业发展的多选项进行编号为：1-收入无上限，2-培训与发展，3-职业价值感，4-行业稳定性，5-社交与人脉，6-塑造个人品牌，7-团队综合素质，8-终身学习；
</li>
<li class="md-list-item">
对兴趣爱好的多选项进行编号为：1-编程，2-听音乐唱歌，3-篮球，4-玩游戏，5-看电影，6-享美食，7-健身，8-旅游，9-看书，10-写作。
</li>
</ul>
</li>
<li class="md-list-item">
存储
<ul class="ul-list" data-mark="-">
<li class="md-list-item">
前端：选择了哪些选项，就传递哪些选项的编号。例如：选择了"编程、篮球"，则传递的编号串为："1,3"
</li>
<li class="md-list-item">
后端：将编号串转换为二进制串，再转换为数字，落库
</li>
</ul>
</li>
<li class="md-list-item">
查询：前端还是只传选项编号；后端将其转换为数字；在数据库层面使用位运算中的与运算，匹配包含了参数选项的记录。
</li>
</ul>
<h2 id="mcetoc_1h68ttla31tr9" class="md-end-block md-heading md-focus">编码：将多选项转换为数字</h2>
现以兴趣爱好为例，为其多选项定义编号：1-编程，2-听音乐唱歌，3-篮球，4-玩游戏，5-看电影，6-享美食，7-健身，8-旅游，9-看书，10-写作
根据二进制位下标与十进制数的互转：
<img src="https://res.hackyle.com/blog/2023/07/e97841b3e50f445bbef0e652e238570a.png">
<h3 id="mcetoc_1h68ttla31tra" class="md-end-block md-heading md-focus">例1</h3>
<blockquote>
用户A勾选的兴趣爱好为："1-编程，2-听音乐唱歌，5-看电影，6-享美食" 前端传递的串为：1,2,5,6 转换为二进制串：0011 0011 转换规则：在有选项编号出现的位下标的位置上填充1，其他位置填充0 转换为十进制后落库：51
</blockquote>
<img src="https://res.hackyle.com/blog/2023/07/baf08e7a01df45a48d72cb2a13de8496.png">
<h3 id="mcetoc_1h68ttla31trb" class="md-end-block md-heading md-focus">例2</h3>
<blockquote>
用户B勾选的兴趣爱好为："3-篮球，4-玩游戏，6-享美食，7-健身，10-写作" 前端传递的串为：3,4,6,7,10 转换为二进制串：0010 0110 1100 转换为十进制后落库：620
</blockquote>
<img src="https://res.hackyle.com/blog/2023/07/03d6e1abf7254392a4376722a1d9a554.png">
<h2 id="mcetoc_1h68ttla31trc" class="md-end-block md-heading">查询原理</h2>
查询的核心思想：与（&）位运算
<ul class="ul-list" data-mark="-">
<li class="md-list-item">
X & Y = X，则说明X的所有值为"1"的二进制位、在Y中对应的二进制位也为"1"。
</li>
<li class="md-list-item md-focus-container">
也就是说，Y为"1"的二进制位包含了X的所有为"1"的二进制位。从而实现了，查询条件X，在Y中存在（包含）
</li>
</ul>
<img src="https://res.hackyle.com/blog/2023/07/4fcddc458e614fb1889177d12fd8fd42.png">
<img src="https://res.hackyle.com/blog/2023/07/9c7e8bcfa5b44620933ff1d796c7dfb0.png">
<h1 id="mcetoc_1h68ttla31trd" class="md-end-block md-heading">项目启动</h1>
Step 1：在application.yml中修改数据库连接参数
Step 2：执行resources/sql.sql下的SQL文件，初始化数据
Step 3：从启动类App.java启动
Step 4：启动成功后进入前端页面：http://localhost:9898/person.html
<h2 id="mcetoc_1h68ttla31tre" class="md-end-block md-heading">新增数据演示</h2>
必要数据，多选项进行勾选：
<img src="https://res.hackyle.com/blog/2023/07/0c1bbd09e9b64b588a2d24300c4c0bc7.png">
去数据库查看刚刚新增的记录：
<img src="https://res.hackyle.com/blog/2023/07/29eeeb3be74a4e78ada71752f5f470bd.png">
<h2 id="mcetoc_1h68ttla31trf" class="md-end-block md-heading">查询数据演示</h2>
选择查询条件，点击“Query”进行查询：
<img src="https://res.hackyle.com/blog/2023/07/23525cb83ce746f0ac51c2403a8ee35c.png">
查看运行日志，显示执行的SQL：
<img src="https://res.hackyle.com/blog/2023/07/aa446ed6b9b946ef99d12a1032fc2a9b.png">
<h1 id="mcetoc_1h68ttla31trg" class="md-end-block md-heading">查询示例</h1>
以多选项”兴趣爱好“为例，展示查询的工作原理
<h2 id="mcetoc_1h68ttla31trh" class="md-end-block md-heading">构造数据</h2>
AA选择了"1-编程，2-听音乐唱歌，5-看电影，6-享美食，7-健身，8-旅游" 前端传递的编号串：1,2,5,6,7,8 转换为二进制串：0000 1111 0011 转换为数字：243
BB选择了"3-篮球，4-玩游戏，8-旅游，9-看书，10-写作" 前端传递的编号串：3,4,8,9,10 转换为二进制串：0011 1000 1100 转换为数字：908
CC选择了"2-听音乐唱歌，3-篮球，5-看电影，6-享美食，7-健身，9-看书，10-写作" 前端传递的编号串：2,3,5,6,7,9,10 转换为二进制串：0011 0111 0110 转换为数字：886
DD选择了"1-编程，3-篮球，4-玩游戏，6-享美食，7-健身，8-旅游，10-写作" 前端传递的编号串：1,3,4,6,7,8,10 转换为二进制串：0010 0110 1101 转换为数字：749
EE选择了"2-听音乐唱歌，4-玩游戏，5-看电影，7-健身，8-旅游，10-写作" 前端传递的编号串：2,4,5,7,8,10 转换为二进制串：0010 1101 1010 转换为数字：730
FF选择了"1-编程，2-听音乐唱歌，3-篮球，4-玩游戏，5-看电影，7-健身" 前端传递的编号串：1,2,3,4,5,7 转换为二进制串：0000 0101 1111 转换为数字：95
GG选择了"1-编程，3-篮球，4-玩游戏，6-享美食，8-旅游，10-写作" 前端传递的编号串：1,3,4,6,8,10 转换为二进制串：0010 1010 1101 转换为数字：685
<h2 id="mcetoc_1h68ttla31tri" class="md-end-block md-heading md-focus">插入到数据库</h2>
<pre class="line-numbers language-sql highlighter-hljs"><code>DROP TABLE IF EXISTS person;
CREATE TABLE person (
id BIGINT AUTO_INCREMENT,
name VARCHAR(50) DEFAULT NULL COMMENT '姓名',
gender INT DEFAULT NULL COMMENT '性别，0-女，1-男',
address VARCHAR(128) DEFAULT NULL COMMENT '地址',
careers BIGINT DEFAULT NULL COMMENT '职业发展多选项',
-- 兴趣爱好多选项。可选项：1-编程，2-听音乐唱歌，3-篮球，4-玩游戏，5-看电影，6-享美食，7-健身，8-旅游，9-看书，10-写作。
-- 例如，全选："11 1111 1111"，保存为十进制=1023，全不选："00 0000 0000"，保存为十进制=0，只选择听音乐唱歌："0000 0010"，保存为十进制=2
-- LONG最大支持64位，最多支持64个多选项的任意选择
interests BIGINT DEFAULT NULL COMMENT '兴趣爱好多选项',
create_time DATETIME DEFAULT NULL,
update_time DATETIME DEFAULT NULL,
deleted INT DEFAULT 0 COMMENT '是否删除：0-否，1-是',
PRIMARY KEY(id)
);</code></pre>
<pre class="line-numbers language-sql highlighter-hljs"><code>-- 将上文中构造的数据，以SQL的形式插入到数据库中，只以多选项"兴趣爱好"为例
INSERT INTO person(name, gender, address, careers, interests, create_time, update_time, deleted)
VALUES ('AA', 1, 'SH CN', 1, 243, '2022-12-12','2023-12-12', 0),
 ('BB', 1, 'SH CN', 1, 908, '2022-12-12','2023-12-12', 0),
 ('CC', 1, 'SH CN', 1, 886, '2022-12-12','2023-12-12', 0),
 ('DD', 1, 'SH CN', 1, 749, '2022-12-12','2023-12-12', 0),
 ('EE', 1, 'SH CN', 1, 730, '2022-12-12','2023-12-12', 0),
 ('FF', 1, 'SH CN', 1, 95, '2022-12-12','2023-12-12', 0),
 ('GG', 1, 'SH CN', 1, 685, '2022-12-12','2023-12-12', 0);</code></pre>
<h2 id="mcetoc_1h68ttla31trj" class="md-end-block md-heading">查询选择了"1-编程"这个选项的记录</h2>
目标：在多选项中查询选择了"1-编程"这个选项的记录
用户的"兴趣爱好"多选项（二进制形式）
<blockquote>
AA： 0000 1111 0011 BB： 0011 1000 1100 CC： 0011 0111 0110 DD： 0010 0110 1101 EE：  0010 1101 1010 FF：  0000 0101 1111 GG： 0010 1010 1101
</blockquote>
将"1-编程"进行转换
<ul class="ul-list" data-mark="-">
<li class="md-list-item">
转换为二进制：0000 0000 0001
</li>
<li class="md-list-item">
转换为十进制：1
</li>
</ul>
查询原理：将查询条件"0000 0000 0001"与AA~GG的二进制位进行与运算后，仍然为查询条件的记录，则是选择了"1-编程"这个选项的记录
查询过程
<ul>
<li>将查询条件与AA的二进制位进行与运算：</li>
</ul>
<pre class="line-numbers language-properties highlighter-hljs"><code> 0000 0000 0001
&0000 1111 0011
#--------------------------
0000 0000 0001与运算结果仍为查询条件，说明这条记录包含了"1-编程"这个选项</code></pre>
<ul>
<li>将查询条件与BB的二进制位进行与运算：</li>
</ul>
<pre class="line-numbers language-properties highlighter-hljs"><code> 0000 0000 0001
&0011 1000 1100
#----------------------------
0000 0000 0000 与运算结果不为查询条件，说明这条记录不包含了"1-编程"这个选项</code></pre>
<ul class="ul-list" data-mark="-">
<li class="md-list-item md-focus-container">
其他记录运算同理
</li>
<li class="md-list-item md-focus-container">
最终发现只有AA、DD、FF、GG的运算结果符合条件，这四个记录就是满足"选择了1-编程这个选项的所有记录"
</li>
</ul>
SQL实现
<pre class="line-numbers language-sql highlighter-hljs"><code>select * FROM person WHERE interests & 1 = 1;</code></pre>
<img src="https://res.hackyle.com/blog/2023/07/986da085eab5476693f1dc689150aeed.png">
<h2 id="mcetoc_1h68ttla31trk" class="md-end-block md-heading">查询选择了"2-听音乐唱歌，5-看电影"这些选项的记录</h2>
目标：在多选项中查询选择了"2-听音乐唱歌，5-看电影"这些选项的记录
用户的"兴趣爱好"多选项（二进制形式）
<blockquote>
AA： 0000 1111 0011 BB： 0011 1000 1100 CC： 0011 0111 0110 DD： 0010 0110 1101 EE：  0010 1101 1010 FF：  0000 0101 1111 GG： 0010 1010 1101
</blockquote>
将"2-听音乐唱歌，5-看电影"进行转换
<ul class="ul-list" data-mark="-">
<li class="md-list-item">
转换为二进制：0000 0001 0010
</li>
<li class="md-list-item">
转换为十进制：18
</li>
</ul>
查询原理：将查询条件"0000 0001 0010"与AA~GG的二进制位进行与运算后，仍然为查询条件的记录，则是选择了"2-听音乐唱歌，5-看电影"这个选项的记录
查询过程
<ul class="ul-list" data-mark="-">
<li class="md-list-item md-focus-container">
将查询条件与AA的二进制位进行与运算：
</li>
</ul>
<pre class="line-numbers language-properties highlighter-hljs"><code>0000 0001 0010
& 0000 1111 0011
#--------------------------
0000 0001 0010与运算结果仍为查询条件，说明这条记录包含了"2-听音乐唱歌，5-看电影"这些选项</code></pre>
<ul>
<li>将查询条件与BB的二进制位进行与运算：</li>
</ul>
<pre class="line-numbers language-properties highlighter-hljs"><code>0000 0001 0010
& 0011 1000 1100
#----------------------------
0000 0000 0000 与运算结果不为查询条件，说明这条记录不包含了"2-听音乐唱歌，5-看电影"这些选项</code></pre>
<ul class="ul-list" data-mark="-">
<li class="md-list-item md-focus-container">
其他记录运算同理
</li>
<li class="md-list-item md-focus-container">
最终发现只有AA、CC、EE、FF的运算结果符合条件，这四个记录就是满足"选择了2-听音乐唱歌，5-看电影这些选项的所有记录"
</li>
</ul>
SQL实现
<pre class="line-numbers language-sql highlighter-hljs"><code>select * FROM person WHERE interests & 18 = 18;</code></pre>
<img src="https://res.hackyle.com/blog/2023/07/2ef948cfe3034769b7b4df09e5daabdb.png">
  
来源：https://www.cnblogs.com/hackyle/p/19530712

頁: [1]

琼殿技术论坛's Archiver

一种多选项的高效存取（存储、查询）解决方案