How to reduce mysql storage columns (taking network store design as an example) _ MySQL

How to reduce the mysql storage column (taking designing a network store as an example) bitsCN.com
When designing a database, we often encounter fields that indicate "no". Wang Wei, a network fisherman, encountered the following situations when designing a shop table:


The store has the following attributes: first, whether it has the consumer protection service:
Second, there are also such attributes as whether they belong to the Zhongguancun business area. if you want to represent the above attributes, I have to design 7 fields according to the general practice; if a platform-guaranteed service is added later, I need to add another field. one of the disadvantages of this operation is that it is not easy to expand. The second is that it occupies too many columns, too many columns will affect the data search speed;

Third, because these fields indicate non-value, indexing is a waste. Because of the above disadvantages, I have abandoned the above method. here I only need a field to indicate all the above attributes.
The procedure is as follows: 1. design a field named protect_str with the type of binary (12). The default value is 000000000000; 12 bits are designed for future expansion. here, I use six bits from left to right to represent six types of anti-protection products, for example, 000000000011 to describe and deliver goods quickly; if the value is 0, the store does not have this property. for example, if the value is 000000000001, the store does not have the fast delivery property. if the value is 7th, the store does not belong to the Zhongguancun circle. 2. query: now you want to query all the stores with truthfully described attributes. the SQL statement is as follows: SELECT * FROM 'shop1' WHERE (protect_str & 000000000001) = 1 query stores with both accurate descriptions and fast delivery attributes. the SQL statement is as follows: SELECT * FROM 'shop1' WHERE ('protect _ str' & 000000000011) = 11 but when SELECT 100101 & 100001 is executed, the result turns to 99841 (as to why this result is, the online fisherman hasn't figured it out yet. if you have any idea, please leave a message to me) therefore, this method does not work. Therefore, the binary data is stored in decimal format. For example, when the first digit of the binary is 1 (that is, 0000001), it indicates true Description. if it is converted to decimal, it is 1. when the first digit of the binary is 1, the second digit is 1 (that is, 0000011) it indicates true description, and fast delivery, which is converted to 3 in decimal storage in the database; why is 3 (1x2 to the power of 0 + 1x2 to the power of 1 ); when the first digit of the binary is 1, the second digit is also 1, and the third digit is also 1 (that is, 0000111), it indicates true description, and fast delivery, return for replacement within seven days, it is converted to 7 in the database in decimal format. when a store has both truthful descriptions and fast delivery, and the seven-day return guarantee belongs to the Zhongguancun business area, it can be expressed as 1000111, converted to decimal and stored in the database, the value is 71.
The SQL statement is as follows: SELECT * FROM 'shop1' WHERE (protect_str & 1) = 1 now you need to query all stores with the fast shipping attribute, the SQL statement is as follows: SELECT * FROM 'shop1' WHERE (protect_str & 2) = 2 now you want to query all shops in the Zhongguancun Online Business Area. the SQL statement is as follows: SELECT * FROM 'shop1' WHERE ('protect _ str' & 64) = 64 LIMIT 0, 30 query all shops that belong to both the Zhongguancun Online business area and provide a true description of service guarantee, the SQL statement is as follows: SELECT * FROM 'shop1' WHERE ('protect _ str' & 65) = 65 LIMIT 0, 30
If there are extensions in the future, you can add numbers to the upper positions, for example, if you have added a platform assurance service to a store with id = 5, you can set the eighth bits of the protect_str field of the store with this service to 1, that is, 10000000 is converted to decimal. 128 (7 power of 1*2) execute the following statement: UPDATE 'test '. 'shops1' SET 'protect _ str' = ('protect _ str' | 128) WHERE 'shop1 '. 'id' = 5. You can change the value of this field to 135. In summary, where we want to add a service, we use the "|" or operation; where you want to query a service, we use "&" and calculation. It is no longer a problem to reduce the number of mysql storage columns after learning binary to decimal and querying and updating. The following is the database used for the test. you can use the above statement to test the create table 'shop1' ('id' int (10) unsigned not null auto_increment, 'shop _ name' char (50) collate utf8_unicode_ci not null, 'protect _ str 'int (12) not null default '0', 'shop _ id' int (11) unsigned not null, primary key ('id') ENGINE = MyISAM default charset = utf8 COLLATE = utf8_unicode_ci COMMENT = 'shop table' AUTO_INCREMENT = 8; ---- export data in the table 'shop1' -- insert into 'shop1' VALUES (1, 'bestshop ', 1, 13); insert into 'shop1' VALUES (3, 'firstshop ', 2, 15); insert into 'shops1' VALUES (4, 'nihao', 71, 23); insert into 'shops1' VALUES (5, 'shis ', 135, 34); insert into 'shop1' VALUES (6, 'tshop ', 6, 45); insert into 'shop1' VALUES (7, 'uush', 65, 65 ); author: sss0213bitsCN.com