oracle SQL：CASE 限制（WHEN、THEN 条件的数量）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1160459/
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
SQL: Limit on CASE (number of WHEN, THEN conditions)
提问by Virat Kadaru
Consider the query (it runs on both Oracle and MySQL)
考虑查询（它在 Oracle 和 MySQL 上运行）
UPDATE table1
SET something_id = CASE
WHEN table1_id = 1446 THEN 423
WHEN table1_id = 2372 THEN 426
WHEN table1_id = 2402 THEN 428
WHEN table1_id = 2637 THEN 429
WHEN table1_id = 2859 THEN 430
WHEN table1_id = 3659 THEN 433
END
WHERE table1_id IN (1446,2372,2402,2637,2859,3659)
This query can get quite large, so I was wondering what is the limit on the number of conditions (WHEN, THEN statements) a single query can hold. Is there a way around it?
这个查询可能会变得非常大，所以我想知道单个查询可以容纳的条件（WHEN、THEN 语句）数量的限制是多少。有办法解决吗？
For example:
I know that the max number of values that can be passed to INis 1000 and to overcome this we can do
例如：
我知道可以传递给的值的最大数量IN是 1000 并且为了克服这个我们可以做
`WHERE TABLE1_ID IN ([1000 values]) OR TABLE1_ID IN ([more values])`
回答by Quassnoi
Put your correspondences into a helper table:
将您的信件放入辅助表中：
id value
1446 423
2372 426
…
and join.
并加入。
In Oracle:
在Oracle：
UPDATE (
SELECT something_id, value
FROM table1
JOIN helper
ON table1.table1_id = helper.id
)
SET something_id = value
(don't forget to make helper.ida PRIMARY KEYfor this to work)
（不要忘了做helper.id一个PRIMARY KEY这个工作）
In MySQL:
在MySQL：
UPDATE table1
JOIN helper
ON table1.table1 = helper.id
SET table1.something_id = value
回答by Welbog
If you have a lot of case statements like this, you should move them into a table to simplify things:
如果你有很多这样的 case 语句，你应该把它们移到一个表中以简化事情：
NewTable
table1_id | result
-----------+---------
1446 | 423
2372 | 426
... | ...
Then join on it in your query:
然后在您的查询中加入它：
UPDATE table1
SET something_id = NewTable.result
INNER JOIN NewTable
ON table1.table1_id = NewTable.table1_id
It's a lot simpler.
这要简单得多。
回答by skaffman
The docs for 10gR2say:
10gR2的文档说：
The maximum number of arguments in a CASE expression is 255. All expressions count toward this limit, including the initial expression of a simple CASE expression and the optional ELSE expression. Each WHEN ... THEN pair counts as two arguments. To avoid exceeding this limit, you can nest CASE expressions so that the return_expr itself is a CASE expression.
CASE 表达式中的最大参数数为 255。所有表达式都计入此限制，包括简单 CASE 表达式的初始表达式和可选的 ELSE 表达式。每个 WHEN ... THEN 对算作两个参数。为避免超出此限制，您可以嵌套 CASE 表达式，以便 return_expr 本身是一个 CASE 表达式。
回答by hobodave
In MySQL you are limited solely by the byte size of the query. This is controlled by the "max_allowed_packet" setting for your server. I cannot speak for Oracle.
在 MySQL 中，您仅受查询字节大小的限制。这由服务器的“max_allowed_packet”设置控制。我不能代表 Oracle。
