mysql的单列多值存储实例详解 mysql的单列多值存储实例详解

序
实例

用bit类型
用int/bigint类型
用varchar类型
用set类型

小结

序本文主要研究一下mysql如何用一个列来存储多个值

实例
用bit类型

建表及数据准备

-- 这里定义了bit(3)，表示有3位，第一位1，第二位2，第三位4create 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/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 = https://www.it610.com/article/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 =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=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 = '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=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='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=7select * from t_set_demo where id=7select * from t_set_demo where id=7+----+-------------+| id | multi_value |+----+-------------+| 7| 1,4|+----+-------------+

小结 mysql用单列存储多值通常用于一对多的反范式处理，具体可以用bit、int/bigint、varchar、set类型来实现，缺点是不支持索引。

doc