SQL 查询长度的实际限制（特别是 MySQL）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/96553/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Practical limit to length of SQL query (specifically MySQL)
提问by Asmor
Is it particularly bad to have a very, very large SQL query with lots of (potentially redundant) WHERE clauses?
有一个非常非常大的 SQL 查询和很多（可能是冗余的）WHERE 子句是不是特别糟糕？
For example, here's a query I've generated from my web application with everything turned off, which should be the largest possible query for this program to generate:
例如，这是我从我的 Web 应用程序生成的一个查询，所有内容都关闭，这应该是该程序生成的最大查询：
SELECT *
FROM 4e_magic_items
INNER JOIN 4e_magic_item_levels
ON 4e_magic_items.id = 4e_magic_item_levels.itemid
INNER JOIN 4e_monster_sources
ON 4e_magic_items.source = 4e_monster_sources.id
WHERE (itemlevel BETWEEN 1 AND 30)
AND source!=16 AND source!=2 AND source!=5
AND source!=13 AND source!=15 AND source!=3
AND source!=4 AND source!=12 AND source!=7
AND source!=14 AND source!=11 AND source!=10
AND source!=8 AND source!=1 AND source!=6
AND source!=9 AND type!='Arms' AND type!='Feet'
AND type!='Hands' AND type!='Head'
AND type!='Neck' AND type!='Orb'
AND type!='Potion' AND type!='Ring'
AND type!='Rod' AND type!='Staff'
AND type!='Symbol' AND type!='Waist'
AND type!='Wand' AND type!='Wondrous Item'
AND type!='Alchemical Item' AND type!='Elixir'
AND type!='Reagent' AND type!='Whetstone'
AND type!='Other Consumable' AND type!='Companion'
AND type!='Mount' AND (type!='Armor' OR (false ))
AND (type!='Weapon' OR (false ))
ORDER BY type ASC, itemlevel ASC, name ASC
It seems to work well enough, but it's also not particularly high traffic (a few hundred hits a day or so), and I wonder if it would be worth the effort to try and optimize the queries to remove redundancies and such.
它似乎工作得很好，但它的流量也不是特别高（每天大约有几百次点击），我想知道是否值得尝试优化查询以消除冗余等。
回答by JosephStyons
Reading your query makes me want to play an RPG.
阅读您的查询让我想玩RPG。
This is definitely not too long. As long as they are well formatted, I'd say a practical limit is about 100 lines. After that, you're better off breaking subqueries into views just to keep your eyes from crossing.
这绝对不会太长。只要它们格式正确，我会说实际限制是大约 100 行。之后，您最好将子查询分解为视图，以防止您的视线交叉。
I've worked with some queries that are 1000+ lines, and that's hard to debug.
我处理过一些超过 1000 行的查询，这很难调试。
By the way, may I suggest a reformatted version? This is mostly to demonstrate the importance of formatting; I trust this will be easier to understand.
顺便说一句，我可以建议重新格式化的版本吗？这主要是为了证明格式的重要性；我相信这会更容易理解。
select *
from
4e_magic_items mi
,4e_magic_item_levels mil
,4e_monster_sources ms
where mi.id = mil.itemid
and mi.source = ms.id
and itemlevel between 1 and 30
and source not in(16,2,5,13,15,3,4,12,7,14,11,10,8,1,6,9)
and type not in(
'Arms' ,'Feet' ,'Hands' ,'Head' ,'Neck' ,'Orb' ,
'Potion' ,'Ring' ,'Rod' ,'Staff' ,'Symbol' ,'Waist' ,
'Wand' ,'Wondrous Item' ,'Alchemical Item' ,'Elixir' ,
'Reagent' ,'Whetstone' ,'Other Consumable' ,'Companion' ,
'Mount'
)
and ((type != 'Armor') or (false))
and ((type != 'Weapon') or (false))
order by
type asc
,itemlevel asc
,name asc
/*
Some thoughts:
==============
0 - Formatting really matters, in SQL even more than most languages.
1 - consider selecting only the columns you need, not "*"
2 - use of table aliases makes it short & clear ("MI", "MIL" in my example)
3 - joins in the WHERE clause will un-clutter your FROM clause
4 - use NOT IN for long lists
5 - logically, the last two lines can be added to the "type not in" section.
I'm not sure why you have the "or false", but I'll assume some good reason
and leave them here.
*/
回答by Chris
Default MySQL 5.0 server limitation is "1MB", configurable up to 1GB.
默认 MySQL 5.0 服务器限制为“ 1MB”，最多可配置为 1GB。
This is configured via the max_allowed_packetsetting on both client and server, and the effective limitation is the lessor of the two.
这是通过客户端和服务器上的max_allowed_packet设置进行配置的，有效限制是两者中的较小者。
Caveats:
注意事项：
- It's likely that this "packet" limitation does not map directly to characters in a SQL statement. Surely you want to take into account character encoding within the client, some packet metadata, etc.)
- 这种“数据包”限制可能不会直接映射到 SQL 语句中的字符。当然，您要考虑客户端内的字符编码、一些数据包元数据等）
回答by Ga1der
SELECT @@global.max_allowed_packet
选择@@global.max_allowed_packet
this is the only real limit it's adjustable on a server so there is no real straight answer
这是在服务器上可调整的唯一真正限制，因此没有真正直接的答案
回答by Kate Bertelsen
From a practical perspective, I generally consider any SELECT that ends up taking more than 10 lines to write (putting each clause/condition on a separate line) to be too long to easily maintain. At this point, it should probably be done as a stored procedure of some sort, or I should try to find a better way to express the same concept--possibly by creating an intermediate table to capture some relationship I seem to be frequently querying.
从实际的角度来看，我通常认为任何最终需要编写 10 多行（将每个子句/条件放在单独的行上）的 SELECT 都太长而不易维护。在这一点上，它可能应该作为某种存储过程来完成，或者我应该尝试找到一种更好的方式来表达相同的概念——可能通过创建一个中间表来捕获我似乎经常查询的一些关系。
Your mileage may vary, and there are some exceptionally long queries that have a good reason to be. But my rule of thumb is 10 lines.
您的里程可能会有所不同，并且有一些非常长的查询是有充分理由的。但我的经验法则是 10 行。
Example(mildly improper SQL):
示例（轻微不当的 SQL）：
SELECT x, y, z
FROM a, b
WHERE fiz = 1
AND foo = 2
AND a.x = b.y
AND b.z IN (SELECT q, r, s, t
FROM c, d, e
WHERE c.q = d.r
AND d.s = e.t
AND c.gar IS NOT NULL)
ORDER BY b.gonk
This is probably too large; optimizing, however, would depend largely on context.
这可能太大了；然而，优化在很大程度上取决于上下文。
Just remember, the longer and more complex the query, the harder it's going to be to maintain.
请记住，查询越长越复杂，维护起来就越困难。
回答by Oskar
Most databases support stored procedures to avoid this issue. If your code is fast enough to execute and easy to read, you don't want to have to change it in order to get the compile time down.
大多数数据库都支持存储过程来避免这个问题。如果您的代码足够快，可以执行并且易于阅读，您就不想为了缩短编译时间而更改它。
An alternative is to use prepared statements so you get the hit only once per client connection and then pass in only the parameters for each call
另一种方法是使用准备好的语句，这样每个客户端连接只得到一次命中，然后只传递每次调用的参数
回答by Matthew Rapati
I'm assuming you mean by 'turned off' that a field doesn't have a value?
我假设您的意思是“关闭”字段没有值？
Instead of checking if something is not this, and it's also not that etc. can't you just check if the field is null? Or set the field to 'off', and check if type or whatever equals 'off'.
而不是检查某些东西是否不是这个，它也不是那个等等。你不能只检查该字段是否为空？或者将该字段设置为“关闭”，并检查类型或其他内容是否等于“关闭”。
