MySQL如何存储多选框的结果?

前言

在实际工作中，经常会遇到要存储多选框结果的情况，多选框的属性的值有多个，通常我们使用的存储方式大多是横向和纵向分隔两种方式。比如我们有一个场景：用户在注册的时候需要选择兴趣爱好，共有10个选项，可不选、单选和多选。

1. 横向分隔

   横向的意思是将所有的数据存储在一条数据的一个字段中，在字段中使用特殊符号将每个结果分隔开，比如：1,2,3,4,5,6。

   id	user_id	value
   1	1	,1,2,3,4,5,6,
   2	2	,3,5,6,8,9,

   这种方式存储简单，查询使用like进行查询；在更新的时候需要整个字段全量更新。

   • 查询兴趣爱好id=3的用户id

     select user_id from sth_user_hobbies where value like '%,3,%';

   • 查询兴趣爱好id=3和4的用户id

     select user_id from sth_user_hobbies where value like '%,3,4,%';

   • 用户user_id=2取消兴趣id=8

     update sth_user_hobbies set value = ',3,5,6,9,' where user_id=2;

2. 纵向分隔

   纵向的意思是每一个结果存储为一条数据，选择了多少个框就有多少条数据，比如：

   id	user_id	value
   1	1	1
   2	1	2
   3	2	8

   • 查询兴趣爱好id=3的用户id

     select user_id from sth_user_hobbies where value=3;

   • 查询兴趣爱好id=3和4的用户id

     select user_id from sth_user_hobbies where value in (3, 4);

   • 用户user_id=2取消兴趣id=8

     delete from sth_user_hobbies where user_id=2 and value=8;

   这种存储方式简单高效，缺点就是数据量大，若多选框有30个可选值，10万个用户都选择了全部，那在表中就会有300万条记录，是横向分隔方案的30倍；优势就是数据分散，可以使用索引，并且不需要对数据进行二次处理。

上面这两种是我们常用的方式，那么是否有其他方式来处理多选框的数据存储问题？下面就来看下另一种方案。

位运算

位运算很简单，程序中的所有数在计算机内存中都是以二进制的形式存储的。位运算就是直接对整数在内存中的二进制位进行操作。比如，and运算本来是一个逻辑运算符，但整数与整数之间也可以进行and运算。举个例子，6的二进制是110，11的二进制是1011，那么6 and 11的结果就是2，它是二进制对应位进行逻辑运算的结果（0表示False，1表示True，空位都当0处理）。

含义	Java	MySQL
按位与	a & b	a & b
按位或	a | b	a | b
按位异或	a ^ b	a ^ b
按位取反	~a	~a
左移	a << b	a << b
右移	a >> b	a >> b

位运算是计算机的基础知识，本篇不做详细讲解，可以去谷歌位运算学习一下。

MySQL位运算解决方案

了解了位运算，那么我们来看下在MySQL中，怎么使用位运算来解决上述场景。

一共有10个兴趣爱好选项，那么我们将这10个兴趣爱好的ID设置为1、2、4、8、16、32、64、128、256、512，我们的表结构为

create table sth_user_hobbies
(
	id bigint auto_increment primary key,
	user_id bigint not null comment '用户ID',
	hobbies bigint null comment '兴趣爱好'
);

create table straw_hats.sth_hobbies
(
    id   bigint      not null primary key,
    name varchar(30) not null comment '兴趣爱好名称'
)comment '兴趣爱好表';

• 当我们插入一条数据时，可以在业务代码中通过位运算计算出最终数据

  List<Integer> hobbyList = new ArrayList<Integer>() {
    {
      add(1);
      add(2);
      add(4);
      add(16);
      add(128);
    }
  };
  // hobby = 151
  int hobby = hobbyList.stream().mapToInt(v -> v).reduce(0, (a, b) -> a | b);

  insert into sth_user_hobbies(user_id, hobbies) values(1, 151);

• 当我们要查询所有对id=4感兴趣的用户id时，可以通过与运算来实现

  select user_id from sth_user_hobbies where hobbies & 4 = 4;

  是不是很简单，但是在查询语句中，我们对列hobbies进行了计算，所以该列无法建立索引，因为当索引列参与计算时，会自动将索引失效。

• 当我们要查询所有对id=4和16感兴趣的用户id时，则可以先将4和16进行或运算，然后再带入到SQL中进行与运算，也可以在SQL中直接进行计算，不过建议是在业务逻辑代码中把数据计算好再带入SQL

  select user_id from sth_user_hobbies where hobbies & (4 | 16) = (4 | 16);

• 当我们对id=16不再感兴趣，取消该兴趣爱好，那么我们就可以用到异或操作

  update sth_user_hobbies set hobbies = hobbies^16 where user_id = 1;

• 当我们新增加了一个或多个兴趣爱好，那么则可以在业务代码中将其计算好二进制的值，然后带入到SQL中进行或操作，再或者直接带入到SQL中进行操作

  update sth_user_hobbies set hobbies = hobbies | (4 | 16) where user_id = 1;

• 当我们要查询所有对id=4或id=8感兴趣的用户id时，则和正常的使用没什么区别，也要使用到or关键字

  select user_id from sth_user_hobbies where hobbies & 4 = 4 or hobbies & 8 = 8;

上面的一些基本操作让我们的查询更加方便，但是如果我们要查询某用户选择的所有兴趣爱好列表，那么该怎么操作？

这个情况就变得有些复杂了，因为我们在数据库中存储的是一个计算后的数字，我们需要通过数字反推出来列表，这就比较麻烦了，那有没有什么好的办法呢？

---

答案是：没有。只能一个个的去做与运算，返回与运算后相等的数据，或者连表查询，比如：

select uh.user_id, h.* 
	from sth_user_hobbies uh 
left join sth_hobbies h 
	on uh.hobbies & h.id = h.id 
where uh.user_id = 1;

---

会造成索引无效喔！