MYSQL set field type how to query

A set can contain up to 64 members whose value is an integer. (Set type base check MySQL data type set type) The binary of this integer indicates which members of the set's value are true. For example, there is a ' Status ' set

The code is as follows	Copy Code
(' Forsale ', ' authsuccess ', ' auditsuccess ', ' intentionreached ', ' salecanceled '), their values are: SET member Decimal Value Binary value －－－－－－－－－－－－－－－－－－－－－－－－－－－－－ Forsale 1 0001 AuthSuccess 2 0010 Auditsuccess 4 0100 Intentionreached 8 1000

If you deposit 9 in the Status field, the binary value is 1001, which means ' forsale ' and ' intentionreached ' are true for this value.
It can be thought that if so, you can use the like command and the Find_in_set () function to retrieve the SET value:

The code is as follows	Copy Code
SELECT * from Tbl_name WHERE Status is like '%value% '; SELECT * from Tbl_name WHERE find_in_set (' value ', Status) >0;

Of course, the following SQL statements are also legal and they appear to be more concise (note the order of two members and connectors):

The code is as follows	Copy Code
SELECT * from tbl_name WHERE Status = ' forsale,intentionreached '; at the same time we can also use the value directly to query: SELECT * from tbl_name WHERE Status = 9

Because the position of the members of the set type is fixed, the value 9 always represents ' Forsale ' and ' intentionreached ' as true, so that even if you add a state later, it will not affect the previous query logic. Let's take a look at the use of SQL statements to modify the Set Type field:
Modify status so that its ' forsale ' members are true

The code is as follows	Copy Code
UPDATE tbl_name SET Status = 1 WHERE Id = 333

Modify status so that its ' forsale ', ' authsuccess ', ' intentionreached ' members are true

The code is as follows	Copy Code
UPDATE tbl_name SET Status = 1 | 2 | 4 WHERE Id = 333;

In the confirmation of the current status of 7 o'clock Remove ' forsale ' members, let 7 also as the operation of the condition is to ensure that the current state is 7, if not this requirement can be directly SET

The code is as follows	Copy Code
Status = 6; UPDATE tbl_name SET Status = 7 & ~ WHERE Id = 333;

Oh, the use of set types to save permissions and other data really makes the development of a lot of convenient and clear wow.