聊聊mysql的单列多值存储

本文主要研究一下mysql如何用一个列来存储多个值

实例

用bit类型

  • 建表及数据准备

    -- 这里定义了bit(3),表示有3位,第一位1,第二位2,第三位4
    create table t_bit_demo(
     id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
     multi_value bit(3) not null default 0
    );
    
    -- 这里插入了1,2,4的组合值
    insert into t_bit_demo(multi_value) values(b'000');
    insert into t_bit_demo(multi_value) values(b'001');
    insert into t_bit_demo(multi_value) values(b'010');
    insert into t_bit_demo(multi_value) values(b'011');
    insert into t_bit_demo(multi_value) values(b'100');
    insert into t_bit_demo(multi_value) values(b'101');
    insert into t_bit_demo(multi_value) values(b'110');
    insert into t_bit_demo(multi_value) values(b'111');
    
    -- 这里直接插入int值也可以,比如5相当于101
    -- insert into t_bit_demo(multi_value) values(5);
    
    SELECT multi_value+0, BIN(multi_value) FROM t_bit_demo;
    +---------------+------------------+
    | multi_value+0 | BIN(multi_value) |
    +---------------+------------------+
    | 0             | 0                |
    | 1             | 1                |
    | 2             | 10               |
    | 3             | 11               |
    | 4             | 100              |
    | 5             | 101              |
    | 6             | 110              |
    | 7             | 111              |
    +---------------+------------------+
  • 位运算查询

    -- 查询第二位有值的数据
    select multi_value+0,BIN(multi_value) from t_bit_demo where multi_value & 2
    +---------------+------------------+
    | multi_value+0 | BIN(multi_value) |
    +---------------+------------------+
    | 2             | 10               |
    | 3             | 11               |
    | 6             | 110              |
    | 7             | 111              |
    +---------------+------------------+
    
    -- 查询第三位有值的数据
    select multi_value+0,BIN(multi_value) from t_bit_demo where multi_value & 4
    +---------------+------------------+
    | multi_value+0 | BIN(multi_value) |
    +---------------+------------------+
    | 4             | 100              |
    | 5             | 101              |
    | 6             | 110              |
    | 7             | 111              |
    +---------------+------------------+
    
    -- 查询只有第三位有值的数据
    select multi_value+0,BIN(multi_value) from t_bit_demo where multi_value = 4
    select multi_value+0,BIN(multi_value) from t_bit_demo where multi_value = 4
    +---------------+------------------+
    | multi_value+0 | BIN(multi_value) |
    +---------------+------------------+
    | 4             | 100              |
    +---------------+------------------+
  • 更新

    select id,multi_value+0,BIN(multi_value) from t_bit_demo
    +----+---------------+------------------+
    | id | multi_value+0 | BIN(multi_value) |
    +----+---------------+------------------+
    | 1  | 0             | 0                |
    | 2  | 1             | 1                |
    | 3  | 2             | 10               |
    | 4  | 3             | 11               |
    | 5  | 4             | 100              |
    | 6  | 5             | 101              |
    | 7  | 6             | 110              |
    | 8  | 7             | 111              |
    +----+---------------+------------------+
    
    -- 将id为7的值移除第二个枚举
    update t_bit_demo set multi_value = b'100' where id=7
    select id,multi_value+0,BIN(multi_value) from t_bit_demo where id=7
    +----+---------------+------------------+
    | id | multi_value+0 | BIN(multi_value) |
    +----+---------------+------------------+
    | 7  | 4             | 100              |
    +----+---------------+------------------+

用int/bigint类型

  • 建表及数据准备

    create table t_bigint_demo(
     id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
     multi_value bigint not null default 0
    );
    
    -- 假设这里定义了1,2,4三个枚举值
    insert into t_bigint_demo(multi_value) values(0);
    insert into t_bigint_demo(multi_value) values(1);
    insert into t_bigint_demo(multi_value) values(2);
    insert into t_bigint_demo(multi_value) values(3);
    insert into t_bigint_demo(multi_value) values(4);
    insert into t_bigint_demo(multi_value) values(5);
    insert into t_bigint_demo(multi_value) values(6);
    insert into t_bigint_demo(multi_value) values(7);
    
    select multi_value from t_bigint_demo
    +-------------+
    | multi_value |
    +-------------+
    | 0           |
    | 1           |
    | 2           |
    | 3           |
    | 4           |
    | 5           |
    | 6           |
    | 7           |
    +-------------+
  • 查询

    -- 查询包含第二个枚举的数据
    select multi_value,BIN(multi_value) from t_bigint_demo where multi_value & 2
    +-------------+------------------+
    | multi_value | BIN(multi_value) |
    +-------------+------------------+
    | 2           | 10               |
    | 3           | 11               |
    | 6           | 110              |
    | 7           | 111              |
    +-------------+------------------+
    
    -- 查询包含第三个枚举的数据
    select multi_value,BIN(multi_value) from t_bigint_demo where multi_value & 4
    +-------------+------------------+
    | multi_value | BIN(multi_value) |
    +-------------+------------------+
    | 4           | 100              |
    | 5           | 101              |
    | 6           | 110              |
    | 7           | 111              |
    +-------------+------------------+
    
    -- 查询值为第三个枚举的数据
    select multi_value,BIN(multi_value) from t_bigint_demo where multi_value =4
    +-------------+------------------+
    | multi_value | BIN(multi_value) |
    +-------------+------------------+
    | 4           | 100              |
    +-------------+------------------+
  • 更新

    select id,multi_value,BIN(multi_value) from t_bigint_demo
    +----+-------------+------------------+
    | id | multi_value | BIN(multi_value) |
    +----+-------------+------------------+
    | 1  | 0           | 0                |
    | 2  | 1           | 1                |
    | 3  | 2           | 10               |
    | 4  | 3           | 11               |
    | 5  | 4           | 100              |
    | 6  | 5           | 101              |
    | 7  | 6           | 110              |
    | 8  | 7           | 111              |
    +----+-------------+------------------+
    
    -- 将id为7的值移除第二个枚举
    update t_bigint_demo set multi_value = b'100' where id=7
    select id,multi_value,BIN(multi_value) from t_bigint_demo where id=7
    +----+-------------+------------------+
    | id | multi_value | BIN(multi_value) |
    +----+-------------+------------------+
    | 7  | 4           | 100              |
    +----+-------------+------------------+

用varchar类型

  • 建表及数据准备

    create table t_varchar_demo(
     id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
     multi_value varchar(255) not null default ''
    );
    
    -- 假设这里定义了1,2,4三个枚举值
    insert into t_varchar_demo(multi_value) values('1');
    insert into t_varchar_demo(multi_value) values('2');
    insert into t_varchar_demo(multi_value) values('1,2');
    insert into t_varchar_demo(multi_value) values('4');
    insert into t_varchar_demo(multi_value) values('1,4');
    insert into t_varchar_demo(multi_value) values('2,4');
    insert into t_varchar_demo(multi_value) values('1,2,4');
    
    select multi_value from t_varchar_demo
    +-------------+
    | multi_value |
    +-------------+
    | 1           |
    | 2           |
    | 1,2         |
    | 4           |
    | 1,4         |
    | 2,4         |
    | 1,2,4       |
    +-------------+
  • 查询

    -- 查询包含第二个枚举的数据
    select multi_value from t_varchar_demo where find_in_set('2',multi_value)
    +-------------+
    | multi_value |
    +-------------+
    | 2           |
    | 1,2         |
    | 2,4         |
    | 1,2,4       |
    +-------------+
    
    -- 查询包含第三个枚举的数据
    select multi_value from t_varchar_demo where find_in_set('4',multi_value)
    +-------------+
    | multi_value |
    +-------------+
    | 4           |
    | 1,4         |
    | 2,4         |
    | 1,2,4       |
    +-------------+
    
    -- 查询只有第三个枚举的数据
    select multi_value from t_varchar_demo where multi_value = '4'
    +-------------+
    | multi_value |
    +-------------+
    | 4           |
    +-------------+
  • 更新

    select * from t_varchar_demo
    +----+-------------+
    | id | multi_value |
    +----+-------------+
    | 1  | 1           |
    | 2  | 2           |
    | 3  | 1,2         |
    | 4  | 4           |
    | 5  | 1,4         |
    | 6  | 2,4         |
    | 7  | 1,2,4       |
    +----+-------------+
    
    -- 将id为7的值移除第二个枚举
    update t_varchar_demo set multi_value = '1,4' where id=7
    select * from t_varchar_demo where id=7
    +----+-------------+
    | id | multi_value |
    +----+-------------+
    | 7  | 1,4         |
    +----+-------------+

用set类型

  • 建表及数据准备

    create table t_set_demo(
     id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
     multi_value set('1','2','4') not null default ''
    );
    
    insert into t_set_demo(multi_value) values('');
    insert into t_set_demo(multi_value) values('1');
    insert into t_set_demo(multi_value) values('2');
    insert into t_set_demo(multi_value) values('1,2');
    insert into t_set_demo(multi_value) values('4');
    insert into t_set_demo(multi_value) values('1,4');
    insert into t_set_demo(multi_value) values('2,4');
    insert into t_set_demo(multi_value) values('1,2,4');
  • 查询

    -- 查询包含第二个枚举的数据,可以用位运算也可以用find_in_set
    select multi_value from t_set_demo where multi_value&2
    select multi_value from t_set_demo where find_in_set('2',multi_value)
    +-------------+
    | multi_value |
    +-------------+
    | 2           |
    | 1,2         |
    | 2,4         |
    | 1,2,4       |
    +-------------+
    
    -- 查询包含第三个枚举的数据,可以用位运算也可以用find_in_set
    select multi_value from t_set_demo where multi_value&4
    select multi_value from t_set_demo where find_in_set('4',multi_value)
    +-------------+
    | multi_value |
    +-------------+
    | 4           |
    | 1,4         |
    | 2,4         |
    | 1,2,4       |
    +-------------+
    
    -- 查询值为第三个枚举的数据
    select multi_value from t_set_demo where multi_value='4'
    +-------------+
    | multi_value |
    +-------------+
    | 4           |
    +-------------+
    
  • 更新

    select * from t_set_demo
    +----+-------------+
    | id | multi_value |
    +----+-------------+
    | 1  |             |
    | 2  | 1           |
    | 3  | 2           |
    | 4  | 1,2         |
    | 5  | 4           |
    | 6  | 1,4         |
    | 7  | 2,4         |
    | 8  | 1,2,4       |
    +----+-------------+
    
    -- 将id为7的值移除第二个枚举
    update t_set_demo set multi_value = '1,4' where id=7
    select * from t_set_demo where id=7
    select * from t_set_demo where id=7
    +----+-------------+
    | id | multi_value |
    +----+-------------+
    | 7  | 1,4         |
    +----+-------------+

小结

mysql用单列存储多值通常用于一对多的反范式处理,具体可以用bit、int/bigint、varchar、set类型来实现,缺点是不支持索引。

doc


code-craft
spring boot , docker and so on 欢迎关注微信公众号: geek_luandun

当一个代码的工匠回首往事时,不因虚度年华而悔恨,也不因碌碌无为而羞愧,这样,当他老的时候,可以很...

11.8k 声望
2k 粉丝
0 条评论
推荐阅读
2022年终总结
最近两年开始陷入颓废中,博客也写的越来越少了。究其原因,主要还是陷入了职业倦怠期,最近一次跳槽感觉颇为失败,但是碍于给的薪资高,为了五斗米折腰,又加上最近行情不好,想要往外跳也跳不了,就这样子一直...

codecraft阅读 711

一次偶然机会发现的MySQL“负优化”
今天要讲的这件事和上述的两个sql有关,是数年前遇到的一个关于MySQL查询性能的问题。主要是最近刷到了一些关于MySQL查询性能的文章,大部分文章中讲到的都只是一些常见的索引失效场合,于是我回想起了当初被那个...

骑牛上青山7阅读 1.8k评论 2

分布式高可用Mysql数据库Percona XtraDB Cluster 8.0 与 Proxysql 史上最详尽用法指南
PXC是Percona XtraDB Cluster的缩写,是 Percona 公司出品的免费MySQL集群产品。PXC的作用是通过mysql自带的Galera集群技术,将不同的mysql实例连接起来,实现多主集群。在PXC集群中每个mysql节点都是可读可写的...

apollo0084阅读 7.2k评论 2

MongoDB 插入时间与更新时间(create_time/update_time)
MongoDB 在数据库层面不能像 MySQL 一样设置自动创建 create_time/update_time,自动更新 update_time

qbit阅读 14k评论 2

Mysql索引覆盖
通常情况下,我们创建索引的时候只关注where条件,不过这只是索引优化的一个方向。优秀的索引设计应该纵观整个查询,而不仅仅是where条件部分,还应该关注查询所包含的列。索引确实是一种高效的查找数据方式,但...

京东云开发者2阅读 727

封面图
SegmentFault 思否技术周刊 Vol.70 — 深入 MySQL 实战
MySQL 软件采用了 GPL( GNU 通用公共许可证),由于其体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,许多中小型网站为了降低网站总体拥有成本而选择了 MySQL 作为网站数据库。

Beverly2阅读 1.5k

封面图
MySQL 数据库索引技术原理初探
一本书 500 页的书,如果没有目录,直接去找某个知识点,可能需要找一会儿,但是借助前面的目录,就可以快速找到对应知识点在书的哪一页。这里的目录就是索引。

mylxsw1阅读 1.3k

当一个代码的工匠回首往事时,不因虚度年华而悔恨,也不因碌碌无为而羞愧,这样,当他老的时候,可以很...

11.8k 声望
2k 粉丝
宣传栏