# ext/mysqli @ mysqlnd: "unofficial" MySQL Server patch for types results / native types

- Thread: https://externals.io/message/40560
- Original archive: https://news-web.php.net/php.internals/40560
- Started by Ulf Wendel on 2008-09-19T07:45:10+00:00
- Messages: 1
- Reply by email to internals@lists.php.net
- More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt

---

## #40560 — Ulf Wendel — 2008-09-19T07:45:10+00:00

URL: https://externals.io/message/40560#40560

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:

   1) you need it for nothing but accessing a patched server
   2) 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 function `mysqli_multi_query()`.
   3) 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