sequence

This article examines how mysql can store multiple values in a single column

The instance

With a bit type

  • Create tables and prepare data
The first bit is 1, the second bit is 2, Create table t_bit_demo(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value bit(3) NOT NULL default 0); Insert into t_bit_demo(multi_value) values(b'000'); 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'); Insert into t_bit_demo(multi_value) values(5); 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 | | 2 | | 10 11 | | 3 | | | 100 | | | 101 | | | 110 | | | 111 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code
  • Bit operation query
Select multi_value+0,BIN(multi_value) from t_bit_demo where multi_value & 2 +---------------+------------------+ | multi_value+0 | BIN(multi_value) | +---------------+------------------+ | 2 | 10 11 | | 3 | | | | 110 | | | 111 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - select query the third has the value of the data 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 | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - query only a third have the value data 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  | +---------------+------------------+Copy the code
  • update
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 | | 101 | | 5 6 7 | | | 110 | | | | 111 | 7 + 8 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - 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) | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | 100 | 4 + 7 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Using int/bigint type

  • Create tables and prepare data
create table t_bigint_demo( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value bigint not null default 0 ); Insert into T_bigINt_demo (multi_value) values(0); 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 7 6 | | | | | + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code
  • The query
Select multi_value,BIN(multi_value) from t_bigint_demo where multi_value & 2 +-------------+------------------+ | multi_value | BIN(multi_value) | +-------------+------------------+ | 2 | 10 | | 3 7 11 | | | | 110 | | 111 | | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- the select multi_value query contains the third enumeration data, BIN (multi_value) from t_bigint_demo where multi_value & 4 +-------------+------------------+ | multi_value | BIN(multi_value) | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 100 | | | 101 | | | 110 | | | 111 | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - Select multi_value,BIN(multi_value) from t_bigint_demo where multi_value =4 +-------------+------------------+ | multi_value | BIN(multi_value) | +-------------+------------------+ | 4 | 100 | +-------------+------------------+Copy the code
  • update
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 | | 100 | | | | 101 | 5 6 7 | | | 110 | | | | 111 | 7 + 8 + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - remove the id as the value of the 7 second enumeration 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) | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | | 100 | 4 + 7 + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Use the varchar type

  • Create tables and prepare data
create table t_varchar_demo( id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value varchar(255) not null default '' );  Insert into T_varchar_demo (multi_value) values('1'); 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, 4-trichlorobenzene); Select multi_value from t_varchar_demo + -- -- -- -- -- -- -- -- -- -- -- -- -- + | multi_value | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 1 | | 2 | | 1, 2 | | 4 | | 1, 4 | 2, 4 | | | 1, 4-trichlorobenzene | + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code
  • The query
- select query contains the second enumeration data multi_value from t_varchar_demo where find_in_set (' 2 ', multi_value) + -- -- -- -- -- -- -- -- -- -- -- -- -- + | multi_value | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 2 | | 1, 2 | | 2, 4 | | 1, 4-trichlorobenzene | + -- -- -- -- -- -- -- -- -- -- -- -- - + - query contains the third enumeration data select multi_value from t_varchar_demo Where find_in_set (' 4 ', multi_value) + -- -- -- -- -- -- -- -- -- -- -- -- -- + | multi_value | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 4 | | 1, 4 | | 2, 4 | | 1, 4-trichlorobenzene | + -- -- -- -- -- -- -- -- -- -- -- -- -- + - only the third enumeration data query to select multi_value from t_varchar_demo where multi_value = '4' + -- -- -- -- -- -- -- -- -- -- -- -- -- + | multi_value | +-------------+ | 4 | +-------------+Copy the code
  • update
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, 4-trichlorobenzene | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + - remove the id as the value of the 7 second enumeration update t_varchar_demo set Multi_value = '1, 4' where id = 7 select * from t_varchar_demo where id = 7 + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id multi_value | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1, 4 7 | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

Use the set type

  • Create tables and prepare data
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, 4-trichlorobenzene);Copy the code
  • The query
Query for data containing the second enumeration, 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, 4-trichlorobenzene | +-------------+ -- query data containing the third enumeration, 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 select multi_value from t_set_demo where Find_in_set (' 4 ', multi_value) + -- -- -- -- -- -- -- -- -- -- -- -- -- + | multi_value | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | 4 | | 1, 4 | | 2, 4 | | 1, 4-trichlorobenzene | + -- -- -- -- -- -- -- -- -- -- -- -- - + -- the value for the select multi_value third enumeration data from t_set_demo where multi_value = '4' + -- -- -- -- -- -- -- -- -- -- -- -- -- + | multi_value | + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code
  • update
select * from t_set_demo +----+-------------+ | id | multi_value | +----+-------------+ | 1 | | | 2 | 1 | | 3 | 2 | | 4 1, 2 | | | | | 4 5 6 | | 1, 4 | | 7 2, 4 | | | | 1 8 4-trichlorobenzene | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + - remove the id as the value of the 7 second enumeration update t_set_demo set Select * from t_set_demo where ID =7 select * from t_set_demo where ID =7 select * from t_set_demo where ID =7 + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | id multi_value | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1, 4 7 | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- +Copy the code

summary

Mysql uses a single column to store multiple values, which is usually used for one-to-many antinormal processing. It can be implemented with the types of bit, int/ BigINT, VARCHar, and set. The drawback is that indexes are not supported.

doc

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