_php tutorial on database optimization design

Most of the time, we may have to add fields to our existing library table structure in order to meet our business needs as our needs change.

To give a simple example:

I need a database table to store user information for a site. This user information needs to include (whether the account is an active account, whether the account is bound to the mailbox, the account has purchased the product, the account is expired ...). And so on), in general, we might be designing this library table:

　　

  () Auto_increment, < span>  ' F001 '  ()  , Span--< Span> ' F002 '  ()  , < span>  ' F003 '  ()  , < span>  ' F004 '  ()  , < span>    ( innodb auto_increment  charsetutf8;         <> <> <>  <>

If the above method is designed, then when the requirements change, such as: Add a field, the account is valid. Then we need to add another field ' F005 ', which can lead to very cumbersome management, and when new fields are added, all or historical data needs to be updated, which could easily lead to data loss.

So, how to solve this problem, so that the correlation between data fields to reduce the impact as much as possible. Now, one way to do this is to put all of these field integrations in one field. such as: The ' FLAG ' field is stored in 10 binary (first bit: 1: Active, 0: Inactive.) Second bit: 1: Bind, 2: unbound. Third place: 1: Purchased product, 0: not purchased .... ), explain: If the value of this field is 5, the conversion to binary is 101, the first bit is 1, the second bit is 0, and the third digit is 1. Then it means that the account is "active, unbound, and has purchased the product." What is the benefit of this approach, the benefit is that the association between the various types is small and does not affect other fields because of updating one field type. So, how do you query for this design? bitwise AND, specific circumstances can give an example, when I need to query all purchased products, but also active accounts, that is (first and third place is 1), other bits we fill 0, that is 101=5:

SQL statement query:

　　　　

< span="">< span="">< span="">< span="">< span="">F005;< span="">< span="">

The above SQL statement will be able to query the purchase of the product and active account.

Then, when updating the changes only need to update the bits in the background program to the value of the requirements, for example, just bought and active, that is, 101, to change to buy, but not active, then use (5&1) = 1, so that the third position will be 0. Here is a PHP function that can handle the business of the program well.

　　

 Value,key must start from 1, value must be 0 or (key-1) the number of times the key represents the bitGetextvalue (= 0,= (!   (  = >  =  (2, -1   ( () | |! () | |!  () | | (! = 0 && !=   = ( & (0xffff^) |    < span> <>    /span>                  

http://www.bkjia.com/PHPjc/743382.html www.bkjia.com true http://www.bkjia.com/PHPjc/743382.html techarticle Most of the time, we may have to add fields to our existing library table structure in order to meet our business needs as our needs change. To give a simple ...

