mysql的单列多值存储实例详解
短信预约 -IT技能 免费直播动态提醒
序
本文主要研究一下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
- bit-type
- bit-functions
- function_find-in-set
- set
到此这篇关于mysql单列多值存储的文章就介绍到这了,更多相关mysql单列多值存储内容请搜索编程网以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程网！
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/2.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
