Introducing Mysql bit operations to simplify one-to-many relationships

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:

1. is to add a varchar field, and save each month separated by a special symbol, for example: "1,2,22 ,65,7"
2. Create a relational table, where numbers from 1 to 12 cannot be used to represent the month, but 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.
   For example, in the months of 1, 10, and 12, you can store the 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!