mysql tutorialThe column introduces bit operations to simplify one-to-many relationships
Recommended (free): mysql tutorial
Syntax
& :
Bitwise AND, binary bits at the same time Bits that are all 1 are set to 1. | :
Bitwise OR, if one binary bit is 1, then it is 1.^ :
Bitwise XOR, when the binary numbers of the corresponding bits are different, Only the result of the corresponding bit is 1; if both corresponding bits are 0 or both are 1, the result of the corresponding bit is 0.
Principle
$a = 6 转化为2进制为 110 $b = 3 转化为2进制为 11 $a & $b即是 110 与 11 将$a和$b中都为1的位设为1,位数不够的补0.即110 与 011 运算结果010,转化为十进制结果为2
Application Scenario
Each scenic spot contains many attributes, such as months suitable for tourism, we generally There may be two methods:
varchar
field, and save each month separated by a special symbol, for example: "1,2,22 ,65,7"
1, 2, 4, 8, 16, 32, 64, 128, 512, 1024, 2048, 4096
to indicate that if there are multiple months, they can be combined and added to each other, and then stored as a value. 1 512 4096=4609
,4096. This technique is suitable for one-to-many scenarios with few attributes. It can store one or more. If there are too many, it is recommended to try the relationship table. Commonly used attributes include: month, message reminder type, various limited type combinations, etc.
Usage tips:
-- 添加一个分类 用 “|” SELECT (4|2|1); --- = 7 -- 去掉一个分类,用“^” SELECT 7 ^ 1; -- 当我们需要查询某个月份的景点时,例如查询3月份的景点,可使用以下语句: SELECT * FROM `spots` WHERE `month` & 4 = 4; -- 当设置某个景点适合某个月份时,例如设置4325的景点适合2月份,可使用下面的语句: UPDATE `spots` SET `month` = `month` | 2 WHERE `id` = 4325 -- 当取消设置某个景点的月份时,可使用下面的语句: UPDATE `spots` SET` month` = `month` ^ 2 WHERE`id`= 4325 -- 查询同时适合多个月份的数据,例如需要查询设置了11,12,1月份的景点,将其三个月份对应的数值加起来,结果为6145,然后使用这个数值进行查询: SELECT * FROM `spots` WHERE `month` & 6145 = 6145 -- 查询只要适合,1,11,12月份其中一个月份的景点就行 SELECT * FROM `spots` WHERE (`month` & 4096 = 4096) or (`month` & 2048 = 2048) or (`month` & 1 = 1)
The above is the detailed content of Introducing Mysql bit operations to simplify one-to-many relationships. For more information, please follow other related articles on the PHP Chinese website!