业务逻辑中常有这样的情况:
得到某一个无限分类下面的全部文章。
统计某一账号下面店铺的全部订单。
经常会用到article: in (cid,……),
order: in (shop_id,……)
如果某个分类下面有很多很多子分类,总之很多很多,可能无限,某一个账号下面也有n个店铺,那么in ()不撑死了啊,sql长度不能很大吧
不知道对于这种情况有没有什么好的方法,项目中很多地方都是这样用in (),从来没有考虑过这个问题,今天突然想到了,意识到了这个问题,不知道有没有好的优化方式。
在线等大神,谢谢了!
回复内容:
业务逻辑中常有这样的情况:
得到某一个无限分类下面的全部文章。
统计某一账号下面店铺的全部订单。
经常会用到article: in (cid,……),
order: in (shop_id,……)
如果某个分类下面有很多很多子分类,总之很多很多,可能无限,某一个账号下面也有n个店铺,那么in ()不撑死了啊,sql长度不能很大吧
不知道对于这种情况有没有什么好的方法,项目中很多地方都是这样用in (),从来没有考虑过这个问题,今天突然想到了,意识到了这个问题,不知道有没有好的优化方式。
在线等大神,谢谢了!
不记得有代替in的语句,可能也是因为我不太关注sql导致水平较低哈。
这种问题通常是由于无限分类表本身的设计问题导致的,例如只有id, parent_id两个字段,所以只要in了,如果无限分类表包含 id, parent_id, level(树高), path这个字段的时候就可以避免使用in了。
我个人使用的一个无限分级表包括以下的字段
id 唯一索引
parent_id 父级的id
number 数轴投影序号
leve 树高
left_number 已当前节点为树根时,其下的最左端的子节点的数轴投影序号
一个简单的例子演变:
<code> [id:1, level:1 number: 1, left_number:1]</code>
插入一个子节点
<code> [id:1, level:1 number: 2, left_number:1]</code>
[id:2, level:2 number: 1, left_number:1]
于level2再插入一个子节点
<code> [id:1, level:1 number: 3, left_number:1]</code>
[id:2, level:2 number: 1, left_number:1] [id:2, level:2 number: 2, left_number:2]
这种树形的数据维护很麻烦,但是搜索要快的多,例如任意节点下的全部子节点就是left_number到number-1。用这个做过一个Chuan啊销系统的人员结构树,效果很不错。
核心思路就是把一颗树的分支转换为数轴上的点/线段,从而在数轴上得到完整的树的投影。
忘了一个top_id,表示节点对应的根节点,否则表里面有多颗树的话就出问题了。
采用Memory引擎表,在拼接in里面的字段时,把这些值全部写到这个表中,然后使用这个表关联查询;in里面最好是索引字段,这样的话,可以避免in长度限制。
不想用 in 的话,可以考虑冗余一个字段。比如所有的商铺订单加上帐号 id。冗余字段带来新问题就是关系变动的维护:比如商铺转移给其他帐号(但历史订单应该还是属于原来的帐号,冗余应该问题不大)
分类的话可能就不适合冗余了,随时可能查询不同级别下的所有分类,还是用 in 比较好,毕竟不是真的无限分类。
mysql in 本身 没有长度限制,而整个SQL 是有长度限制的,明知道 in 的法子有一定的缺陷或者 存在隐患,不妨 换一种思路,一个SQL 很难搞定,实在不济, 动态拼凑其多个union all ,也可以搞定的
IN没有限制,不过整个SQL长度在my.cnf可以配置限制的max_allowed_packet=2M。SQL太长肯定会影响传输和查询分析器分析的效率,越精简越好。
IN的这种做法,本质上底层IN(1,2) 和... where id = 1 union all ..where id = 2差不多的。
从业务上要归避这种垃圾SQL。
1、业务上如果可以,把IN(id1,id2) 把这些id放到一个表中管理。然后JOIN这个表。
2、通常你的id1,id2是由某个查询产生的,可以用子查询来做。
3、产品上妥协,改善
mysql中的In操作,只要字段上建立好有效的索引,效率完全不用担心。
不过id In('1','2', ....,'1000' )这种语句,最好数量控制在一千以内,再多的话,sql效率就会下降。如果有需求超过一千以上,说明楼主需要冗余字段了,比如冗余你业务场景中的用户账号ID。
至于楼上建议join联表的,我觉得如果表数量级在10万级别还可以;一旦达到百万级,联表就是灾难。
in 实在显示不了,试试left join on 连接一下吧

TomodifydatainaPHPsession,startthesessionwithsession_start(),thenuse$_SESSIONtoset,modify,orremovevariables.1)Startthesession.2)Setormodifysessionvariablesusing$_SESSION.3)Removevariableswithunset().4)Clearallvariableswithsession_unset().5)Destroythe

Arrays can be stored in PHP sessions. 1. Start the session and use session_start(). 2. Create an array and store it in $_SESSION. 3. Retrieve the array through $_SESSION. 4. Optimize session data to improve performance.

PHP session garbage collection is triggered through a probability mechanism to clean up expired session data. 1) Set the trigger probability and session life cycle in the configuration file; 2) You can use cron tasks to optimize high-load applications; 3) You need to balance the garbage collection frequency and performance to avoid data loss.

Tracking user session activities in PHP is implemented through session management. 1) Use session_start() to start the session. 2) Store and access data through the $_SESSION array. 3) Call session_destroy() to end the session. Session tracking is used for user behavior analysis, security monitoring, and performance optimization.

Using databases to store PHP session data can improve performance and scalability. 1) Configure MySQL to store session data: Set up the session processor in php.ini or PHP code. 2) Implement custom session processor: define open, close, read, write and other functions to interact with the database. 3) Optimization and best practices: Use indexing, caching, data compression and distributed storage to improve performance.

PHPsessionstrackuserdataacrossmultiplepagerequestsusingauniqueIDstoredinacookie.Here'showtomanagethemeffectively:1)Startasessionwithsession_start()andstoredatain$_SESSION.2)RegeneratethesessionIDafterloginwithsession_regenerate_id(true)topreventsessi

In PHP, iterating through session data can be achieved through the following steps: 1. Start the session using session_start(). 2. Iterate through foreach loop through all key-value pairs in the $_SESSION array. 3. When processing complex data structures, use is_array() or is_object() functions and use print_r() to output detailed information. 4. When optimizing traversal, paging can be used to avoid processing large amounts of data at one time. This will help you manage and use PHP session data more efficiently in your actual project.

The session realizes user authentication through the server-side state management mechanism. 1) Session creation and generation of unique IDs, 2) IDs are passed through cookies, 3) Server stores and accesses session data through IDs, 4) User authentication and status management are realized, improving application security and user experience.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

PhpStorm Mac version
The latest (2018.2.1) professional PHP integrated development tool

mPDF
mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),

MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

EditPlus Chinese cracked version
Small size, syntax highlighting, does not support code prompt function
