关于mysql:聊聊mysql的单列多值存储

32次阅读

共计 7071 个字符,预计需要花费 18 分钟才能阅读完成。

本文次要钻研一下 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

  • bit-type
  • bit-functions
  • function_find-in-set
  • set

正文完
 0