Database Optimization Design_PHP Tutorial

Many times in our projects, as our needs continue to change, we need to add fields to the original library table structure to meet our business needs.

Give a simple example:

I need a database table to store user information for a website. The user information needs to include (whether the account is an active account, whether the account is bound to an email address, whether the account has purchased products, whether the account has expired... etc.). Under normal circumstances, we may design this database table like this:

<span CREATE</span> <span TABLE</span> <span '</span><span ACCOUNT</span><span '</span><span  (
      `ID` </span><span int</span>(<span 22</span>)  <span NOT</span> <span NULL</span> AUTO_INCREMENT, <span --</span><span 自增id</span>
      `F001` <span TINYINT</span>(<span 1</span>) <span NOT</span> <span NULL</span>, <span --</span><span 是否活跃（1:是/0:否）</span>
      `F002` <span TINYINT</span>(<span 1</span>) <span NOT</span> <span NULL</span>, <span --</span><span 是否绑定 （1:是/0:否）</span>
      `F003`  <span TINYINT</span>(<span 1</span>) <span NOT</span> <span NULL</span>, <span --</span><span 是否购买产品 （1:是/0:否）</span>
      `F004`  <span TINYINT</span>(<span 1</span>) <span NOT</span> <span NULL</span>, <span --</span><span 是否过期 （1:是/0:否）</span>
      <span PRIMARY</span> <span KEY</span>(<span '</span><span ID</span><span '</span><span )                  
) ENGINE</span><span =</span>InnoDB AUTO_INCREMENT<span =</span><span 1</span> <span DEFAULT</span> CHARSET<span =</span>utf8;

If designed according to the above method, then when the requirements change, such as adding a field, whether the account is valid. Then we need to add another field `F005`, which will make management very troublesome. When adding a new field, all or historical data need to be updated, which can easily lead to data loss.

So, how to solve this problem and reduce the impact of the correlation between data fields as much as possible. Now there is a way to integrate all these fields into one field. For example: the `FLAG` field is stored in decimal notation (the first digit: 1: active, 0: inactive. The second digit: 1: bound, 2: unbound. The third digit: 1: purchased product , 0: Not purchased....), explain: If the value of this field is 5, converted to binary it is 101, the first digit is 1, the second digit is 0, and the third digit is 1. Then it means that the account is "Active, unbound, and purchased products." What are the benefits of this approach? The advantage is that the correlation between each type is very small, and updating one field type will not affect other fields. So, with this design, how should we query it? Bitwise AND, for a specific situation, I can give an example. When I need to query all purchased products and active accounts, that is (the first and third digits are 1), we fill in the other digits with 0, that is, 101=5:

SQL statement query:

<span SELECT</span> <span *</span> <span FROM</span> ACCOUNT <span WHERE</span> F005<span &</span><span 5</span><span ></span><span 0</span>;

The above SQL statement can query the accounts that have purchased products and are active.

Then, when updating and modifying, you only need to update each binary bit to the required value in the background program. For example, if you just purchased and was active, that is, 101, you want to change it to purchased, but not active. , then use (5&1)=1, thereby setting the third bit to 0. Here is a PHP function that can handle the program business very well.

<?<span php
</span><span /*</span><span *
 * 取按位与要写入的数据
 * @param  $param 原值
 * @param  $postData  提交的数据key=>value,key必须从1开始的数据，value必须为0或者（key-1）次方数 key代表位
 </span><span */</span>
<span function</span> getExtValue(<span $param</span> = 0,<span $postData</span> = <span array</span><span ()){
    </span><span if</span>(!<span empty</span><span $postData</span><span ){
        </span><span foreach</span>(<span $postData</span> <span as</span> <span $key</span>=><span $value</span><span ){
            </span><span $tmp1</span> = <span pow</span>(2,(<span $key</span>-1<span ));
            </span><span if</span> (<span empty</span>(<span $key</span>) || !<span is_numeric</span>(<span $key</span>) || !<span is_numeric</span>(<span $value</span>) || (<span $value</span> != 0 && <span $value</span> !=<span $tmp1</span><span )){
                </span><span continue</span><span ;
            }
            </span><span $param</span> = (<span $param</span> & (0xffff^<span $tmp1</span>)) | <span $value</span><span ;
        }
    }
    </span><span return</span> <span $param</span><span ;
}</span>