SQL:大小写限制(WHEN，THEN条件的数量) [英] SQL: Limit on CASE (number of WHEN, THEN conditions)

问题描述

考虑查询(它同时在Oracle和MySQL上运行)

Consider the query (it runs on both Oracle and 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)

该查询可能会变得非常大，所以我想知道单个查询可以容纳的条件数(WHEN，THEN语句)的限制是多少?有办法解决吗?

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?

例如:
我知道可以传递给IN的值的最大数量为1000，要克服这一点，我们可以做到

For example:
I know that the max number of values that can be passed to IN is 1000 and to overcome this we can do

`WHERE TABLE1_ID IN ([1000 values]) OR TABLE1_ID IN ([more values])`

推荐答案

a中的最大参数个数 CASE表达式为255.全部 表达式计入此限制， 包括a的初始表达式 简单的CASE表达式和 可选的ELSE表达式.每个时间 ...然后将这对视为两个参数. 为避免超出此限制，您可以 嵌套CASE表达式，以便 return_expr本身是一个案例 表达.

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.

这篇关于SQL:大小写限制(WHEN，THEN条件的数量)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！