How many bind variables can I use in a SQL query in MySQL 5?

The maximum number of placeholders for values in a prepared statement is the maximum value of a 16-bit unsigned integer, or specfically: 65,536.

This can be seen in the MySQL code here: sql/sql_prepare.cc:

static bool init_param_array(Prepared_statement *stmt)
{
  LEX *lex= stmt->lex;
  if ((stmt->param_count= lex->param_list.elements))
  {
    if (stmt->param_count > (uint) UINT_MAX16)
    {
      /* Error code to be defined in 5.0 */
      my_message(ER_PS_MANY_PARAM, ER(ER_PS_MANY_PARAM), MYF(0));
      return TRUE;
    }

There is limit 65,535 (2^16-1) place holders in MariaDB 5.5 which is supposed to have identical behaviour as MySQL 5.5.

Not sure if relevant, I tested it on PHP 5.5.12 using MySQLi / MySQLND.

Tags:

Mysql