序
本文主要研究一下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 = https://www.it610.com/article/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 = https://www.it610.com/article/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 =https://www.it610.com/article/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 = https://www.it610.com/article/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 = 'https://www.it610.com/article/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 = 'https://www.it610.com/article/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='https://www.it610.com/article/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 = 'https://www.it610.com/article/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| +----+-------------+
doc
- bit-type
- bit-functions
- function_find-in-set
- set
推荐阅读
- 聊聊mysql的多列组合查询
- 2022年MySQL最新面试题
- servlet|servlet tomcat mysql_Jsp+Servlet+Tomcat+Mysql+JDBC+MVC模式 简单实用范例
- C#|从零开始手把手教你,.net 6用EF Core基本创建表,迁移到mysql数据库
- 数据库|从 SQL Server 到 MySQL (一)(异构数据库迁移)
- mysql|7、mysql的redo log、bin log日志
- linux|CentOS7忘记MySQL密码后重置
- Linux|centos7 安装 mysql5.7走过的坑
- 实用经验|【mysql】解决MySQL GPG密钥过期问题