Basic principles and examples of database field reuse

I. basis of logical algebra: 1. numbers are represented in binary. all possible numbers are 0 and 1. 2. there are only three basic operations: AND, OR, and not. The operation is defined as: (expressed and calculated) 00 = 001 = 010 = 011 = 1 can be simply understood as: as long as there is a 0, the result is 0, and the multiplication is similar. Or is defined as: (| "> <LINKhref =" ht

I. logical algebra basics:

1. digits are represented in binary. all possible numbers are 0 and 1.

2. there are only three basic operations: "and", "or", and "not.

And operation is defined as: (use & expression and operation)

0 & 0 = 0

0 & 1 = 0

1 & 0 = 0

1 & 1 = 1

It can be simply understood that if there is a 0, the result is 0, which is similar to multiplication.

Or is defined as: (use | representation and operation)

0 | 0 = 0

0 | 1 = 1

1 | 0 = 1

1 | 1 = 1

It can be simply understood as: if there is a 1, the result is 1, and the addition method is similar.

II. logic operation example:

01111010101010101111111111111111 & 1100000 = 1100000

Generally, it can be understood:

If you want to obtain a number N-bit value, you only need to calculate the number and the N-1 power of 2 (mask.

III. database field definition:

Take the data table binary_sample as an example:

Create table binary_sample (

Uid int unsigned not null,

Status int unsigned not null default 0,

Primary key (uid ),

Key I _s (status)

) Engine = innodb;

Status field definition:

The status field is a 32-bit integer. to store as many attributes as possible, we define it as follows:

The descriptions of all the following "bits" are represented in ascending order (from right to left.

0-2 bits indicate the user registration status:

000 indicates that the new registration is not approved

001 indicates registration is approved

010 represents a senior user

011 indicates administrator

100 indicates Super Administrator

101 reserved

110 reserved

111 mask

3-5 User gender:

000 indicates that the gender is uncertain.

001 indicates male

010 indicates gender as female

011 reserved

100 reserved

101 reserved

110 reserved

111 mask

If we want to query all male users:

Select * from binary_sample where status & B '000000' = B '000000 ';

If we want to query all administrator users:

Select * from binary_sample where status & B '111' = B '011 ';

If we want to query all male administrator users:

Select * from binary_sample where status & B '000000' = B '000000 ';

If we want to query all non-new registered unapproved users:

Select * from binary_sample where status & B '111 '! = B '000 ';

4. use the PHP program for such calculation:

Define ("USER_NEW", 0); // 000

Define ("USER_NORMAL", 1); // 001

Define ("USER_ADVANCE", 2); // 010

Define ("USER_MANAGE", 3); // 011

Define ("USER_SUPER", 4); // 100

Define ("USER_MASK", 7); // 111

Define ("GENDER_UNKNOWN", 0); // 000000

Define ("GENDER_MALE", 8); // 001000

Define ("GENDER_FEMALE", 9); // 010000

Define ("GENDER_MASK", 56); // 111000

If we want to query all male users:

$ Status = GENDER_MALE;

$ Mask = GENDER_MASK;

$ SQL = "select * from binary_sample where status & $ {mask} '=$ {status }";

If we want to query all administrator users:

$ Status = USER_MANAGE;

$ Mask = USER_MASK;

$ SQL = "select * from binary_sample where status & $ {mask} '=$ {status }";

If we want to query all male administrator users:

$ Status = GENDER_MALE & USER_MANAGE;

$ Mask = GENDER_MASK & GENDER_MASK;

$ SQL = "select * from binary_sample where status & $ {mask} '=$ {status }";

If we want to query all non-new registered unapproved users:

$ Status = USER_NEW;

$ Mask = USER_MASK;

$ SQL = "select * from binary_sample where status & $ {mask }! =$ {Status }";

So long as the meaning of each value is defined, the query is basically set.