服务器之家：专注于VPS、云服务器配置技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Mysql - mysql的单列多值存储实例详解

mysql的单列多值存储实例详解

2022-10-17 15:10go4it Mysql

数据库市场需要细分,行式数据库不再满足所有的需求,而有很多需求需要,下面这篇文章主要给大家介绍了关于mysql单列多值存储的相关资料,文中通过示例代码介绍介绍的非常详细,需要的朋友可以参考下

序

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

实例

用bit类型

  • 建表及数据准备
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- 这里定义了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              |
+---------------+------------------+
  • 位运算查询
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 查询第二位有值的数据
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              |
+---------------+------------------+
  • 更新
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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类型

  • 建表及数据准备
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
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           |
+-------------+
  • 查询
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查询包含第二个枚举的数据
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              |
+-------------+------------------+
  • 更新
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
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类型

  • 建表及数据准备
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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       |
+-------------+
  • 查询
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- 查询包含第二个枚举的数据
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           |
+-------------+
  • 更新
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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类型

  • 建表及数据准备
?
1
2
3
4
5
6
7
8
9
10
11
12
13
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');
  • 查询
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 查询包含第二个枚举的数据，可以用位运算也可以用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           |
+-------------+
  • 更新
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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

到此这篇关于mysql单列多值存储的文章就介绍到这了,更多相关mysql单列多值存储内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://juejin.cn/post/7082207515789754404

  • mysql
  • 多值
  • 单列

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    150 　 1044 　 1062 　 1053 　 1449 　 1045 　 1290 　 1819 　 2013 　 1406 　 1005 　 1864 　 1862 　 1146 　 1054 　 2006 　 1615 　 1215 　 1175 　 10048 　 2003 　 1366 　 28000 　 1577 　 1135 　 1786 　 1093 　 1067 　 1698 　 2002 　
    © 2019-2022 服务器之家(www.tuohang.net) 版权所有关于我们联系我们版权申明网站地图