Types in PHP and MySQL

Since PHP 7.0 has been released there’s more attention on scalar types. Keeping types for data from within your application is relatively simple. But when talking to external systems, like a database things aren’t always as one eventually might initially expect.

For MySQL the type we see — in the first approximation — is defined by the network protocol. The MySQL network protocol by default converts all data into strings. So if we fetch an integer from the database and use PHP 7’s typing feature we get an error:

<?php
declare(strict_types=1);

function getInteger() : int {
  $mysqli = new mysqli(...);
  return $mysqli->query("SELECT 1")->fetch_row()[0];
}

var_dump(getInteger());
?>

Fatal error: Uncaught TypeError: Return value of getInteger() must be of the type integer, string returned in t.php:6

Of course the solution is easy: Either we cast ourselves or we disable the strict mode and PHP will cast for us.

Now let’s take a look at another case. Assume we have an application where we fetch an integer ID from the database. We know MySQL will send us a string and we treat the ID as opaque data anyways so we have the type check for a string. Now we refactor the code slightly and make use of prepared statements. What will the result be?

<?php
declare(strict_types=1);

function getId() : string {
  $mysqli = new mysqli(...);
  $stmt = $mysqli->prepare("SELECT 1");
  $stmt->execute();
  return $stmt->get_result()->fetch_row()[0];
}

var_dump(getId());
?>

Fatal error: Uncaught TypeError: Return value of getId() must be of the type string, integer returned in t.php:8

Wait! – What’s up there!? — Didn’t I just say that the MySQL protocol will always send a string, thus we retrieve a string in PHP!? – Yes I did and that’s true for "direct queries." It’s not true for results from prepared statements. With prepared statements the MySQL protocol uses a binary encoding of the data and therefore mysqlnd and mysqli will try to find the matching PHP type. This isn’t always possible, especially if we’re going into the range of big values. So let’s query for PHP_INT_MAX and PHP_INT_MAX+1 and look at the types:

<?php
$mysqli = new mysqli(...);
$stmt = $mysqli->prepare("SELECT 9223372036854775807, 9223372036854775808");
$stmt->execute();
var_dump($stmt->get_result()->fetch_row());
?>

array(2) {
  [0]=>
  int(9223372036854775807)
  [1]=>
  string(19) "9223372036854775808"
}

Here 9223372036854775807 is the largest value a PHP integer can represent and thus is an integer. 9223372036854775808 however is to large and can’t fit in a signed 64bit integer thus it is converted in a string, as this keeps all information and can be handled at least to some degree.

Similar things happens to other types which can’t be properly represented in PHP:

<?php
$mysqli = new mysqli(...);
$stmt = $mysqli->prepare("SELECT 1.23");
$stmt->execute();
var_dump($stmt->get_result()->fetch_row());
?>

array(2) {
  [0]=>
  string(4) "1.23"
}

Yay – yet another wtf! So what is going on this time? — Well, a literal in SQL is treated as DECIMAL. A DECIMAL field is supposed to be precise. If this were to be converted into a PHP float aka. double we probably would loose the precision, thus treating it as string again makes sure we’re not loosing information. If we had a FLOAT or DOUBLE field this could safely be represented as float in PHP:

<?php
$mysqli = new mysqli(...);
$stmt = $mysqli->prepare("SELECT RAND()");
$stmt->execute();
var_dump($stmt->get_result()->fetch_row());
?>

array(2) {
  [0]=>
  float(0.16519711461402206)
}

So to summarize:

• For a direct query the MySQL server sends strings, PHP returns all data as string
• For prepared statements MySQL sends data in binary form and PHP will use a corresponding type
• If the value could only be represented with a potential data loss in PHP it is converted to a string by PHP, even with prepared statements

Now we might expect the same when using PDO. Let’s check:

<?php
$pdo = new PDO("mysql:host=localhost", "...", "...");
$stmt = $pdo->prepare("SELECT 9223372036854775808, RAND()");
$stmt->execute();
var_dump($stmt->fetch(PDO::FETCH_NUM));
?>

array(2) {
  [0]=>
  string(1) "1"
  [1]=>
  string(18) "0.3217373297752229"
}

This example uses prepared statements, but returns strings!? The reason is that PDO by default doesn’t use prepared statements on the network layer but an emulation within PHP. This means PHP will replace potential placeholders and then runs a direct query. As mentioned above with a direct query the MySQL server will send strings, thus PHP will represent all data as string. However we can easily ask PDO to disable the emulation:

<?php
$pdo = new PDO("mysql:host=localhost", "...", "...");
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
$stmt = $pdo->prepare("SELECT 1, RAND()");
$stmt->execute();
var_dump($stmt->fetch(PDO::FETCH_NUM));
?>

array(2) {
  [0]=>
  int(1)
  [1]=>
  float(0.24252333421495)
}

This leaves the question whether you should disable the emulation in order to get the correct types. Doing this has some impact on performance characteristics: With native prepared statements there will be a client-server round-trip during the prepare and another round-trip for the execute. With emulation only during the execute. The native prepared statements also require some server resources to store the handle. However if a single statement is executed multiple times there might be some savings. Also the type representation means that different type conversions happen and a different amount of data is transfered. For most cases this shouldn’t have notable impact, but in the end only a benchmark will tell.

Hope this helps to give a better understanding, or more confusion

PlanetMySQL Voting: Vote UP / Vote DOWN
via Planet MySQL
Types in PHP and MySQL