Application of MySQL bit arithmetic

In MySQL, if there is a one-to-many relationship between a piece of data and other data, it is natural for us to think of creating a relational table. For example, there is a data table of attraction information, which is structured as follows:

Id	Int (primary key)
Name	varchar (attraction name)
Province	Int (province)
City	Int (city)

Each attraction contains many properties, such as the Month for the tour, we generally practice two ways: one is to add a varchar field, each month is separated by a special symbol, such as "1,2,3,11,12"; the other is to create a relational table, as follows:

spots_id	int (attraction ID)
Month	Int (for month, value 1-12)

The first method, the query is very inconvenient, for example, to find suitable for the February travel attractions, it is necessary to use the like statement, the efficiency is extremely low. The second method is only suitable for occasions where the attraction is less than the attribute. If the attraction also contains other properties, such as "Alpine", "grassland" and other categories of attributes, as well as "food", "shopping" and other theme attributes, it is necessary to build a relational table according to each attribute, the extension is extremely inconvenient, query may need to query the table, also affect efficiency.

We know that the error level constants in PHP are integers that are determined based on the bits feature, and can be easily customized by the bitwise operation of PHP error reports. We can also apply it to MySQL, or take the above list of attractions as an example, we add a field with the following structure:

Id	Int (primary key)
Name	varchar (attraction name)
Province	Int (province)
City	Int (city)
Month	Int (for travel month)

The table statement is:

CREATE TABLE ' spots ' (' id ' int (ten) unsigned not null auto_increment COMMENT ' attraction id ', ' name ' varchar () NOT NULL COMMENT ' Attraction name ', ' Province ' int (5) unsigned not NULL COMMENT ' sights in the province ', ' City ' int (ten) unsigned not null COMMENT ' attractions to Town ', ' Mont h ' int (ten) unsigned not NULL DEFAULT ' 0 ' COMMENT ' for travel month ', PRIMARY key (' id '), UNIQUE key ' name ' (' name '), key ' Locati On ' (' Province ', ' city ') engine=innodb DEFAULT Charset=utf8 comment= ' Attractions Information table '

Note: You cannot use a 1-12 number to represent the month, but instead use 1,2,4,8,16,32,64,128,512,1024,2048,4096 to represent it.

Here are the tips for using:
1. When we need to inquire about the sights of a certain month, such as the sights of March, you can use the following statement:

SELECT * from ' spots ' WHERE ' month ' & 4 = 4

2. When setting an attraction for a certain month, for example setting 4325 of the attraction for February, use the following statement:

UPDATE ' spots ' SET ' month ' = ' Month ' | 2 WHERE ' id ' = 4325

3. When you cancel the month of an attraction, you can use the following statement:

UPDATE ' spots ' SET ' month ' = ' month ' ^ 2 WHERE ' id ' = 4325

4. The query is suitable for multiple months of data, such as the need to query set 11, 12, January attractions, the corresponding value of three months add up, the result is 6146, and then use this value to query:

SELECT * from ' spots ' WHERE ' month ' & 6146 = 6146


Applied From: http://blog.sae.sina.com.cn/archives/3506

Application of MySQL bit arithmetic