Documentation Home
MySQL 5.5 Reference Manual
Related Documentation Download this Manual Excerpts from this Manual mysql_stmt_bind_param()

my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)


mysql_stmt_bind_param() is used to bind input data for the parameter markers in the SQL statement that was passed to mysql_stmt_prepare(). It uses MYSQL_BIND structures to supply the data. bind is the address of an array of MYSQL_BIND structures. The client library expects the array to contain one element for each ? parameter marker that is present in the query.

Suppose that you prepare the following statement:


When you bind the parameters, the array of MYSQL_BIND structures must contain three elements, and can be declared like this:

MYSQL_BIND bind[3];

Section 23.8.9, “C API Prepared Statement Data Structures”, describes the members of each MYSQL_BIND element and how they should be set to provide input values.

Return Values

Zero for success. Nonzero if an error occurred.


See the Example in Section, “mysql_stmt_execute()”.

Download this Manual
User Comments
  Posted by Sela Lerer on March 4, 2007
The MYSQL_BIND array is only pointed to by the MYSQL_STMT so it must not be changed or freed until the MYSQL_STMT is either not used any more or rebound to a new MYSQL_BIND array.
  Posted by michael splendid on August 18, 2008
The length of the BIND structure in v4.1.xx is 55 bytes.
The boundary must be aligned as per:
"... buffers that will store integer types need to be properly aligned, and unless you allocate every single buffer with an own "malloc" you risk getting an alignment error (sigbus)."
I found that adding a single byte variable to the declaration at the end like:
my_bool pad; /* Align boundary */
bringing the total bytes in the structure up to 56, cures the error I was getting:
Using unsupported buffer type: 0 (parameter: 2)
Because the variable buffer_type was not being read from the second element of the array in the expected location.

In Ver 5.1, this is not needed.
Mike Trader

  Posted by Angus M on April 1, 2009
For the most part, prepared statements are used to call repeated INSERTs or UPDATEs. Seeing as your going to be marshaling a lot of data through these MYSQL_BIND structures, you have to ask yourself one question: should I bind to a new MYSQL_BIND array on each execution, or just bind once and do a lot of memcpy()s into the MYSQL_BIND::buffer member? So the question is really about how fast mysql_stmt_bind_*() calls are.

And since the overwhelming majority of time spent writing to a database is tied up in socket communications, the bottom-line is does mysql_stmt_bind_*() communicate with the server? I asked that question, and could not get a theoretical answer, so I went for an empirical one. The short answer is: "I guess not". And this is how I arrived at it.

I made a bogus project on a Linux machine that connects to a Linux MySQL 5.0.66 server across a TCP/IP LAN. Essentially what I tried to do was rotate sets of data through a MYSQL_BIND, and then repeatedly call only mysql_stmt_bind_param()--no mysql_stmt_execute(). Here's the loop:
bind.length = &length;
for (uint64_t i = 0; i < n; i++) {
const bindtype &__bindtype = _bindtype[i%6];
bind.buffer_type = __bindtype.m_type;
bind.buffer = __bindtype.m_buf;
bind.buffer_length = __bindtype.m_nLength;
length = __bindtype.m_nLength;
mysql_stmt_bind_param(pStmt, &bind);

By choosing a really big n, this loop took 52 seconds to run. When I ran a loop that had all of the above in it, except the mysql_stmt_bind_param(), it took 25 seconds to run. Futhermore, I monitored the network interface, through which ran the connection to the server, and counted 9,501 bytes were transmitted while this benchmark ran. Since n was 10 billion, that means that the most mysql_stmt_bind_param() could have transmitted to the server was 9.501μb per call.

Unfortunately, this doesn't prove that mysql_stmt_bind_param() doesn't access the server, as no benchmark could. However, it does seem safe to indulge in rebinding parameters without fear of creating a bottleneck.

  Posted by Evan Jones on September 17, 2009
The first comment does not appear to be correct. The comment I am referring to states:

The MYSQL_BIND array is only pointed to by the MYSQL_STMT so it must not be changed or freed until the MYSQL_STMT is either not used any more or rebound to a new MYSQL_BIND array.

This is not true, at least on my Ubuntu system using MySQL 5.0 and 5.1 client libraries. I've created a test program which allocates the MYSQL_BIND array using malloc, then frees it immediately after calling mysql_stmt_bind_param. Running this test with valgrind shows no errors, and it executes correctly. Thus, it is safe to either use a local MYSQL_BIND array, or free it immediately after binding.

Sign Up Login You must be logged in to post a comment.