oracle 每个数据库提供程序类型允许的最大参数数是多少？

What are the max number of allowable parameters per database provider type?

提问by Bittercoder

There is a limit of 2,100 parameters which can be passed to a Sql Server query i.e. via ADO.Net, but what are the documented limits for other common databases used by .Net developers - in particular I'm interested in:

有 2,100 个参数的限制，可以通过 ADO.Net 传递给 Sql Server 查询，但是 .Net 开发人员使用的其他常用数据库的记录限制是什么 - 特别是我感兴趣：

• Oracle 10g/11g
• MySql
• PostgreSql
• Sqlite

• 甲骨文 10g/11g
• 数据库
• PostgreSQL
• Sqlite

Does anyone know?

有人知道吗？

回答by chillysapien

Oracle: 64,000. Source

甲骨文：64,000。来源

MySQL:

MySQL：

• By default, there is no limit. The MySQL "text protocol" requires that the .NET client library substitute all parameters before sending the command text to the server; there is no server-side limit that can be enforced, and the client has no limit (other than available memory).
• If using "prepared statements" by calling MySqlCommand.Prepare()(and specifying IgnorePrepare=falsein the connection string), then there is a limit of 65,535 parameters (because num_paramshas to fit in two bytes).

• 默认情况下，没有限制。MySQL“文本协议”要求.NET客户端库在向服务器发送命令文本之前替换所有参数；没有可以强制执行的服务器端限制，客户端也没有限制（可用内存除外）。
• 如果通过调用MySqlCommand.Prepare()（并IgnorePrepare=false在连接字符串中指定）使用“准备好的语句” ，则有 65,535 个参数的限制（因为num_params必须适合两个字节）。

PostgreSql: EDIT: 34464 for a query and 100 for a function as per Magnus Hagander's answer (Answer copied here to provide a single point of reference)

PostgreSql：编辑：34464 用于查询，100 用于根据 Magnus Hagander 的回答的函数（此处复制答案以提供单点参考）

SqlLite: 999 (SQLITE_MAX_VARIABLE_NUMBER, which defaults to 999, but can be lowered at runtime) - And for functions default is 100 parameters. See section 9 Of Run-time limits documentation

SqlLite：999（SQLITE_MAX_VARIABLE_NUMBER，默认为 999，但可以在运行时降低） - 对于函数，默认值为 100 个参数。请参阅运行时限制文档的第 9 节

回答by Magnus Hagander

The correct answer for PostgreSQL appears to be 34464, when talking about bound parameters to a query. The response 100 is still correct for number of parameters to a function.

当谈到查询的绑定参数时，PostgreSQL 的正确答案似乎是 34464。对于函数的参数数量，响应 100 仍然正确。

回答by Lukas Eder

In jOOQ, we've worked around these limitations by inlining bind values once we reach the relevant number per vendor. The numbers are documented here. Not all numbers are necessarily the correct ones according to vendor documentation, we've discovered them empirically by trial and error through JDBC. They are (without tying them to a specific version):

在 jOOQ 中，一旦我们达到每个供应商的相关数字，我们就通过内联绑定值来解决这些限制。此处记录了这些数字。根据供应商文档，并非所有数字都一定是正确的，我们通过 JDBC 通过反复试验凭经验发现了它们。它们是（不将它们绑定到特定版本）：

• Ingres : 1024
• Microsoft Access : 768
• Oracle : 32767
• PostgreSQL : 32767
• SQLite : 999
• SQL Server : 2100 (depending on the version)
• Sybase ASE : 2000

• 输入：1024
• 微软访问：768
• 甲骨文：32767
• PostgreSQL : 32767
• SQLite：999
• SQL Server：2100（取决于版本）
• Sybase ASE : 2000

Other databases do not seem to have any limitations - at least we've not discovered them yet (haven't been looking far beyond 100000, though).

其他数据库似乎没有任何限制 - 至少我们还没有发现它们（虽然还没有远远超过 100000）。

回答by Stas Boyarincev

The PostgreSQL wire protocol uses 16-bit integers for count of parameters in the bind message (http://www.postgresql.org/docs/9.4/static/protocol-message-formats.html).

PostgreSQL 有线协议使用 16 位整数作为绑定消息中的参数计数 ( http://www.postgresql.org/docs/9.4/static/protocol-message-formats.html)。

Thus the PostgreSQL protocol doesn't allow over 65535 parameters for a single statement. This is, OK to send a single ado.net command with two statements, each of which has 65535 parameters.

因此，PostgreSQL 协议不允许单个语句超过 65535 个参数。也就是说，可以发送带有两个语句的单个 ado.net 命令，每个语句有 65535 个参数。

回答by Steve Rukuts

In my view, the MySQL question actually has two answers. The prepared statement protocol defines a signed 2 byte short to describe the number of parameters that will be retrieved from the server. The client firstly calls COM_STMT_PREPARE, for which it receives a COM_STMT_PREPARE responseif successful.

在我看来，MySQL 问题实际上有两个答案。准备好的语句协议定义了一个带符号的 2 字节短，以描述将从服务器检索的参数数量。客户端首先调用COM_STMT_PREPARE，如果成功，它会收到COM_STMT_PREPARE 响应。

The documentation for the response states:

响应的文档指出：

If num_params > 0 more packets will follow:

Parameter Definition Block

• num_params* Protocol::ColumnDefinition

• EOF_Packet

如果 num_params > 0 更多数据包将跟随：

参数定义块

• num_params* Protocol::ColumnDefinition

• EOF_Packet

Given that num_paramscan only be a maximum of 2^16 (signed short), it would follow that this is the limit of parameters and as my company has a custom MySQL driver we chose to follow this rule when implementing it and an exception is thrown if the limit is exceeded.

鉴于num_params最多只能是 2^16（签名短），因此这是参数的限制，由于我公司有一个自定义 MySQL 驱动程序，我们在实现它时选择遵循此规则，如果超出限制。

However, COM_STMT_PREPAREdoes not actually return an error if you send more than this number of parameters. The value of num_paramsis actually just 2^16 and more parameters will follow afterwards. I'm not sure if this is a bug but the protocol documentation does not describe this behaviour.

但是，COM_STMT_PREPARE如果您发送的参数数量超过此数量，则实际上不会返回错误。的值num_params实际上只是 2^16，之后会出现更多参数。我不确定这是否是一个错误，但协议文档没有描述这种行为。

So long as you have a way on your client-side to know the number of parameters (client_num_paramsif you will), you could implement your MySQL client in such a way that it expects to see client_num_paramsx Protocol::ColumnDefinition. You could also watch for EOF_Packetbut that's only actually sent if CLIENT_DEPRECATE_EOFis not enabled.

只要您在客户端有办法知道参数的数量（client_num_params如果您愿意的话），您就可以以期望看到client_num_paramsx的方式实现您的 MySQL 客户端Protocol::ColumnDefinition。您也可以监视，EOF_Packet但只有CLIENT_DEPRECATE_EOF在未启用时才会实际发送。

It's also interesting to note that there's a reserved byte after num_params, indicating that the protocol designers probably wanted the option to make this a 24-bit number, allowing about 8.3 million parameters. This would also require an extra client capability flag.

还有趣的是，在 之后有一个保留字节num_params，表明协议设计者可能希望选择将其设为 24 位数字，允许大约 830 万个参数。这还需要额外的客户端功能标志。

To summarise:

总结一下：

• The client/server protocol documentation seems to indicate that the maximum number of parameters could be 32768
• The server doesn't seem to care if you send more but this doesn't appear to be documented and it might not be supported in future releases. I very much doubt this would happen though as this would break multiple drivers including Oracle's own ADO.NET Connector.

• 客户端/服务器协议文档似乎表明参数的最大数量可以是 32768
• 服务器似乎并不关心您是否发送更多信息，但这似乎没有记录在案，并且可能在未来版本中不受支持。我非常怀疑这会发生，因为这会破坏多个驱动程序，包括 Oracle 自己的 ADO.NET 连接器。