千家信息网

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

发表于:2025-01-18 作者:千家信息网编辑
千家信息网最后更新 2025年01月18日,本篇内容介绍了"mysql的单列多值存储实例分析"的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!实例用
千家信息网最后更新 2025年01月18日mysql的单列多值存储实例分析

本篇内容介绍了"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 = 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 = 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 =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=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 = '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=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='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=7select * from t_set_demo where id=7select * from t_set_demo where id=7+----+-------------+| id | multi_value |+----+-------------+| 7  | 1,4         |+----+-------------+

    "mysql的单列多值存储实例分析"的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站,小编将为大家输出更多高质量的实用文章!

    0