Hi!
Based on some discussions with Facebook, Tuenti and Andrey we have
developed an "unofficial" MySQL Server patch. Furthermore we have added
a new function to ext/mysqli which only purpose is to access
functionality available only with a patched MySQL Server. Therefore we
have not committed this new function into the PHP CVS but only made
available on Launchpad [1]. The license is - as usual - PHP license. If
you want it in the PHP CVS, we can add it, but I am -1.
Let me give details: the background.
When fetching data from MySQL in PHP using any of mysql_query(),
mysqli_query() or mysqli_real_query(), you will get a result set that
consists of PHP string and PHP NULL variables. The reason for that is
that the protocol used between the client and server is text based. The
client (PHP) receives all results as strings. Regardless of the SQL type
used in MySQL, you will get a string. For example, INTEGER 1234
becomes string "1234"
If you use Prepared Statements you will get typed results, sort of
native types as I like to call them. As far as possible you will get
typed results. For example, INTEGER 1234 from MySQL becomes integer 1234
in PHP. That is because MySQL is using a different protocol using
different data encoding: the binary protocol for sending data to the client.
There is a number of reasons why you want native types / typed results
in PHP. Comparing $some_int_as_string with 1234 is slower than comparing
$some_int_as_int with 1234 - no type conversions need to be done before
the comparison can be done. $some_int_as_string requires a few bytes
more than $some_int_as_int to be stored. And, of course,
$some_int_as_string === 1234 will evaluate to false. Last but not least
its a convenience feature.
On the server side the binary protocol can save a few cast operations
and on the wire the footprint can (does not have to be!) a little smaller.
How big is the difference for the average user? Rather minimal. But
under high load you appreciate each and every possible optimization.
Putting it into a larger scale, memory savings inside PHP can translate
into memory savings in caches such as APC and memcached.
How can you get types results?
You can get typed results with any of [mysql_query(),] mysqli_query() or
mysqli_real_query() by either:
a) patching the server
b) casting upon fetch inside PHP using meta data information
NOTE: b) = PHP 5.3+, ext/mysqli using mysqlnd
Andrey has hacked a patch for the server. The server patch is by no
means official - its a suggestion for those who want to experiement. I
don't know if the patch will ever go into MySQL. The patch will make the
server use the binary protocol for mysqli_query() after setting an
option. To be able to set this new option, available only with a patched
server, we have exported the C function mysql_set_server_option() to
ext/mysqli as mysqli_set_server_option(). This is not in the PHP CVS
currently because:
- you need it for nothing but accessing a patched server
- you can mess around with MYSQL_OPTION_MULTI_STATEMENTS_ON which
has been keept away from the mysqli API so far and encapsulated in an
extra functionmysqli_multi_query(). - we try to block you from messing around with
MYSQL_OPTION_MULTI_STATEMENTS_ON
Therefore: no functionality hidden that is available in the regular
MySQL Server. The function would be API bloat in regular PHPs. So, we
keep like 10 lines of code only in our bazaar repository at Launchpad [1].
b) is in the PHP CVS. Its available with ext/mysqli when compiled
against mysqlnd. Here's a simple usage example:
$link = mysqli_connect("localhost", "root", "root");
mysqli_query($link, "USE test");
mysqli_query($link, "DROP TABLE IF EXISTS test");
mysqli_query($link, "CREATE TABLE test(col1 INT, col2 FLOAT)");
mysqli_query($link, "INSERT INTO test(col1, col2) VALUES(1, 12345.67)");
$res = mysqli_query($link, "SELECT col1, col2, col2 * 2 AS _col3 FROM
test");
var_dump(mysqli_fetch_assoc($res));
mysqli_free_result($res);
/* mysqlnd only */
mysqli_options($link, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true);
$res = mysqli_query($link, "SELECT col1, col2, col2 * 2 AS _col3 FROM
test");
var_dump(mysqli_fetch_assoc($res));
mysqli_free_result($res);
mysqli_close($link);
--->
array(3) {
["col1"]=>
string(1) "1"
["col2"]=>
string(7) "12345.7"
["_col3"]=>
string(14) "24691.33984375"
}
array(3) {
["col1"]=>
int(1)
["col2"]=>
float(12345.7)
["_col3"]=>
float(24691.33984375)
}
I'll be in meetings for the rest of the day, please be patient if I
don't reply to questions immediately. More details and thoughts on the
idea can be found at [2].
Ulf
[1] https://code.launchpad.net/~andrey-mysql/php-mysqlnd/binary_protocol
[2] http://blog.ulf-wendel.de/?p=198