likes
comments
collection
share

聊聊mysql的单列多值存储

作者站长头像
站长
· 阅读数 60

本文主要研究一下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 = 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              |
    +----+---------------+------------------+

用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=7
    select 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=7
    select * 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_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         |
    +----+-------------+

小结

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

doc

转载自:https://segmentfault.com/a/1190000041651455
评论
请登录