Beispielanalyse für einen einspaltigen MySQL-Speicher mit mehreren Werten

Beispiel

Bittyp

• zum Erstellen verwenden eine Tabelle und Datenvorbereitung 🎜#

  Update

-- 这里定义了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              |
+---------------+------------------+

Nach dem Login kopieren
• Typ int/bigint verwenden

• -- 查询第二位有值的数据
  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              |
  +---------------+------------------+

  Nach dem Login kopieren
  Varchar-Typ verwenden

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              |
+----+---------------+------------------+

Nach dem Login kopieren

• query

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           |
+-------------+

Nach dem Login kopieren

• # 🎜🎜#Update

  # 🎜🎜#

-- 查询包含第二个枚举的数据
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              |
+-------------+------------------+

Nach dem Login kopieren

• Tabellen erstellen und Daten vorbereiten

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              |
+----+-------------+------------------+

• 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       |
  +-------------+

  Nach dem Login kopieren

-- 查询包含第二个枚举的数据
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           |
+-------------+

Das obige ist der detaillierte Inhalt vonBeispielanalyse für einen einspaltigen MySQL-Speicher mit mehreren Werten. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!