本篇内容介绍了“mysql的单列多值存储实例分析”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
建表及数据准备
-- 这里定义了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 | +----+---------------+------------------+
建表及数据准备
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 | +----+-------------+------------------+
建表及数据准备
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 | +----+-------------+
建表及数据准备
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的单列多值存储实例分析”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
