数据库教程: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         |  +----+-------------+