Let’s start with a common requirement in a system:

There is an AD table, we want to do display control for the AD:

  • Manually connect and unload cables.
  • Vips only.
CREATE TABLE `finger_ad` (
  `ad_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `ad_name` varchar(50) NOT NULL COMMENT 'AD Name',
  `ad_image_url` varchar(255) NOT NULL COMMENT 'Advertising picture',
  `ad_url` varchar(255) NOT NULL COMMENT 'AD image URL jump address',
  `is_vip` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Is it only for VIP?',
  `display` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Display status: 1 show, 0 hide',
  PRIMARY KEY (`ad_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='AD table';Copy the code

Let’s say later, our requirements change. A few more restrictions need to be added:

  • Logged in User
  • Unlogged user
  • Users who have not logged in within 30 days
  • Sign up for 30 days

Is it nerve-racking to develop students in the face of such constraints?

Probably the first thing many developers do is add this new constraint field to the table structure. Everything seemed rosy.

Indeed, this is the fastest and easiest way to add fields. It can also fulfill our needs.

However, this can lead to the following problems:

  • Add restrictions each time. We all need to add fields. This is more energy to the database less change less change. After all, adding fields without limit is not desirable.
  • Suppose the AD table has a large amount of data. Large enough that adding a field can take several minutes, which can cause read and write stress on the database server.
  • The more conditions there are, the longer the SQL conditional statement will be.

So, is there a better way to solve these problems?

Answer: Yes!

This is our advanced technique for bitwise and operators.


Let’s change the above table structure:

DROP TABLE IF EXISTS `finger_ad`;
CREATE TABLE `finger_ad` (
  `ad_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'primary key',
  `ad_name` varchar(50) NOT NULL COMMENT 'AD Name',
  `ad_image_url` varchar(255) NOT NULL COMMENT 'Advertising picture',
  `ad_url` varchar(255) NOT NULL COMMENT 'AD image URL jump address',
  `bit_condition` INT(11) UNSIGNED NOT NULL COMMENT 'Bit operation conditions: 1- login accessible, 2- unlogged accessible, 4-30 days registration accessible, 8-30 days unlogged accessible, 16-unconsumed accessible, 32-VIP accessible',
  `display` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Display status: 1 show, 0 hide',
  PRIMARY KEY (`ad_id`)
) ENGINE=InnoDB DEFAULT CHARSET UTF8 COMMENT='AD table';Copy the code

We took all the conditions out. Added a field: bit_condition. Combine all the conditions into a single field.

So how do we write code at this point?

For example, now add the following restrictions to the AD:

Only logged-in users or those who have been registered for 30 days or VIP users are allowed to access the ads.

So how do I set the value of bit_condition for this AD? It’s easy to just add up the bits of these conditions. The value is 37.

A lot of it might be weird. If I set it to 37, how do I know it’s the sum of these values? If you are familiar with Linux system permissions, you can easily understand this approach. In fact, the bitwise and property is used here: the values added in any combination are not repeated.

It’s a little hard to understand. It’s hard for me to sort it out for you. You can dig into this knowledge on the Internet. That’s all you need to know.

So, how do we write SQL now?

The following is an example:

SELECT * FROM finger_ad WHERE display = 1 AND bit_condition & 3 = bit_conditionCopy the code

The 3 in this SQL statement corresponds to the value obtained by the current user for this number of conditions. If the bit_condition bit value is 3 and the bit_condition result is the same, the condition is met.

We solved the problem of all conditions with a single field. Thanks, indeed, to the bitwise and operator properties. I’m also happy that MySQL supports bitwise operators.

So, what’s the downside?

I think experienced students have already seen that. This way of writing only satisfies the inclusion relation. Not if you want to achieve access to meet three conditions at the same time. Or, one satisfies the other. The advantages are obvious, so are the disadvantages. You should choose according to the actual situation.

Extended Learning Files:

<? PHP // Example 1echo '<h2>demo1</h2><br>'; // Define constant define('D1', 1); define('D2', 2); define('D3', 4); define('D4'And 8); define('D5', 16);function showStatus($state) {for($i= 1;$i< = 5;$i{+ +)$d = 'D'.$i;
        $dd = constant($d); // take the corresponding constant valueif(($state & $dd) > 0) {echo 'the first'.$i.'Lamp light'.'<br>'; / / 8}else{
            echo < p style=" max-width: 100%; clear: both; min-height: 1em;.$i.'Lights out '.'<br>'; }}echo SELECT * FROM 'table' WHERE state & {$state} = state;";
    echo '<br>';
    echo '<br>';
}
$state = D4;
showStatus($state); // Turn on the 4th light$state = D1;
showStatus($state); // Turn on only the first light$state = D4 | D1;
showStatus($state); // Turn on the first light and the fourth light$state = (D4 | D2 | D1) & (~D1);
showStatus($state); // Turn on the first light, the second light, and the fourth light, then turn off the first lightecho '<h2>demo2</h2><br>'; /** * 1, permission application * have permissions, the corresponding value of these permissions * example: If the moderator has permission (add, delete, modify, query), then the moderator's permission value is stored as 15 (8+4+2+1) * then [sum of permission values] is compared with [actual permission values] * If the result is true, then the moderator has permission * if the result is false, then the moderator has no permission * * Note: The permission value must be 2 ^ N, starting from 0, ^ 31 = 2147483648 * ^ 32 = 4294967296, has exceeded the common int(10) maximum storage 4294967295, */ / select * from bitINT, vARCHar, etc. */ / select * from bitINT, varchar, etc. */ / select * from bitINT, varchar, etc. */ / Select * from bitINT, varchar, etc. */ /'mDELETE'And 8); define('mUPLOAD', 4); define('mWRITE', 2); define('mREAD', 1); / / VVVVVVVVVVVVV instructions VVVVVVVVVVVVV permissions for the / / department manager (assuming it has the department of all permissions), | is an operator or running, not familiar with the check dataecho 'All permissions are:'.(mDELETE|mUPLOAD|mWRITE|mREAD).'<br>'; 8+4+2+1=15 * should be: 4 + 2 + 1 = 7 * / / / ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ / / judge someone access available, set permissions value in$key*/ // upload and */ /readPermissions,$key= (mUPLOAD|mREAD); // Add the upload and read-only permissions: 4+1=5echo 'Current permissions are:'.$key.'<br>';;
if($key & mDELETE){
    echo 'Have delete permission'.'<br>'; / / 8}else{
    echo '.'<br>'; / / 8}if($key & mUPLOAD){
    echo 'Have upload permission'.'<br>'; / / 4}else{
    echo ' .'<br>'; / / 4}if($key & mWRITE){
    echo 'Have write permission'.'<br>'; / / 2}else{
    echo ' .'<br>'; / / 2}if($key & mREAD){
    echo 'Have read-only permission'.'<br>'; / / 1}else{
    echo '.'<br>'; / / 1}? >Copy the code

Running results: