Mysql result data types

Am 12.10.2017 um 13:55 schrieb Reinis Rozitis:

is there a reason (technical or historical) why the data coming from MySQL is always strings?
I've found only one case where the data type is "honored" - PDO+mysqlnd+emulation off [1]

not entirely true, our database-layer acts directly with mysqli and
unconditionally enables native types for the last 8 months or so after
we mirgated our whole code to PHP7 native types

no idea what the state of PDO is

http://blog.ulf-wendel.de/2008/php-new-network-traffic-cpu-and-memory-savings-with-mysqlnd/

if(mysqli_options($this->conn, MYSQLI_OPT_INT_AND_FLOAT_NATIVE, true)
=== false)
{
$this->error('mysqli_options(MYSQLI_OPT_INT_AND_FLOAT_NATIVE) failed');
}

Hello,
is there a reason (technical or historical) why the data coming from
MySQL is always strings?
I've found only one case where the data type is "honored" -
PDO+mysqlnd+emulation off [1]

The reason for this is that this is the way the protocol works. With
non-prepared statements the server sends the data in textual form. With
prepared statements it switches to a different protocol sending values
in binary form.
There was a proposal in the server once that would use binary also for
non-prepared statements, but was rejected, for reasons i don't recall.
(maybe backwards compatibility)
MySQL's new X protocol uses binary representation by defalt, but is no
drop in replacment, but new protocol, with new clients etc.

We made a fairly simple patch to 'mysqlnd' which enables
(configurable via ini) data to be returned (trying to match) as
defined in database/table. 

In general something like:

switch( field->type ){
case MYSQL_TYPE_TINY:
case MYSQL_TYPE_SHORT:
case MYSQL_TYPE_LONG:
case MYSQL_TYPE_LONGLONG:
case MYSQL_TYPE_INT24:
convert_to_long(data);
break;
case MYSQL_TYPE_DECIMAL:
case MYSQL_TYPE_DOUBLE:
case MYSQL_TYPE_FLOAT:
case MYSQL_TYPE_NEWDECIMAL:
convert_to_double(data);
break;
}

It's not that trivial: With such a conversion the values might
overflow. What happens if the server returns an huge unsigned value
which PHP's signed integers can't represent? Does the type then change
based  on the specific value?
You're also converting a precise DECIMAL in an imprecise double which
will lose precision.
Also consider that many applications don't do extra calculations on
such returned data, but carry it through and then return as a string
(be it HTML, JSON; XML or whatever) thus have two "useless" conversions
(while carrying a tiny bit more memory around ...)
Traditional in PHP the string is no problem, as the engine will convert
when needed (and only then ...), I don't know how scalar typing in the
large view changed this ..but even in sch a code base here'd be a
conversion only for those who want it ...

Does it make sense to create a PR and/or RFC for something like this?

If the community wants this, this could be done, I'm a bit skeptical
and I'd suggest to benchmark some systems passing lots of data around.

johannes