Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.1Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


23.8.11.4 mysql_stmt_bind_param()

my_bool mysql_stmt_bind_param(MYSQL_STMT *stmt, MYSQL_BIND *bind)

説明

mysql_stmt_bind_param() は、mysql_stmt_prepare() に渡された SQL ステートメント内のパラメータマーカーに入力データをバインドするために使用します。それは MYSQL_BIND 構造を使用して、データを提供します。bindMYSQL_BIND 構造の配列のアドレスです。クライアントライブラリは、配列に、クエリーに存在する ? パラメータマーカーごとに 1 つの要素が含まれることを期待します。

次のステートメントを準備するとします。

INSERT INTO mytbl VALUES(?,?,?)

パラメータをバインドする場合、MYSQL_BIND 構造の配列は、3 つの要素が含まれている必要があり、このように宣言できます。

MYSQL_BIND bind[3];

セクション23.8.9「C API プリペアドステートメントデータ構造」に、各 MYSQL_BIND 要素のメンバーおよび入力値を提供するためにそれらを設定する方法について説明しています。

戻り値

成功の場合はゼロ。エラーが発生した場合、ゼロ以外。

エラー
  • CR_UNSUPPORTED_PARAM_TYPE

    変換はサポートされていません。buffer_type 値は無効か、サポートされるいずれの型でもない可能性があります。

  • CR_OUT_OF_MEMORY

    メモリー不足。

  • CR_UNKNOWN_ERROR

    不明なエラーが発生しました。

セクション23.8.11.10「mysql_stmt_execute()」の例を参照してください。


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:
http://bugs.mysql.com/bug.php?id=8550
"... 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:
Error: 2036 (CR_UNSUPPORTED_PARAM_TYPE)
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.