What is the length of mysqlIn?

In business logic, This is often the case: Get all the articles under an infinite classification. Count all orders of a store under an account. Articles: in (cid ,......), Order: in (shop_id ,......) If there are many sub-categories under a category, there are many sub-categories...

The business logic is often like this:

Get all the articles under an infinite classification.

Count all orders of a store under an account.

Articles: in (cid ,......),
Order: in (shop_id ,......)

If there are many sub-categories under a specific category, but there are many sub-categories, there may be no limit, and there are also n stores under an account, then in () will not survive, the SQL length cannot be too large.

I don't know if there is any good way to deal with this situation. in () is used in many aspects of the project, and I have never considered this problem. Today I suddenly thought of it and realized this problem, I wonder if there is any good optimization method.

Thank you!

Reply content:

The business logic is often like this:

Get all the articles under an infinite classification.

Count all orders of a store under an account.

Articles: in (cid ,......),
Order: in (shop_id ,......)

If there are many sub-categories under a specific category, but there are many sub-categories, there may be no limit, and there are also n stores under an account, then in () will not survive, the SQL length cannot be too large.

I don't know if there is any good way to deal with this situation. in () is used in many aspects of the project, and I have never considered this problem. Today I suddenly thought of it and realized this problem, I wonder if there is any good optimization method.

Thank you!

I do not remember the statements that replace in. It may also be because I am not very concerned about SQL, which leads to a low level.
This problem is usually caused by the design of the infinite classification table. For example, there are only two fields: id and parent_id. Therefore, if an in table contains id, parent_id, level (tree height), you can avoid using in when using the path field.

An unlimited classification table I personally use includes the following fields
Unique id Index
Parent_id parent id
Number axis projection number
Leve height
Number of the leftmost subnode under the root of the current node
A simple example of evolution:

  [id:1, level:1 number: 1, left_number:1]

Insert a subnode

   [id:1, level:1 number: 2, left_number:1]

[Id: 2, level: 2 number: 1, left_number: 1]
Insert a subnode at level2

    [id:1, level:1 number: 3, left_number:1]

[Id: 2, level: 2 number: 1, left_number: 1] [id: 2, level: 2 number: 2, left_number: 2]

This tree-like data is difficult to maintain, but the search speed is much faster. For example, all the subnodes under any node are left_number to number-1. I used this as a staff structure tree for the Chuan sales system. The results were very good.

The core idea is to convert the branches of a tree into vertices/line segments on the number axis to get the complete projection of the tree on the number axis.

If you forget a top_id, it indicates the root node corresponding to the node, otherwise there will be problems if there are multiple trees in the table.

The Memory engine table is used. When splicing fields in the in table, all these values are written to the table, and then the table is used for association query. in, it is best to use the index field. in this case, the in length limit can be avoided.

If you do not want to use in, you can consider redundant fields. For example, add the account id to all shop orders. The maintenance of link changes is a new problem caused by redundant fields: for example, the store is transferred to another account (but historical orders should still belong to the original account, and redundancy should not be a problem)
Classification may not be suitable for redundancy. It is better to use in to query all types at different levels at any time. After all, it is not an infinite classification.

Mysql in itself has no length limit, and the entire SQL statement has a length limit. Knowing that the in method has certain defects or risks, you may wish to change your mind, an SQL statement is hard to solve. It is really difficult. It can also be done by dynamically piecing together multiple union all statements.

There is no limit on IN, but the length of the entire SQL statement is IN my. cnf. You can configure the limit of max_allowed_packet = 2 M. If the SQL statement is too long, it will definitely affect the analysis efficiency of the transmission and query analyzer. The simpler the SQL statement, the better.
Essentially, IN () is similar to... where id = 1 union all... where id = 2.

This type of junk SQL should be avoided from the business.
1. If possible, put IN (id1, id2) to a table for management. Then JOIN the table.
2. Generally, your id1 and id2 are generated by a query. You can use subquery to do this.
3. Product compromise and Improvement

1. Mysql In THE In operation, as long as the field to establish a valid index, the efficiency does not have to worry.

2. However, id In ('1', '2 ',...., '200') It is recommended that the number of such statements be less than 1000. If there are more statements, the SQL efficiency will decrease. If you need more than one thousand, the landlord needs redundant fields, such as the user account ID in your business scenario.

3. We recommend that you join a joined table upstairs. In my opinion, if the order of magnitude of the table is 0.1 million, the join table would be a disaster.

In cannot be displayed. Try left join on.