How to query the MYSQLSET Field Type

When I wrote a dedecms function yesterday, I suddenly used to understand the flag field in dedecms, but it uses the set type. I started to directly query wherexx to find a single character, however, it is difficult to query multiple fields. Let's summarize the set field query method.

When I wrote a dedecms function yesterday, I suddenly used to understand the flag field in dedecms, but it uses the set type. I started to directly query where = xx to find a single character, however, it is difficult to query multiple fields. Let's summarize the set field query method.

A set can contain up to 64 members. Its value is an integer. (For details about the SET type, refer to the set type of mysql Data Type.) the binary code of this integer indicates which members of the SET value are true. For example, The 'Status' set

The Code is as follows:
('Forsale', 'authsuccess ', 'auditsuccess', 'intentionreached', 'salecanced'), then their values are: SET member Decimal value Binary value ----------------------------- ForSale 1 0001 AuthSuccess 2 0010 AuditSuccess 4 0100 IntentionReached 8 1000

If 9 is saved to the Status field, the binary value is 1001, that is, the values 'forsale' and 'intentionreached' are true.
You can use the LIKE command and the FIND_IN_SET () function to retrieve the SET value:

The Code is as follows:
SELECT * FROM tbl_name WHERE Status LIKE '% value % '; SELECT * FROM tbl_name WHERE FIND_IN_SET ('value', Status)> 0;

Of course, the following SQL statements are also valid, and they are more concise (pay attention to the order and connector of the two members ,):

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

Because the positions of SET members are fixed, the value 9 always indicates that 'forsale' and 'intentionreached' are true, even if a State is added later, it does not affect the previous query logic. Let's take a look at the SQL statement tips for modifying SET fields:
Modify Status to make its 'forsale' member true

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

Modify the Status so that the 'forsale', 'authsuccess', and 'intentionreached' members are true.

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

When you confirm that the current status is 7, remove the 'forsale' member and make sure that the current status is 7. If you do not have this requirement, SET

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

The use of the SET type to save permissions and other data makes development easier and clearer.