实例
用bit类型
建表及数据准备
%ign%ignore_a_1%re_pre_1%位运算查询
-- 查询第二位有值的数据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 = 4select 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=7select 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=7select 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=7select * 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_setselect multi_value from t_set_demo where multi_value&2select 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_setselect multi_value from t_set_demo where multi_value&4select 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=7select * from t_set_demo where id=7select * from t_set_demo where id=7+----+-------------+| id | multi_value |+----+-------------+| 7 | 1,4 |+----+-------------+
以上就是mysql的单列多值存储实例分析的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/153209.html
微信扫一扫 
支付宝扫一扫 
