SQL开发知识：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 |
+—-+————-+