本文主要內容

• 對於多選項的值，如何保存？本文提供了一種非常規的方案。
• 對於記錄在數據庫中的多選項的值，如何快速查詢那些記錄是包含了某個（某些）選項？本文使用了“與位運算”解決查詢問題。
• 源碼地址：https://github.com/HackyleShawe/JavaBackEndDemos/tree/master/BusinessCommonSolution/multi-options-storage-query-demo

文章前置知識：SpringBoot、JDBCTemplate、位運算（與運算）、jQuery

內容導覽 

背景

在項目開發中，如何保存多選項的值呢？例如下圖中的職業發展和興趣愛好

• 最容易想到的就是，選擇了哪些選項，就把該選項值存儲起來。

• 在數據庫層面設置一個VARCHAR，例如選擇了"收入無上線、培訓與發展、職業價值感"，前端就傳遞"收入無上線、培訓與發展、職業價值感"，數據庫就保存為"收入無上線、培訓與發展、職業價值感"。

可是這樣存在一個問題，我要查詢那些人選擇了其中的某個、某些選項，就很難實現。例如，查詢那些人的興趣愛好是"編程、籃球"，查詢興趣愛好是"看書、寫作"的人數有多少。

為了解決這種多選項的高效查詢問題，本人設計了一種方法，可以實現快速、高效地查詢多選項中有某個、某些選項的所有記錄。

設計思想

核心思路：

1. 編碼：將每個選項與特定的比特位上對應起來，選中為1，未選中為0

2. 存儲：將比特位轉換為Long型（64bit，最大支持64個多選項），數據庫表字段為BIGINT

3. 查詢：與（&）位運算

主要流程

• 編碼：將多選項進行編號：

  • 例如：對職業發展的多選項進行編號為：1-收入無上限，2-培訓與發展，3-職業價值感，4-行業穩定性，5-社交與人脈，6-塑造個人品牌，7-團隊綜合素質，8-終身學習；

  • 對興趣愛好的多選項進行編號為：1-編程，2-聽音樂唱歌，3-籃球，4-玩遊戲，5-看電影，6-享美食，7-健身，8-旅遊，9-看書，10-寫作。

• 存儲

  • 前端：選擇了哪些選項，就傳遞哪些選項的編號。例如：選擇了"編程、籃球"，則傳遞的編號串為："1,3"

  • 後端：將編號串轉換為二進制串，再轉換為數字，落庫

• 查詢：前端還是隻傳選項編號；後端將其轉換為數字；在數據庫層面使用位運算中的與運算，匹配包含了參數選項的記錄。

編碼：將多選項轉換為數字

現以興趣愛好為例，為其多選項定義編號：1-編程，2-聽音樂唱歌，3-籃球，4-玩遊戲，5-看電影，6-享美食，7-健身，8-旅遊，9-看書，10-寫作

根據二進制位下標與十進制數的互轉：

例1

用户A勾選的興趣愛好為："1-編程，2-聽音樂唱歌，5-看電影，6-享美食"
前端傳遞的串為：1,2,5,6
轉換為二進制串：0011 0011
轉換規則：在有選項編號出現的位下標的位置上填充1，其他位置填充0
轉換為十進制後落庫：51

例2

用户B勾選的興趣愛好為："3-籃球，4-玩遊戲，6-享美食，7-健身，10-寫作"
前端傳遞的串為：3,4,6,7,10
轉換為二進制串：0010 0110 1100
轉換為十進制後落庫：620

查詢原理

查詢的核心思想：與（&）位運算

• X & Y = X，則説明X的所有值為"1"的二進制位、在Y中對應的二進制位也為"1"。

• 也就是説，Y為"1"的二進制位包含了X的所有為"1"的二進制位。從而實現了，查詢條件X，在Y中存在（包含）

項目啓動

Step 1：在application.yml中修改數據庫連接參數

Step 2：執行resources/sql.sql下的SQL文件，初始化數據

Step 3：從啓動類App.java啓動

Step 4：啓動成功後進入前端頁面：http://localhost:9898/person.html

新增數據演示

必要數據，多選項進行勾選：

去數據庫查看剛剛新增的記錄：

查詢數據演示

選擇查詢條件，點擊“Query”進行查詢：

查看運行日誌，顯示執行的SQL：

查詢示例

以多選項”興趣愛好“為例，展示查詢的工作原理

構造數據

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

插入到數據庫

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

-- 將上文中構造的數據，以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);

查詢選擇了"1-編程"這個選項的記錄

目標：在多選項中查詢選擇了"1-編程"這個選項的記錄

用户的"興趣愛好"多選項（二進制形式）

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

將"1-編程"進行轉換

• 轉換為二進制：0000 0000 0001

• 轉換為十進制：1

查詢原理：將查詢條件"0000 0000 0001"與AA~GG的二進制位進行與運算後，仍然為查詢條件的記錄，則是選擇了"1-編程"這個選項的記錄

查詢過程

• 將查詢條件與AA的二進制位進行與運算：

   0000 0000 0001
&  0000 1111 0011
#--------------------------
   0000 0000 0001  與運算結果仍為查詢條件，説明這條記錄包含了"1-編程"這個選項

• 將查詢條件與BB的二進制位進行與運算：

   0000 0000 0001
&  0011 1000 1100
#----------------------------
   0000 0000 0000   與運算結果不為查詢條件，説明這條記錄不包含了"1-編程"這個選項

• 其他記錄運算同理

• 最終發現只有AA、DD、FF、GG的運算結果符合條件，這四個記錄就是滿足"選擇了1-編程這個選項的所有記錄"

SQL實現

select * FROM person WHERE interests & 1 = 1;

查詢選擇了"2-聽音樂唱歌，5-看電影"這些選項的記錄

目標：在多選項中查詢選擇了"2-聽音樂唱歌，5-看電影"這些選項的記錄

用户的"興趣愛好"多選項（二進制形式）

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

將"2-聽音樂唱歌，5-看電影"進行轉換

• 轉換為二進制：0000 0001 0010

• 轉換為十進制：18

查詢原理：將查詢條件"0000 0001 0010"與AA~GG的二進制位進行與運算後，仍然為查詢條件的記錄，則是選擇了"2-聽音樂唱歌，5-看電影"這個選項的記錄

查詢過程

• 將查詢條件與AA的二進制位進行與運算：

  0000 0001 0010
& 0000 1111 0011
#--------------------------
   0000 0001 0010  與運算結果仍為查詢條件，説明這條記錄包含了"2-聽音樂唱歌，5-看電影"這些選項

• 將查詢條件與BB的二進制位進行與運算：

  0000 0001 0010
& 0011 1000 1100
#----------------------------
  0000 0000 0000   與運算結果不為查詢條件，説明這條記錄不包含了"2-聽音樂唱歌，5-看電影"這些選項

• 其他記錄運算同理

• 最終發現只有AA、CC、EE、FF的運算結果符合條件，這四個記錄就是滿足"選擇了2-聽音樂唱歌，5-看電影這些選項的所有記錄"

SQL實現

select * FROM person WHERE interests & 18 = 18;