Remember to query in the database: The method of "contains" or "contains only" orders for certain goods

There is such a requirement:

1. Identify orders from the database that contain "goods 1" and "Commodities 2";
2. Identify orders from the database that contain "goods 1" or "Commodities 2";
3. Identify orders from the database that contain only "goods 1" and "Goods 2";
4. Identify orders from the database that contain only "goods 1" or "Goods 2";

For example, only "Product 1" and "Item 2" can be extended to the needs of multiple commodities.

The tables involved are probably as follows:

What should I do? Taking the 1th requirement as an example, a feasible method is to find out all the orders containing "goods 1", then traverse these orders, select the orders containing "goods 2", if you want to query the orders containing more goods, we need to do multiple traversal, layer screening, inefficient. Moreover, in real-world situations, it is often necessary to support paging queries, which is not feasible or complex to implement.

The second approach is this: Suppose the query contains orders for "Goods 1", "2", "Goods 3", the goods_id are 1, 2, and 3,sql as follows:

SELECT *  from`Order` WHEREorder_idinch ( SELECTtmp.order_id from(SELECTorder_id fromOrder_itemWHEREgoods_id= 1) astmpINNER JOIN(SELECTorder_id fromOrder_itemWHEREgoods_id= 2) asT2 ontmp.order_id=t2.order_idINNER JOIN(SELECTorder_id fromOrder_itemWHEREgoods_id= 3) asT3 ontmp.order_id=t3.order_id) LIMIT0,Ten

If the requirement is only included, you can write this:

SELECT *  from`Order` WHEREorder_idinch ( SELECTtmp.order_id from(SELECTorder_id fromOrder_itemWHEREgoods_id= 1) astmpINNER JOIN(SELECTorder_id fromOrder_itemWHEREgoods_id= 2) asT2 ontmp.order_id=t2.order_idINNER JOIN(SELECTorder_id fromOrder_itemWHEREgoods_id= 3) asT3 ontmp.order_id=t3.order_idWHERE(SELECT Count(*) fromOrder_itemWHEREorder_id=TMP.ORDER_ID)= 3) LIMIT0,Ten

Add a where condition to limit the order to only three items.

The requirement points 1 and 3 are implemented as described above, and demand points 2 and 4 can be implemented in a similar manner.

This implementation is indeed able to meet the demand, but there is a serious problem: when the associated goods more, the use of multiple inner join, will make the query efficiency is very low, especially when the order volume is large, will be more slow, if coupled with other query conditions, such as: Order time, delivery time, The order status and so on all kinds of conditions, (cannot be described here). Pro-Test Query 7 to 8 items, hundreds of thousands of orders, the time has been slow to do not want to.

The last way to achieve this is the focus of this article, which requires a small change to the data structure, such as:

, the goods table and the Order table all add a field: bit.

First say the bit in the goods table, the field represents the product encoding, the value is 2 of the N-square (n>=0), assuming that the goods table has 5 items, then the bit value is 1, 2, 4, 8, 16, 32.

Again, the bit in the order table, which represents the sum of the bit values of the goods that the order contains, assuming that an order contains a bit value of 1 and 2, then its bit is 3.

How do you implement demand? First say demand 1, assuming that "commodity 1" bit is 1, "Commodity 2" bit is 2, then query contains "Goods 1" and "Goods 2" of the Order of SQL as follows:

SELECT *  from 'orderwherebit&3=3

The 3 here is the sum of "merchandise 1" and "Item 2" bit. How can this be understood, from the bit of goods, the bit of the commodity is expressed in binary notation such as:

By, you can imagine the binary form of the value of the bit in the order table, if the order contains goods 1, 2, then bits 11, if it contains goods 1, 3, then bits 101, if it contains goods 1, 2, 3, then bits 111, ... Various combinations of goods, the bit value of the addition does not produce carry, because each product bit value of "1" bit value for other goods bit values are "0" bit, so the various combinations of goods corresponding to the order of the bit value is unique, this is why the bit requirements of the product value is 2 of the N-square (n>=0).

Therefore, in the above SQL, if the order bit and the bit and bitwise AND operation of the item to be queried, if the result is the sum of the bits of the query item, then the order bit corresponds to the bit 1, and the item is ordered.

Demand 2, including "Merchandise 1" or "Merchandise 2" how to say? SQL is as follows:

SELECT *  from 'orderwherebit&3>0

The corresponding bit of the order bit has at least one bit of the product.

Requirement 3: What does "Product 1" and "Item 2" mean? SQL is as follows:

SELECT *  from 'orderwherebit=3

Requirement 4: What does "Product 1" or "Item 2" mean? In this case, there is only one item in the order, "Item 1" or "Item 2". SQL is as follows:

SELECT *  from 'orderwherebit& (bit-1=  0andbit&3!=0

Here Bit & (bit-1) = 0 guarantee that there is only one item in the order, bit & 3! = 0 guarantee that the goods in the order are "item 1" or "Item 2".

This approach is highly efficient, satisfying requirements and simplifying SQL.

Remember to query in the database: The method of "contains" or "contains only" orders for certain goods