Analysis of single-column Multi-value Storage example of mysql
This article introduces the relevant knowledge of "mysql single-column multi-value storage instance analysis". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
The instance uses the bit type
Table building and data preparation
-- bit (3) is defined here, which means that there are 3 bits, the first bit 1, the second bit 2, and the third bit 4create table t_bit_demo (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value bit (3) not null default 0);-- here inserts the combined value insert into t_bit_demo (multi_value) values (bounded 000'); insert into t_bit_demo (multi_value) values (baked 001') Insert into t_bit_demo (multi_value) values (baked 010'); insert into t_bit_demo (multi_value) values (baked 011'); insert into t_bit_demo (multi_value) values (baked 100'); insert into t_bit_demo (multi_value) values (baked 101'); insert into t_bit_demo (multi_value) values (baked 110'); insert into t_bit_demo (multi_value) values (baked 111') -- you can insert int values directly here, for example, 5 equals 101 insert into t_bit_demo-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 | +-+ |
Bit operation query
-- query the second valuable data select multi_value+0 BIN (multi_value) from t_bit_demo where multi_value & 2 thanks to multi_value+0 | BIN (multi_value) | +-+-+ | 2 | 10 | | 3 | | 11 | | 6 | 110 | | 7 | 111 | +-query the third valuable data select multi_value+0 | BIN (multi_value) from t_bit_demo where multi_value & 4 percent multi_value+0 | BIN (multi_value) | +-+-+ | 4 | 100 | | 5 | | 101 | | 6 | 110 | | 7 | 111 | +-query the data select multi_value+0 with only the third value. | 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 colors Musashi + | multi_value+0 | BIN (multi_value) | +-+ | 4 | 100 | +- -+
Update
Select id,multi_value+0 BIN (multi_value) from tweets, demography, demotion, 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 | +-remove the value with an id of 7 and remove the second enumeration update t_bit_demo set multi_value = bounded 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 | | +-+ use int/bigint type |
Table building and data preparation
Create table t_bigint_demo (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value bigint not null default 0);-- suppose there are three enumerated values 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 tasking demographics + | multi_value | +-+ | 0 | | 1 | | 2 | 3 | 4 | | 5 | | 6 | | 7 | +-+
Query
-- query the data select multi_value containing the second enumeration BIN (multi_value) from t_bigint_demo where multi_value & 2 thanks to multi_value | BIN (multi_value) | +-+-+ | 2 | 10 | 3 | 11 | | 6 | 110 | | 7 | 111 | +-query the data select multi_value containing the third enumeration | BIN (multi_value) from t_bigint_demo where multi_value & 4 colors Musashi + | multi_value | BIN (multi_value) | +-+-+ | 4 | 100 | | 5 | 101 | | 6 | 110 | | 7 | 111 | +-query the data select multi_value whose value is the third enumeration | BIN (multi_value) from t_bigint_demo where multi_value = 4 colors Musashi + | multi_value | BIN (multi_value) | +-+ | 4 | 100 | +- -- +
Update
Select id,multi_value BIN (multi_value) from tweets, demographics, Musashi + | 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 | | | +-remove the value of id 7 from the second enumeration update t_bigint_demo set multi_value = bounded 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 | + -+ use varchar type
Table building and data preparation
Create table t_varchar_demo (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value varchar (255) not null default');-- suppose there are three enumerated values 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 ('1Mague 2'); insert into t_varchar_demo (multi_value) values ('4') Insert into t_varchar_demo (multi_value) values ('1jin4'); insert into t_varchar_demo (multi_value) values ('2mag4'); insert into t_varchar_demo (multi_value) values ('1JEI 2jing4') Select multi_value from tactile varchartered demographics + | multi_value | +-+ | 1 | | 2 | | 1magin2 | | 4 | | 1magin4 | | 2magin4 | +-+ |
Query
-- query the data containing the second enumeration: select multi_value from t_varchar_demo where find_in_set ('2 enumerated multiple values) +-+ | multi_value | +-+ | 2 | | 1Magin2 | | 2magin4 | | 1magin2 | | 4 | +-- query the data containing the third enumeration: select multi_value from t_varchar_demo where find_in_set ('4enumerary multidisciplinary value) +-+ | multi_value | +-+ | 4 | | 1Magin4 | | 2magin2 | | 4 | +-- query the data with only the third enumeration select multi_value from t_varchar_demo where multi_value = '4percent enumerated color + | multi_value | +-+ | 4 | +-+
Update
Select * from tactile varchartered demographics, Musashi Muffin + | id | multi_value | +-- +-+ | 1 | 1 | 1 | 2 | 2 | 3 | 1pian 2 | | 4 | 4 | 5 | 1pyr4 | 6 | 2pyr4 | | 7 | 1pyr2 | | 4 | remove the value with an id of 7 from the second enumeration update t_varchar_demo set multi_value ='1 where id=7select * from t_varchar_demo where id=7+----+-+ | id | multi_value | +-- +-+ | 7 | 1Person4 | + -+-+ uses set type
Table building and data preparation
Create table t_set_demo (id int NOT NULL AUTO_INCREMENT PRIMARY KEY, multi_value set)) 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 ('1Mague 2') Insert into t_set_demo (multi_value) values ('4'); insert into t_set_demo (multi_value) values ('1je 4'); insert into t_set_demo (multi_value) values (' 2je 4'); insert into t_set_demo (multi_value) values ('1je 2m 4')
Query
-- query the data containing the second enumeration You can use bit operations or use find_in_setselect multi_value from t_set_demo where multi_value&2select multi_value from t_set_demo where find_in_set ('2magic magnifying multidisciplinary value) +-+ | multi_value | +-+ | 2 | | 1Magin2 | | 2Magin4 | | 1Magne2 | 4 | +-- query the data containing the third enumeration You can use bit operations or use find_in_setselect multi_value from t_set_demo where multi_value&4select multi_value from t_set_demo where find_in_set ('4minute magnifying multidisciplinary value) +-+ | multi_value | +-+ | 4 | | 1Magin4 | | 2Preline 4 | | 1Magne2 | | 4 | +-- query the data select multi_value from t_set_demo where multi_value='4'+-+ whose value is the third enumeration | multi_value | +-+ | 4 | +-+
Update
Select * from tactile setbacks, demographics, multi_value, +-- +-+ | 1 | | 2 | 1 | 3 | 4 | 1 | 4 | 5 | 4 | 6 | 1 | 4 | 7 | 2 | 2 | 8 | 1 | 2 | 4 | +-remove the value of id 7 from the second enumeration update t_set_demo set multi_value = '1authoring 4' where id=7select * from t_set_demo where id=7select * from t_set_demo where id=7+----+-+ | id | multi_value | +-- +-+ | 7 | 1 mysql 4 | +-+-+ "instance analysis of single-column and multi-valued storage in a single column" ends here. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
