首页 > 数据库 > mysql教程 > mysql的单列多值存储实例分析

mysql的单列多值存储实例分析

WBOY
发布: 2023-05-29 17:46:29
转载
1508 人浏览过

    实例

    用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的单列多值存储实例分析的详细内容。更多信息请关注PHP中文网其他相关文章!

    相关标签:
    来源:yisu.com
    本站声明
    本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn
    热门教程
    更多>
    最新下载
    更多>
    网站特效
    网站源码
    网站素材
    前端模板